agent's migration config pointed at sqlite while production runs postgres - the migration passed locally and failed...

TL;DR

Sqlite and postgres are different databases with different DDL, types, and transactional behavior: a migration green on sqlite proves nothing about postgres. Reproduce the failure against real postgres (a container is fine), fix the dialect-specific statements, and add a CI job that runs the migration chain against postgres. Local-dev convenience must never be the migration test.

The query

agent's migration config pointed at sqlite while production runs postgres - the migration passed locally and failed in prod

Use this when

• Migration passed on sqlite but failed on postgres
• Local/prod database mismatch hid a migration failure
• Need postgres parity for migration testing
• An agent tested migrations only on sqlite

Not for

• Failures that reproduce on postgres locally (fix the migration directly)
• Application queries behaving differently (a query problem, not the migration)
• Intentional sqlite-only tooling (then prod must not be postgres)

Steps

1. Reproduce against real postgres

docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=[pw] postgres:16

Point the migration config at it and run the chain from zero. Expected output: the production failure reproduces locally. Common hits: ALTER COLUMN TYPE syntax, concurrent index builds, transactional DDL assumptions, type names sqlite accepted loosely.

2. Fix the dialect differences

Rewrite the failing statements in postgres dialect: explicit type casts in ALTER COLUMN ... TYPE ... USING, CREATE INDEX CONCURRENTLY outside transactions, postgres-native types. Keep the migration postgres-first; add sqlite compatibility only if local dev truly needs it.

Expected output: the chain runs clean on postgres from zero.

3. Find every other sqlite-ism in the chain

Grep the migrations for sqlite-isms: bare ALTER TABLE ... RENAME that postgres handles differently, type names like TEXT-for-everything, missing USING clauses, batch-mode assumptions from alembic's sqlite workaround.

Expected output: a list, fixed one by one, each verified on postgres.

4. Add postgres migration testing to CI

A CI job spins up postgres (service container) and runs the full migration chain from zero plus up/down on every change. Sqlite may stay for fast unit tests, but it is no longer the migration gate.

Expected output: the next dialect bug breaks the build, not production.

5. Make the agent target-aware

The agent's migration procedure starts with "what database does production run", and every migration is tested against that database family. "It passed on sqlite" stops being evidence.

Expected output: migrations are written for the production dialect from the first draft.

Tool compatibility

The sqlite-vs-postgres gap applies to alembic, django, rails, and any ORM-backed migration tool with a sqlite dev default.

Variant phrasings

migration passed sqlite failed postgres

Steps 1 and 2: reproduce on real postgres, fix the dialect.

test migrations against postgres in CI

Step 4: service-container postgres running the full chain from zero.

alembic sqlite vs postgres migration differences

Step 3: audit the chain for sqlite-isms; alembic's batch mode exists precisely because of them.

Why it happens

Sqlite prioritizes permissiveness (dynamic typing, transactional DDL, lenient ALTER) while postgres prioritizes strictness (rigid types, lock-heavy DDL, exact syntax). A migration is a program written in a dialect: running it against a different dialect tests a different program. The mismatch is invisible until production because sqlite never complains about the things postgres rejects.

Edge cases

• Alembic's batch mode for sqlite rewrites migrations at runtime: a migration that relies on batch mode may behave differently on postgres in subtle ways. Read the rendered SQL.
• Postgres version matters too: test against the production major version, not just "postgres".
• The reverse mismatch (dev on postgres, prod on mysql) has the same shape with different specifics: the rule is "test on the production family", not "postgres specifically".

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_8PfyPEZ6BkkE1i5IGaZG2w