This article is part of in the series


Alembic is a lightweight database migration tool for SQLAlchemy. It is created by the author of SQLAlchemy and it has become the de-facto standard tool to perform migrations on SQLAlchemy backed databases.

Database Migration in SQLAlchemy

A database migration usually changes the schema of a database, such as adding a column or a constraint, adding a table or updating a table. It's often performed using raw SQL wrapped in a transaction so that it can be rolled back if something went wrong during the migration. In this article, we are going to use a sample database to demonstrate how to write Alembic migration scripts for a SQLAlchemy database.

To migrate a SQLAlchemy database, we add an Alembic migration script for the intended migration, perform the migration, update the model definition and then start using the database under the migrated schema. These steps sound like a lot, but they are quite straightforward to do, which is illustrated in the following section.

Sample Database Schema

Let's create a SQLAlchemy database with a department and a employee table.

After the database alembic_sample.sqlite has been created, we realize we forgot to add a many-to-many relationship between Employee and Department.


Instead of changing the schema directly and then recreate the database from scratch, we choose to migrate the database using alembic. In order to do that, we install alembic, initialize an alembic environment, write a migration script to add the link table, perform the migration, and then use an updated model definition to access the database again.

The migration script is as follows:

Now that the database alembic_sample.sqlite has been upgraded, we can use an updated piece of model code to access the upgraded database.

Notice that we did not delete the database alembic_sample.sqlite but instead performed a migration to add a link table instead. After the migration, the relationship Department.employees and Employee.departments are working as expected.


Since Alembic is a lightweight database migration tool built specifically for SQLAlchemy, it allows you to re-use the same kind of database model APIs to perform simple migrations. However, it's not an do-it-all-for-you tool. For very database specific migration, such as adding a trigger function in PostgreSQL, a raw DDL statement is still required.

About The Author