The tg-admin script that is bundled with turbogears is really helpful, but I had a hard time learning how to use it.
Before you read any more, you should know that this only works when you use SQLObject, not SQLAlchemy, for your ORM.
These are my notes on how I use tg-admin to upgrade an existing database.
- I have a production database that uses prod.cfg;
- I have a development database that uses dev.cfg;
- Neither databases have a sqlobject_db_version table initially, because I never payed attention to it yet.
The development database has a bunch of new columns, tables, and indexes that I want to add to the production database. For this example, I’ll pretend that all I want to do is add an index to a table.
First, I made sure that the dev database matches sqlobject classes:
tg-admin -c dev.cfg sql status
If those are out of sync, then do whatever you need to do to make sure your actual dev database matches your classes. Of course, tg-admin sql status is not perfect. For example, it overlooks missing indexes and constraints, at least with postgres.
Next, I recorded the state of the development database:
tg-admin -c dev.cfg sql record --force-db-version=2008-03-21
This will make a new table in the dev database called sqlobject_db_version. I am forcing it to have a value of today’s date (March 21st, 2008).
Now I connect to the production database and set a version on it with yesterday’s date:
tg-admin -c prod.cfg sql record --force-db-version=2008-03-20
Now I run this to try to upgrade the production database to match the development database:
tg-admin -c prod.cfg sql upgrade
Of course, that should fail, and I see some error message sort of like this:
$ tg-admin -c prod.cfg sql upgrade
Using database URI postgres://staffknex:staffknex@localhost/staffknex320
No way to upgrade from 2008-03-20 to 2008-03-21
(you need a 2008-03-20/upgrade_postgres_2008-03-21.sql script)
This is an example of a helpful error message. I need to write a script that will explain how to upgrade from yesterday’s version to today’s version.
That script will be really simple:
BEGIN;
CREATE UNIQUE INDEX majestic12 ON ufo_theorists (first_name, last_name);
END;
I suggest using BEGIN and END so that in case something goes wrong in the middle, your transaction will be rolled back automatically.
Now I can run this:
tg-admin -c prod.cfg sql upgrade
And my production database will be upgraded with the new index.
Now for some complaints:
- Why isn’t this advertised better? This is a really nice feature.
- You’re supposed to be able to specify the URI on the command-line with the –connection option, but I could never get it to work.
- I really wish that tg-admin sql status detected stuff like missing indexes and constraints. I use these things heavily.
- It would be nice to be able to mix python into the upgrade script, rather than just SQL. For example, I recently dropped a column that had both an employee’s first and last name, and separated this into two columns. I used SQL to make the new columns, then I used python to read data out of the old single column and write it to the two new columns. Then I used SQL again to drop the old column.
Like I said at the beginning, this is a really helpful script and I’m very grateful to whoever wrote it.