SQLite doesn't enforce foreign keys by default, and it cost us three bugs

Our test suite runs on SQLite. Production runs on PostgreSQL. That is a common setup and for a long time it felt free: tests were fast, isolated, and green.

Over about three weeks we hit three separate bugs that all came from the same gap. None of them were caught by 1200+ passing tests, because the tests were running on an engine that quietly forgives what production rejects.

Here they are, in the order we found them.

1. Two tests that were red in production and green locally

We were making a schema change, so we ran the suite against a real Postgres instance for once. Two tests failed. On SQLite the same commit was 1241/1241 green.

First reaction: today's change broke something. We stashed it and re-ran against a fresh Postgres database. The same two tests failed. So they had been broken for a while and nobody had seen it.

Both were test bugs, not product bugs:

# To simulate "an inbox owned by someone else"
other_user_id = box.user_id + 999   # a user id that does not exist

SQLite accepts that row. Postgres rejects it with inboxes_user_id_fkey. And the test was weak on top of being broken: it wanted to prove "another person's inbox is not accessible", but what it actually created was "an inbox owned by nobody".

The second one was subtler:

owner = session.query(User).filter_by(is_admin=True).first()   # no ORDER BY

An unordered .first(). SQLite happened to return the admin the test needed. Postgres returned a different admin, and the test failed looking for an inbox that belonged to someone else.

Neither bug was in the product. Both were in tests that had been passing for weeks while measuring the wrong thing.

2. A migration that could not fail locally, because it never ran locally

Later we wrote a data migration to backfill some rows. It looked fine. It passed review. Then we ran it against Postgres and it blew up:

organizations = sa.table("organizations", sa.column("name"), sa.column("region"))
res = conn.execute(organizations.insert().values(...))
org_id = res.inserted_primary_key[0]   # IndexError: tuple index out of range

sa.table() is SQLAlchemy's lightweight table construct. It has no primary key definition, so the driver never asks for the generated id and inserted_primary_key comes back empty. The fix is a real sa.Table() with an explicit primary key column.

The point is not the API detail. The point is this: our test suite does not run Alembic at all. Tests create the schema directly. So migration code the code that runs against production data, once, with no undo was the least tested code in the repository. Not under-tested. Zero lines executed.

We caught this one on a staging box with real Postgres. If we had not had one, we would have found it in production.

3. Deleted rows that came back to life

The third one was the strangest.

We changed a uniqueness check to ask the api_keys table instead of a legacy column:

if session.query(ApiKey).filter_by(inbox_id=inbox.id, revoked=False).first():
    return {"error": "This inbox already has a key."}

Suddenly, brand new inboxes started reporting that they already had a key.

The chain took a while to see:

1. api_keys.inbox_id has ondelete="CASCADE". In production Postgres, deleting an inbox deletes its key rows.
2. SQLite does not enforce foreign keys unless you turn them on. So in tests, deleting an inbox left its key rows behind as orphans.
3. SQLite reuses row ids. A newly created inbox could take the id of a deleted one, and inherit its orphaned key rows.

So a fresh inbox "already had a key" a key belonging to an inbox that had been deleted.

The old code never saw this, because the old check read a column on the inbox itself, and on a new inbox that column is NULL. The bug was not new. It became visible.

The fix, and what it does not fix

One line, in the engine setup:

@event.listens_for(engine, "connect")
def _sqlite_pragmas(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

SQLite ships with foreign key enforcement off for backwards compatibility. It has been that way for years and it is documented, but the default is silence: no warning, no error, just a database that accepts rows Postgres would refuse.

Turning it on made our tests behave like production. Case 3 disappeared. Case 1 would have been caught the day it was written.

But be clear about what one pragma does not do:

• It does not make SQLite run your migrations. Case 2 is still invisible locally.
• It does not change id allocation. SQLite still reuses ids; Postgres sequences do not.
• It does not make the two engines equivalent. It closes one specific gap.

What we changed beyond the pragma

Migrations get a real Postgres before they run anywhere else. Not the full suite, just the migration, against a database that has the production schema. Ours runs on a small staging machine. Twice now, that box caught something that SQLite structurally could not: once the migration above, once a duplicate revision id that had forked the migration tree.

"Green" now has a scope. A green suite means "green on SQLite". That sentence used to be implicit and it was doing a lot of quiet damage. Writing it down changed how we read the result.

The uncomfortable version of the lesson: for three weeks our tests were measuring a different database than the one our users touch. They were not lying. We were reading them as if they said more than they did.

We hit these while building MailFlat, an email API where agents and test suites get real inboxes. The bug journal these came from is kept per-incident, symptom first, which is why three separate weeks turned out to be one story.