Database Migrations with Alembic and SQLAlchemy

07-11-2021

Overview

Alembic is a database migration tool built by the authors of SQLAlchemy. Alembic provides an intuitive interface to create migration scripts used to upgrade or downgrade the state of your database schemas.

Migration Environment

To start using Alembic, you need to initialise a migration environment. This migration environment is created once and maintained alongside the source code.

To install Alembic:

pip install alembic

To create a migration environment:

alembic init <environment_name> # 'environment_name' is usually 'alembic'

This will create the following files and folders:

.
├── alembic.ini
├── /alembic
│   ├── env.py
│   ├── README
│   └── script.py.mako
└── /versions
    └── ...

alembic.ini - a file that alembic looks for when a script is invoked (keep in the same directory where migration scripts will be invoked)
alembic/ - a directory that contains all necessary information
- env.py - a script which is run whenever a migration is invoked and contains the configuration neccessary to create a migration engine with SQLAlchemy
- README
- script.py.mako - a Mako template used to generate migration scripts
versions/ - a directory which holds the individual reversion scripts (ie the upgrade() and downgrade() functions)

To view the revision history:

alembic history # which returns nothing because we haven't made a revision yet

Create Table Model

We will use SQLAlchemy to create a basic Table model (in this case a customer table).

from sqlalchemy import Column, Integer, MetaData, String, Table
 
 
base_meta_data = MetaData()
 
table = Table(
    "customer",
    base_meta_data,
    Column("id", Integer, autoincrement=True, primary_key=True),
    Column("first_name", String(), nullable=False),
    Column("last_name", String(), nullable=False),
    Column("address", String(), nullable=False),
    Column("city", String(), nullable=False),
)

Configure Database Connection URL

Within the alembic.ini file, a sqlalchemy.url attribute is defined. This attribute must be overriden with an appropriate database connection string. In our case, we will use the sqlite3 library to setup an in-memory database on top of our local file system.

sqlalchemy.url = sqlite:///application.db

If you don't want to hard-code a sqlalchemy.url attribute into the alembic.ini configuration file, you can override this configuration value within env.py. For example:

from config import DATABASE_URL
 
url = DATABASE_URL
config.set_main_option("sqlalchemy.url", url)

Furthermore, there may be connection string problems due to characters not escaping correctly, try this:

url = str(url).replace("%", "%%")

Configure Autogeneration

Alembic provides the ability to automatically compare snapshots of Table objects across each revision and determine relevant migrations scripts based on these differences. For autogenerated scripts to work, we must modify the env.py module so that it gets access to the table metadata (this metadata object contains Table objects that define the database). We pass this metadata object to the context via the target_metadata variable within env.py.

from models import base_meta_data
 
target_metadata = base_meta_data

Create First Migration Script

To autogenerate a migration script:

alembic revision --autogenerate -m "Add customer table"

Which returns the following output:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'customer'
  Generating /project/alembic/versions/b4352a83e78a_add_customer_table.py ...  done

And creates the following migration script:

