Rename Postgres table with Alembic migrations

November 27, 2015

In this article I’ll discuss the approach I take to rename Postgres tables using Alembic. This includes renaming all references to the old table name such as sequences and indexes.

I’m a fan of SQLAlchemy and Postgres, I like to use them with Alembic to manage my database migrations. My normal workflow with Alembic is:

Edit SQLAlchemy models
Auto-generate the migration

alembic -c alembic.ini revision --autogenerate -m "Migration description"

Run the migration

alembic -c alembic.ini upgrade head

Let’s say I have business-critical software which tracks when I buy and eat Marathon bars. Marathon bars were renamed to Snickers in 1990 in the UK so it makes sense to update my software to reflect this, this will help avoid confusion with legacy chocolate bar naming.

I have an SQLAlchemy model called Marathon which represents a DB table marathon. I want to rename the model to Snickers and have my DB table called snickers. The original Marathon model looks like this:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Marathon(Base):
    __tablename__ = 'marathon'
    id = Column(Integer, primary_key=True)
    weight = Column(Integer, nullable=False)
    bought = Column(DateTime(timezone=True), nullable=False)
    eaten = Column(DateTime(timezone=True), nullable=False)

I edit it to look like this:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Snickers(Base):
    __tablename__ = 'snickers'
    id = Column(Integer, primary_key=True)
    weight = Column(Integer, nullable=False)
    bought = Column(DateTime(timezone=True), nullable=False)
    eaten = Column(DateTime(timezone=True), nullable=False)

I then run:

alembic -c alembic.ini revision --autogenerate -m "Renaming Marathon model to Snickers"

This produces the following migration for upgrade:

def upgrade():
    op.create_table('snickers',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('weight', sa.Integer(), nullable=False),
    sa.Column('bought', sa.DateTime(timezone=True), nullable=False),
    sa.Column('eaten', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.drop_table('marathon')

The migration is dropping the marathon table and creating a new snickers table. This is going to be a problem if I want to keep existing data, as it’ll be deleted along with the marathon table. I need to manually edit the migration to use the alembic rename_table command:

def upgrade():
    op.rename_table('marathon', 'snickers')

All seems good in the world until I take a look in Postgres. Running \d shows:

snacks=# \d
              List of relations
 Schema |      Name       |   Type   | Owner
--------+-----------------+----------+--------
 public | alembic_version | table    | snacks
 public | marathon_id_seq | sequence | snacks
 public | snickers        | table    | snacks
(3 rows)

What is the troublesome marathon_id_seq doing hanging about?

On further inspection of the snickers table \d snickers I can see that marathon_id_seq is used for auto-incrementing the id:

snacks=# \d snickers
                                  Table "public.snickers"
 Column |  Type   | Modifiers
--------+---------+------------------------------------------------------
 id     | integer | not null default nextval('snickers_id_seq'::regclass)

I rename the sequence in the migration like this:

op.execute('ALTER SEQUENCE  marathon_id_seq RENAME TO snickers_id_seq')

Are we finished? Of course not, this is a development blog post, we need at least three things to go wrong. Let’s inspect the snickers table again to see our last problem:

snacks=# \d snickers
...
Indexes:
    "marathon_pkey" PRIMARY KEY, btree (id)

I’ve got this pesky index marathon_pkey hanging about. I can rename the index in the migration too:

op.execute('ALTER INDEX marathon_pkey RENAME TO snickers_pkey')

This is my finished migration:

def upgrade():
    op.rename_table('marathon', 'snickers')
    op.execute('ALTER SEQUENCE marathon_id_seq RENAME TO snickers_id_seq')
    op.execute('ALTER INDEX marathon_pkey RENAME TO snickers_pkey')

def downgrade():
    op.rename_table('snickers', 'marathon')
    op.execute('ALTER SEQUENCE snickers_id_seq RENAME TO marathon_id_seq')
    op.execute('ALTER INDEX snickers_pkey RENAME TO marathon_pkey')

Remember kids, winners don’t do drugs and they always write downgrade database migrations.

It would be amazing if Alembic could automatically produce these type of rename migrations but I’m not sure if this is possible to implement. One solution would be to add an extra command to Alembic especially for renaming tables. The code for my example project is over on Github.

If you'd lke to contact me about this article then use twitter @petexgraham or email articles@petegraham.co.uk.