Database / Alembic DB migration tool Interview questions

Alembic is a lightweight database migration tool for Python, created by Mike Bayer, the author of SQLAlchemy. It records schema changes as versioned Python scripts so every environment can be brought to the same structure in a repeatable way.

Each script is called a revision and contains an upgrade() and a downgrade() function. Alembic stores the current revision in a table named alembic_version, which is how it knows which scripts are still pending.

Because it sits on top of SQLAlchemy, it works with PostgreSQL, MySQL, SQLite, Oracle, SQL Server and any other backend SQLAlchemy supports. It can also draft migrations for you by comparing your models with the live database (autogenerate).

Hand-run SQL scripts drift apart. One developer adds a column locally, someone forgets to run it on staging, and production breaks on release day. A migration tool turns schema changes into version-controlled, ordered steps that every environment applies the same way.

Alembic gives you:

• Repeatability - one command builds the current schema from an empty database.
• Reviewability - migrations are plain files that go through pull requests.
• Reversibility - each revision can define how to undo itself.
• Traceability - the database knows exactly which revision it is on.

It also removes the temptation to call create_all() in production, which never alters existing tables.

Install it from PyPI inside your project's virtual environment:

pip install alembic
alembic --version

Pip pulls in SQLAlchemy, Mako and typing_extensions automatically. You still need a database driver for your backend, for example psycopg2-binary for PostgreSQL or pymysql for MySQL.

Pin the version in requirements.txt or pyproject.toml so every developer and CI job uses the same release. After installing, the alembic command is available on your PATH.

If you use Poetry or uv, the equivalent is poetry add alembic or uv add alembic. Installing inside the same environment as your app matters, because env.py imports your models.

alembic init <directory> creates a migration environment in your project. It writes files and folders only; it never touches the database.

Use -t async for an async SQLAlchemy engine or -t multidb for several databases:

alembic init alembic
alembic init -t async migrations

alembic.ini is the configuration file the alembic command reads at startup. It tells Alembic where the migration folder is and how to name and log things.

• script_location - path to the migration folder
• sqlalchemy.url - default database URL
• file_template - naming pattern for revision files
• prepend_sys_path - adds your project to the import path
• version_locations - extra folders for revisions
• [loggers] sections - logging setup used by env.py

It is parsed with Python's configparser, so a literal % must be written as %%, which matters for URL-encoded passwords. Use -c other.ini to point at a different file.

env.py is the Python script Alembic executes every time you run a command such as upgrade or revision --autogenerate. It decides how Alembic connects to the database and what metadata to compare against.

A standard file has two functions, run_migrations_offline() and run_migrations_online(), and picks one with context.is_offline_mode(). It also loads logging config and reads alembic.ini values through context.config.

from alembic import context
config = context.config
target_metadata = Base.metadata

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

This is the right place to inject the database URL from an environment variable or to set options like compare_type.

A revision is one migration script in the versions/ folder. It represents a single, named step in the schema history and carries an ID, a link to its parent, and the upgrade() / downgrade() code.

IDs are short random hex strings such as ae1027a6acf, so two developers can create migrations on separate branches without number clashes. Revisions form a chain through their parent links, not through file names.

"""create users table

Revision ID: 3f2a9c1d7e45
Revises: 
Create Date: 2026-11-11 09:30:00
"""
revision = "3f2a9c1d7e45"
down_revision = None

The docstring, including the message you passed with -m, is what alembic history prints.

revision is the unique ID of the current script. down_revision is the ID of its parent, the migration that must run before it. The very first revision sets down_revision = None.

Alembic builds the migration order by following these links, so file names and timestamps do not matter. A merge revision uses a tuple for down_revision because it has two parents.

# 2nd migration
revision = "b7c2d4e8f901"
down_revision = "3f2a9c1d7e45"

# merge migration
revision = "c9d1e3f50a12"
down_revision = ("b7c2d4e8f901", "a1b2c3d4e5f6")

Editing down_revision by hand is how you re-order or re-attach a migration, but it must be done before the revision is applied anywhere shared.

Run alembic revision -m "message". Alembic creates a new file in versions/ using script.py.mako, links it to the current head, and leaves empty upgrade() and downgrade() bodies.

1. Run alembic revision -m "add orders table".
2. Open the generated file.
3. Write the operations with op.* calls.
4. Test with alembic upgrade head and alembic downgrade -1.

Add --autogenerate to let Alembic draft the body from your models. Other useful flags are --rev-id for a custom ID, --head to choose the parent, and --branch-label for branched histories.