"""Add customer table
 
Revision ID: b4352a83e78a
Revises:
Create Date: 2021-11-06 16:46:02.203960
 
"""
from alembic import op
import sqlalchemy as sa
 
 
# revision identifiers, used by Alembic.
revision = 'b4352a83e78a'
down_revision = None
branch_labels = None
depends_on = None
 
 
def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('customer',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('first_name', sa.String(), nullable=False),
    sa.Column('last_name', sa.String(), nullable=False),
    sa.Column('address', sa.String(), nullable=False),
    sa.Column('city', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###
 
 
def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('customer')
    # ### end Alembic commands ###

As you can see, Alembic has autogenerated the required revision scripts and assigned this script with the revision identifier b4352a83e78a. On upgrade, a customer table is created with three columns (id, first_name, last_name). On downgrade, the table is dropped.

Creating a revision script doesn't run a migration. You must first make sure to check that the scripts conform to your expectations. Following this, migrations must be run with the upgrade and downgrade alembic commands, as explained below.

Run First Migration

To run an upgrade revision:

alembic upgrade head

Which returns the following:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> b4352a83e78a, Add customer table

We can then inspect that these changes have been reflected in our database. We use the sqlite3 command-line interface tool to inspect the database:

sqlite3 application.db
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
 
sqlite> .tables
alembic_version  customer
 
sqlite> .schema customer
CREATE TABLE customer (
        id INTEGER NOT NULL,
        first_name VARCHAR NOT NULL,
        last_name VARCHAR NOT NULL,
        address VARCHAR NOT NULL,
        city VARCHAR NOT NULL,
        PRIMARY KEY (id)
);

As you can see, our SQLite database has been updated with the customer table.

To view Alembic history:

alembic history
<base> -> b4352a83e78a (head), Add customer table

SQLite is a lightweight, fast, self-contained disk-based database that runs on top your file system. It is useful for prototyping and testing.

Installation:

On macOS, SQLite should be installed by default
On Windows, SQLite can be downloaded from the SQLite website. To install: extract and add the resulting executables to your path environement variable.

Basic usage:

# run sqlite
sqlite3
 
# display all databases in current connection
sqlite> .database
 
# open database file
sqlite> .open path/to/database.db
 
# add additional database to current connection
sqlite> ATTACH DATABASE "path/to/database.db" AS db_name;
 
# view table schema
sqlite> .schema <table_name>
 
# exit sqlite3
sqlite>.exit

Upgrade Commands

To upgrade to the most recent revision:

alembic upgrade head

To upgrade the current state by a certain number of revisions:

alembic upgrade +1

To upgrade to a particular revision:

alembic upgrade rl6192cd49d6

Downgrade Commands

To downgrade to the earliest revision:

alembic downgrade base

To downgrade the current state by a certain number of revisions:

alembic downgrade -1

To downgrade to a particular revision:

alembic downgrade rl6192cd49d6

Update Table Model

Now that we have run our first migration and Alembic is pointing to the latest revision, we will update our table to include an age attribute.

table = Table(
    "customer",
    base_meta_data,
    Column("id", Integer, autoincrement=True, primary_key=True),
    Column("first_name", String(), nullable=False),
    Column("last_name", String(), nullable=False),
    Column("address", String(), nullable=False),
    Column("city", String(), nullable=False),
    Column("age", Integer, nullable=False),
)

Create Second Migration Script

Given this update to our code, we would like these changes to be reflected in our database.

To autogenerate a migration script:

alembic revision --autogenerate -m "Add age column"

Which returns the following output:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added column 'customer.age'
  Generating /project/alembic/versions/c2e7eea3f80e_add_age_column.py ...  done

And creates the following migration script:

"""Add age column
 
Revision ID: c2e7eea3f80e
Revises: b4352a83e78a
Create Date: 2021-11-06 17:28:01.437872
 
"""
from alembic import op
import sqlalchemy as sa
 
 
# revision identifiers, used by Alembic.
revision = 'c2e7eea3f80e'
down_revision = 'b4352a83e78a'
branch_labels = None
depends_on = None
 
 
def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('customer', sa.Column('age', sa.Integer(), nullable=False))
    # ### end Alembic commands ###
 
 
def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('customer', 'age')
    # ### end Alembic commands ###

As you can see, Alembic has autogenerated the required revision scripts and assigned this script with the revision identifier c2e7eea3f80e. On upgrade, the age column is added. On downgrade, the age column is dropped.

Run Second Migration

To run an upgrade revision:

alembic upgrade head

Which returns the following output:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade b4352a83e78a -> c2e7eea3f80e, Add age column

We can then inspect that these changes have been reflected in our database. Again, we use the sqlite3 command-line interface tool to inspect the database:

> sqlite3 application.db
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
 
sqlite> .tables
alembic_version  customer
 
sqlite> .schema customer
CREATE TABLE customer (
        id INTEGER NOT NULL,
        first_name VARCHAR NOT NULL,
        last_name VARCHAR NOT NULL,
        address VARCHAR NOT NULL,
        city VARCHAR NOT NULL,
        age INTEGER NOT NULL,
        PRIMARY KEY (id)
);

As you can see, our SQLite database has been updated with the age column in the customer table.

To view Alembic history:

alembic history
b4352a83e78a -> c2e7eea3f80e (head), Add age column
<base> -> b4352a83e78a, Add customer table