SQLite Is More Powerful Than You Think: What It Can Do That Most Developers Miss

SQLite is more powerful than you think, and the gap between its reputation and its actual capabilities is wider than almost any other piece of infrastructure you will run.

Ask a developer what SQLite is for and you will hear some version of "a toy database" or "fine for a prototype, then you migrate to Postgres."

Neither is accurate. SQLite is the most widely deployed database engine in existence, and for a surprising list of workloads it is not a stepping stone, it is the end state.

This is the sysadmin read on SQLite: what the engine can actually do today, the version numbers that made each capability possible, how to back it up properly, and the honest limits that tell you when to stop and reach for a client/server engine instead.

The short version

• Most deployed database on earth. The SQLite project estimates more than one trillion SQLite databases in active use, in every Android phone, every iPhone, every Mac, every Windows 10/11 install, and every Firefox, Chrome, and Safari browser. Likely more than all other database engines combined.
• A library, not a server. Zero configuration, no daemon to run, one portable file, SQL compiled to a virtual machine. Public domain, compact enough to ship inside an OS.
• Modern SQL is built in. Window functions, UPSERT, RETURNING, recursive CTEs, generated columns, and ALTER TABLE DROP COLUMN have all been there for years.
• JSON and JSONB are built in. JSON functions compile into the core by default since 3.38.0, and the binary JSONB format since 3.45.0.
• Full-text search is built in. FTS5 gives you MATCH queries, ranking, and snippets without Elasticsearch, Solr, or a search service.
• STRICT tables give you types. If manifest typing is why you dislike SQLite, turn it off per table since 3.37.0.
• WAL mode changes the concurrency story. Since 3.7.0, one writer plus many readers without the old locks, and a single app process can do thousands of writes per second.
• Backup is boring in the good way. One file, .backup, VACUUM INTO, and since 3.47.0 sqlite3_rsync for live backups over SSH.
• The limits are real and knowable. One writer at a time, no network protocol, afraid of NFS. When those matter, graduate to PostgreSQL.

Why the "toy database" reputation is wrong

The SQLite project keeps a page titled "Most Widely Deployed and Used Database Engine" that makes the scale concrete.

Because SQLite is used in essentially every smartphone, and there are more than four billion smartphones in active use, each holding hundreds of SQLite database files, the project estimates over one trillion SQLite databases in the wild. The page also claims SQLite is "likely used more than all other database engines combined." That is the installed base of a toy? No. It is the default storage engine of the computing world.

What makes that scale possible is the architecture. SQLite is a C library you link into your process. It is not a server you install and babysit. There is no daemon, no port to open, no connection pool to tune, no user provisioning. Your database is one ordinary file on disk, which means deployment is "ship the file" and backup is "copy the file" (with one WAL caveat, covered below).

The project also does the unglamorous engineering that earns trust: the file format is stable and cross-platform since the 2000s, the library is under 1 MiB with everything enabled, and the source is public domain, which is why it ends up inside operating systems, browsers, and avionics rather than only in your toy project.

The SQL you did not know it had

If your mental model of SQLite is from a decade ago, the SQL surface has moved underneath you:

• Window functions since 3.25.0 (2018-09-15). ROW_NUMBER(), RANK(), LAG(), LEAD(), running totals, and moving averages all work. The analytical queries you think require Postgres mostly do not.

SELECT id, created_at,
       ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn,
       LAG(created_at) OVER (ORDER BY created_at) AS prev
FROM events;

• UPSERT since 3.24.0 (2018-06-04). PostgreSQL-style ON CONFLICT with DO UPDATE or DO NOTHING, including partial conflict targets.

INSERT INTO counters (name, n) VALUES ('visits', 1)
ON CONFLICT(name) DO UPDATE SET n = counters.n + 1
RETURNING name, n;

• RETURNING since 3.35.0 (2021-03-12). Get the affected rows back from INSERT, UPDATE, and DELETE, the same way modern databases do, including inside the UPSERT above.

DELETE FROM old_rows WHERE created_at < date('now', '-1 year')
RETURNING id;

• Recursive common table expressions. Tree traversal, graph walking, and WITH RECURSIVE number series all work, same syntax as Postgres.
• Generated columns. Virtual and stored generated columns, including indexed JSON expressions (expression indexes landed in 3.9.0).
• ALTER TABLE DROP COLUMN since 3.35.0. You are no longer stuck recreating a table just to remove one column.

For most reads and a healthy share of analytic workloads, you will not notice a capability gap between a local SQLite file and a networked Postgres. The gap is concurrency and multi-writer, not SQL.

JSON without a document database

The JSON functions and operators are compiled into the core SQLite by default since 3.38.0 (2022-02-22).

Before that they were an opt-in extension; now you only need -DSQLITE_OMIT_JSON to remove them. The surface is large: more than thirty scalar and aggregate functions plus the -> and ->> operators, and table-valued functions like json_each() and json_tree() for decomposing documents.

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  payload TEXT NOT NULL
);

