pythonsqlitepostgresqlherokusqlalchemy-migrate

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


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

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