alembic revision -m "add orders table"
alembic revision --autogenerate -m "add orders table"

upgrade() applies the schema change when Alembic moves forward past this revision. downgrade() reverses it when Alembic moves backward. Both are plain Python functions that call op methods.

def upgrade():
    op.add_column("users", sa.Column("phone", sa.String(20)))

def downgrade():
    op.drop_column("users", "phone")

A good downgrade() undoes the steps in reverse order. Some changes cannot be undone safely, for example a dropped column that held data. In that case raise an exception or document it, rather than leaving a silent pass.

Alembic never calls both in one run. Which one executes depends only on the direction of the command, upgrade or downgrade.

alembic upgrade head reads the current revision from alembic_version, finds every pending revision up to the target, runs each upgrade() in order, and updates the version table.

If the database is already at the target, nothing happens, which makes the command safe to run on every deploy.

Use alembic downgrade. Alembic runs the downgrade() of each revision from the current one back to the target, then updates alembic_version.

alembic downgrade -1      # undo the last revision
alembic downgrade ae10   # go back to a specific revision
alembic downgrade base   # undo everything

Rollbacks can lose data, because a downgrade that drops a column or table cannot bring the values back. Test downgrades in a copy of production data, and in live systems many teams prefer to roll forward with a fix migration instead.

alembic_version is the bookkeeping table Alembic creates on the first migration. It has one column, version_num (VARCHAR(32)), and normally one row holding the current revision ID.

After each successful migration step Alembic updates that row. If the history has branches, there can be several rows, one per head. Because the column is 32 characters wide, very long custom revision IDs will fail to insert.

You can rename the table with version_table and place it in another schema with version_table_schema inside context.configure().

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    version_table="schema_history",
)

script.py.mako is the Mako template Alembic renders whenever it creates a new revision file. It controls the header, imports and function skeletons that every migration starts with.

"""${message}
Revision ID: ${up_revision}
Revises: ${down_revision | comma,n}
"""
from alembic import op
import sqlalchemy as sa
${imports if imports else ""}

def upgrade():
    ${upgrades if upgrades else "pass"}

def downgrade():
    ${downgrades if downgrades else "pass"}

Edit it when you want every migration to import something extra, such as import sqlmodel, or to add a team header. Changes affect only new revisions, not existing files.

alembic current prints the revision ID(s) the connected database is at, by reading the alembic_version table. A (head) marker means the database is already on the newest revision.

$ alembic current
b7c2d4e8f901 (head)

Add -v for the full message and parent. If nothing is printed, the database has never been migrated or stamped. Since it queries the database, it needs a working connection, unlike alembic history.

It is the quickest sanity check after a deploy: compare its output with alembic heads. If the two IDs match, the database is fully migrated.

alembic history lists every revision in the chain from newest to oldest, showing parent, ID and message. It reads the files in versions/ and does not need a database connection.

$ alembic history -i
b7c2d4e8f901 -> c9d1e3f50a12 (head), add orders
3f2a9c1d7e45 -> b7c2d4e8f901 (current), add phone
<base> -> 3f2a9c1d7e45, create users

• -v shows full details
• -r base:head limits the range
• -i marks the current database revision, which does need a connection

Because it only reads files, it is safe to run in CI or on a laptop with no database, which makes it handy for spotting forked histories.

A head is a revision that no other revision lists as its down_revision, in other words the newest tip of a chain. In a linear history there is exactly one head, and head is shorthand for it.

List them with alembic heads. If two developers create migrations from the same parent, you end up with two heads, and alembic upgrade head fails with a "Multiple head revisions" error.

Fix it with alembic merge, or upgrade to heads if the branches are truly independent.

The simplest way is the sqlalchemy.url key in alembic.ini. That works for local work, but committing real credentials is a bad idea, so most projects override it in env.py.

import os
config = context.config
config.set_main_option(
    "sqlalchemy.url", os.environ["DATABASE_URL"]
)

A third option is a custom command-line argument read with context.get_x_argument(as_dictionary=True):

alembic -x db=staging upgrade head

Remember that set_main_option goes through configparser, so escape any % in the URL as %%.

Whichever option you pick, keep one source of truth. Mixing an ini value and an environment variable is how migrations end up running against the wrong database.

op (from alembic import op) is a proxy to the Operations object bound to the migration currently running. Every schema change inside upgrade() and downgrade() is a method call on it.

