SQLite: The Embedded Database That Replaces Solr, MongoDB, Kafka, and More

Introduction

SQLite has a longer lifespan than most systems running today. Many once considered it a toy database — just a simple file-based store for mobile apps to avoid writing config parsers. Production systems were expected to use real databases with ports, daemons, and 3 a.m. on-call alerts.

The author identifies three pillars of SQLite's strength:

Stable and reliable

Easy to run, install, and scale (because nothing needs to run)

More than a relational database — it is also a full-text search engine, document store, cache layer, vector index, and even an application file format, dramatically simplifying the tech stack.

1. Stable and Reliable

SQLite is an unglamorous old technology first released in 2000, yet it is the most widely deployed database engine in the world. It runs on your phone, browser, car, and even airplanes. SQLite instances outnumber all other databases combined.

Database systems need time to fix vulnerabilities. SQLite has had that time and continues to improve. Its test suite achieves 100% branch coverage under the MC/DC standard (used in avionics), with test code roughly 500× the size of the library code. The project promises maintenance until 2050 — a horizon longer than many corporate roadmaps.

SQLite is in the public domain, not open source. No license, contributor agreements, attribution clauses, or vendor backed by venture capital that might change terms.

Despite its age, SQLite quietly adds modern features: window functions, RETURNING, strict tables, generated columns, jsonb, etc. Each release brings small, well-tested, backward-compatible improvements — the most unglamorous but most valuable trait for a database.

2. Easy to Run, Install, and Scale

In a sense, installing SQLite locally is trivial. It ships with every major Linux distribution and is built into Python, Ruby, PHP, Go, Rust, .NET, Android, and iOS. Whether you need it or not, it is already on your device.

Want a test database identical to production? The problem isn't Testcontainers; it's using :memory: mode. Your test suite can spin up a fresh database instance per test case in microseconds, in parallel, with no Docker daemon and no port conflicts. The database you test with is the exact same library compiled into your production binary.

On servers, SQLite is already running because it comes with the OS.

Scaling advantages:

Vertical scaling: A SQLite round-trip is a function call, not a network hop. A modern NVMe SSD with 128 GB RAM can handle astonishing throughput. No connection pooling, no TLS handshake, no pgbouncer; latency drops from milliseconds to nanoseconds.

Replication and backup: Litestream streams WAL logs to S3; LiteFS provides distributed reads. Both are tiny single binaries.

Managed services: If you need a control plane, Turso, Cloudflare D1, and rqlite offer managed SQLite with dashboards.

These traits make SQLite one of the most deployed pieces of software. For you, that means less maintenance and more time building business features.

3. Simplify IT Architecture Deployment

SQLite deploys to the cloud with near-zero configuration because it is just a regular file alongside your application. Its advantage goes further: it can replace an entire suite of independently operated system components.

4. Replace Solr and Elasticsearch: Full-Text Search

SQLite includes the FTS5 full-text search engine, integrated directly into the library you already link. It supports tokenizers, prefix queries, phrase queries, NEAR proximity search, Boolean operators, custom BM25-based ranking, and snippet/highlight functions for result display.

Two key points:

No data-sync problem because no separate system is deployed. The search index and business data update in the same transaction, always. Every “stale search index” incident stems from unnecessarily complex architectures.

FTS5 query speed often exceeds expectations. Simon Willison's Datasette runs faceted full-text search on multi‑GB SQLite files on a modest VM with millisecond latency and zero extra cost.

FTS5 cannot handle multi-language analysis chains or distributed sharding across 40 nodes. But do you really need 40 nodes? Most likely not.

More details: https://sqlite.org/fts5.html

5. Replace MongoDB: Excellent JSON Support

SQLite provides comprehensive JSON storage and querying. JSON functions are built-in; -> and ->> operators behave as developers expect. Since version 3.45, SQLite offers the jsonb binary storage format, avoiding repeated JSON text reparsing.

