Database Schema Migrations

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.

Getting Started

TurboGears provides a gearbox migrate command to manage schema migration. Run these commands from an installed quickstarted project; during development, installing the project in editable mode is enough:

$ python -m pip install -e '.[testing]'

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 non-transactional DDL.

By default the database version is unset 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
<generated revision>

This is exactly like running the gearbox migrate db_version command; both should tell you the same database version.

Integrating Migrations in the Development Process

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.

Creating migrations

The gearbox migrate create 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_initial_schema.py
empty.txt

Edit the Script

Each change script provides an upgrade and downgrade method, and we implement those methods by creating and dropping the account table respectively. Keep the revision and down_revision values that Alembic generated for your project; do not copy the example identifiers below into your own script:

revision = '2a3f515bad0'  # example; keep your generated revision
down_revision = None      # example; keep your generated down_revision

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')

Test the Script

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 non-transactional DDL.
Running upgrade <base> -> <generated revision>
Context impl SQLiteImpl.
Will assume non-transactional DDL.
Running downgrade <generated revision> -> <base>

If you receive an error while testing your script, one of two issues is probably the cause:

• There is a bug in the script

• You are testing a script that conflicts with the schema as it currently exists.

If there is a bug in your change script, you can fix the bug and rerun the test.

Applying migrations

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 non-transactional DDL.
Running upgrade <base> -> <generated revision>

Keeping your websetup on sync

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. Current quickstarted projects already include equivalent stamping code in websetup/schema.py. If you maintain an older or custom project that lacks it, add commands like these at the end of websetup/schema.py to set the schema version to the latest revision:

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")

Downgrading your schema

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 non-transactional DDL.
Running downgrade <generated revision> -> <base>