The same call renders different SQL for different databases, because Alembic picks the dialect from the connection. op.get_bind() returns the active connection when you need to query data.

Call op.create_table() in upgrade() with the table name and sa.Column objects, and mirror it with op.drop_table() in downgrade().

def upgrade():
    op.create_table(
        "orders",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("user_id", sa.Integer, sa.ForeignKey("users.id"), nullable=False),
        sa.Column("total", sa.Numeric(10, 2), nullable=False),
        sa.Column("created_at", sa.DateTime, server_default=sa.func.now()),
    )
    op.create_index("ix_orders_user_id", "orders", ["user_id"])

def downgrade():
    op.drop_index("ix_orders_user_id", table_name="orders")
    op.drop_table("orders")

Give constraints and indexes explicit names. Unnamed ones get database-generated names that differ between backends and are painful to drop later.

If you generated the migration with --autogenerate, the same create_table call appears automatically, so this is mainly useful when you write or fix a migration by hand.

Autogenerate compares the MetaData in your models (target_metadata) with the schema reflected from the live database, then writes the differences as op calls into a new revision file.

1. Alembic runs env.py and connects to the database.
2. It reflects the existing tables, columns, indexes and foreign keys.
3. It diffs them against target_metadata.
4. It renders the diff as Python in upgrade() and the inverse in downgrade().

The output is a draft. Always read it, because renames show up as a drop plus an add, and some changes are not detected at all. The database must be at the latest revision first, otherwise Alembic refuses to autogenerate.

alembic upgrade head
alembic revision --autogenerate -m "add phone to users"

Autogenerate is good at structure but blind to anything it cannot reflect or infer. Treat the generated file as a starting point.

Server defaults are only compared when compare_server_default=True is set. If you rely on any item in the right column, write those migrations by hand.

Set target_metadata to the MetaData object your models are registered on, then pass it to context.configure(). Autogenerate uses it as the 'desired' schema.

from myapp.db import Base
import myapp.models  # noqa: registers all model classes

target_metadata = Base.metadata

context.configure(
    connection=connection,
    target_metadata=target_metadata,
)

The most common mistake is forgetting to import the model modules. An unimported model is not on the metadata, so autogenerate thinks the table should be dropped. With several metadata objects, target_metadata can be a list.

If it is left as None, autogenerate produces nothing useful.

Online mode connects to the database and executes the migration. Offline mode never connects; it emits the SQL as text, usually to a file or stdout, so a DBA can review and run it.

Offline mode uses literal_binds=True so values are written into the SQL instead of bound parameters. Anything in a migration that queries data, such as op.get_bind().execute(select...), cannot work offline.

Add --sql to upgrade or downgrade. Alembic runs in offline mode and prints the SQL, including the alembic_version updates.

alembic upgrade head --sql > release.sql
alembic upgrade ae10:head --sql > patch.sql
alembic downgrade head:ae10 --sql > rollback.sql

Without a database Alembic cannot read the current revision, so a plain upgrade head --sql starts from base. For an incremental script, give an explicit start:end range.

This is the usual route when production changes must pass through a DBA or a change-approval process.

A direct add_column(..., nullable=False) fails on a populated table because existing rows have no value. Use a three-step approach.

1. Add the column as nullable (or with a server_default).
2. Backfill existing rows with an UPDATE.
3. Alter the column to nullable=False.

def upgrade():
    op.add_column("users", sa.Column("status", sa.String(10), nullable=True))
    op.execute("UPDATE users SET status = 'active'")
    op.alter_column("users", "status", existing_type=sa.String(10), nullable=False)

On big tables, run the backfill in batches to avoid long locks. Supplying server_default is shorter when a constant value is fine, and you can drop the default afterwards if the app should always provide the value.

SQLite supports only a few ALTER TABLE forms. It cannot drop a column in older versions, change a type, or add a constraint to an existing table. op.batch_alter_table() works around this with move and copy.

1. Create a new temporary table with the desired structure.
2. Copy the data across.
3. Drop the old table.
4. Rename the new table to the original name.

with op.batch_alter_table("users") as batch_op:
    batch_op.alter_column("age", type_=sa.BigInteger)
    batch_op.drop_column("legacy_flag")

Set render_as_batch=True in context.configure() so autogenerate emits batch blocks automatically. Unnamed constraints need a naming_convention, otherwise batch mode cannot refer to them. On other databases, batch mode just runs ordinary ALTER statements.

