Why can a Django project whose tests all pass on SQLite still break when it runs on PostgreSQL or MySQL in production?
answer
- same ORM, different engines underneath
- string matching and case
- decimals stored as floats
- locks that silently do nothing
- schema changes without transactions
basics
~20 sThe ORM hides SQL syntax, not engine behaviour: SQLite matches strings differently, stores decimals as floats, ignores select_for_update(), rebuilds tables for schema changes and serialises writers, so code tested only on SQLite can fail or change results on PostgreSQL or MySQL.
solid answer
~40 sDjango's ORM translates queries per backend, but each engine keeps its own semantics. On SQLite, `contains` is case-insensitive and `iexact` is case-sensitive for non-ASCII text, `DecimalField` values are stored as floating point, and `select_for_update()` has no effect, so race conditions never show up in tests. PostgreSQL enforces column types and lengths strictly, supports `distinct(*fields)` that other backends reject with `NotSupportedError`, and runs many concurrent writers where SQLite raises `database is locked`. MySQL's default `utf8mb4_0900_ai_ci` collation makes string comparisons and unique constraints case-insensitive, and its schema changes are not transactional, so a failed migration leaves a half-applied state. The fix is to run the suite, and ideally development, on the production engine.
go deeper
Recall that SQLite, PostgreSQL and MySQL behave differently even though the Django code is identical, so tests should run on the production database.
Name concrete differences: string matching and collation, decimal storage, select_for_update having no effect, schema changes, and concurrent writers.
Show how each difference becomes an incident, such as a race hidden by SQLite or a half-applied MySQL migration, and how CI on the real engine prevents it.
Own the decision to standardise development and CI on the production engine and budget for the tooling that makes it painless.
## The ORM is not a database abstraction layer for behaviour Django's ORM compiles one `QuerySet` into the SQL dialect of the configured backend. That hides **syntax**, but not **semantics**: how strings compare, how numbers are stored, how locks and concurrent writers behave, and how schema changes are applied all come from the engine. A test suite that runs on SQLite proves the code works on SQLite. ## Differences that bite, by area | Area | SQLite | PostgreSQL | MySQL / MariaDB | |---|---|---|---| | `contains` | Case-insensitive | Case-sensitive | Follows the column collation | | `iexact` with non-ASCII text | Behaves like `exact` | Case-insensitive | Follows the collation | | Plain string equality | Case-sensitive | Case-sensitive | Case-insensitive (`utf8mb4_0900_ai_ci`) | | `DecimalField` storage | Floating point (`REAL`) | Exact numeric | Exact numeric | | `select_for_update()` | No effect | Row locks | Row locks | | `distinct("field")` | `NotSupportedError` | Supported | `NotSupportedError` | | Concurrent writers | One at a time; `database is locked` | Many | Many | | Schema changes | Table rebuild emulated by Django | Transactional DDL | Not transactional | ## How each difference turns into a production bug - **String matching.** A search test asserting that `filter(name__contains="aa")` finds `"Aabb"` passes on SQLite and fails on PostgreSQL, where `contains` is case-sensitive and you needed `icontains`. The reverse happens for non-ASCII names with `iexact`. - **Case-insensitive uniqueness on MySQL.** With the default collation, `"Fred"` and `"freD"` are equal, so a `unique=True` username column rejects the second one; SQLite and PostgreSQL accept both. - **Money arithmetic.** SQLite stores decimals as 8-byte floats, so arithmetic done in SQL (an annotated sum, a filter on a computed total) is not correctly rounded. Assertions written against SQLite's results can encode those rounding artefacts, and the exact numeric types on PostgreSQL or MySQL then produce different values. - **Locks that were never tested.** `select_for_update()` is silently ignored on SQLite, so a double-booking race protected by it looks fine in tests and only the real engine proves the lock placement right, or wrong. - **Length and type enforcement.** SQLite does not enforce a `CharField`'s declared length at the column level; PostgreSQL rejects an over-long value with a `DataError`, often from a code path that skipped form validation. - **PostgreSQL-only features.** `distinct(*fields)`, `django.contrib.postgres` fields and lookups raise `NotSupportedError` or fail at migration time elsewhere. - **Migrations.** Django emulates `ALTER TABLE` on SQLite by creating a new table, copying rows, dropping and renaming. On MySQL a failed migration cannot roll back its DDL, leaving the schema half-changed; PostgreSQL wraps it in a transaction. - **Concurrency.** SQLite allows one writer at a time; under load you see `OperationalError: database is locked`, which the `timeout` option only delays. ## What to do about it 1. **Test on the production engine.** Point the test settings at PostgreSQL (or MySQL) in CI; a throwaway server in a container is cheap. 2. **Develop on it too** where practical, so query plans, locking and errors look like production. 3. **Write tests that exercise engine behaviour**: a concurrent booking test, a case-sensitivity search test, a decimal rounding test. 4. **Keep SQLite for what it is good at**: quick prototypes, embedded or read-mostly tools, and unit tests of code that genuinely never touches engine-specific behaviour. ## A checklist before trusting a SQLite-green suite - Does any query use `contains`, `iexact` or `startswith` on user-entered text? Test it with mixed case and non-ASCII input on the real engine. - Does any code rely on `select_for_update()`, `unique=True` on text, or a `DecimalField` computed in SQL? Those are the places where SQLite gives a different answer or no protection. - Do migrations include data migrations or large table changes? Run them against a production-sized copy of the target engine, not only against the test database. - Does anything use `django.contrib.postgres` or `distinct(*fields)`? Then the suite cannot be meaningful on SQLite at all. ## MySQL has its own traps Django sets MySQL connections to **read committed** rather than MySQL's default repeatable read, because repeatable read can make `get_or_create()` raise `IntegrityError` for a row a later `get()` cannot see. Overriding `isolation_level` in `OPTIONS` to repeatable read reintroduces that behaviour.
- A booking view uses select_for_update() and passes a concurrency test on SQLite; why is that test worthless?SQLite does not support `SELECT ... FOR UPDATE`, and Django documents that calling `select_for_update()` there has no effect. SQLite also serialises writers, so the race the lock guards against cannot even occur. Only a test against PostgreSQL or MySQL, with two real transactions, shows whether the lock is taken in the right place.
- Why can a unique username column accept 'Fred' and 'fred' on PostgreSQL but not on MySQL?With a UTF-8 database, MySQL's default collation `utf8mb4_0900_ai_ci` compares strings case-insensitively, so the two values are equal and violate the unique index. PostgreSQL compares strings case-sensitively by default. Choose the rule deliberately: a case-sensitive collation on MySQL, or a case-insensitive constraint on PostgreSQL.
Rehearsing on SQLite and performing on PostgreSQL is like practising a speech in an empty room and delivering it in a full hall: the words are the same, but the echo, the interruptions and the timing are not.
saying these in an interview costs you the question
- The ORM guarantees identical behaviour on every backend
- select_for_update() raises an error on SQLite so you would notice
- SQLite stores DecimalField values as exact decimals
- Failed migrations roll back cleanly on MySQL
- database is locked is fixed by raising the timeout option