SQLAlchemy Session and Alembic Migrations: Can rollback() Safely Recover After a Failed Schema Change?

Short answer

Call session.rollback() to clear the failed transaction state — that part is mandatory — but then discard the Session and start a new one rather than continuing with the same mapped objects. Whether rollback() actually reverts the schema depends entirely on the backend, and the ORM's identity map has no way to know which case it is in. Discarding the Session is the only recovery path that behaves identically on PostgreSQL and MySQL.

Why rollback-and-continue is unsafe

The failure modes differ per dialect:

• PostgreSQL supports transactional DDL. A failed ALTER TABLE inside the migration transaction is reverted by rollback(), so the schema returns to its prior state.
• MySQL implicitly commits around most DDL. The same rollback() clears SQLAlchemy's transaction bookkeeping but leaves any already-executed schema changes in place — a silent partial migration.

Separately, after any DBAPI error SQLAlchemy 2.x requires a rollback() before the connection can be used again; otherwise the next statement raises PendingRollbackError. That rollback fixes the connection state, but it does nothing for the Session state: instances in the identity map still carry attribute snapshots taken against whatever schema existed when they were loaded, and SQLAlchemy does not revalidate them against the altered schema.

Is transactional_ddl a reliable decision input?

It is a reliable indicator of capability, not of outcome. Alembic's per-dialect transactional_ddl flag tells you whether the backend can wrap DDL in a transaction (true for PostgreSQL, false for MySQL). You can inspect it via the migration context or dialect, e.g. context.get_bind().dialect.transactional_ddl. But it cannot make MySQL transactional, and even on PostgreSQL it does not tell you whether your specific failed statement was inside the transaction that got rolled back. Treat it as documentation of why behavior differs, not as a runtime switch that makes in-place rollback safe.

Is expiring instances enough on PostgreSQL?

Not reliably. session.expire_all() (or expire() on affected classes) forces attributes to reload on next access, which refreshes column values — but it assumes the objects' identity keys and the table structure they map to are still valid. A failed migration may have dropped or renamed columns mid-flight before the rollback, and any flush attempted during the failure may have left the Session in a questionable state. Expire handles stale data; it does not prove structural consistency. Since a fresh Session costs almost nothing, expiring is an optimization with no real payoff.

Recommended recovery pattern

try:
    run_migration()  # alembic upgrade, or ORM DDL work
except Exception:
    session.rollback()   # required: clears PendingRollbackError state
    session.close()      # release connection, drop identity map
    session = Session()  # fresh session for any recovery/logging work
    raise

Keep migration connections and application Sessions separate where possible, so a failed migration never shares an identity map with live ORM objects.

Verifying the behavior yourself

Because DDL transactional behavior is backend- and version-specific, confirm on your actual stack:

1. On PostgreSQL: run a migration that alters a table then fails; call rollback(); confirm via information_schema.columns that the column is absent.
2. On MySQL: repeat and confirm the column persists after rollback.
3. After a deliberate DBAPI error, issue a SELECT without rolling back and confirm PendingRollbackError is raised.

One caveat: exact implicit-commit behavior on MySQL varies by statement and server version, so verify against your target version rather than assuming.