SQLite Is All You Need

I read Prefer STRICT tables in SQLite by Evan Hahn last week, and it got me thinking. Here is someone treating SQLite as a database you put real, typed, production data into. Which raises the obvious question: how far does that actually go?

So we found out. We built a social network on SQLite, 50,000 users and a million posts in a single 343MB file, put it behind a Node API, made every table STRICT on Evan's advice, and hammered it until the numbers stopped being flattering. This post is what came back: the benchmarks, the config, the code, and the places it falls over.

The short version: one file, one process, and the heaviest page in the app served 315 million requests a day on a laptop. For almost anything you are building, SQLite in WAL mode is enough, and the Postgres container you spun up out of habit was never needed.

If you want the evidence rather than the argument, skip straight to the benchmarks. The limits are here too, and they are real.

What we built

A social app is a good stress test because the hard query is unavoidable. A home timeline has to join what you posted against who I follow, sort by time, and count likes. You cannot cache your way out of it on the first request, and it gets slower as the graph grows.

So: Chirp, a social network that lives in one file.

Every table is STRICT, which we will come back to. The whole backend is one Node process talking to chirp.db sitting next to it. No database server, no connection string, no port 5432, no container.

The entire production configuration is five pragmas:

const db = new Database('chirp.db');
db.pragma('journal_mode = WAL');       // readers and the writer stop blocking each other
db.pragma('synchronous = NORMAL');     // fsync at checkpoints, not on every commit
db.pragma('busy_timeout = 5000');      // wait for the write lock instead of throwing
db.pragma('foreign_keys = ON');        // off by default, which surprises people
db.pragma('cache_size = -64000');      // 64MB of page cache

That is the whole setup. There is no step where you provision anything.

The numbers

Everything below ran on an Apple M1 laptop, 8 cores, 16GB of RAM, on Node 22 with better-sqlite3 (SQLite 3.49.2). Not a server. A laptop.

First, end to end over real HTTP: a real Node server, real sockets, real JSON serialization, 50 concurrent connections, autocannon on the other end. This is the whole stack, not the database in isolation. Every figure is the median of four runs, and run-to-run variance is around 10%.

Sit with the last row for a second. That is the worst query in the app, the one that joins two and a half million follows against a million posts and counts likes on every result, served alongside live writes, from a single Node process, on a laptop.

3,654 requests per second is 315 million requests a day. On the heaviest endpoint. The point read would do 4.4 billion.

If your product serves more than 315 million timeline loads a day, you are not in the 99.99%, and you already know who you are. Everyone else is arguing about connection poolers for a workload that fits in a file.

Underneath the HTTP layer, the raw query numbers look like this. Each benchmark runs in a fresh process against a fresh copy of the database, three times, and we take the median.

Every write there is a real, committed, durable transaction. Not a batch, not a buffer, not a queue. Twenty-three thousand committed transactions a second, on a laptop, with foreign keys on.

WAL is the part that matters

The old objection to SQLite is that it locks. One writer takes the database, everyone else waits. That objection is about the rollback journal, which has been the wrong default for most apps since 2010.

Write-Ahead Logging changes the shape of the problem. The writer appends to a log instead of mutating pages in place, so readers keep reading the last committed snapshot while a write is in flight. Readers do not block the writer. The writer does not block readers.

We tested it instead of asserting it. Seven reader threads run the timeline query. First alone, then next to a writer doing a realistic 1,000 writes per second, then next to a writer going flat out. We ran the identical test in WAL and in the old rollback journal mode so the difference is visible.

journal_mode = WAL

journal_mode = DELETE (the rollback journal, the thing people remember)

Same query, same data, same machine. One pragma.

With a writer running at a thousand writes a second, WAL serves 5.6x the read throughput and holds its 99th percentile at 4.40ms. The rollback journal collapses to 133ms at p99, with individual reads stalling for nearly eight hundred milliseconds. That is the SQLite people complain about, and it is a database from a decade ago.

Under WAL, across every scenario, zero SQLITE_BUSY errors. Not "few." Zero.

Where the numbers stop

If this post only had the good tables in it, you should not trust it. Here is what we found that does not flatter SQLite.

Reads stop scaling once anything writes. Seven reader threads with no writer do 17,581 reads/s. Add a writer doing only 1,000 writes/s and reads drop to 2,792. That is a 6.3x fall, and it does not get meaningfully worse if you go to 14,000 writes/s, which tells you it is not about write volume.

It is about cache invalidation. Those readers were getting their speed from a 256MB memory-mapped window and a warm page cache. Every commit invalidates mapped pages, so readers fall back to real I/O and re-validation. We confirmed it by sweeping the config: with mmap off, the read-only number drops from 17,069/s to 6,034/s and the penalty from a concurrent writer mostly disappears. The 17,581 figure is a read-only artifact. The honest mixed-workload number is around 2,800 reads/s of the heaviest query per machine, and that is the one we built the argument on.

