Home/Methods

Engineering · Databases

SQLite and database traps in small production apps

The database traps that hurt small production apps are not about scale: a long read on SQLite blocks every writer, a filter on an encrypted column silently matches nothing, DISTINCT fails on a table with a json column, commit() and rollback() expire ORM objects even inside the error handler, and a fixed epsilon is too small for a float32 column. Each one passes a quick test and fails in production, usually without a clear error.

Updated 2026-10-11

Which database traps hit small production apps

The most common database traps in a small production app are not about scale. A long read on SQLite blocks every writer, a WHERE on an encrypted column silently matches nothing, SELECT DISTINCT fails on a table that has a json column, ORM objects expire after commit() and rollback() (including inside the error handler), and a fixed epsilon guard is too small for a float32 column. Each one passes a quick test and fails in production, usually without a clear error.

We run small apps on SQLite and Postgres behind Python services that use SQLAlchemy. Each trap below cost us an incident or a wrong report. For each one we give the cause, how it showed up, and the rule we follow now.

Trap 1: a read-only query on SQLite can stop production

On SQLite in the classic rollback-journal mode, a reader holds a shared lock for as long as its query runs, and no writer can commit until the reader finishes. On Postgres a long SELECT does not block writers; on SQLite outside WAL (write-ahead logging) mode it can, every time.

We learned this during an investigation that had an explicit read-only mandate. Two of our AI agents wrote a verification script and ran a long query against the live database file of a warehouse app. The database was not in WAL mode. While the query ran, the endpoint a warehouse station uses to claim a batch of work answered HTTP 500 "database is locked", and the station could not load its batch. Another session noticed, stopped the process, made a backup and switched the database to WAL permanently.

The rule now. Never investigate on a live production SQLite file. Make a consistent copy with the online backup API (sqlite3 live.db ".backup copy.db", or connection.backup() in Python) and work on the copy; the same API produces our nightly backups, because a plain file copy is not guaranteed to be consistent while the database is being written. If the live file must be read, open it read-only (?mode=ro) and check PRAGMA journal_mode first; if it is not wal, stop and ask.

Trap 2: an encrypted column cannot be filtered in SQL

When a column is encrypted in the application with a random initialisation vector (IV), the same plain text produces a different ciphertext every time. A SQL filter such as WHERE zip = '-' compares against ciphertext and can never match. The query does not fail; it returns zero rows, and zero rows looks exactly like "there are no such cases".

We widened a selector to catch placeholder postal codes. The syntax passed, the run passed, and it reported 21 extra orders. The filter could never have worked: the postal code was encrypted in 19,390 of 19,549 rows over 7 days, and the extra orders were only the rows where the column was NULL. We confirmed the encryption was random rather than deterministic by checking that the most frequent ciphertext values each appeared once.

The rule now. Before any WHERE on an address, phone or email field, check the model to see whether the column is an encrypted type. If it is, filter in application code after the ORM has decrypted the value, or use a blind index where one exists: a separate column holding a keyed hash of the normalised value, which supports equality lookups without revealing the value. Or normalise at entry, while the value is still plain text: we put the rule "a postal code with no digit is filler" in the webhook handler.

Trap 3: DISTINCT on an entity with a json column fails

In Postgres, SELECT DISTINCT needs an equality operator for every selected column, and the json type has none (jsonb does). An ORM query that selects a whole entity selects every column, so select(Order).join(LineItem).where(...).distinct() fails with could not identify an equality operator for type json whenever the orders table has any json column, whatever the WHERE clause says.

The message names neither the column nor DISTINCT, and the join looks like the obvious culprit, since a product can appear on several lines of one order and that is why you asked for distinct rows in the first place. Calling .unique() on the result does not help: it removes duplicates in Python after the SQL has already failed.

The rule now. Select the ids first, since DISTINCT on one scalar column works. Remove duplicates in Python while keeping order (dict.fromkeys(ids)), then load the entities by id in batches with IN (...), eager-loading the relationships you need.

Trap 4: commit() and rollback() expire your objects, even in the error handler

In SQLAlchemy, by default, every object in a session is expired after commit() and after rollback(), and the next attribute access reloads it from the database. In an async session that implicit reload is not allowed and raises MissingGreenlet. A loop that commits once per item therefore works on the first item and fails from the second, even when the relationships were eager-loaded.

The worse part is the error handler. Our cleanup loop logged the order's name in its except block. That attribute had expired too, so the handler raised a second exception from inside except and ended the whole run. The logs showed two tracebacks stuck together and no message of ours, and the effect was the opposite of the function's purpose: the tickets it was meant to close stayed open indefinitely.

The same trap returned with rollback(). A job that marks cash-on-delivery orders as paid hit an expected error, rolled back, then read the order's name for the log. One session was shared by every store in the loop, so the error stopped every store. For 4 days no cash-on-delivery order was marked paid anywhere in the fleet, with a single warning per run.

