An automated method of applying database schema migrations helps to create a robust and reliable upgrade path for an application as it changes over time. TurboGears 2 comes with a tool to incrementally test and automatically deploy schema changes as needed.
TurboGears 2 relies on the sqlalchemy-migrate project to automate database schema migration.
This document assumes that you have an existing TurboGears 2.1 project that uses the built-in support for SQLAlchemy. If you are not yet at that stage, you may want to review the following:
Additionally, it is assumed that you have reached a point in the development life cycle where a change must be made to your current data model. This could mean adding a column to an existing table, adding a table, removing a table, or any number of other database schema changes.
The examples in this document will be based on the The TurboGears 2 Wiki Tutorial, but the information applies to any TurboGears 2 project.
The sqlalchemy-migrate library provides a migrate script that should
be in your path. The migrate script wraps several
sqlalchemy-migrate commands much like the paster script wraps
commands. You can verify that the migrate script is in your path and
retrieve a list of available commands by running the following:
$ migrate --help
Two additions to your TurboGears 2 project are required for sqlalchemy-migrate to manage the database schema:
To create a repository of schema revisions we issue the following command in the root of the project:
$ migrate create migration "Wiki20 Migrations"
The first argument to the create command, migration, is the
directory that will contain the repository of schema revisions. The second
argument to the create command, ‘Wiki20 Migrations’, is the name of
the newly created migration repository. The command should return
without generating any output, and a new directory, migration, should
now exist in the project root with the following content:
__init__.py
__init__.pyc
manage.py
migrate.cfg
README
versions
Our repository is ready. Now we must create a table
for maintaining revision state in our managed database. The migrate
script provides for this step as well:
$ migrate version_control sqlite:///devdata.db migration
The two arguments to the version_control command are a valid
SQLAlchemy database URL and the path to your sqlalchemy-migrate
revision repository. You will need to run the version_control
command against each database instance for your application. If you
have a development, test, and production database, all three databases
will need to be placed under version_control.
If you examine your database, you will now find a new table named
migrate_version. It will contain one row:
sqlite> .headers on
sqlite> select * from migrate_version;
repository_id|repository_path|version
Wiki20 Migrations|migration|0
Note that the repository_id column should uniquely identify your
project’s set of migrations. Should you happen to deploy multiple
projects in one database, each sqlalchemy-migrate repository will
insert and maintain a row in the migrate_version table.
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.
The migrate script will create an empty change script for you,
automatically naming it and placing it in your repository:
$ migrate script --repository=migration initial_schema
The command will return without producing any output, but the new script will be in your repository:
$ ls migration/versions
001_initial_schema.py __init__.py __init__.pyc
Each change script provides an upgrade and downgrade method, and
we implement those methods by creating and dropping the pages_table
respectively:
from sqlalchemy import *
from migrate import *
metadata = MetaData(migrate_engine)
pages_table = Table("pages", metadata,
Column("id", Integer, primary_key=True),
Column("pagename", Text, unique=True),
Column("data", Text)
)
def upgrade():
# Upgrade operations go here. Don't create your own engine; use the engine
# named 'migrate_engine' imported from migrate.
pages_table.create()
def downgrade():
# Operations to reverse the above upgrade go here.
pages_table.drop()
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:
$ migrate test migration sqlite:///devdata.db
Upgrading... done
Downgrading... done
Success
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.
If you are working through this document with an existing application,
your database probably already contains the initial schema for your
project. In this case, you cannot test the change script against your
existing database because it will try to create tables that already
exist. To test the script while preserving your existing data, you
will need to create a second database, place it under version_control,
and test the script against the new database. Since your original database
already contains the schema defined in your change script, you will need
to update the migrate_version table manually to reflect this situation:
sqlite> update migrate_version set version=1;
The script is now ready to be deployed:
migrate upgrade sqlite:///devdata.db migration
One quirk to note: the arguments to upgrade are in the opposite
order compared to the test command. 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:
0 -> 1... done
Many of the sqlalchemy-migrate developers are on the SQLAlchemy mailing list. Problems integrating sqlalchemy-migrate into a TurboGears project should be sent to the TurboGears mailing list.