Running Alembic across multiple databases and tenant schemas

For schema-per-tenant, loop over the schemas and run the same revisions with connection.execution_options(schema_translate_map={None: schema}) and version_table_schema=schema, committing per schema so an interrupted run resumes; for genuinely separate databases, use Alembic's multidb template and give each its own revision directory. This guide belongs to configuring Alembic with async SQLAlchemy engines.

Quick Answer

The common case is one set of models replicated across many PostgreSQL schemas, one per tenant. The migrations are identical; only the schema changes.

Before — one run against the default schema, which migrates nothing:

# alembic/env.py — the single-target template
async def run_async_migrations() -> None:
    connectable = create_async_engine(database_url(), poolclass=pool.NullPool)
    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)
    await connectable.dispose()
# Applies the revisions to "public" only. The 200 tenant schemas are untouched.

After — one revision set, applied to every schema, resumably:

# alembic/env.py (excerpt)
import logging

from alembic import context
from sqlalchemy import pool, text
from sqlalchemy.ext.asyncio import create_async_engine

from shop.models import Base

log = logging.getLogger("alembic.tenants")

TENANT_SCHEMAS = text(
    "SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant\\_%' ORDER BY nspname"
)


def do_run_migrations(connection, schema: str) -> None:
    context.configure(
        connection=connection,
        target_metadata=Base.metadata,
        version_table_schema=schema,     # each tenant records its own revision
        include_schemas=False,
        transaction_per_migration=True,
    )
    with context.begin_transaction():
        context.run_migrations()


async def run_async_migrations() -> None:
    engine = create_async_engine(database_url(), poolclass=pool.NullPool)
    async with engine.connect() as conn:
        schemas = list((await conn.execute(TENANT_SCHEMAS)).scalars())

    log.info("migrating %d tenant schemas", len(schemas))
    for position, schema in enumerate(schemas, start=1):
        async with engine.connect() as conn:
            await conn.execute(text("SET lock_timeout = '5s'"))
            await conn.commit()
            scoped = await conn.execution_options(schema_translate_map={None: schema})
            async with scoped.begin():
                await scoped.run_sync(do_run_migrations, schema)
        log.info("migrated %s (%d/%d)", schema, position, len(schemas))

    await engine.dispose()

Each schema gets its own connection, its own transaction and its own alembic_version table, so a run interrupted at schema forty can simply be started again: the first forty are already at head and are skipped in milliseconds.

Execution Context & Async Workflow Integration

Two SQLAlchemy and Alembic features do the work here, and they are independent.

schema_translate_map is an execution option that rewrites schema names at statement-compilation time. With {None: "tenant_17"}, every table in the metadata that has no explicit schema is rendered as tenant_17.orders rather than orders. Because it happens during compilation, it applies to the DDL the migrations emit without any revision having to know about tenants — the same mechanism the application uses per request, described in switching schemas per request with schema_translate_map.

version_table_schema tells the migration context where its bookkeeping table lives. Setting it to the tenant's schema is what makes progress per tenant rather than global. The alternative — one shared version table — records "we are at head" after the first schema, and a re-run then does nothing while a hundred and ninety-nine schemas remain behind.

Both are set per connection, which is why the loop takes a fresh connection per schema rather than reusing one. It also keeps each tenant's work in its own transaction: with async with scoped.begin(), a failure in one tenant rolls back only that tenant, and the loop can report which one failed.

SET lock_timeout deserves its place in the loop. A tenant with a long-running query will block DDL on its tables, and without a timeout the whole run waits — and, worse, every query arriving behind the waiting ALTER queues too. Five seconds turns that into a failed schema that can be retried rather than a stalled deploy. The reasoning is the same as for single-database migrations, covered in managing enums, constraints and indexes in migrations.

For genuinely separate databases — a billing database and a catalogue database with different models — the tool is different. alembic init --template multidb generates an env.py that reads several named engines from alembic.ini and runs each with its own version table, and revisions carry per-database branches. In practice, a separate revision directory per database (alembic -c alembic.billing.ini) is easier to reason about, because each database's history is then linear and reviewable on its own.

Under async, all of this runs through run_sync() as usual. Note that run_sync passes extra positional arguments to the function, which is how do_run_migrations(connection, schema) receives the schema name without a global.

Resolving Warnings, Errors & Common Mistakes

The include_schemas trap is worth expanding, because it looks like the setting that makes multi-schema autogenerate work. With it enabled, Alembic reflects every schema it can see and compares all of them with the metadata, so a database with two hundred tenants produces two hundred copies of every difference — and the reflection itself takes minutes. The workable arrangement is to keep one schema canonical:

# Generating a revision: compare the models against ONE reference schema.
REFERENCE_SCHEMA = os.environ.get("ALEMBIC_REFERENCE_SCHEMA", "tenant_reference")


def do_run_migrations(connection, schema: str) -> None:
    context.configure(
        connection=connection,
        target_metadata=Base.metadata,
        version_table_schema=schema,
        include_schemas=False,
        transaction_per_migration=True,
    )
    ...