Use op.execute() with a SQL string or a SQLAlchemy construct. It works in both online and offline modes.

from sqlalchemy import text

def upgrade():
    op.execute("UPDATE orders SET status = 'new' WHERE status IS NULL")
    op.execute(text("CREATE VIEW active_users AS SELECT * FROM users WHERE active"))

def downgrade():
    op.execute("DROP VIEW active_users")

Views, triggers, functions and CHECK constraints are typical reasons for raw SQL, since autogenerate cannot produce them. For bulk inserts of static data, op.bulk_insert() is cleaner. Avoid string-formatting user values into SQL; use bound parameters through text() and op.get_bind() when values vary.

Treat data changes as separate, small and repeatable. The biggest rule: do not import your application's ORM models into a migration. Models change over time and would break old revisions.

Define a lightweight table in the migration itself:

users = sa.table("users", sa.column("id", sa.Integer), sa.column("email", sa.String))

def upgrade():
    conn = op.get_bind()
    conn.execute(
        users.update().values(email=sa.func.lower(users.c.email))
    )

• Keep schema and data steps in separate revisions when possible.
• Batch updates on large tables, for example by primary key ranges.
• Write a real downgrade() or document why it is impossible.
• Use op.bulk_insert() for seed or lookup rows.

alembic stamp <rev> writes a revision ID into alembic_version without running any migration. It tells Alembic: 'treat this database as already at this revision'.

• Adopting Alembic on a database that already has the schema: alembic stamp head after writing a baseline migration.
• Recovering after a manual fix or a failed run left the table out of sync.
• Marking a freshly built database (via create_all()) as current.

alembic stamp head
alembic stamp ae1027a6acf
alembic stamp --purge base

Use it carefully. Stamping the wrong revision makes Alembic skip or repeat migrations. --purge clears existing version rows first, which helps when the stored ID no longer exists.

Alembic accepts shortcuts so you rarely type a full 12-character ID.

alembic upgrade +2
alembic downgrade -1
alembic upgrade ae10+1

If a prefix matches more than one revision, Alembic reports it as ambiguous and you need to type more characters.

Commands that target a single head stop with an error such as Multiple head revisions are present for given argument 'head'. Alembic cannot guess which branch should be the main line.

It usually happens when two developers each create a migration from the same parent on separate Git branches. After merging, the chain forks into two tips.

• alembic heads lists the tips.
• alembic upgrade heads applies every branch.
• alembic merge heads -m "merge" joins them into one head.

Many teams add a CI check that fails when alembic heads returns more than one line, so the fork is caught before release.

Create a merge revision that lists all current heads as parents:

alembic merge heads -m "merge feature branches"
# or name them explicitly
alembic merge -m "merge" b7c2d4e8f901 a1b2c3d4e5f6

The new file has a tuple down_revision and usually empty upgrade() and downgrade(). After that, the history has a single head again and alembic upgrade head works.

revision = "c9d1e3f50a12"
down_revision = ("b7c2d4e8f901", "a1b2c3d4e5f6")

Before merging, check that the two branches do not touch the same table or column in conflicting ways. Alembic only joins the graph; it does not reconcile conflicting schema changes.

Flask-Migrate is a thin Flask extension by Miguel Grinberg that wraps Alembic for Flask-SQLAlchemy projects. Alembic does the real work; Flask-Migrate wires it into Flask's app context and CLI.

Flask-Migrate reads the database URL and metadata from your Flask config, so you skip most of the manual env.py work. The migration files themselves are standard Alembic revisions.

FastAPI has no built-in migrations, so Alembic is added as a separate tool next to the app. It only needs your SQLAlchemy Base.metadata and a database URL.

1. Run alembic init alembic in the project root.
2. In env.py, import your Base and all model modules, and set target_metadata = Base.metadata.
3. Load the URL from your settings object instead of alembic.ini.
4. Generate and apply with alembic revision --autogenerate and alembic upgrade head.

from app.core.config import settings
from app.db.base import Base  # imports every model

config.set_main_option("sqlalchemy.url", settings.DATABASE_URL)
target_metadata = Base.metadata

If the app uses an async driver such as asyncpg, initialise with -t async. Run upgrade head as a deploy step, not inside the FastAPI startup event, so several workers do not race.

Django migrations are built into Django's ORM and track app models automatically. Alembic is a standalone tool for SQLAlchemy, with more manual control.

Django keeps a per-app history and builds model state from the migration files. Alembic keeps one global chain, which is simpler but gives you more room to make mistakes in env.py.

