Ask HN: Have you used SQLite as a primary database?

I periodically hear about projects that use/have used sqlite as their sole datastore. The theory seems to be is that you can test out an idea with fewer dependencies (and cost) and that it scales surprisingly far.

There are even distributed versions being built for reliability in the cloud: dqlite by canonical (of Ubuntu fame) and rqlite

Given the complexity it seems like there are use cases or needs here that I'm not seeing and I'd be very interested to know more from those who've tried.

Have you tried this? Did it go well? Or blow up? Were there big surprises along the way?

- https://sqlite.org - https://dqlite.io - https://github.com/rqlite/rqlite

We are using Sqlite as a primary datastorage for "file blobs" and implement a software based replication using our queue to multiple servers for redundancy.

The use case is storing trace/profiling data, where we use one sqlite file for each customer per day. This way its easy to implement retention based cleanup and also there is little contention in write locking. We store about 1 terrabyte of data over the course of 2 weeks this way.

Metadata is stored in Elasticsearch for querying the search results and then displaying a trace hits the Sqlite database. As looking at traces is a somewhat rare occurence we iterate over all fileservers and query them for trace data given an ID until we find the result.

Reference https://www.sqlite.org/fasterthanfs.html

Simon Willison has written about using SQLite for a "Baked in data" architecture which is a super interesting method for some situations: https://simonwillison.net/2021/Jul/28/baked-data/

As he notes https://www.mozilla.org/ uses this pattern:

> They started using SQLite back in 2018 in a system they call Bedrock ... Their site content lives in a ~22MB SQLite database file, which is built and uploaded to S3 and then downloaded on a regular basis to each of their application servers.

I'm particularly interested in the "Sessions" extension (https://www.sqlite.org/sessionintro.html) and would love to hear if anyone has successfully used it for an eventually consistent architecture built on top of SQLite?

I'm using SQLite in Notion Backups (most of the workload happens in background jobs; the web app itself doesn't get that many visits)

Except for some rare exceptions, it's been doing pretty great. I don't have any plans to migrate from SQLite any time soon.

glad to see pieter here, I am keeping an eye on rebase ;)