One writer, globally. SQLite takes a single write lock for the whole database. Writes do not run in parallel, they queue. At 23,000 committed transactions a second that queue drains fast, but it is a queue, and no amount of hardware makes it two queues.

One machine. There is no failover. If the box dies, you are down until it comes back, and your recovery time is however long it takes to restore a file. For a lot of products that is a completely acceptable trade for the operational simplicity. For some it is not, and if you are in a regulated industry with an uptime SLA, you already know which one you are.

Reach for Postgres when you have many writers contending on the same rows, when you need read replicas or automatic failover, when you need a real analytics engine over hundreds of millions of rows, or when your team genuinely needs the extension ecosystem. Those are real reasons. "We might scale one day" is not one of them, and it is the reason most of these containers exist.

"But you tested on a laptop"

Fair. An M1 is not a fast machine by 2026 standards, but it is not a $6 VPS either, and the whole argument falls apart if these numbers only exist on Apple silicon.

We did not rent the boxes and re-run this, so what follows is an estimate, and it is labelled as one. But it is a more constrained estimate than it looks, because of something our own numbers already told us.

The Node server is single-threaded. That 3,543 req/s came from one core. And remember the concurrency result: seven reader threads next to a writer did 2,792 reads/s, which is less than one thread on its own managed. Once writes are in the mix, this workload plateaus at roughly one core's worth of throughput no matter how many cores you throw at it.

So the question "how fast is this on a cheap VPS" collapses into "how fast is one core on that VPS." That is a question you can answer from published single-core scores, within a sensible margin.

Assume plus or minus 30% on every estimated row, and treat the prices as approximate list prices that will drift.

The method: an M1 performance core scores roughly 1.7x to 2.3x a typical cloud core on single-threaded work, with Ampere's Arm cores at the lower end and current AMD EPYC cores at the higher end. The 343MB database fits in page cache on every box in that table, so none of them are going to disk on reads. Writes will be somewhat worse than the ratio suggests, because fsync on cloud NVMe is slower than on a laptop SSD, and that lands in the write tail rather than the read path.

Even taking the worst row and the pessimistic end of its range: a $12 droplet serves something like 110 million timeline requests a day. The heaviest page in the app. On the cheapest box on the list. That is the entire argument, and it survives leaving Apple silicon behind.

What to actually buy

Since the workload is one core plus page cache, the shopping list is short and slightly counterintuitive.

Buy single-core speed, not core count. A 2 vCPU box with fast cores beats an 8 vCPU box with slow ones for this workload. This is the opposite of how people usually size a database server, and it is because you are not running one.

Buy enough RAM to hold the database. Your working set wants to live in page cache. A 343MB database barely registers, and any of these boxes will happily keep a several-gigabyte database resident. When your database no longer fits in RAM, that is a real signal, and it is the first one worth acting on.

Insist on local NVMe. Never put SQLite on network storage. This is the one that will actually hurt you. A DigitalOcean Volume, an EBS volume, an NFS mount: these are networks pretending to be disks, and SQLite's locking and fsync behaviour assumes a local filesystem. You will get latency you cannot explain and, on NFS, correctness problems you do not want. The local disk that comes with the droplet is the right disk. This is the sharpest edge in the whole approach, and it is easy to walk into by accident.

Pay for a dedicated core if you care about p99. The cheapest tiers put you on a physical core alongside other tenants, and their noise lands directly in your tail latency. Hetzner's CCX line and DigitalOcean's dedicated plans cost a few dollars more and remove the variable.

The punchline is that the entire server costs less per month than most teams' managed Postgres instance, and the database is a file on it.

What the 99.99% actually means

So the performance holds up, on a laptop and on a cheap server, and we have been clear about where it does not. Which leaves the part of the argument that is not about SQLite at all.

We keep saying 99.99%, and that sounds like marketing. Here is what we mean by it, because it is not a claim about the database. It is a claim about you.

Your new app does not have users. Not yet, and quite possibly not ever. That is not an insult, it is the base rate, and it applies to almost everything anyone ships. The hard part of building software was never the database.

We would know. We have been building DB Pro for over six months now, and by a distance the hardest thing we work on is not query parsing, or schema diffing, or shipping a native app on three platforms. It is user acquisition. Getting people to find the thing, try the thing, and come back to the thing a second time. Every engineering problem we have hit has been solvable with enough care. That one is not, and it is the one that decides whether any of the rest mattered.

So when you provision a Postgres instance for the app you vibecoded last weekend, look closely at what you are doing. You are engineering for the traffic you imagine, in a future where you already won, and paying for it in complexity now, in a present where you have not. You have added a moving part, a service that must be running, a connection string that must be right in three environments, and an ongoing bill, to a product that does not yet have a single user.

