SQLite Is All You Need - DB Pro Blog<br>Limited Time Offer: 50% off

Toggle theme<br>Download

h1]:font-serif [&>h1]:text-3xl [&>h1]:font-semibold [&>h1]:tracking-tight [&>h1]:text-ink [&>h1]:mt-12 [&>h1]:mb-8 [&>h1:first-child]:mt-0 [&>h2]:font-serif [&>h2]:text-2xl [&>h2]:font-semibold [&>h2]:tracking-tight [&>h2]:text-ink [&>h2]:mt-16 [&>h2]:mb-6 [&>h3]:font-serif [&>h3]:text-xl [&>h3]:font-semibold [&>h3]:text-ink [&>h3]:mt-8 [&>h3]:mb-4 [&>p]:text-muted-foreground [&>p]:leading-7 [&>p]:mb-6 [&_p_a]:text-brand [&_p_a]:no-underline [&_p_a:hover]:underline [&>ul]:mb-6 [&>ul]:list-disc [&>ul]:list-inside [&>ul>li]:text-muted-foreground [&>ul>li]:mb-4 [&>ul>li>a]:text-brand hover:[&>ul>li>a]:underline [&>ol]:mb-6 [&>ol]:list-decimal [&>ol]:list-inside [&>ol>li]:text-muted-foreground [&>ol>li]:mb-4 [&>ol>li>a]:text-brand hover:[&>ol>li>a]:underline [&>strong]:text-ink [&>strong]:font-semibold [&>code]:text-ink [&>code]:bg-muted [&>code]:px-1.5 [&>code]:py-0.5 [&>code]:rounded [&>code]:text-sm [&>code]:font-mono [&>pre]:bg-muted [&>pre]:border [&>pre]:border-border [&>pre]:rounded-lg [&>pre]:p-4 [&>pre]:mb-6 [&>pre]:overflow-x-auto [&>pre]:text-sm [&>blockquote]:border-l-4 [&>blockquote]:border-brand [&>blockquote]:bg-surface [&>blockquote]:pl-4 [&>blockquote]:py-2 [&>blockquote]:my-6 [&>blockquote]:italic [&>a]:text-brand [&>a]:no-underline hover:[&>a]:underline [&_table]:w-full [&_table]:mb-6 [&_table]:border-collapse [&_th]:text-left [&_th]:text-ink [&_th]:font-semibold [&_th]:text-sm [&_th]:px-3 [&_th]:py-2 [&_th]:border-b [&_th]:border-border [&_td]:text-muted-foreground [&_td]:text-sm [&_td]:px-3 [&_td]:py-2 [&_td]:border-b [&_td]:border-border ">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.

TableRowsusers50,000posts1,000,000follows2,498,799likes4,999,764Total 343 MB in one file<br>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:

JAVASCRIPT

const db = new Database('chirp.db');<br>db.pragma('journal_mode = WAL'); // readers and the writer stop blocking each other<br>db.pragma('synchronous = NORMAL'); // fsync at checkpoints, not on every commit<br>db.pragma('busy_timeout = 5000'); // wait for the write lock instead of throwing<br>db.pragma('foreign_keys = ON'); // off by default, which surprises people<br>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%.

Endpointreq/sp50p99ErrorsGET /post/:id (point read)51,427 0ms1ms0GET /u/:handle (profile)47,776 0ms2ms0GET /timeline/:id (the heavy one)3,543 13ms27ms0Mixed: 95% timeline reads, 5% writes3,654 13ms27ms0<br>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...