INSERT INTO events (payload) VALUES
  (json('{"user": "ada", "tags": ["sqlite", "json"], "meta": {"ok": true}}'));

SELECT payload ->> '$.user'      AS user,
       payload ->> '$.tags[0]'   AS first_tag,
       json_array_length(payload, '$.tags') AS tag_count
FROM events;

Since 3.45.0 (2024-01-15), SQLite can store the internal parse tree as a binary JSONB BLOB instead of text.

JSONB is smaller than the text form and skips the parse/render round trip on every read, which the docs describe as potentially several times faster for heavy JSON workloads. But there is no JSONB column type, and SQLite will not convert for you: the engine stores only NULL, integers, floats, text, and BLOBs, and the binary format exists only when you produce it with the jsonb() function.

A column declared JSONB is just an unrecognized type name that lands on NUMERIC affinity, so an ordinary json('...') insert stays plain text, and STRICT tables reject the name outright. Declare the column TEXT or BLOB and convert on insert with jsonb():

CREATE TABLE settings (key TEXT PRIMARY KEY, value BLOB);
INSERT INTO settings VALUES ('theme', jsonb('{"dark": true, "accent": "green"}'));
SELECT key, value ->> '$.accent' FROM settings;

Heads up on naming: SQLite's JSONB is not PostgreSQL's JSONB. Same inspiration, different binary format, and unlike Postgres it does not promise O(1) element lookup. Treat it as an opaque BLOB and the performance win is real. Any function or operator that takes text JSON also takes a JSONB blob, so values stored as JSONB work everywhere text JSON does, just faster.

Full-text search with FTS5

FTS5, the fifth generation of SQLite's full-text search virtual table, has shipped in the amalgamation since 3.9.0 (2015-10-14). It gives you a MATCH query language, bm25() ranking, snippet() and highlight(), prefix and phrase queries, and external content tables so the index can stay separate from your source data. One build note: FTS5 ships in the amalgamation source, but it is only compiled into the library when the build defines SQLITE_ENABLE_FTS5. Most distribution and language builds do enable it, but it falls under the same check-your-build caveat as the CSV module below, PRAGMA compile_options lists ENABLE_FTS5 when your build has it.

CREATE VIRTUAL TABLE docs USING fts5(title, body);

INSERT INTO docs (title, body) VALUES
  ('sqlite notes', 'sqlite full-text search ranks results and works offline'),
  ('backup guide', 'use the backup API or vacuum into for consistent copies');

SELECT title, bm25(docs) AS rank
FROM docs
WHERE docs MATCH 'sqlite OR backup'
ORDER BY rank;

This is a real answer for site search, documentation search, note apps, and log browsing without standing up a search service. It is not going to outrun Elasticsearch at scale, but for a self-hosted stack it removes an entire category of infrastructure.

STRICT tables: typed SQLite when you want it

The main legitimate complaint about SQLite is manifest typing: you can insert a text value into an INTEGER column and SQLite will happily store it. Since 3.37.0 (2021-11-27), you can opt a table out of that behavior entirely. STRICT tables enforce that columns hold only their declared type, reject unknown type names, and behave much closer to PostgreSQL and MySQL.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  balance REAL NOT NULL
) STRICT;

One detail worth knowing: STRICT restricts allowed types to INT, INTEGER, REAL, TEXT, BLOB, and ANY. So NUMERIC and VARCHAR(255) are rejected inside STRICT tables. The trade is deliberate: strictness in exchange for a smaller, more predictable type system.

Virtual tables and extensions

The virtual table mechanism is what makes FTS5 and JSON table-valued functions possible, and it is an extension point, not a dead end. You can query a CSV file as a table, index spatial data with the RTree module, walk the page-level layout with dbstat, or register your own C functions and collations through the documented extension API. The CSV module and many others compile in or load as runtime extensions depending on your build, which is the one thing to check in your distribution's package.

WAL mode: the concurrency story

Write-Ahead Logging landed in 3.7.0 (2010-07-21) and quietly rewrote what people believe about SQLite concurrency. In WAL mode, readers do not block the writer and the writer does not block readers. A single application process can sustain thousands of writes per second, and multiple processes can read while one writes. And one hard rule comes with the mode: WAL keeps its coordination state in a shared-memory -shm file, so every process using the database must be on the same host, WAL does not work over a network filesystem at all, not merely badly.

PRAGMA journal_mode = WAL;
-- verify
PRAGMA journal_mode;
-- output: wal

