Since version 2.1.1 TurboGears has integrated migrations support for each new quickstarted project.
TurboGears 2.3 and newer rely on the alembic project to automate database schema migration.
TurboGears provides a
gearbox migrate command to manage schema migration.
You can run
gearbox migrate db_version to see the current version
of your schema:
$ gearbox migrate -c development.ini db_version Context impl SQLiteImpl. Will assume transactional DDL. Current revision for sqlite:////tmp/migr/devdata.db: None
By default the database version is
None until a migration is applied.
The first time a migration is applied the
migrate_version table is
created. This table will keep the current version
of your schema to track when applying migrations is required.
If you examine your database, you should be able to see schema version tracking table and check what it is the current version of your schema:
sqlite> .headers on sqlite> select * from migrate_version; version_num 4681af2393c8
This is exactly like running the
gearbox migrate db_version command, both
should tell you the same database version.
In this case the reported version is 4681af2393c8.
With the database under version control and a repository for schema change scripts, you are ready to begin regular development. We will now walk through the process of creating, testing, and applying a change script for your current database schema. Repeat these steps as your data model evolves to keep your databases in sync with your model.
gearbox migrate script command will create an empty change script for you,
automatically naming it and placing it in your repository:
$ gearbox migrate create 'Initial Schema'
The command will return by just printing the migrations repository where it is going to create the new script:
$ gearbox migrate create 'Initial Schema' Generating /tmp/migr/migration/versions/2a3f515bad0_initial_schema.py... done $ ls migration/versions 2a3f515bad0_this_is_an_example.py
Each change script provides an
downgrade method, and
we implement those methods by creating and dropping the
revision = '2a3f515bad0' down_revision = '4681af2393c8' from alembic import op import sqlalchemy as sa def upgrade(): op.create_table( 'account', sa.Column('id', sa.Integer, primary_key=True), sa.Column('name', sa.String(50), nullable=False), sa.Column('description', sa.Unicode(200)), ) def downgrade(): op.drop_table('account')
Anyone who has experienced a failed schema upgrade on a production database knows how uniquely uncomfortable that situation can be. Although testing a new change script is optional, it is clearly a good idea. After you execute the following test command, you will ideally be successful:
$ gearbox migrate test Context impl SQLiteImpl. Will assume transactional DDL. Running upgrade 4681af2393c8 -> 2a3f515bad0 Context impl SQLiteImpl. Will assume transactional DDL. Running downgrade 2a3f515bad0 -> 4681af2393c8
If you receive an error while testing your script, one of two issues is probably the cause:
If there is a bug in your change script, you can fix the bug and rerun the test.
The script is now ready to be deployed:
$ gearbox migrate upgrade
If your database is already at the most recent revision, the command will produce no output. If migrations are applied, you will see output similar to the following:
Context impl SQLiteImpl. Will assume transactional DDL. Running upgrade 4681af2393c8 -> 2a3f515bad0
Each time you create a new migration you should consider keeping your
websetup in sync with it. For example if you create a new table inside
a migration when you will run
gearbox setup-app on a new database
it will already have the new table as you probably declared it in your
model too but the migrations version will be
None. So trying to run any
migration will probably crash due to the existing tables.
To prevent this your
websetup script should always initialize the
database in the same state where it would be after applying all the
available migrations. To ensure this you will have to add at the end
websetup/schema.py script a pool of commands to set the
schema version to the last one:
import alembic.config, alembic.command alembic_cfg = alembic.config.Config() alembic_cfg.set_main_option("script_location", "migration") alembic_cfg.set_main_option("sqlalchemy.url", config['sqlalchemy.url']) alembic.command.stamp(alembic_cfg, "head")
There are some cases in which downgrading your schema might be required.
In those cases you can perform the
gearbox migrate downgrade command:
$ gearbox migrate downgrade Context impl SQLiteImpl. Will assume transactional DDL. Running downgrade 2a3f515bad0 -> 4681af2393c8