Constraints you do not name get database-generated names, such as users_email_key on PostgreSQL, and those differ per backend. Dropping or altering them later then needs guesswork. A naming_convention on MetaData gives every constraint a predictable name.

from sqlalchemy import MetaData
convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}
Base = declarative_base(metadata=MetaData(naming_convention=convention))

Autogenerate then writes explicit names into migrations, op.drop_constraint() works reliably, and SQLite batch mode can find constraints. Set it before your first migration; changing it later means renaming existing constraints.

Write the rename by hand with op.alter_column(..., new_column_name=...). Autogenerate would instead emit a drop and add, which destroys the data in that column.

def upgrade():
    op.alter_column(
        "users", "fullname",
        new_column_name="full_name",
        existing_type=sa.String(100),
    )

def downgrade():
    op.alter_column(
        "users", "full_name",
        new_column_name="fullname",
        existing_type=sa.String(100),
    )

MySQL needs existing_type because its syntax re-declares the column. For a live system with older app versions still running, use expand and contract: add the new column, copy data, switch the code, then drop the old column in a later release.

Both are context.configure() options that tell autogenerate to also look at column types and server defaults, not just presence of columns.

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    compare_type=True,
    compare_server_default=True,
)

Server default comparison can produce false positives because databases normalise default expressions, for example now() versus CURRENT_TIMESTAMP. Review the output carefully or supply a custom comparison function.

Turning on compare_type is a good habit for new projects, since silent type drift between models and the database otherwise goes unnoticed until a query fails.

Pass an include_object function to context.configure(). Alembic calls it for each table, column, index and constraint, and anything for which it returns False is skipped.

def include_object(obj, name, type_, reflected, compare_to):
    if type_ == "table" and name in {"spatial_ref_sys", "celery_taskmeta"}:
        return False
    return True

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    include_object=include_object,
)

This is useful for tables owned by extensions or other services, which would otherwise be proposed for dropping because they are not in your metadata. A lighter option is include_name, which filters by name before reflection.

There are two common patterns. The built-in multidb template keeps one revision chain for several databases; separate configs keep one chain per database.

• multidb template: alembic init -t multidb, with databases = engine1, engine2 in alembic.ini. Revisions contain upgrade_engine1(), upgrade_engine2() and so on.
• Separate environments: one folder and ini per database, selected with alembic -c db2.ini upgrade head, or sections picked with -n.

Use the multidb template when the databases always move together. Choose separate chains when they are owned by different services, because their release cycles will differ.

All three apply versioned schema changes, but they come from different ecosystems. Flyway and Liquibase are JVM tools built around SQL or XML/YAML changelogs. Alembic is Python-first and ties into SQLAlchemy.

Pick Alembic when your models are in SQLAlchemy and you want migrations in the same language. Pick Flyway or Liquibase for polyglot teams or when SQL is the source of truth.

alembic upgrade head loads the config, reads the revision scripts, runs env.py, and applies each pending revision inside a transaction when the database supports it.

sequenceDiagram
    participant CLI as alembic CLI
    participant SD as ScriptDirectory
    participant ENV as env.py
    participant DB as Database
    CLI->>SD: read alembic.ini, load versions/
    SD-->>CLI: revision graph
    CLI->>ENV: run env.py
    ENV->>DB: connect, begin transaction
    DB-->>ENV: current rev from alembic_version
    loop each pending revision
        ENV->>DB: run upgrade()
        ENV->>DB: update alembic_version
    end
    ENV->>DB: commit

1. The CLI parses alembic.ini and resolves script_location.
2. ScriptDirectory reads files in versions/ and builds the revision map from down_revision links.
3. env.py runs, creates the engine and calls context.configure().
4. The MigrationContext reads the current revision and computes the path to the target.
5. Each upgrade() runs and the version row is updated.
6. The transaction commits.

A failure rolls back the whole run on PostgreSQL. On MySQL, DDL commits implicitly, so earlier steps stay applied.

Autogenerate is a pipeline: reflect, compare, convert, render. It lives in alembic.autogenerate and the public entry point for the comparison step is compare_metadata().

flowchart LR
    A["Models: target_metadata"] --> C[compare_metadata]
    B["Live DB: Inspector reflection"] --> C
    C --> D["Diff tuples: add_table, remove_column ..."]
    D --> E["MigrationScript directives"]
    E --> F["Render to Python op calls"]
    F --> G["New revision file"]