The reference schema is an ordinary tenant schema kept at head and containing no data, so alembic revision --autogenerate against it produces a clean, reviewable diff. The loop then applies the resulting revision everywhere.

One more failure mode has no error message: a tenant created during the run. It is created from the current schema template, which may be at the old revision or the new one depending on timing, and the loop already passed its position in the list. Re-running the loop after the deploy is the simple answer, and it is cheap because every other schema is already at head.

Advanced: Creating New Tenant Schemas at the Right Revision

A schema-per-tenant system has a second migration path that is easy to overlook: creating a new tenant. It has to arrive at exactly the revision the application expects, and there are two ways to get there.

Run the migrations into the new schema. This is the correct default, because it reuses the tested path and leaves a version table at head:

from alembic import command
from sqlalchemy import text

from shop.migrate import alembic_config


def _upgrade_schema(connection, schema: str) -> None:
    cfg = alembic_config()
    cfg.attributes["connection"] = connection
    cfg.attributes["schema"] = schema
    command.upgrade(cfg, "head")


async def create_tenant(engine, schema: str) -> None:
    async with engine.begin() as conn:
        await conn.execute(text(f'CREATE SCHEMA "{schema}"'))
    async with engine.connect() as conn:
        scoped = await conn.execution_options(schema_translate_map={None: schema})
        async with scoped.begin():
            await scoped.run_sync(_upgrade_schema, schema)

env.py then reads config.attributes["schema"] to set version_table_schema, so the same env.py serves both the deploy loop and tenant creation. The quoted CREATE SCHEMA is deliberate: the schema name comes from your own code, and quoting keeps a name with unusual characters from becoming a syntax error — but it must never come from user input, because no quoting makes that safe.

Or clone a template schema. Copying a prepared schema is faster for a system that creates tenants constantly, and PostgreSQL has no CREATE SCHEMA LIKE, so it means generating DDL from a template — which is a second code path that can drift from the migrations. If you take this route, add a test that creates a tenant both ways and compares the resulting schemas with the inspector.

For the deploy itself, two operational details matter at scale. Migrating two hundred schemas sequentially takes as long as the sum of their lock waits, so a deploy job should log progress and be safe to re-invoke — which the per-schema version table already provides. And the job should be a separate step, not application start-up: replicas racing to migrate two hundred schemas is the worst case of the problem described in running Alembic migrations programmatically from async code.

Finally, a readiness check per tenant is cheap and worth having. Reading alembic_version from each schema and comparing it with the expected head tells you exactly which tenants are behind, which is the question that matters after a partially failed deploy:

from sqlalchemy import text

from shop.migrate import alembic_config
from alembic.script import ScriptDirectory


async def tenants_behind(engine) -> dict[str, str | None]:
    expected = set(ScriptDirectory.from_config(alembic_config()).get_heads())
    behind: dict[str, str | None] = {}
    async with engine.connect() as conn:
        schemas = list((await conn.execute(TENANT_SCHEMAS)).scalars())
        for schema in schemas:
            current = await conn.scalar(
                text(f'SELECT version_num FROM "{schema}".alembic_version')
            )
            if current not in expected:
                behind[schema] = current
    return behind

Choosing Between Schemas, Databases and a Tenant Column

The migration mechanics above are the consequence of a modelling decision, and it is worth revisiting that decision before investing in them, because the cost difference is large.

A tenant column in a shared schema needs no multi-target migrations at all: one database, one schema, one migration run, and isolation enforced by row-level security. It scales to very large tenant counts, because adding a tenant is an insert. The trade-off is that isolation is a property of the queries and policies rather than of the storage, and per-tenant operations — restoring one tenant's data, moving one tenant to another server — are much harder.

Schema per tenant gives real separation of tables and makes per-tenant backup and restore straightforward, at the cost of everything in this guide: migrations multiplied by tenant count, catalogue bloat with thousands of schemas, and connection pooling that has to work per schema rather than per tenant. It suits tens or low hundreds of tenants, not tens of thousands.

Database per tenant gives the strongest isolation and the ability to place tenants on different servers, and multiplies operational work by tenant count: connections, backups, monitoring and migration runs. It suits a small number of large tenants — the enterprise customers whose contracts require it.

Many systems end up hybrid, and deliberately: a shared schema with a tenant column for the long tail, and dedicated databases for the few tenants that need it. That keeps the common path simple, and it is a decision the migration tooling can accommodate as long as the shared path is the default rather than an afterthought.

Whichever layout is in use, write down which one it is and what the migration procedure is, next to env.py. The procedure — which schemas, in what order, with which version tables, resumable how — is not inferable from the revision files, and it is what the next person needs at two in the morning when a deploy has stopped at schema forty of two hundred. The routing side of the same decision is covered in dynamic schema and multi-tenant routing.

Frequently Asked Questions

Related

• Configuring Alembic with Async SQLAlchemy Engines — The parent guide: async env.py structure and options.
• Loading database URLs and secrets in Alembic env.py — Building the connection this loop uses.
• Switching schemas per request with schema_translate_map — The same mechanism in the application.
• Routing models to multiple databases with session binds — When the targets are separate databases rather than schemas.