Create a Database Migration Script for Kotti

Usage of Alembic starts with the creation of the Migration Environment. This is a directory of scripts that is specific to a particular application. The migration environment is created just once and is then maintained along with the application’s source code itself. The environment is created using the init command of Alembic, and is then customizable to suit the specific needs of the application.

The structure of this environment, including some generated migration scripts, looks like:

kotti_addon/
    alembic/
        env.py
        README
        script.py.mako
        versions/
            3512b954651e_add_account.py
            2b1ae634e5cd_add_order_id.py
            3adcc9a56557_rename_username_field.py

With the environment in place we can create a new revision, using alembic revision:

alembic -c alembic.ini revision -m "create account table"
Generating /path/to/kotti_addon/alembic/versions/1975ea83b712_create_account_table.py...done

A new file 1975ea83b712_create_account_table.py is generated. Looking inside the file:

"""create account table

Revision ID: 1975ea83b712
Revises:
Create Date: 2011-11-08 11:40:27.089406

"""

# revision identifiers, used by Alembic.
revision = '1975ea83b712'
down_revision = None
branch_labels = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    pass

def downgrade():
    pass

Every time kotti-migrate runs an Alembic operation against the versions/ directory, it reads all the files and composes a list based on how the down_revision identifiers link together, with the down_revision of None representing the first file. In theory, if a migration environment had thousands of migrations, this could begin to add some latency to startup, but in practice a project should probably prune old migrations anyway (see the section Building an Up to Date Database from Scratch for a description on how to do this, while maintaining the ability to build the current database fully).

We can then add some directives to our script, suppose adding a new table account:

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

Running our Migration Script

create_table() and drop_table() are Alembic directives. Alembic provides all the basic database migration operations. An overview of all Alembic directives is at Operation Reference.

We now want to run our migration. Assuming our database is totally clean, it’s as yet unversioned. The kotti-migrate command will run upgrade operations, proceeding from the current database revision.

We can also use the alembic upgrade command to run the upgrade operations

path/to/your/kotti/bin/kotti-migrate alembic.ini upgrade --scripts path/to/your/kotti_addon/alembic

OUTPUT:
INFO  [sqlalchemy.engine.base.Engine][MainThread] select version()
INFO  [sqlalchemy.engine.base.Engine][MainThread] {}
INFO  [sqlalchemy.engine.base.Engine][MainThread] select current_schema()
INFO  [sqlalchemy.engine.base.Engine][MainThread] {}
INFO  [sqlalchemy.engine.base.Engine][MainThread] SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO  [sqlalchemy.engine.base.Engine][MainThread] {}
INFO  [sqlalchemy.engine.base.Engine][MainThread] SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO  [sqlalchemy.engine.base.Engine][MainThread] {}
INFO  [sqlalchemy.engine.base.Engine][MainThread] show standard_conforming_strings
INFO  [sqlalchemy.engine.base.Engine][MainThread] {}
INFO  [alembic.runtime.migration][MainThread] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration][MainThread] Will assume transactional DDL.
INFO  [sqlalchemy.engine.base.Engine][MainThread] BEGIN (implicit)
INFO  [sqlalchemy.engine.base.Engine][MainThread] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO  [sqlalchemy.engine.base.Engine][MainThread] {'name': u'kotti_addon/alembic/_alembic_version'}
INFO  [sqlalchemy.engine.base.Engine][MainThread] SELECT "kotti_addon/alembic/_alembic_version".version_num 
FROM "kotti_addon/alembic/_alembic_version"
INFO  [sqlalchemy.engine.base.Engine][MainThread] {}
  - upgrading from ae1027a6acf to 1975ea83b712...
INFO  [alembic.runtime.migration][MainThread] Running upgrade ae1027a6acf -> 1975ea83b712, Create stored procedure to get layers
INFO  [sqlalchemy.engine.base.Engine][MainThread] UPDATE "kotti_addon/alembic/_alembic_version" SET version_num='1975ea83b712' WHERE "kotti_addon/alembic/_alembic_version".version_num = 'ae1027a6acf'
INFO  [sqlalchemy.engine.base.Engine][MainThread] {}
INFO  [sqlalchemy.engine.base.Engine][MainThread] COMMIT

We’ve now created the accounts table in the database. We could have used alembic -c alembic.ini upgrade head to upgrade our database as well, but we choose to do the Kotti way.

Go Back

Create a Database Migration Script for Kotti

Running our Migration Script

Menu