Does anyone test that migration rollbacks actually preserve data (not just exit 0)?

[croc100]

One failure mode that's bitten me a few times: alembic downgrade exits 0,
no exceptions, CI goes green — but rows are silently gone. The column came
back in downgrade(); the data didn't.

The usual setup (upgrade → downgrade → check exit codes) doesn't catch this
because the SQL is valid. The schema is correct. The data is just missing.

I ended up building a pytest plugin to catch it: pytest-mrt. It seeds rows
before each migration, runs downgrade, and asserts the data survived. Also
does static analysis for the obvious patterns (drop_column in upgrade,
no-op downgrade, PostgreSQL ENUM adds that can't be rolled back, etc.)
without needing a database at all.

Curious if others have run into this class of bug and how you're handling it.
Any patterns that aren't on the list yet would be good to know about:
https://croc100.github.io/pytest-mrt/patterns/

GitHub: https://github.com/croc100/pytest-mrt

[nazeeh111]

A concrete SQLite-specific pattern worth checking is changing PRAGMA foreign_keys inside an already-open transaction. SQLite leaves the setting unchanged. If the connection started with enforcement off, a migration can appear to enable it and still commit orphan rows.

Here is a minimal reproduction using Python's standard library:

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("PRAGMA foreign_keys=OFF")
db.execute("CREATE TABLE parent(id INTEGER PRIMARY KEY)")
db.execute("CREATE TABLE child(id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES parent(id))")
db.execute("BEGIN")
db.execute("PRAGMA foreign_keys=ON")
assert db.execute("PRAGMA foreign_keys").fetchone()[0] == 0
db.execute("INSERT INTO child VALUES(1, 999)")
db.execute("COMMIT")
assert db.execute("PRAGMA integrity_check").fetchall() == [("ok",)]
assert db.execute("PRAGMA foreign_key_check").fetchall() == [("child", 1, "parent", 0)]
db.close()

I reproduced the same case with a file-backed database and reopened it before checking: the orphan persisted while integrity_check returned ok. Set and assert the intended enforcement state before BEGIN, then run foreign_key_check separately after migration. The checks serve different purposes. SQLite documents the transaction restriction and the separate referential check.

Another useful class is lost post-migration behavior. For example, removing an audit trigger preserves existing row values and passes both integrity checks, but a subsequent committed update no longer creates its required audit row. A small application operation followed by assertions on its expected side effects catches that gap; a snapshot of the starting data alone cannot.

I would also keep two test contracts separate: failure recovery within an uncommitted transaction, and reversibility after a committed upgrade. For the first, inject a failure between meaningful steps, explicitly handle rollback, reopen the database, and compare rows plus schema. For the second, assert the migration's declared preservation policy using stable keys and original values. A downgrade that recreates a dropped column cannot infer discarded values. Alembic's data-migration guidance explains why those expectations need to be migration-specific.

These reproductions used SQLite 3.50.4 through Python's sqlite3 module; they are SQLite behavior checks, not an Alembic or pytest-mrt integration test.

[croc100]

Thank you, this was genuinely useful — and it reproduces here as well, on SQLite 3.37.2 too rather than only 3.50.4.

One small thing I noticed while checking it against a stock alembic init setup (Alembic 1.20.0, SQLAlchemy 2.0.54): the pragma does still apply at the top of a migration, and only turns into a no-op once a row has been written. pysqlite defers the physical BEGIN until the first INSERT/UPDATE/DELETE, so Connection.in_transaction() reads True in both orderings and doesn't separate them.

Which is really just your "set it before BEGIN" point — in SQLAlchemy the place that reliably means that seems to be a connect listener rather than anything in the migration:

@event.listens_for(engine, "connect")
def _fk_on(dbapi_conn, _rec):
cur = dbapi_conn.cursor()
cur.execute("PRAGMA foreign_keys=ON")
cur.close()
That one held even after DML had already opened the transaction.

Thanks again for the detail, especially on integrity_check and foreign_key_check being separate concerns — that part I had wrong.

[marcuspaula-seceng]

Another silent-loss pattern for the list, specific to SQLite: table rebuilds with foreign keys enabled.

SQLite can't alter most column definitions in place, so batch-mode migrations rebuild the table (create new → copy → drop old → rename). With PRAGMA foreign_keys=ON, DROP TABLE performs an implicit DELETE, so any child table with ON DELETE CASCADE loses its rows. No error is raised, and PRAGMA integrity_check still returns ok.

import sqlite3
db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("PRAGMA foreign_keys=ON")
db.execute("CREATE TABLE parent(id INTEGER PRIMARY KEY, name TEXT)")
db.execute("CREATE TABLE child(id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE)")
db.execute("INSERT INTO parent VALUES (1,'a'),(2,'b')")
db.execute("INSERT INTO child VALUES (10,1),(11,2)")
db.execute("BEGIN")
db.execute("CREATE TABLE _parent_new(id INTEGER PRIMARY KEY, name TEXT, extra TEXT)")
db.execute("INSERT INTO _parent_new(id,name) SELECT id,name FROM parent")
db.execute("DROP TABLE parent")
db.execute("ALTER TABLE _parent_new RENAME TO parent")
db.execute("COMMIT")
print(db.execute("SELECT COUNT(*) FROM child").fetchone())  # (0,) with foreign_keys=ON, (2,) with OFF

Tested on SQLite 3.45.1. The SQLite docs describe both halves: DROP TABLE does an implicit delete when foreign keys are enabled (foreignkeys.html), and the generalized ALTER TABLE procedure starts by turning foreign keys off (lang_altertable.html). The pragma has no effect inside an open transaction, so it must be set before BEGIN. A data-preservation check like yours catches this; a schema-only check doesn't, because the rebuilt schema is correct.