Simple SQLObject DB Migration how-to

I’ve been using sqlobject for plnnr.com for quite some time now. So far my experience with it has been positive. Although I’ll probably change ORM when I move to django, for now it stays. While it stays, I need to be able to upgrade my schema to add features.
SQLObject already has a tool for the job, sqlobject-admin. There are instructions on how to use it, but I found them unsatisfactory.
(By the way, both django’s ORM and sqlalchemy also have tools for that, django-south and sqlalchemy-migrate respectively.)

So here is how I use sqlobject-admin to do migrations. Note that if you’re using turbogears 1.0, you would probably be using tg-admin. In that case, bear in mind that tg-admin just simplifies the job for you by adding various standard parameters, but apart from that, the idea stays the same.
Notes:
* I wrote these instructions on a windows machine. On linux machines it should be almost the same, but might require tweaking.
* I used a specific db URI in the examples. You can change it to whatever you want.
* I once had to tweak the main sqlobject-admin file to add the current dir to sys.path. YMMV.

1. Example project:
Let’s setup a project that uses sqlobject. We’ll create a single file, ‘main.py’ with the following content:

import sqlobject
 
sqlobject.sqlhub.processConnection = sqlobject.connectionForURI('sqlite:/D|/work/sotest/sotest.sqlite')
 
class MyThing(sqlobject.SQLObject):
    bla = sqlobject.StringCol()

This is about as simple as I could get it with sqlobject.

2. Starting to use sqlobject-admin
Sqlobject-admin has quite a bit of bureaucracy to go through before you get everything to work right. For a simple project, I cheat (i.e. fake an egg :), and do the following:
a. Create a directory in your project called sqlobject-history
b. If your project name is sotest, create a directory inside your project called sotest.egg-info
c. Inside that dir create a file called sqlobject.txt
d. Inside that file write:

db_module=main
history_dir=$base/sqlobject-history

(note that the main here is the name of the module we created earlier).

3. Start using sqlobject-admin
This will be the workflow with sqlobject-admin:
1. Have the creation sql for the current code version.
2. Update your code
3. Generate the creation sql for the new code version, *without updating the db*
4. Create an upgrade script using the diff between the versions
5. Use the upgrade script.

More specifically:
1. First time – do:

sqlobject-admin record –egg=sotest -c sqlite:/D|/work/sotest/sotest.sqlite

2. To see that everything works, do:

sqlobject-admin list –egg=sotest -c sqlite:/D|/work/sotest/sotest.sqlite

and:

sqlobject-admin status –egg=sotest -c sqlite:/D|/work/sotest/sotest.sqlite

3. Update your database definition (in the Python file). For example, change the contents of main.py to:

import sqlobject
 
sqlobject.sqlhub.processConnection = sqlobject.connectionForURI('sqlite:/D|/work/sotest/sotest.sqlite')
 
class MyThing(sqlobject.SQLObject):
    bla = sqlobject.StringCol()
    bla2 = sqlobject.StringCol()

4. Here is the critical part. Do

sqlobject-admin record –egg=sotest -c sqlite:/D|/work/sotest/sotest.sqlite –no-db-record

In the sqlobject-history directory there should be now two subdirectories, for each version. Let’s call the old version X and the new version Y. In the old version directory create a file:
upgrade_sqlite_Y.sql (where Y is the new version’s name).
In this file, write down the sql to add the bla2 column to the MyThing table. You can use the creation sql commands in the respective versions’ directories to write it.

(note: if we used –edit we would get an editor opened, and if the edited file has any content when you close it, it will be saved as the upgrade script. I don’t like using this method. Note that if you’re on windows you’ll have to fix sqlobject-admin to open your editor, as the command it uses works only on linux machines.)

5. run

sqlobject-admin upgrade –egg=sotest -c sqlite:/D|/work/sotest/sotest.sqlite

6. Make sure everything is OK with sqlobject-admin status.

3. After using the upgrade script
You can use the same upgrade script for other instances of your project. Just make sure that you have the versions numbers correct, and the first version recorded in the database.

I hope this will be useful for someone using sqlobject, I know I needed this kind of how-to. If you have any questions, feel free to ask them in comments below.