Using SQLite for a PHP SaaS

What you get

SQLite runs inside the application process and stores data in a local database file. Simple Stack enables WAL mode, uses prepared queries and applies numbered migrations. That removes the need to operate a separate database server for the default deployment.

The tradeoffs

WAL supports readers alongside a writer, but SQLite still permits one writer at a time. Long transactions, heavy write traffic and filesystem choices can cause contention. There is no universal requests-per-second figure that proves your application will fit.

What to try first

Keep transactions short, index real query patterns, use consistent backups and verify restores. Load-test realistic reads and writes. Consider a server database when the concurrency, availability or multi-server requirements justify it; the included MySQL guide describes a migration task, not a one-switch completed port.

SQLite appropriate-use guidance

A race that a normal smoke test misses

Consider two requests that both read a balance of 100 credits before reducing it to a free allowance of 25. If each calculates a debit of 75 from that old balance, the final balance can become negative. Sequential tests pass because the second request sees the first request’s completed change.

We reproduced this in Simple Stack’s subscription cancellation path during the September 2026 review. The fix puts the balance read, calculation and ledger write inside one transaction and reserves SQLite’s writer before reading. The second request then observes the committed balance and has nothing left to subtract. This is an application correctness issue; WAL alone does not solve it.

Why a busy timeout is not the whole answer

A deferred transaction can begin as a reader and later try to become a writer. Another connection can win the write lock between those steps. SQLite documents that this upgrade can fail with SQLITE_BUSY. Adding a timeout does not make a stale read safe or guarantee every transaction upgrade succeeds.

For code that owns a credit transaction, the starter now performs a no-op UPDATE of the account row immediately after beginning the transaction, before reading account state. That reserves the writer while preserving PDO’s transaction tracking for the existing nested helpers. A transaction style using BEGIN IMMEDIATE can also reserve the writer at the outset; choose one consistent approach and verify how your database abstraction tracks it.

A useful concurrency test

1. Create one disposable user with five credits and an action that costs one credit.
2. Start independent PHP processes, hold them at a shared barrier, then release them together. Sequential HTTP calls do not exercise the same race.
3. Assert five successful actions, an explicit insufficient-credit response for the remainder, five business rows and five matching debit rows. Check the final balance as well as status codes.
4. Repeat the same grant deduplication key concurrently. Assert one ledger grant, not merely one successful HTTP response.
5. Run overlapping period top-ups and cancellation clamps. Read the ledger and balance after every batch.
6. Force the business insert to fail after the debit. Assert that both the debit and balance change roll back.

The release regression runs five batches of 24 independent processes across metered notes, webhook claims, duplicate grants, period top-ups and cancellation clamps, plus a forced-insert rollback. These 120 operations check specific invariants; they are not a throughput benchmark, a capacity promise or proof of every possible race in a new product.

Keep slow work outside the write transaction

Do not hold the database writer while waiting for an email provider, payment API or image generation. Keep the database transition short and make external completion recoverable. Index the queries your real workload uses, and measure lock errors and transaction duration before deciding that a different database is necessary.

Back up a consistent state, then restore it

Copying only a live database file can miss committed data still in its WAL. Use SQLite’s online backup mechanism or another documented consistent snapshot method. Restore into a separate directory, run an integrity check and inspect representative accounts, purchases and ledger entries. Never test a restore by overwriting the live database.

Further reading: SQLite transactions and writer upgrades, the online backup API, and the starter’s credit model and tradeoffs.