The honest version of scale looks like this. If you ever get enough traffic that SQLite cannot serve it, you will have revenue, a team, and an extremely clear signal about exactly what to fix. Moving a working product with real users onto Postgres is a good problem to have, and you will be far more qualified to solve it then than you are today. Pre-solving it now, for a repository nobody has cloned, is not engineering. It is anxiety with a docker-compose.yml.

Ship the file. Go and find the users. That is the hard part, and until you have done it, the database was never the thing standing in your way.

Use STRICT tables

So that is the case. The rest of this post is how to actually run it.

Start with the schema, and back to the post that started this. Evan's argument is the right companion to everything above: if you are going to run SQLite as your real database, run it in the mode that does not let you put text in an integer column.

By default, SQLite will take anything you give it:

CREATE TABLE loose (age INTEGER);
INSERT INTO loose (age) VALUES ('garbage');
-- works fine. stored: "garbage"

Add one word to the end of the table definition and it stops:

CREATE TABLE tight (age INTEGER) STRICT;
INSERT INTO tight (age) VALUES ('garbage');
-- Error: cannot store TEXT value in INTEGER column tight.age

That error text is real output, not paraphrased. Values that convert losslessly still work, so '123' goes in and comes back as the number 123, not the string.

The second thing it catches is the one that surprised us. Without STRICT, SQLite accepts column types that do not exist:

CREATE TABLE t1 (id UUID, created DATETIME, meta JSON);
-- accepted, no complaint, none of those are SQLite types

You get a table where every column has the wrong affinity and nothing tells you. With STRICT, all three are rejected outright:

Error: unknown datatype for t2.id: "UUID"
Error: unknown datatype for t3.created: "DATETIME"
Error: unknown datatype for t4.meta: "JSON"

Only INT, INTEGER, REAL, TEXT, BLOB, and ANY are allowed, and ANY is there when you actually want a key-value column. Store your JSON in a TEXT column and use the JSON functions on it, which is what SQLite was doing under the hood anyway.

Run it yourself:

The one real cost is that you cannot ALTER an existing table into strictness. You create a new table, copy the data across, and rename, and if the old data has text sitting in integer columns the copy will fail, which is the point. Every table in Chirp is STRICT, and it cost us nothing.

DB Pro

Work With Your Databases Like A Pro

Query, explore, and manage your databases with a beautiful desktop app and built-in AI.

Explore DB Pro

The actual code

There is not much of it, which is the point. Here is the whole data layer.

The connection, with the five pragmas from earlier. This runs once, at startup:

import Database from 'better-sqlite3';

export function open(path = 'chirp.db') {
  const db = new Database(path);
  db.pragma('journal_mode = WAL');
  db.pragma('synchronous = NORMAL');
  db.pragma('busy_timeout = 5000');
  db.pragma('foreign_keys = ON');
  db.pragma('cache_size = -64000');    // 64MB page cache
  db.pragma('mmap_size = 268435456');  // 256MB memory-mapped
  return db;
}

The queries, prepared once and reused for the life of the process. Preparing a statement on every request is the single easiest way to make SQLite look slow, so do not do that:

export function prepare(db) {
  return {
    // The home timeline: posts from everyone you follow, newest first,
    // with the author and a live like count. This is the expensive one.
    timeline: db.prepare(`
      SELECT p.id, p.body, p.created_at,
             u.handle, u.display_name,
             (SELECT COUNT(*) FROM likes l WHERE l.post_id = p.id) AS likes
      FROM posts p
      JOIN follows f ON f.followee_id = p.user_id
      JOIN users u   ON u.id = p.user_id
      WHERE f.follower_id = ?
      ORDER BY p.created_at DESC
      LIMIT 20
    `),

    createPost: db.prepare(
      `INSERT INTO posts (user_id, body, created_at) VALUES (?, ?, ?)`
    ),

    likePost: db.prepare(
      `INSERT OR IGNORE INTO likes (user_id, post_id, created_at) VALUES (?, ?, ?)`
    ),
  };
}

And the server. All of it:

import { createServer } from 'node:http';
import { open, prepare } from './db.js';

const db = open();
const q = prepare(db);

createServer((req, res) => {
  const [, route, param] = req.url.split('/');
  res.setHeader('content-type', 'application/json');

  if (route === 'timeline') {
    const posts = q.timeline.all(Number(param));   // 0.23ms, no await
    return res.end(JSON.stringify(posts));
  }

  if (route === 'post' && req.method === 'POST') {
    const { lastInsertRowid } = q.createPost.run(Number(param), 'hello', Date.now() / 1000 | 0);
    res.writeHead(201);
    return res.end(JSON.stringify({ id: lastInsertRowid }));
  }

  res.writeHead(404).end('{}');
}).listen(8080);