An overlooked capability: SQLite supports indexing JSON internal fields . You can create a generated column from a JSON path and index it, enabling high-speed queries on fields not defined in the schema. One file gives you schemaless writes and indexed reads.

Advantages: document storage + ACID transactions; no separate service process, no replica-set configuration, no sharding setup, no mongod daemon — just a single copyable file on disk. Does MongoDB still have a reason to exist?

6. Replace Kafka and RabbitMQ: SQLite as a Message Queue

Events, queues, and persistent logs grow in importance. Kafka, RabbitMQ, SQS can do this, but maintaining them is tedious, requires heavy customization, and demands specialized ops hires.

Good news: a single SQLite table suffices as a message queue .

BEGIN IMMEDIATE;
UPDATE jobs SET status = 'running', worker = ?
WHERE id = (SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY id LIMIT 1)
RETURNING *;
COMMIT;

BEGIN IMMEDIATE

acquires the write lock early; RETURNING returns the claimed row directly. Transactions guarantee only one worker gets the task. With WAL mode, reads are never blocked, so monitoring dashboards querying queue length don't contend with workers.

Real limitation: SQLite only supports single-threaded writes . No SKIP LOCKED syntax because there are no locks to skip. Multiple consumers serialize on the write lock; if enqueue rate truly hits tens of thousands per second, the bottleneck appears.

Note the fundamental difference: with PostgreSQL the queue is just a table in a remote DB; with SQLite the queue table runs inside your application process. Messages never leave the machine — no independent broker, no consumer-group rebalancing, no “why did partition assignment change during publish” incidents.

Recommendation: Start with SQLite as your message queue . When it genuinely can't keep up, you'll have real business metrics to justify migrating to Kafka — not a gut feeling.

7. Replace ClickHouse: High-Throughput Time-Series Data

Time-series data is special: high-volume writes followed by aggregation, statistics, and pre-aggregation.

SQLite offers:

File-based data partitioning: Partition by day, week, or tenant into separate database files. Archive with mv; delete old data with rm — constant-time operations, no VACUUM. Cross-file queries use ATTACH and UNION ALL views. Looks crude, works exceptionally well.

Rollup tables: Generate pre-aggregated summary tables via triggers or the same business logic that writes raw data. Even with dedicated time-series databases you still implement continuous aggregation yourself.

Bulk inserts: Tens of thousands of inserts in one transaction trigger a single fsync. On commodity hardware this yields hundreds of thousands of rows per second with zero network protocol overhead.

Columnar analysis when needed: DuckDB reads SQLite files natively. The same data file written by your app gives you vectorized OLAP analysis with no ETL.

Specialized time-series databases are powerful. If you ingest millions of points per second, use them. But most “time-series” workloads are only a few million rows per day — trivial for a single file on SSD.

8. SQLite as a Vector Database for AI Workflows

sqlite-vec

is a single-file, zero-dependency extension that turns SQLite into a vector database. Written in C, it runs wherever SQLite runs, including browsers via WASM. Vectors live in ordinary tables.

This is SQLite's sweet spot: vector embeddings, raw documents, metadata, and full-text indexes all reside in one file . Hybrid search becomes a single table join, not a distributed query across three services with three different consistency models. One SQL statement can filter by tenant, time, keyword, and vector similarity — all within a transaction.

Even more important: the entire RAG index is one file . Email it, bake it into a Docker image, deploy to an offline laptop. Managed vector clusters simply cannot do that.

9. Replace Redis: Non-Persistent High-Performance Cache

Caching matters. Most apps use Redis for sessions and hot data. By definition, cache data can be lost and regenerated.

Why deploy a separate service? SQLite offers multiple approaches, trading off durability as you wish:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = OFF;  -- it's a cache, live a little

You can bypass disk entirely with :memory: or PRAGMA temp_store=MEMORY; or use file:cache?mode=memory&cache=shared for a shared in-memory database across connections.

