SQLite and D1

SQLite is a database engine that keeps an entire database in one file and runs inside your process rather than as a server you connect to. D1 is Cloudflare's hosted version of it: the file lives in Cloudflare's storage, a Worker gets a binding rather than a connection string, and a query travels as a request. The SQL dialect is SQLite's, including the FTS5 full-text extension and the JSON functions, so most of what you know transfers. What you lose is the server, and with it the connection pool, the tuning knobs and the monthly bill for an idle instance.

How we use it

It is the default store for anything we build on Workers. Objectify runs ten separate D1 databases and spreads tenants across them, with a Durable Object holding the directory that maps a tenant to a shard and a lock, copy, switch, verify sequence for moving one while the service is up. The reasoning, and the parts of it that hurt, are set out in when many small databases beat one big one.

DMARC Engine splits its storage in two: the raw aggregate report attachment goes to object storage untouched and the parsed rows go to D1, so a parser bug means a re-run rather than lost evidence. Jonosakti puts every content type behind it, petitions through to fact-checks, and runs site-wide search over the same database. @Heartbeat holds its posts, follows, likes and notifications in D1 across fifteen migrations, and carries configuration for four extra shard databases that are deliberately switched off, because a timeline with five demo accounts on it does not need sharding and the config was cheaper to leave in than to argue about.

What it costs you

There is a hard ceiling of 10 GB per database. That number decides architecture rather than annoying you later, which is why Objectify picked a shard count on day one.

There are no interactive transactions. You can send a batch and have it applied atomically, but you cannot open a transaction, read a row, decide something in TypeScript and write inside the same transaction. Anything that needs read-then-write safety wants optimistic versioning or a Durable Object in front of it.

Writes go to one region. Reads can be served from replicas, writes are a round trip to the primary, so a write-heavy user on the far side of the world feels it.

SQLite's ALTER TABLE is thin. Renaming a column or changing a type is create, copy, swap, and that gets less charming once the change has to be applied across ten shards with partial failure possible. There is no cross-database join either, so any question about all your customers at once means fanning out or keeping counters as you go.

Point-in-time restore covers the last thirty days, which is real insurance, but restoring one shard while nine others carry on serving is a judgement call rather than a button press.

When we would choose something else

If a build has concurrent writers contending over the same rows, needs extensions such as PostGIS or pg_cron, or has a query planner problem rather than a storage problem, we use PostgreSQL and accept a server. If the product wants a database plus authentication plus file storage and nobody is going to build those, Supabase gets you there faster. For values that are only ever read by key and never queried, D1 is the wrong shape and key-value storage is cheaper and quicker. And for a counter, a lock or anything that needs one writer and a strict order, we put a Durable Object in front rather than trying to make the database do it.