The word that matters is "one" in "one writer." SQLite serializes write transactions with a database-wide lock. When two processes try to write at the same moment, the loser gets SQLITE_BUSY, immediately, in fact, because the default busy_timeout is zero. Set PRAGMA busy_timeout = 5000; (or rely on a binding that sets one for you, like Python's sqlite3 module with its five-second default) and the loser waits and retries instead of failing on the first collision. For a web app where every request goes through one backend process, or a self-hosted tool with light write traffic, that is a non-issue. For many concurrent writers, it is the wall.

The CLI is a real tool

The sqlite3 command line program is not a REPL toy, it is an operational tool. You get .mode, .import and .export, .backup, .schema, .dump, .json output, and --safe mode since 3.37.0 that blocks side-effecting statements outside the target file, handy for read-only inspection. All of it works without a server.

$ sqlite3 notes.db
sqlite> .tables
notes  tags
sqlite> .schema notes
CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT, body TEXT);
sqlite> SELECT count(*) FROM notes;
42
sqlite> .mode json
sqlite> SELECT id, title FROM notes LIMIT 2;
[{"id":1,"title":"first"},{"id":2,"title":"second"}]
sqlite> .backup notes-backup.db
sqlite> .exit

Backup: one file, but do it properly

Because the whole database is one file, SQLite has the simplest backup story in the industry: a single file you can copy to a USB stick, a second disk, or another server. With one important caveat. In WAL mode, the main database file does not always contain the latest committed transactions; recent commits may still sit in the -wal file. Copying only the .db file can silently miss data.

Use the tools that know this:

• .backup in the CLI, which drives the online backup API and produces a consistent snapshot, even from a live, WAL-mode database.
• VACUUM INTO 'backup.db', which writes a compacted, consistent copy, ideal for compressing a bloated database before archiving.
• sqlite3_rsync since 3.47.0 (2024-10-21), a bandwidth-efficient tool for live backups of a running database over SSH, a genuine answer to "how do I back this thing up every hour without stopping it."

Whatever you pick, the file-based backup pattern fits naturally into our 3-2-1 backup post: the SQLite file is your copy one, the second disk or remote host is copy two, and VACUUM INTO or sqlite3_rsync gets the copy off your machine.

The honest limits

• One writer at a time. Reads scale fine, especially in WAL mode; writes queue behind a database-wide lock.
• No network protocol. SQLite is an embedded library. Remote access means wrapping it, at which point you have built the server SQLite deliberately is not.
• Afraid of NFS. Network filesystems have weak locking, so SQLite recommends against putting database files on NFS where multiple hosts touch them. In WAL mode it is not a recommendation: shared memory means single-host, so WAL does not work over a network filesystem at all.
• No user model. Access control is file permissions and application code. There is no row-level security or per-user grants.
• Not your HA layer. Single file, single machine by design. Failure across machines is your problem, not the engine's.

These limits are why "when do I outgrow SQLite" has a clear answer: when more than one process needs to write concurrently, when the data must live across machines, or when you need database-level user management. Until then, the SQLite file is often the most robust database you can run, because there is nothing to crash, no port to fight over, and no timeout to tune.

Feature timeline at a glance

One more sign of the project's discipline: in March 2026 SQLite released 3.52.0, then withdrew it a week later because some of the new features were not 100% compatible with prior releases. The problem was narrow, only databases with expression indexes, or indexes on VIRTUAL computed columns whose expression produces a floating-point value derived from text or JSONB, could in rare cases interoperate incorrectly with older versions, but the project shipped patch 3.51.3 in 3.52.0's place and re-released the enhancements as 3.53.0 on 2026-04-09, adding fixes for stale expression indexes. An engine that yanks a release over a rare backwards-compatibility edge is one you can set your clocks by.

The same episode surfaced something that matters even more if you run WAL mode: the WAL-reset bug, a database-corruption data race likely present in every SQLite since WAL shipped in 3.7.0 (2010), found on 2026-03-03. It requires two or more connections writing or checkpointing at exactly the same instant, and it is tight enough that the developers could not reproduce it organicall, but the consequence is corruption, so it is worth acting on: the fix shipped in 3.51.3 (2026-03-13) and in every 3.53.x, with backports to 3.44.6 and 3.50.7. If you run WAL mode, get onto 3.51.3, 3.53.x, or a backport, and treat anything from 3.7.0 through 3.51.2 as overdue for an upgrade.

Official sources

• SQLite home page: https://www.sqlite.org/
• Most Widely Deployed SQL Database Engine: https://www.sqlite.org/mostdeployed.html
• Distinctive Features Of SQLite: https://www.sqlite.org/different.html
• Recent SQLite news and release history: https://www.sqlite.org/news.html
• JSON Functions And Operators (JSONB): https://www.sqlite.org/json1.html
• FTS5 full-text search: https://www.sqlite.org/fts5.html
• Write-Ahead Logging: https://www.sqlite.org/wal.html
• CREATE TABLE and STRICT tables: https://www.sqlite.org/lang_createtable.html
• Latest release log (3.53.4): https://www.sqlite.org/releaselog/3_53_4.html

If your stack is otherwise self-hosted and you are weighing a hosted backend versus local storage, our BaaS vs FaaS comparison covers where an embedded file fits against Supabase and Appwrite, and the small-stack philosophy that makes a single local file attractive is the same one behind our OpenTelemetry traces post.

What is the first thing you would move from a client/server database back into a SQLite file? And have you actually hit SQLITE_BUSY yet, or is that fear older than your current workload? Drop it in the comments.

Until next time, keep your systems thoughtful.