Look at what is missing. There is no connection pool, because there is no connection. There is no await on the query, because there is no network to wait for. There is no retry, no circuit breaker, and no timeout, because the failure mode those exist to handle does not exist here. The database cannot be unreachable. It is a file on the same disk, and the query is a function call.

That synchronous q.timeline.all() looks like a mistake to anyone trained on Node, and it is not one. The query returns in a fraction of a millisecond, so there is nothing to yield the event loop for. Making it async would add overhead and buy nothing.

Transactions are a function you call:

const publish = db.transaction((userId, body, mentions) => {
  const { lastInsertRowid: postId } = q.createPost.run(userId, body, now());
  for (const userId of mentions) q.mention.run(postId, userId);
  return postId;
});

publish(42, 'hello @jay', [7]);   // commits, or rolls back entirely

If the function throws, the transaction rolls back. If it returns, it committed. That is the whole API.

What about Bun?

Bun ships SQLite in the runtime (bun:sqlite), so the dependency count goes from one to zero, and Bun's HTTP server is faster than Node's. It seemed like a free win, so we ported the server and ran the identical load test against it.

The result is more interesting than "Bun is faster," because Bun is not faster. It depends entirely on the endpoint.

Medians of several runs each, same machine, same database file, same queries, same pragmas, same autocannon settings. The direction never flipped across runs.

The split falls exactly where the bottleneck moves. On a point read the query costs 0.004ms, so almost the entire request is HTTP and JSON, and Bun's server is meaningfully quicker at both. On the timeline the query costs 0.23ms and returns twenty rows of six columns, so the request is dominated by pulling rows out of SQLite and turning them into JavaScript objects, and there better-sqlite3 is quicker than bun:sqlite.

Which means the honest answer to "should I use Bun for this" is: it depends on whether your hot endpoints are cheap or expensive. If you serve a lot of small reads, Bun wins and gives you one fewer dependency. If your capacity is decided by a heavy query, as it is here, Node with better-sqlite3 was 21% ahead on our workload, and 21% of your capacity is not nothing.

This is one workload on one machine with Bun 1.3.12, so do not take it as a general verdict on either runtime. Take it as a reason to measure your own hot path instead of picking a runtime from a benchmark on the internet, including this one.

Backups are a file copy

This is the part that sells it to whoever is on call.

The claim: you can back up a live SQLite database, under write load, without stopping anything. The test: hammer the database from another thread while taking an online backup, then check whether the backup is a valid database.

backup taken while 183,608 writes landed (45,902 writes/s)

  took              4,060ms
  size              353 MB
  integrity_check   ok
  foreign_key_check no violations
  posts             1,183,608 (source had 1,000,000 before the writes started)

  the writer never stopped, and never saw an error.

A consistent, integrity-checked, 353MB backup of a live database taking 45,000 writes a second, in four seconds. The writer did not pause, did not error, and did not notice.

You can also do it from the shell:

sqlite3 chirp.db "VACUUM INTO 'backup-$(date +%F).db'"

That is your backup strategy. A file, on S3, from a cron job. If you want continuous replication, Litestream streams the WAL to object storage and gives you point-in-time recovery, and it is a single binary that does nothing else.

Compare that to the runbook you have for your managed Postgres, and ask who it is serving.

The part nobody puts in the benchmark

The performance argument is the one that gets attention, but it is not the one that will change your week.

Local development is one file. There is no service to start. Check out the project, install your dependencies, and the database is already there. Nothing listens on a port. Nothing needs to be running before the tests do.

Resetting your dev database is rm. Not docker compose down -v, not dropping and recreating a schema, not a migration you have to un-apply. Delete the file. It is gone.

Tests get a real database each. Point every test at its own file, or at :memory:, and they run in parallel against a real SQL engine with real foreign keys and real constraints. No shared test database, no truncating tables between runs, no mystery ordering failures at 2am.

Deploys are a binary and a file. The database lives on the same disk as the process that reads it, which means a query is a function call, not a network round trip. That is where the 0.004ms point read comes from. There is no network in the path, because there is no network.

There is nothing to operate. No version upgrades, no connection limits, no pooler, no failover drill, no separate thing to monitor, no separate thing to pay for. The database is a library your application links against, and it is the same library whether you are on your laptop or in production.

So use it

The reflex to start with a database server is a habit, not an engineering decision. It made sense when SQLite did lock the whole file on every write. That stopped being true a long time ago, and the tooling caught up: WAL for concurrency, STRICT for type safety, Litestream for replication.

Meanwhile the thing you are actually building probably serves a few hundred requests a second at its peak, and we watched a laptop do 3,654 of the worst kind, all day, without a single error.

One file. One process. Back it up with cp.

Jay