This is not tutorial - it’s my understanding! If I’m worng - let me know.

Before dive into the tutorial - first read Alembic getting started manual.

Setup

After you setup your working enviroment ( env, pip, sqlalchemy etc. ) run:

$ alembic init alembic

This command will add alembic folder ( with banch of folder and files in it ) and alembic.ini file.

In alembic.ini change sqlalchemy.url variable in [alembic] section to your database connection string. For example:

[alembic]
; ... configuration ...
sqlalchemy.url = postgresql+psycopg2://user:password@localhost:5432/application?sslmode=disable
; ... configuration ...

This thing will allow alembic to connect to the database and detect changes. As I understand - if you want to create initial migration file with all changes in it and every thing is already in the database, alembic will autogenerate empty migration file. Because there are no difference between metadata and actual schema in database.

Autogenerate migration

Main goal of using alembic - autogenerating migrations. You can create migrations and manualy declare all tables and relation in migration files. But, more for me ( and most of the userers in the internet, as I can see ) autogeneration is the main use case.

After initial setup and defining all required files and connections declare your table. For example:

from sqlalchemy import create_engine, Column, Integer, Text, Table, MetaData
engine = create_engine(DATABASE_URL)
metadata = MetaData()

users = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", Text),
    Column("image", Text),
    Column("active", Text),
    Column("created_at", Text),
)

I using SQLAlchemy Core. It’s enough for me. For SQLAlchemy ORM steps will be mostly the same - declare models, import metadata

Define metadata

After alembic initialization you can fined alembic folder in the folder where you start this command. In this folder exist env.py file. Here we need to import all models and metadata. For example:

# ./alembic/env.py

import models as db
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = db.metadata

As you can see from the autogenerated comments - alembic asc you to import you model ( or multiple models ) and change target_metadata value to your actual metadata. Very important to import your models because in another way your metadata.tables will be empty.

Create initial migration

And finaly run this command:

$ alembic revision --autogenerate -m "initial"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'users'
  Generating /home/alex/projects/memosynth/memosynth-
  infrastructure/database/alembic/versions/c46dab3fa34a_initial.py ...  done

After that you will find new file with content like this:

"""initial

Revision ID: c46dab3fa34a
Revises: 
Create Date: 2023-08-12 13:55:00.336951

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('email', sa.Text(), nullable=True),
    sa.Column('phone', sa.Text(), nullable=True),
    sa.Column('image', sa.Text(), nullable=True),
    sa.Column('role', sa.Text(), nullable=True),
    sa.Column('active', sa.Text(), nullable=True),
    sa.Column('password_hash', sa.Text(), nullable=True),
    sa.Column('public', sa.Text(), nullable=True),
    sa.Column('created_at', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('users')
    # ### end Alembic commands ###

Also, if you connect to database you will find new table alembic_revision:

application=# \dt
            List of relations
 Schema |      Name       | Type  | Owner 
--------+-----------------+-------+-------
 public | alembic_version | table | user
(1 row)

application=# SELECT * FROM alembic_version;
 version_num 
-------------
(0 rows)

Models in separate files

For example you have users model in separate file:

# ./models/users.py
from sqlalchemy import Column, Integer, Table, Text
from . import metadata

users = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", Text),
    Column("image", Text),
    Column("active", Text),
    Column("created_at", Text),
)

And ./models/__init__.py file with metadata and engine defined:

from sqlalchemy import create_engine, MetaData
DATABASE_URL: str = "postgresql+psycopg2://user:password@localhost:5432/application?sslmode=disable"

engine = create_engine(DATABASE_URL)
metadata = MetaData()

In this case if you just import metadata - migration file will be empty. This is heppening because your MetaData will not contain any tables. To include tables add this table in ./alembic/env.py like this:

from models.users import users

Or include them in ./models/__init__.py like this:

from sqlalchemy import create_engine, MetaData
DATABASE_URL: str = "postgresql+psycopg2://user:password@localhost:5432/application?sslmode=disable"
engine = create_engine(DATABASE_URL)
metadata = MetaData()
from models.users import users

To understand why this is happening read python documentation about module importing and what is goin on under the hood - 5. The import system.

Running migration

$ alembic upgrade head

Updating database schema

Let’s say you want to extend user model and add a few more tables like this:

# ./models/users.py
from sqlalchemy import Column, Table, String, Text, ForeignKey, Boolean, DateTime, Integer
# from sqlalchemy.dialects.postgresql import UUID
# import uuid
from datetime import datetime
from . import metadata


users = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", Text),
    Column("email", Text),
    Column("phone", String(16)),
    Column("image", Text),
    Column("role", String(16), ForeignKey("roles.name", ondelete="CASCADE")),
    Column("active", Boolean, default=False),
    Column("password_hash", Text),
    Column("public", Boolean, default=False),
    Column("created_at", DateTime, default=datetime.now),
    Column("updated_at", DateTime, default=datetime.now, onupdate=datetime.now),
)

roles = Table(
    "roles",
    metadata,
    Column("name", String(16), primary_key=True)
)

providers = Table(
    "providers",
    metadata,
    Column("name", String(16), primary_key=True)
)

user_providers = Table(
    "user_providers",
    metadata,
    Column("user_id", Integer, ForeignKey("users.id")),
    Column("provider", String(16), ForeignKey("providers.name")),
)

After making changes in your file run another command:

$ alembic revision --autogenerate -m "update user table"

This command should add another file in ./alembic/revisions folder:

"""update user table