Expiration logic: add an expires_at column and run DELETE ... WHERE expires_at < unixepoch() on a timer. Redis does the same thing under the hood, but remotely, forcing you to study its eviction policies.

Critical point: A Redis GET on localhost takes ~100 µs. A warmed SQLite point query takes ~1 µs. Removing the dependency doesn't degrade performance — it improves it. The fastest network call is a local function call.

Redis is excellent software, but it's a separate process: new failure modes, separate memory overhead, extra security hardening, another component to maintain.

10. Replace the File System: Store Raw Data

Many assume reading a small binary blob from a regular file is faster than from a database. Not true. This isn't opinion — it's SQLite's published benchmark titled “35% Faster Than the Filesystem.”

For blobs under ~100 KB, SQLite read/write beats scattered files on disk, while using ~20% less disk space. Reason: each file read/write requires open(), close(), and directory traversal; SQLite reuses an open file handle and does a single B‑tree lookup.

Free bonuses: atomic multi-blob updates, no half-written corruption on crash, no filename escaping vulnerabilities, no performance disaster from millions of files in one directory, no inode exhaustion slowing rsync backups for hours. Backup is just copying one database file.

Store data in a BLOB column; for extreme compactness serialize with a tight format and deserialize client-side. The SQLite team explicitly aims to be a better fopen() — a serious design goal, not a joke.

11. Replace Graph Databases

Recursive queries in SQL can handle hierarchical data, but traditional syntax is painful to read, maintain, and debug.

SQLite supports recursive CTEs; the official documentation is a top-tier resource. Closure tables, materialized paths, adjacency lists all work well. No LTREE type, so materialized paths use TEXT with GLOB indexes — less elegant but comparable performance.

For true graph workloads, the simple-graph extension implements a property graph model (nodes, edges, traversal) in a few hundred lines of SQL on ordinary SQLite tables.

The general principle shines here: your graph probably has ~10 000 nodes. That fits in CPU L3 cache. You don't need Neo4j; you need proper indexes and a cup of coffee.

12. Replace Microservices

Most microservices today are: one data model, one query, output JSON.

SQLite's json_object() and json_group_array() turn any query result into JSON, eliminating the serialization layer.

But the real value: SQLite runs inside your application process. Microservices aren't replaced by stored procedures; they're replaced by a local function call. No separate service deployment, no health checks, no retry logic, no circuit breakers, no distributed tracing. P99 latency is no longer dominated by network jitter.

Datasette proves the concept: point it at a SQLite file, write zero business code, get a JSON API, web UI, faceted search, and plugin ecosystem. Litestream guarantees durability. Two binaries + one data file = a production-ready data service.

Trade-offs exist, but many services exist only to wrap a database query in a network call.

13. Replace PlayStation 5 (Just for Fun)

The SQLite docs include a Mandelbrot renderer written in recursive CTE — right in the manual as a query syntax example, casually.

Developers have built Conway's Game of Life, Sudoku solvers, maze generators, even chess engines entirely in SQLite CTEs. Someone ran Doom's fire effect in a single SQL query.

Crazy, but a database that can render fractals in its official docs commands respect.

Conclusion

The list is not exhaustive. SQLite is highly flexible and supports loadable extensions. Almost any problem you'd solve by deploying a separate service likely has a corresponding extension.

Simplicity is the foundation of rapid iteration. Every component in your stack needs deployment, monitoring, hardening, upgrades, backups, payment, and onboarding explanations. PostgreSQL shrinks that checklist; SQLite nearly reduces it to zero. It's no longer a separate system — just a file and a set of function calls.

SQLite has a ceiling: single-threaded, single-machine. When you hit that ceiling, migrate to PostgreSQL. That's a moment to celebrate — it means you have enough users.

Until then, for every new requirement ask yourself two questions: Can SQLite handle this directly? Do we really need that shiny new technology?

SQLite isn't a silver bullet. But the scenarios it can handle are far more than most people imagine, and it's often already pre-installed in your environment.