# Production SQLite: what you need to know SQLite is great for web use, we've all seen plenty of articles about "sqlite is all you need". BUT. Its defaults are tuned for backward compatibility and portability, which is fine for an embedded database inside a phone app, but terrible for web use, you can't just switch to SQLite and be OK, you _need_ to be aware of the pitfalls. For a webapp with one writer on a machine you control, the defaults mean giving up most of the throughput and concurrency you could be getting, and, more importantly, they also leave a couple of nasty failure modes switched on. So here's every pragma I set on every SQLite-backed project I run and why, as well as things I wish I'd known from the start. If you want to feed this to an LLM, there's a plain-markdown version at [/databases.md](/databases.md). ## The config ```sql PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA busy_timeout = 5000; PRAGMA temp_store = MEMORY; PRAGMA mmap_size = 134217728; PRAGMA journal_size_limit = 67108864; PRAGMA cache_size = -64000; ``` Run these on every connection. Most language bindings have some kind of "connection opened" hook, that's where they go. Some of these persist in the database file (`journal_mode = WAL` does), others reset on every new connection (like `foreign_keys`). I don't bother remembering which is which. Setting all of them every time costs nothing for the persistent ones and is required for the rest, so that's what I do. ## The pragmas ### `foreign_keys = ON` *per connection* Foreign key enforcement is off by default. SQLite [added it in 3.6.19](https://sqlite.org/foreignkeys.html), back in 2009[^1]. Out of the box, your foreign key columns are decorative, which is a problem. Turn it on. The only reason I can think of to leave it off is that you're working with an existing DB that doesn't use them (to which I'd say, go fix your data integrity first), or you're working on such a latency-sensitive project that the extra lookup on every insert, update and delete is a problem. ### `journal_mode = WAL` *stored in the db* The default journal mode is a rollback journal. Before touching the database, the writer will copy the original pages into a `-journal` file, and every reader will be locked out until the write finishes. That allows one writer or many readers, but not both. WAL (write-ahead logging) makes that a non-issue. A writer appends its new pages to a `-wal` file and leaves the main database file alone until a checkpoint copies them across, which by default happens once the WAL reaches 1000 pages, about 4MB (that's the `wal_autocheckpoint` pragma). Each reader sees the database as it was when its own transaction started (including whatever was in the WAL at the time). This is the biggest concurrency win you can get out of SQLite, and it's the reason a "single writer" SQLite webapp can still serve a lot of readers at once, because it takes _a lot_ of scale to get to a point where you need more than 1 concurrent writer. One warning: WAL needs shared memory to coordinate between processes, so it doesn't work over network filesystems (stuff like NFS and Windows/Mac Docker bind mounts, because they go over a Linux VM). I learned that one the hard way[^2]. ### `synchronous = NORMAL` *per connection* This controls how often SQLite calls `fsync()` to make sure writes have actually been persisted to disk. - `FULL` (the default): fsync on every commit. You laugh in the face of a power loss or OS crash. - `NORMAL`: fsync at each WAL checkpoint instead of on every commit. A power loss means that you can actually lose the last few transactions that were committed but not yet checkpointed. In WAL mode the database file itself can't be corrupted. (In rollback mode there's a small chance it can, which is one more reason to be on WAL.) - `OFF`: no fsync. A power loss can corrupt the database. You do not laugh. For a webapp, `NORMAL` is the right tradeoff. You get roughly double the write throughput, and in exchange, a power loss drops everything committed since the last checkpoint. On a busy site that's a second or two of writes. On a quiet one it can be a few minutes, because the WAL only gets checkpointed once it fills up. For most of the projects I use SQLite for, that's a risk I'm willing to accept. But it's also not a static database setting: If some of your writes matter more than that (payments, audit logs), you can use `FULL` for the connections that handle them; with most ORMs you can define more than one connection config, so you can have a `FULL` connection that only the sensitive models use and `NORMAL` everywhere else. ### `busy_timeout = 5000` *per connection* By default, any operation that runs into a locked database gets `SQLITE_BUSY` back immediately. It doesn't wait and it doesn't retry. So the moment two connections try to write at the same time, which in a webapp is all the time, you start seeing random failures. With `busy_timeout = 5000`, SQLite will wait for up to 5 seconds for the lock to clear before giving up. Obviously in most cases, contention will go away in a few milliseconds (however long the locking write takes), but it's good to have a bit of leeway just in case. This way you avoid throwing exceptions just because your DB needed you to wait for 1 second[^3]. There's a catch: this only helps transactions that were opened with `BEGIN IMMEDIATE` or `BEGIN EXCLUSIVE`. I explain why in [Transactions: `IMMEDIATE` vs `DEFERRED`](#transactions-immediate-vs-deferred). ### `temp_store = MEMORY` *per connection* Where SQLite keeps temporary tables, temporary indexes, and the scratch space for sorts and `GROUP BY`. The default is `FILE` (another one of those great for portability, completely useless for web defaults). `MEMORY` keeps it all in RAM, which is a lot faster than the filesystem. It takes up RAM proportional to your biggest sort. For most webapps that's just a few MB. ### `mmap_size = 134217728` *per connection* This maps up to 128MB of the database file straight into the process's address space. Reads from mapped pages skip the `read()` syscall and the copy out of the page cache, and go straight to the kernel's file cache. 128MB is a safe ceiling for most webapp databases. If you have a multi-GB database, you can go higher. However, make sure to read the value back with `PRAGMA mmap_size;` after setting it. [`SQLITE_MAX_MMAP_SIZE`](https://sqlite.org/mmap.html) is a compile-time cap, and if you ask for more than that, SQLite gives you the cap instead of an error. The number you asked for isn't necessarily the number you got. ### `journal_size_limit = 67108864` *per connection* This one is a disk space setting. When you hit a checkpoint, SQLite will copy the WAL's pages into the main database, but it [doesn't shrink the `-wal` file](https://sqlite.org/pragma.html#pragma_journal_size_limit). The writer will just start overwriting the file from the beginning (overwriting is faster than appending). So the file will remain at its high-water mark for as long as the database stays open, which in a webapp with constant traffic means forever. That high-water mark can get big. A checkpoint can only finish when no reader is still using the WAL, so a long report query, or a migration that takes a while, will keep the writer appending well past the default 4MB. `journal_size_limit` forces a cleanup after a checkpoint: each time the WAL resets, SQLite compares the file against the limit and truncates it if it's over. 64MB means "keep up to 64MB of scratch file around for reuse, throw away the rest". The limit is also per database, so if you [attach more files](#sharding-attach-database), each one needs its own. ### `cache_size = -64000` *per connection* The size of the per-connection page cache. Positive numbers are pages (4KB each by default), negative numbers are kilobytes. `-64000` is 64MB. The default is `-2000`, which is a measly 2MB. Anything with a working set bigger than a handful of tables will be going to disk constantly. 64MB covers most webapp working sets, and RAM is cheap. If you know how big your hot data is and you have RAM to spare, go higher. Just remember this is per connection, so 10 connections at 64MB can grow to 640MB. It's a ceiling, and the cache only fills as pages get read, but that's what it'll get to under load. ## Transactions: `IMMEDIATE` vs `DEFERRED` This is the other half of [`busy_timeout`](#busy_timeout--5000). SQLite's default transaction mode is `BEGIN DEFERRED`, which means the transaction starts as a read transaction and only upgrades to a write transaction when you issue your first write. That _sounds_ completely reasonable, but the problem is that the upgrade step ignores `busy_timeout`. If another writer holds the lock at the moment you try to upgrade, you get `SQLITE_BUSY` on the spot. You can set `busy_timeout` to 60 seconds, run a completely ordinary transaction, and still see random `SQLITE_BUSY` errors under any concurrent write load. The fix is to open any transaction that's going to write with `BEGIN IMMEDIATE`. That takes the write lock upfront, and taking it upfront does honor `busy_timeout`, so everything works fine. Almost every "SQLite can't handle concurrency" complaint I've read comes down to this. The database handles it fine. The default transaction mode is just wrong for concurrent writes. ## Types: `STRICT` vs `CHECK` Column types in SQLite are affinities, not constraints. They're basically type hints: the column says "I'd prefer an integer", SQLite converts what it can, and when it can't convert, it stores whatever you gave it anyway: ```sql CREATE TABLE t (n INTEGER); INSERT INTO t VALUES ('not a number'); SELECT typeof(n) FROM t; -- 'text' ``` There are two ways to make the declared type actually mean something, and which one you can use depends on whether you control your schema queries or if your ORM does. ### If you control your schema queries: `STRICT` ```sql CREATE TABLE t (n INTEGER) STRICT; INSERT INTO t VALUES ('not a number'); -- Error: cannot store TEXT value in INTEGER column t.n ``` `STRICT` has been around [since 3.37.0](https://sqlite.org/stricttables.html) (2021). It's less strict than you'd expect from the name: `INSERT INTO t VALUES ('123')` still converts the string and stores the integer 123. It only rejects what can't convert. With `STRICT`, every `PRIMARY KEY` column becomes `NOT NULL`, which closes the [ancient surprise](https://sqlite.org/quirks.html#primary_keys_can_sometimes_contain_nulls) that a `TEXT PRIMARY KEY` accepts NULL. And every column has to be one of six types: `INT`, `INTEGER`, `REAL`, `TEXT`, `BLOB`, `ANY`. Those six are the only ones allowed by `STRICT`. Stuff like `VARCHAR(255)` or `DATETIME` will fail. There's no pragma to make STRICT the default. The keyword has to go on each `CREATE TABLE`. What I do instead is check the whole schema in a test: ```sql SELECT name FROM pragma_table_list WHERE schema = 'main' AND type = 'table' AND name NOT LIKE 'sqlite_%' AND strict = 0; ``` An empty result means every table is strict. That's one assertion, and you can even make a workflow in your CI or pre-commit hooks to ensure you never let a non-strict table through. ### If your ORM writes your schema queries: `CHECK(typeof(...))` Most ORMs use the standard `VARCHAR`, `DATETIME` and `BOOLEAN` types, and those won't work with STRICT. A CHECK constraint gets you the same enforcement without touching the declared type: ```sql CREATE TABLE orm ( name VARCHAR(255) CHECK(typeof(name) IN ('text', 'null')), n INTEGER CHECK(typeof(n) IN ('integer', 'null')) ); ``` Notice the `'null'` in there. `typeof(NULL)` returns `'null'`, so the obvious `CHECK(typeof(n) = 'integer')` rejects every NULL, and so you'll accidentally be making the column `NOT NULL`. Only drop it where that's what you want. CHECK is fun, it can express a lot more than STRICT can: ```sql CHECK(status IN ('draft', 'published', 'archived')) CHECK(json_valid(payload)) CHECK(length(slug) > 0) ``` And the two work together. You can use STRICT to constrain the types and CHECK to constrain the values. If you can do both that's honestly ideal. Also note: There's no `ALTER TABLE ... SET STRICT`. Converting an existing schema means recreating every table and copying the data across. ## Backups: `VACUUM INTO` ```sh sqlite3 db.sqlite "VACUUM INTO 'backup.sqlite'" ``` This produces a complete copy of the database in a single file, and it's safe to run while the app is busy writing. The reason you can't just `cp` the file is WAL. With WAL on, the main database file isn't the whole database; recent commits are still sitting in the `-wal` file, and so you need SQLite to be the one figuring out how to build the backup. Not that different from having to use `mysqldump` to export a live DB. ## Sharding: `ATTACH DATABASE` SQLite can attach up to 10 databases to one connection, which allows you to shard in a really nice way. A common split is `main.db` plus `fts.db` for the full-text search index. Writes to `main.db` never contend with reads of `fts.db`, and you can rebuild the FTS index without blocking the main database at all. Other splits worth considering are audit logs (write-heavy, rarely read) and session data (high churn). The constraint is that [foreign keys can't cross database files](https://sqlite.org/foreignkeys.html), so anything that references a table in `main.db` has to live in `main.db` too. Warning: In WAL mode a commit is only atomic per file: if the machine dies in the middle of a `COMMIT` that touched two DB files, one can end up with the changes and the other without. So only split off data that you can rebuild from the main file, like an FTS index, and make sure you have commands to resolve data integrity issues (e.g. to rebuild the FTS index) for when you need them. This is a cheap way to squeeze more concurrency out of SQLite, if you get to a scale where that's necessary. ## Sources - gcollazo, [Optimal SQLite settings for Django](https://gcollazo.com/optimal-sqlite-settings-for-django/) - HN comment by [`0123456789ABCDE`](https://news.ycombinator.com/item?id=48046807) on the SQLite production thread - SQLite docs: [Pragma statements](https://sqlite.org/pragma.html), [Write-Ahead Logging](https://sqlite.org/wal.html), [Foreign key support](https://sqlite.org/foreignkeys.html), [Memory-mapped I/O](https://sqlite.org/mmap.html), [STRICT tables](https://sqlite.org/stricttables.html), [Quirks](https://sqlite.org/quirks.html), [VACUUM INTO](https://sqlite.org/lang_vacuum.html#vacuuminto). --- This page is a living reference and I'll keep updating it as I learn more. If you spot a mistake, or you think something is missing, reach out via [email](mailto:hello@pocketarc.com) or [X/Twitter](https://x.com/pocketarc). [^1]: Which I didn't know before writing this lil' article. I grew up thinking that SQLite didn't have foreign keys, and then when I saw this I thought "wow, you are dumb, you missed this all that time ago?", but nope, it turns out it was just not there at all back then. [^2]: I had a project up with Docker Compose, but I had the SQLite .db file open in TablePlus so I could manipulate a few things, and then I kept seeing data get corrupted and rows disappearing, and I thought I was going crazy, until I realized what was happening. All dev, not prod, which, you know, phew. [^3]: MySQL does this for you: when InnoDB hits a row lock it just waits, for up to `innodb_lock_wait_timeout` (50 seconds by default) before it gives up. Most of the time you're not even aware it's happening. SQLite's equivalent timeout is zero unless you set one.