Revision ID: aabbe38f60da
Revises: 327ea701a73a
Create Date: 2023-08-12 14:37:37.199586

"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision: str = 'aabbe38f60da'
down_revision: Union[str, None] = '327ea701a73a'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('providers',
    sa.Column('name', sa.String(length=16), nullable=False),
    sa.PrimaryKeyConstraint('name')
    )
    op.create_table('roles',
    sa.Column('name', sa.String(length=16), nullable=False),
    sa.PrimaryKeyConstraint('name')
    )
    op.create_table('user_providers',
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('provider', sa.String(length=16), nullable=True),
    sa.ForeignKeyConstraint(['provider'], ['providers.name'], ),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], )
    )
    op.add_column('users', sa.Column('updated_at', sa.DateTime(), nullable=True))
    op.create_foreign_key(None, 'users', 'roles', ['role'], ['name'], ondelete='CASCADE')
    # ### end Alembic commands ###

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'users', type_='foreignkey')
    op.drop_column('users', 'updated_at')
    op.drop_table('user_providers')
    op.drop_table('roles')
    op.drop_table('providers')
    # ### end Alembic commands ###

As you can see - alembic does not recognize column type change… May be I’m doing something wrong but probably this should be added manualy. And also:

    op.create_foreign_key(None, 'users', 'roles', ['role'], ['name'], ondelete='CASCADE')

Will create foreign key constraing without name?… That’s why this files has this comments:

# ### commands auto generated by Alembic - please adjust! ###

Important! Always check what actualy does it want to write into database! Do not trust the machines :)

So basicaly what I did is add all necessary changes into migration file and upgrade head. By the way, as you can see columns active and created_at had type Text, after changes I have change this types to more convinient Boolean and DateTime, but you cannot see this in migration file. Change column types is a little bit paintfull. I made it by adding new column is_active, move all data into new column from old active, remove old column and rename new column to active. And in downgrade function this process should be reversed. This is the code:

op.add_column('users', sa.Column('is_active', sa.Text))
connection = op.get_bind()
connection.execute(text("UPDATE users SET is_active = (active::text)"))
op.drop_column('users', 'active')
op.alter_column("users", "is_active", new_column_name="active")

This shows us the importantse of designing database schema on early stages of the project.

Managing migrations

After you did a couple of changes you can jump on previous migration simple by:

$ alembic downgrade -1

Or jump up from previus to next:

$ alembic upgrade +1

More you can find by reading friendly manual

Local/dev/prod

Interesting thing about alembic.ini file - you cannot use environment variables there. So the sqlalchemy.url will be hardcoded. And if you have different path’s for local, staging or production databases - it will be hard to manage. I find a way how to dial with different configurations. You need to add this thing instead of actual db url:

sqlalchemy.url = postgresql+psycopg2://{}:{}@{}:5432/{}?sslmode=disable

This is template string. And in ./alembic/env.py change run_migrations_* functions to this:

def run_migrations_offline() -> None:
    url = config.get_main_option("sqlalchemy.url").format(POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_HOST, POSTGRES_DB)
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online() -> None:
    url = config.get_main_option("sqlalchemy.url").format(POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_HOST, POSTGRES_DB)
    connectable = create_engine(url)
    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()

You see? We imported env variables and fill the cells in template string. Why this is not added out of the box? Don’t know. So inconvenience.

Sources:

Conclusions

Start project with Alembic. It’s not easy to integrate it into existed project.

P.S. - this is not tutorial, this is set of thoughts and “recepies”. And it’s my first atempt to write articles. I’m sory if you disapointed at the end of article.