Alembic migrations

SQLArgon models share a single MetaData with a preconfigured naming convention, so Alembic only needs to be pointed at it. The setup below runs migrations over the async engine, with no synchronous driver (psycopg2 or similar) installed.

Setup

alembic init -t async migrations

Then replace the generated migrations/env.py with:

import asyncio
from logging.config import fileConfig

from alembic import context

# the Database instance and every module defining models must be imported,
# so that all tables are registered on the shared metadata
from myapp.db import db
import myapp.models  # noqa: F401

config = context.config

if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = db.Model.metadata


def run_migrations_offline() -> None:
    context.configure(
        url=str(db.engine.url),
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
        compare_type=True,
    )
    with context.begin_transaction():
        context.run_migrations()


async def run_migrations_online() -> None:
    def do_migrations(connection):
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            dialect_opts={"paramstyle": "named"},
            compare_type=True,
        )
        with context.begin_transaction():
            context.run_migrations()

    async with db.engine.connect() as connection:
        await connection.run_sync(do_migrations)

    await db.engine.dispose()


if context.is_offline_mode():
    run_migrations_offline()
else:
    asyncio.run(run_migrations_online())

db.Model is the declarative Base, so db.Model.metadata and sqlargon.Base.metadata are the same object — use whichever reads better in your project. With db = Database.from_env() the URL comes from DATABASE_URL, so sqlalchemy.url in alembic.ini can be left empty:

[alembic]
script_location = migrations
sqlalchemy.url =

Naming convention

Base.metadata is created with this convention:

{
    "ix": "ix_%(table_name)s__%(column_0_N_name)s",
    "uq": "uq_%(table_name)s__%(column_0_N_name)s",
    "ck": "ck_%(table_name)s__%(constraint_name)s",
    "fk": "fk_%(table_name)s__%(column_0_N_name)s__%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

Autogenerated revisions therefore get deterministic constraint names, and altering or dropping a constraint later does not require looking up a database-assigned name. When adopting SQLArgon in a project with existing tables, the first autogenerate run may want to rename constraints created under a different convention — review that revision before applying it.

Generating a revision

alembic revision --autogenerate -m "add user table"
alembic upgrade head

Autogenerate has limits worth knowing about here: server defaults built from function elements such as GenerateUUID() or now() are rendered into the initial revision, but later changes to them are not reliably detected. See the types reference for what those defaults compile to per dialect.

Clusters and shards

create_all / drop_all on a DatabaseCluster fan out to every writable member, but Alembic tracks its own alembic_version table per database. Migrate members one at a time — usually one Alembic invocation per shard with DATABASE_URL pointed at it:

for url in "$SHARD_0_URL" "$SHARD_1_URL"; do
  DATABASE_URL="$url" alembic upgrade head
done

Replicas need no migrations of their own: they are ReadOnlyDatabase members and reject DML, so schema changes reach them through the primary's replication stream.