sqlalchemygrate 0.3

convert

When migrating, you can provide a conversion function to funnel data through. Here’s what one could look like:

# migration/v1.py:

def convert(table, row):
    """
    :param table: SQLAlchemy table schema object.
    :param row: Current row from the given table (immutable, must make a copy to change).

    Returns a dict with column:value mappings.
    """
    if table.name == 'user':
        row = dict(row)
        row['email'] = row['email'].lower()
    elif table.name == 'job':
        row = dict(row)
        del row['useless_column']

    return row

Then we would use this function with --convert=migration.v1:convert. There’s pretty obvious performance detriment from using this feature, namely having to run each row through a function with its own logic, but with small datasets it’s not too bad and too convenient to ignore.

upgrade

When performing an upgrade command, you can do in-place changes without a full re-insert. This is a more realistic alternative to larger datasets or small schema changes.

# migration/001_add_fancy_column.py:

from sqlalchemy import *
from migrate import * # sqlalchemy-migrate lets us do dialect-agnostic schema changes

# sqlalchemygrate also provides some helpers just in case
from grate.migrations import table_migrate

def upgrade(metadata):
    """
    :param metadata: SQLAlchemy MetaData bound to an engine and autoreflected.
    """
    fancy_table = metadata.tables['fancy_table']

    # Create column using sqlalchemy-migrate
    col = Column('fancy_column', types.Integer)
    col.create(fancy_table)

    ## Or run some arbitrary SQL
    # metadata.bind.execute(...)

    ## Need to do a row-by-row re-insert? Use the table_migrate helper
    ## We do a migration from one engine to the same engine, but between two different tables this time.
    # table_migrate(metadata.bind, metadata.bind, table, renamed_table, convert_fn=None, limit=100000)

def downgrade(metadata):
    fancy_table = metadata.tables['fancy_table']
    fancy_table.c.fancy_column.drop()

This feature becomes even more powerful if you combine it with sqlalchemy-migrate. This way you can use dialect-agnostic SQLAlchemy DDLs to generate your schema changes, but without having to depend on sqlalchemy-migrate’s revision tracking and other needless complexities which drove me to write this.

And now we can upgrade and downgrade our schema, for example:

grate upgrade "sqlite:///development.db" migration.001_change_fancy_column:upgrade --show-sql
grate upgrade "sqlite:///development.db" migration.001_change_fancy_column:downgrade --shoq-sql

Maybe this should be called something other than upgrade? Perhaps grade? Anyways…

Row-by-row re-insert (migrate)

Thousands of rows takes seconds, millions of rows takes minutes. The details are dependent on the schema, server, and specific numbers.

In-place schema changes (upgrade)

If you’re not doing a full re-insert, this is about as efficient as you can get with any other schema migration tool. Typically on the order of seconds.