Automatic migrations in SQLAlchemy with Alembic

Background

While using ORMs in Python offers a great deal of flexibility in many respects, one of the biggest drawbacks is database maintenance. What happens if you just alter the models in your code? If you do so, you will have to either:

• manually alter/drop/create the tables in your database – can seriously decrease maintainability, especially with versioning and deployment in mind
• write your own migration script – a rather painful and unnecessary process

If you are using SQLAlchemy, you can automatically generate migrations with Alembic. This make the whole process of altering the database very easy: once Alembic is set, you will only need two simple commands to upgrade your database! It recognizes the differences between the tables in your database and the models defined in your Python code.

Install Alembic

Alembic is a very convenient tool for performing migrations with SQLAlchemy (see documentation).

You can easily set it up with either…

pip install alembic

…or…

pip3 install alembic

…depending on the version of Python you are using.

Configure Alembic

Execute the following command at the root of your project:

alembic init alembic

This will create both an alembic directory and an alembic.ini configuration file. These need a little tuning for Alembic to work properly:

• alembic.ini – give the connection string to your database, e.g. sqlalchemy.url = postgresql://user:password@host/dbname
• alembic/env.py – tell where the metadata about the models can be found in your project (an example of usage is given in the script)

Automatic migrations

Well, the hardest has been done! Now, if you want to generate a new migration script, you can simply run:

alembic revision --autogenerate

You can also name it, if you need accurate version follow-up:

alembic revision --autogenerate -m "Message for this migration"

Now that the migration script has been automatically generated, upgrade the structure of your database to its latest version using:

alembic upgrade head

What can it do?

Alembic’s automatic migration generator can recognize:

• Table additions and removals
• Column additions and removals
• Change of nullable status on columns
• Basic changes in indexes, explicitly-named unique constraints, and foreign keys