1. SQLAlchemy's Inspector reflects tables, columns, indexes and foreign keys from the database.
2. Alembic compares them with target_metadata and produces diff tuples such as ('add_column', ...).
3. The diffs become directive objects (UpgradeOps, DowngradeOps).
4. Renderers turn directives into op.* source code, which is placed into script.py.mako.

You can modify the directives before rendering with process_revision_directives, for example to skip empty migrations:

def process_revision_directives(context, revision, directives):
    script = directives[0]
    if script.upgrade_ops.is_empty():
        directives[:] = []

Alembic's history is a graph, not just a line. Branch labels name independent lines of migrations, and depends_on adds a cross-branch ordering rule without making one the parent of the other.

alembic revision -m "plugin base" --head=base --branch-label=plugin_a
alembic upgrade plugin_a@head
alembic revision -m "needs core" --head=plugin_a@head --depends-on=ae1027a6acf

• --head=base starts a new, separate branch.
• --branch-label gives it a name you can use as label@head.
• depends_on = "ae1027a6acf" inside a revision says it must run after that revision, even in another branch.

This suits plugin-style projects where each module owns its tables but may depend on a core schema. Use version_locations to store each branch's files in its own folder.

Initialise with the async template, which provides an env.py that creates an AsyncEngine and runs the sync migration code inside run_sync.

alembic init -t async alembic

from sqlalchemy.ext.asyncio import async_engine_from_config
import asyncio

def do_run_migrations(connection):
    context.configure(connection=connection, target_metadata=target_metadata)
    with context.begin_transaction():
        context.run_migrations()

async def run_async_migrations():
    engine = async_engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
    )
    async with engine.connect() as conn:
        await conn.run_sync(do_run_migrations)
    await engine.dispose()

asyncio.run(run_async_migrations())

The URL must use an async driver, for example postgresql+asyncpg://. The migration scripts themselves stay synchronous, since Alembic's op calls are not coroutines.

This error means the database's alembic_version holds an ID that Alembic cannot find in the versions/ folder. The database and the code are out of step.

1. Run alembic current and note the ID.
2. Search versions/ for it. A missing file is the usual cause: a deleted or unmerged migration, or a branch not checked out.
3. Run alembic heads and alembic history to see what the code knows.
4. Confirm you are pointed at the right database; staging and production IDs get mixed up often.

Fixes depend on the cause. Restore or merge the missing file if the revision was real. If the ID is truly stale, correct the pointer:

alembic stamp --purge head   # only if the schema really matches head

Never stamp blindly. Compare the actual schema first, or you will skip migrations.

Long locks come from DDL that rewrites a table or waits behind running queries. Plan each operation so it holds the lock only briefly.

• Indexes: use CREATE INDEX CONCURRENTLY, which cannot run inside a transaction.
• Lock timeout: set lock_timeout so the migration fails fast instead of blocking traffic.
• Backfills: update in small batches, not one huge statement.
• Constraints: add as NOT VALID and VALIDATE later.

def upgrade():
    op.execute("SET lock_timeout = '5s'")
    with op.get_context().autocommit_block():
        op.create_index(
            "ix_orders_created_at", "orders", ["created_at"],
            postgresql_concurrently=True,
        )

autocommit_block() pauses the migration transaction so the concurrent index build is allowed. Adding a nullable column without a default is a quick, metadata-only change.

Treat migrations as a controlled release step: validate them on every pull request, then apply them once per deploy, before the new code starts.

1. Check one head: fail if alembic heads lists more than one.
2. Check drift: alembic check fails if models and migrations disagree.
3. Test the chain: alembic upgrade head on an empty database, plus a downgrade test where it matters.
4. Deploy: run alembic upgrade head as a single job, not on every app instance.
5. Back up before risky changes in production.

Write migrations to be backward compatible (expand then contract), so the old app version keeps working while the new schema rolls out. For approval workflows, generate SQL with --sql and attach it to the release.

Transactional DDL means schema changes such as CREATE TABLE can be rolled back like normal data changes. PostgreSQL supports it, so a failed migration leaves no partial changes. MySQL commits DDL implicitly, so a failure can leave the schema half-migrated.

Alembic checks the dialect's transactional_ddl flag and wraps the run in one transaction when it is supported. By default all pending revisions share a single transaction. Set transaction_per_migration=True in context.configure() to commit after each revision instead.

On non-transactional databases, keep each migration small, and be ready to fix state with alembic stamp.