Locara

SQLite

What it is: Embedded SQL database engine. Single-file storage, public domain, written in C, exhaustively tested. Most-deployed database in the world by a huge margin (browsers, phones, OSes, devices, apps). Status: Not just mature — paradigm of long-term maintainability. Still maintained by D. Richard Hipp’s company (Hwaci). Most relevant to Locara: Locara’s storage primitive. Also: a model for how to ship a critical OSS dependency that lasts decades.

Background

SQLite started in 2000 (D. Richard Hipp) as a replacement for files-on-disk storage in a Navy combat system. It became the default embedded DB for iOS, Android, browsers, planes, every Mac, every Windows install, etc. It’s everywhere because it’s small (~600KB), reliable, and zero-ops — no daemon, no setup, no admin.

The project is famous for its testing rigor: their internal TH3 test suite has billions of test cases and 100% MC/DC coverage.

Key design decisions

  • Single file = entire database. mydatabase.sqlite is the whole thing. Move it, copy it, email it, version it.
  • Embedded library, not server. Linked into your process. No IPC, no network, no auth.
  • Public domain. Genuinely no license. No legal friction whatsoever.
  • C language for portability and speed (see references — they wrote a manifesto on it).
  • Minimal dependencies. Memcpy/memset/strlen and that’s mostly it.
  • Long-term commitment. “Designed to be supported through the year 2050.” This is a stated public commitment, with the project structured to make it credible.
  • Backwards compatibility forever. A SQLite 3.0 database from 2004 still opens in 2024.
  • WAL mode for concurrent reads + single writer with good throughput.
  • FTS5 full-text search extension built in.
  • JSON support as native type/operator extension.
  • Loadable extensions (sqlite-vec, R*Tree, etc.).
  • Aggressive optimization — sub-microsecond simple queries, fast even on slow disks.
  • Testing is the moat. Billions of test cases, fuzzed, MC/DC coverage. Bugs that ship to production are rare and fixed quickly.

What worked

  • Ubiquity is the proof. It’s in basically every device. Apple, Google, Microsoft, Mozilla all use it.
  • The “embedded, not server” thesis was right. For 95% of use cases, you don’t need a database server. SQLite removed an entire ops surface from millions of products.
  • Public domain destroyed every legal objection. No org has ever had to argue about SQLite licensing.
  • Long-term backwards compat allowed it to ossify into infrastructure. People trust it.
  • Performance + simplicity beat “more features.” Hipp explicitly chose to keep things small even when popular features could have been added.
  • TH3 testing infrastructure is itself a story — it’s why SQLite is more reliable than databases 100x its size.

What failed / criticisms

  • Single-writer lock is a real ceiling. Heavy-write workloads need a “real” DB.
  • Network access is intentionally absent — not a failure, but it bounds what SQLite is for.
  • Type system is weak. Dynamic typing in columns surprises people coming from Postgres.
  • Tooling ecosystem is thinner than Postgres — fewer GUIs, ORMs, etc. (Improving.)
  • Public domain confuses some legal teams. A few orgs have insisted on a “real” license, which is why SQLite offers paid commercial licenses for these cases.
  • The project culture is closed. Outside contributions are rare; it’s effectively Hipp-and-team. This works for SQLite but is unusual.

Specific learnings for Locara

  1. SQLite is the right storage default. Embedded, zero-ops, single file per app, content-addressable backups, FTS5 + JSON + sqlite-vec coverage. Use it without hesitation.
  2. Aspire to the longevity thesis. Locara should commit to long-term backwards compatibility for its manifest schema, app artifacts, and storage layout. Apps published in year 1 should work in year 10. SQLite proves you can do this if you take it seriously.
  3. Single-file-per-app storage maps cleanly. Each Locara app gets ~/Library/Containers/<app>/Data/app.sqlite (or equivalent on Linux/Windows). One file = trivially backupable, exportable, deletable.
  4. Content-addressable backups. Because sqlite files are bit-identical for the same data, can be deduped/CDN-cached if Locara ever ships a backup feature.
  5. FTS5 + sqlite-vec covers most search needs. Hybrid keyword + semantic in one file. Don’t reach for external search engines without strong reason.
  6. Loadable extensions = the right pattern for Locara’s vec backend choice. sqlite-vec is a sqlite extension; if zvec gets a sqlite extension wrapper, even better. Apps don’t switch DBs, they enable extensions.
  7. Testing rigor matters more than features. Locara should invest in test infrastructure early. A flaky framework on which apps run is much worse than a feature-poor one. SQLite’s TH3 is the model.
  8. Keep the surface small. Hipp resisted feature creep for 25 years. Locara should similarly resist — adding more capabilities, more modalities, more APIs is easy; maintaining all of them forever is the cost.
  9. License clearly. Apache 2.0 (matches zvec, Tauri, most modern OSS) is the right choice — public domain has corner cases SQLite has had to address. Don’t underthink licensing.

References