Skip to content

#86: Database Migrations With Alembic and SQLAlchemy

SQLAlchemy does not modify existing tables. What can we do to keep our database in sync with our application?

Alembic?

Alembic is a lightweight database migration tool written by the author of SQLAlchemy. You can create migrations on your own and make changes to the database structure (schemas, tables, indexes, sequences and so on), or you let Alembic figure out the difference between the database and your Python models and create the migration for you.

Alembic is similar to Active Record Migrations for Ruby on Rails or Migrations in Entity Framework and lets you upgrade or downgrade your migrations.

There are a lot of edge cases Alembic covers. If you get stuck, check the documentation and the tutorials for a solution to your problem.

Installation

You can install Alembic with this pip command:

1
pip install alembic

Initialise Alembic

Alembic needs a few files to work that are specific to your project. The location for these files depends on your project structure. As a rule of thumb, I put the alembic configuration next to the tests folder. As with tests these files belong to the project, but I do not need them at runtime.

We can initialise Alembic with this command that creates the files it needs in a folder called alembic (the last part of the command):

1
alembic init alembic

Edit the .ini file

The alembic.ini file is the only file that is outside our newly created alembic folder. In this file we need to set the connection string for our database.

Search for this part of the alembic.ini file:

1
2
3
4
5
# the output encoding used when revision files
# are written from script.py.mako
# output_encoding = utf-8

sqlalchemy.url = driver://user:pass@localhost/dbname

Replace the dummy connection string with the one for your database. For SQLite we can use a relative path from the alembic.ini file to our *.sqlite file:

1
sqlalchemy.url = sqlite:///./db/Bookstore.sqlite

Edit the alembic/env.py file

Alembic can only generate migrations for us when it knows our models. Open the file alembic/env.py and search for this part:

1
2
3
4
5
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = None

We need to add our data folder (the one with our models) to the Python path, import our models and then hand the metadata to Alembic. We can do this with this code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
import sys
import os

folder = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
sys.path.insert(0, folder)

from data.modelbase import ModelBase
import data.__all_models

target_metadata = ModelBase.metadata

Save the file and check if Alembic works:

1
alembic current

INFO [alembic.runtime.migration] Context impl SQLiteImpl.

INFO [alembic.runtime.migration] Will assume non-transactional DDL.

Disable the automatic table creation in SQLAlchemy

You can save yourself a lot of trouble if you switch off table creation in SQLAlchemy. In SQLAlchemy ORM we have something link these two lines we no longer need:

1
2
import data.__all_models
    ModelBase.metadata.create_all(engine)

In SQLAlchemy Core we used this line to create our tables that you can comment out:

1
metadata.create_all(engine)

Without the automatic table creation, you can create your models, generate migrations with Alembic and apply them to your database.

However, if you already have some tables in your database that got created by SQLAlchemy and not by Alembic, you have a consistency problem. Your current database works but you cannot recreate it. In the next post I show you a way to solve that problem.

Create an auto-generated migration for your changed model

To have a change for Alembic, we add the description property to our BookDetails class (form the Bookstore example):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
import sqlalchemy as sa
from data.modelbase import ModelBase

class BookDetails(ModelBase):
    __tablename__ = 'BookDetail'

    book_id = sa.Column('Id', sa.Integer, sa.ForeignKey('Book.Id'), primary_key=True)
    cover = sa.Column('Cover', sa.String)
    book = sa.orm.relationship("Book", back_populates="details")
    description = sa.Column('Description', sa.String, nullable=True)


    def __repr__(self):
        return f'<BookDetails {self.book_id} ({self.cover})>'

With this change in place, we can autogenerate the migration with this command:

1
alembic revision --autogenerate -m "Add description"

INFO [alembic.runtime.migration] Context impl SQLiteImpl.

INFO [alembic.runtime.migration] Will assume non-transactional DDL.

INFO [alembic.autogenerate.compare] Detected added column 'BookDetail.Description' Generating D:\Python\ORM\alembic\versions\49367a22b507_add_description.py ... done

Alembic created the file 49367a22b507_add_description.py with our migration to add the description to the BookDetail table in the folder alembic/versions:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
"""Add description

Revision ID: 49367a22b507
Revises: 
Create Date: 2021-06-12 13:01:41.666716

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '49367a22b507'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('BookDetail', sa.Column('Description', sa.String(), nullable=True))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('BookDetail', 'Description')
    # ### end Alembic commands ###

The upgrade() method runs when we migrate our database forward, while the downgrade() method helps us to revert changes to our database.

The first part of the file name is the revision number and is generated at random. Your migration will get a different name and to apply it you need to use the one you get.

Run your migration

Alembic offers us a few ways to run our migrations. If we want to run all, we can use upgrade head:

1
alembic upgrade head

INFO [alembic.runtime.migration] Context impl SQLiteImpl.

INFO [alembic.runtime.migration] Will assume non-transactional DDL.

INFO [alembic.runtime.migration] Running upgrade -> 49367a22b507, Add description

With the first migration Alembic creates the table alembic_version with one column where it stores the revision number of our last applied migration:

Alembic keeps track of our applied migrations in the database.

If we now ask Alembic what the current state is, it gives us our revision number back:

1
alembic current

INFO [alembic.runtime.migration] Context impl SQLiteImpl.

INFO [alembic.runtime.migration] Will assume non-transactional DDL. 49367a22b507 (head)

If we want to run migrations up to a specific migration, we can use the revision number:

1
alembic upgrade 49367a22b507

We do not need to type the full revision number; it is enough to write enough of the number to get a unique value:

1
alembic upgrade 493

We can even do relative upgrades and say apply the next two migrations with this command:

1
alembic upgrade +2

Revert a migration

If you run your migration too early, you can revert the last applied migration with this command:

1
alembic downgrade -1

Manually creating a migration

For the case that you want to create the migration all by yourself, you can create an empty migration with this command:

1
alembic revision -m "Add manual change"

Generating D:\Python\ORM\alembic\versions\b3259a323bf6_add_manual_change.py ... done

The generated migration looks like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
"""Add manual change

Revision ID: b3259a323bf6
Revises: 49367a22b507
Create Date: 2021-06-12 13:20:39.350898

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'b3259a323bf6'
down_revision = '49367a22b507'
branch_labels = None
depends_on = None


def upgrade():
    pass


def downgrade():
    pass

It is now up to you to fill it with code.

Next

Alembic is a great help for SQLAlchemy and allows us to keep our database in sync with our changing application. But what do we do when we want to start with Alembic after we already have some tables in the database? Next week we look at how we can solve this problem.