Chapter 6: Alembic for apps with multiple databases or schema-per-tenant. Setup, migration strategies, common patterns.

When this matters

  • App with multiple databases (e.g., users DB + analytics DB).
  • Schema-per-tenant SaaS.
  • DB-per-tenant SaaS.
  • Sharded databases.

Multi-database template

alembic init -t multidb migrations

env.py iterates databases:

# alembic.ini
[alembic]
databases = users, analytics

[users]
sqlalchemy.url = postgresql://...
script_location = migrations/users

[analytics]
sqlalchemy.url = postgresql://...
script_location = migrations/analytics

Per-database migrations. Each gets its own version table.

Multi-DB migrations

alembic --name users upgrade head
alembic --name analytics upgrade head

Or wrap in a script that iterates.

For consistency across DBs (e.g., shared user_id column): keep schemas decoupled where possible.

Schema-per-tenant

Each tenant has its own Postgres schema. One migration must run on every schema.

env.py:

def run_migrations_online():
    schemas = get_tenant_schemas()  # from your app
    
    for schema in schemas:
        connectable = create_engine(...)
        with connectable.connect() as conn:
            conn.execute(text(f"SET search_path TO {schema}"))
            context.configure(
                connection=conn,
                target_metadata=target_metadata,
                version_table_schema=schema,
            )
            with context.begin_transaction():
                context.run_migrations()
        connectable.dispose()

Iterates schemas; runs each migration against each.

Per-tenant version tables

Each schema gets its own alembic_version table:

context.configure(
    connection=conn,
    version_table_schema=schema,  # places the table in the tenant's schema
)

Without this: all tenants share one version table; can’t migrate independently.

DB-per-tenant

Each tenant: its own database. Iterate databases:

def run_for_all_tenants():
    for tenant in get_tenants():
        connectable = create_engine(tenant.connection_string)
        with connectable.connect() as conn:
            context.configure(connection=conn, target_metadata=target_metadata)
            with context.begin_transaction():
                context.run_migrations()
        connectable.dispose()

For 100s of tenants: takes minutes / hours. Parallelize.

Parallel migrations

import concurrent.futures

def migrate_one(tenant):
    # ...

with concurrent.futures.ThreadPoolExecutor(max_workers=10) as ex:
    list(ex.map(migrate_one, tenants))

Watch DB connection counts; don’t blow past limits.

Onboarding a new tenant

For schema-per-tenant on tenant signup:

async def create_tenant(tenant_name):
    async with engine.connect() as conn:
        await conn.execute(text(f"CREATE SCHEMA tenant_{tenant_name}"))
        # Run migrations for the new schema
        cfg = Config("alembic.ini")
        cfg.set_main_option("sqlalchemy.url", DATABASE_URL)
        # ... configure for the schema ...
        command.upgrade(cfg, "head")

Or stamp the schema with current head and don’t run all historical migrations:

command.stamp(cfg, "head")

Combine with Base.metadata.create_all() to bootstrap the tables.

Migrations that don’t apply to all tenants

Sometimes a migration is per-tenant; sometimes global (shared schema).

def upgrade():
    if should_run_for_this_tenant():
        op.add_column("users", ...)

Or split into per-tenant and global migration tracks.

Sharded databases

Each shard runs the same migration. Iterate shards.

For consistency: snapshot the version state before; run all shards; verify after.

Watch for partial failures: some shards migrated; others not. Rerun safely.

Multi-app monorepo

app1/
  migrations/
  alembic.ini
app2/
  migrations/
  alembic.ini
shared/
  models.py

Each app has its own Alembic. Shared models in a separate package; each app’s target_metadata references it.

For consistency: align migration cadence and avoid one app changing shared schemas without coordination.

CI for multi-DB

- run: alembic --name users upgrade head
- run: alembic --name analytics upgrade head
- run: alembic --name users history
- run: alembic --name analytics history

Test each DB separately.

Drift detection per DB

For each DB:

def test_no_drift_users(engine):
    with engine.connect() as conn:
        ctx = MigrationContext.configure(conn)
        diff = compare_metadata(ctx, UsersBase.metadata)
        assert diff == [], f"users drift: {diff}"

Common mistakes

1. One alembic_version table for all tenants

Each tenant’s state collapses. Use version_table_schema per-tenant.

2. Sequential migrations on 1000 tenants

Hours. Parallelize.

3. Schema-per-tenant + 10000 tenants

Postgres struggles with thousands of schemas. Cap at hundreds; otherwise consider RLS instead.

4. Sharing alembic.ini across apps

One app’s revision affects another. Separate envs.

5. Manual schema creation drift

If new tenants get tables via Base.metadata.create_all() and existing tenants migrate via Alembic, they can drift. Use Alembic for both.

What’s next

Chapter 7: CI integration.

Read this next

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .