How to write alter column name migrations with sqlalchemy-migrate?

Question

I'm trying to alter a column name. First attempt was with this script:

meta = MetaData()

users = Table('users', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(50), unique=True),
    Column('email', String(120), unique=True)
    )

def upgrade(migrate_engine):
    meta.bind = migrate_engine
    users.c.id.alter(name='id')

def downgrade(migrate_engine):
    meta.bind = migrate_engine
    users.c.id.alter(name='user_id')

Running migrate.py test on my dev database (sqlite) works and so does upgrading and downgrading. But when deploying it to my test environment on Heroku (where PostgreSQL 8.3 is used) I get a trace when I try to upgrade. Gist is this message:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "id" does not exist 

I then tried to use users.c.user_idin the upgrade method. That fails in both environments.:

AttributeError: user_id

The workaround I'm using now is this script:

meta_old = MetaData()
meta_new = MetaData()

users_old = Table('users', meta_old,
    Column('user_id', Integer, primary_key=True),
    Column('name', String(50), unique=True),
    Column('email', String(120), unique=True)
    )

users_new = Table('users', meta_new,
    Column('id', Integer, primary_key=True),
    Column('name', String(50), unique=True),
    Column('email', String(120), unique=True)
    )

def upgrade(migrate_engine):
    meta_old.bind = migrate_engine
    users_old.c.user_id.alter(name='id')

def downgrade(migrate_engine):
    meta_new.bind = migrate_engine
    users_new.c.id.alter(name='user_id')

It's already recommended practice to copy-paste the model to the sqlalchemy-migrate scripts. But this extra duplications gets a bit too much for me. Anyone knows how this should be done. Assuming it's a bug, I'd like suggestions on how to DRY up the workaround some.

Solution 1

Turns out there's an even DRY:er solution to this than I had hoped for. Introspection! Like so:

def upgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    users = Table('users', meta, autoload=True)
    users.c.user_id.alter(name='id')

def downgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    users = Table('users', meta, autoload=True)
    users.c.id.alter(name='user_id')

Works like a charm!

Solution 2

This one also works:

from alembic import op
....
def upgrade(migrate_engine):
    op.alter_column('users', 'user_id', new_column_name='id')

def downgrade(migrate_engine):
    op.alter_column('users', 'id', new_column_name='user_id')

Solution 3

I bet that it can't generate any SQL because your metadata references are getting mixed up. You seem to be using two different metadata objects in your Table classes, and that's really not good. You only need one. The metadata tracks stale-ness of objects, whether it needs to issue queries for object updates, foreign key constraints, etc. and it needs to know about all your tables and relationships.

Change to use a single MetaData object, and pass echo=True to your sqlalchemy.create_engine call and it will print the SQL query that it's using to standard output. Try executing that query yourself while logged in as the same role (user) to Postgres. You may find that it's a simple permissions issue.

Regarding copy-pasting: I think Django has a good convention of placing Table and declarative classes in their own module and importing them. However, because you have to pass a MetaData object to the Table factory, that complicates matters. You can use a singleton/global metadata object, or just convert to declarative.

For a while I chose to implement one-argument functions that returned Table objects given a metadata and cached the result--in effect implementing a singleton model class. Then I decided that was silly and switched to declarative.

Comments

[osjerick]

In addition, if you are using MySQL every column alter operation requires existing_type.