The rule now. In a loop that commits per item, do not hold ORM objects across the commit. Select the ids and the plain values needed for logging up front, reload each object fresh inside the iteration, and in except use only those plain values, never attributes of the object that just failed. Write with UPDATE ... WHERE id where possible, and give each independent unit (a store, a tenant) its own session, so one failure cannot stop the others.

Trap 5: a fixed epsilon calibrated for the wrong float

A rounding guard such as ceil(x - 1e-9) is correct for a float64 value, whose error near one is around 2e-16. It is wrong for a value read from a Postgres real column, which is float32: 0.1 comes back as 0.10000000149011612, ten units give 1.0000000149, and that error of 1.5e-8 is 15× the guard. The ceiling is then 2, not one.

We found it when an order for a single box left with two shipping labels. Parcels-per-unit densities were stored in a real column, and the guard's comment said, correctly, that it existed to turn ten times 0.1 into one. That was true for the literal in the test and false for the value that actually arrived. Measured across the real map of 939 densities and quantities from 1 to 60, 849 pairs on 136 products came out with an extra parcel, all at the round quantities in which sets are sold, which is exactly where people order. Each one meant shipping an empty box.

The guard also has to be relative. A fixed threshold fails as the number grows: 0.3 as float32 was off by 6e-7 at 50 units, so 15.0000006 became 16. Rounding to six decimals fails the same way. We now take the tolerance from the error of the source (a real or float4 column, a numpy.float32, JSON from a system written in C), make it relative, for example max(1e-9, abs(x) * 1e-6), and keep one shared guard: the same guard existed in three copies and only one had been fixed when the bug was found.

Where we were wrong

In every case something reassured us: a read-only mandate, a run that returned rows, a comment explaining the guard, eager loading that should have made the attribute available. The reassurance is what stopped us checking. A query that returns zero rows, or a handful, is not evidence that a filter works; run the filter against a case you know exists.

Our first verification of the float fix rewrote the rounding inside the test and confirmed a fix that was still wrong in 10 cases. A test must execute the function from the file that ships, not a copy of it.

Limits of these rules

These rules come from one stack: SQLite, Postgres and SQLAlchemy with async sessions. Other ORMs expire objects differently and other databases lock differently, so check the behaviour of yours.

Checklist

1. SQLite. Investigate on a copy made with the backup API; check PRAGMA journal_mode before touching a live file.

2. Encryption. Check the column type before any WHERE on personal data; filter after decryption, through a blind index, or at entry.

3. DISTINCT. Select ids, remove duplicates in Python, load entities in batches.

4. Sessions. No ORM objects across commit() or rollback(); plain values in except; one session per independent unit.

5. Floats. Take the tolerance from the source type and make it relative; one shared guard, tested from the shipped file.

Questions and answers

Why does a read-only query cause 'database is locked' on SQLite?

In SQLite's default rollback-journal mode, a reader holds a shared lock for as long as its query runs, and writers cannot commit until it is released. A long read-only query on a production file therefore blocks every write, and the application returns 'database is locked'. WAL mode lets readers and a writer work at the same time, but investigation is still safer on a copy made with the online backup API.

Why does my WHERE clause on an encrypted column return no rows?

If the application encrypts a column with a random initialisation vector, the same value is stored as different ciphertext every time, so comparing it with plain text in SQL can never match. The query does not fail; it returns zero rows, which looks like 'no such cases'. Filter in application code after decryption, use a blind index column that stores a keyed hash of the normalised value, or normalise the value at entry.

How do I fix 'could not identify an equality operator for type json' with DISTINCT?

Postgres defines no equality operator for the json type, and SELECT DISTINCT compares every selected column, so selecting a whole entity that has a json column with DISTINCT fails. Select only the ids with DISTINCT, which works on a scalar column, remove duplicates in Python, then load the entities by id in batches. Calling unique() on the result does not help, because the SQL has already failed.

Why does SQLAlchemy raise MissingGreenlet after commit() in a loop?

By default SQLAlchemy expires every object in the session after commit() and rollback(). In an async session the next attribute access would need an implicit reload, which is not allowed, so it raises MissingGreenlet. Select ids and plain values before the loop, reload each object inside the iteration, and use only plain values in the except block, otherwise the error handler itself can raise and stop the run.

What epsilon should I use when rounding values from a float32 column?

Take the tolerance from the source type, not from the literal in your test. A Postgres real column is float32, so 0.1 is read back with an error around 1.5e-8, far above an epsilon of 1e-9 chosen for float64. Use a relative tolerance such as max(1e-9, abs(x) * 1e-6), because any fixed threshold fails as the value grows, and keep a single shared guard function.

All methods