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
Run the migration
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:
I edit it to look like this:
I then run:
This produces the following migration for upgrade:
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
All seems good in the world until I take a look in Postgres. Running
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:
I rename the sequence in the migration like this:
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:
I've got this pesky index
marathon_pkey hanging about. I can rename the index in the migration too:
This is my finished migration:
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 firstname.lastname@example.org.