Manual Database Schema Migration in TurboGears 2

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.

Prerequisites

This document assumes that you have an existing TurboGears 2.1.5 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.

Getting Started

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:

  • A repository on the file system of schema revisions
  • A database table for maintaining migration state in the managed database.

Create a Repository

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

Place the Database under Version Control

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.

Integrating sqlalchemy-migrate 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.

Create Your First Change Script

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

Edit the Script

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

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:

$ 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:

  • 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.

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;

Deploy the Script

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

Additional Information and Help

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.