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