SQLite

There is a database on your phone right now. Several, actually. It's in your browser, your text messages, your camera roll, your smart TV, and the flight-control software on the plane you flew last summer. You've never installed it, never configured it, never paid for it — and it may be the most widely deployed piece of software in the history of computing. It's SQLite, and there are more than a trillion copies in active use. Yet most developers think of it as the "toy database" you use before you graduate to a "real" one. That's a mistake. Let's fix it.

A Brief History

SQLite was written in the spring of 2000 by D. Richard Hipp, originally for a U.S. Navy program running on a General Dynamics guided-missile destroyer. The requirement was unusual: the database had to keep working even when there was no dedicated database server available. So Hipp built an engine that needs no server at all — the entire database is a single file on disk, and the "database software" is just a library your program links against.

The result was released into the public domain (not merely open source — the code has no copyright and no license to accept), which is a large part of why it ended up everywhere. Apple, Google, Microsoft, Adobe, Mozilla, and thousands of others could adopt it with zero legal friction. Today SQLite is developed by a small, famously disciplined team, backed by an aviation-grade test suite with over 90 million lines of test code — roughly 600 times more test code than library code. It is one of the most thoroughly tested pieces of software on Earth.

The One Idea That Makes SQLite Different

Every database you've probably used before — MySQL, PostgreSQL, SQL Server, Oracle — is a client/server database. There's a separate server process running somewhere, and your application talks to it over a socket or the network. That server has to be installed, started, secured, tuned, backed up, and kept alive.

SQLite is serverless (in the original sense of the word, not the cloud-marketing sense). There is no process to run. Your application calls into the SQLite library directly, and the library reads and writes an ordinary file:

# There is no server to start. There is just a file.
sqlite3 mydata.db

That single design decision is the source of every SQLite strength and every SQLite limitation. It's why SQLite is trivially embeddable and requires zero administration — and also why it isn't built for hundreds of applications on different machines hammering the same database at once.

Getting Started

SQLite ships with a command-line shell that is genuinely pleasant to use.

# Open (or create) a database file
sqlite3 shop.db

# Inside the shell:
CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL,
    price   REAL,
    in_stock INTEGER DEFAULT 1
);

INSERT INTO products (name, price) VALUES ('Widget', 9.99), ('Gadget', 19.95);

SELECT * FROM products WHERE price < 15;

The shell has a set of dot-commands — meta-commands that start with a . and aren't SQL. These are where a lot of the day-to-day power lives:

.tables              # list all tables
.schema products     # show the CREATE statement for a table
.mode column         # pretty, aligned output
.headers on          # show column names
.mode box            # draw a nice unicode table
.import data.csv items   # load a CSV straight into a table
.output report.txt   # redirect query results to a file
.quit

Want to see the whole database as SQL text? .dump prints every CREATE and INSERT needed to rebuild it — which doubles as a perfect, human-readable backup:

sqlite3 shop.db .dump > backup.sql
# ...restore it anywhere:
sqlite3 restored.db < backup.sql

The Type System Is Weird (On Purpose)

Here is the one thing that surprises people coming from other databases: SQLite is dynamically typed. In most databases, a column declared INTEGER will reject a string. In SQLite, types are advisory. A column has a "type affinity" that nudges values toward a preferred type, but you can generally store any kind of value in any column.

CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES (42);        -- stored as integer
INSERT INTO t VALUES ('hello');   -- stored as text, and SQLite allows it

This flexibility can hide bugs. If you want the strict behavior you're used to, SQLite added STRICT tables in version 3.37 (2021):

CREATE TABLE t (x INTEGER) STRICT;
INSERT INTO t VALUES ('hello');   -- now this fails, as you'd expect

If you're starting a new project, STRICT tables are almost always the right call.

It's More Capable Than You Think

The "toy database" reputation is badly out of date. Modern SQLite supports a large slice of advanced SQL:

-- Window functions (since 3.25)
SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

-- Common Table Expressions and recursion (since 3.8.3)
WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

-- Full-text search (via the FTS5 extension, built in)
CREATE VIRTUAL TABLE docs USING fts5(title, body);
SELECT * FROM docs WHERE docs MATCH 'sqlite AND fast';

-- First-class JSON support
SELECT json_extract(payload, '$.user.email') FROM events;

-- Generated columns, UPSERT, RETURNING, partial indexes...
INSERT INTO products (id, name) VALUES (1, 'Widget')
  ON CONFLICT(id) DO UPDATE SET name = excluded.name
  RETURNING *;

Several of these earn their own deep dive — and the SQL is identical in SQLite — so see our full guides to SQL Window Functions and Common Table Expressions in SQL. There's even a companion library, sqlite-vec, that adds vector similarity search — so you can build a small AI/RAG application entirely inside a single SQLite file.

The One Setting Everyone Should Change: WAL Mode

By default, SQLite uses a rollback journal, and writers block readers. For anything with concurrent access, switch on Write-Ahead Logging. You do this once per database and it sticks:

PRAGMA journal_mode = WAL;

In WAL mode, readers no longer block the writer and the writer no longer blocks readers — a single writer and many simultaneous readers can all proceed at once. Combined with a couple of companion pragmas, this transforms SQLite's real-world throughput:

PRAGMA journal_mode = WAL;      -- readers and writer stop fighting
PRAGMA synchronous = NORMAL;    -- safe with WAL, much faster
PRAGMA foreign_keys = ON;       -- enforce foreign keys (off by default!)
PRAGMA busy_timeout = 5000;     -- wait 5s instead of failing on a lock

That foreign_keys = ON line catches many newcomers: SQLite parses foreign-key constraints but does not enforce them unless you turn enforcement on for the connection.

When SQLite Is the Right Choice

SQLite's own maintainers put it well: the real question isn't SQLite versus other databases, it's SQLite versus a plain file. Reach for it when:

• It's an application file format. Instead of inventing a custom binary format or a pile of loose files, store your app's document as one SQLite database. This is exactly how many desktop and mobile apps work.
• It runs on the edge or on-device. Phones, browsers, IoT devices, embedded systems — anywhere you can't run a server.
• It's a small-to-medium website. A read-heavy site comfortably serving most of the web's traffic patterns runs fine on SQLite. The whole "database" is a file your web server already has open.
• It's a cache, a queue, or a scratchpad. Local, transactional, zero-setup storage for a single process.
• It's for testing. Spin up a real SQL database in-memory (sqlite3 :memory:) for fast, isolated tests.
• It's for data analysis. Load a few CSVs and run real SQL over them without standing up any infrastructure. (For heavier, column-oriented analytics, see our DuckDB Crash Course — often described as "SQLite for analytics.")

When to Reach for Something Else

Be honest about the limits. Choose a client/server database when:

• Many machines need to write concurrently. SQLite allows only one writer at a time. High write concurrency across a fleet of app servers is exactly what Postgres and MySQL are built for.
• The data is huge. SQLite handles databases into the terabytes, but at very large scale a dedicated server with replication and sophisticated query planning earns its keep.
• You need network access built in. SQLite is a library, not a network service. If clients on other machines must connect directly to the database, you want a server. (Note the growing ecosystem — Turso/libSQL, rqlite, Litestream — that adds networking, replication, and streaming backups on top of SQLite when you need them.)
• You need fine-grained user permissions. SQLite has no concept of database users or GRANT. Access control is just filesystem permissions.

Quick Reference

Setup Checklist for a New Project

✅ Create tables with STRICT for real type checking
✅ Turn on WAL mode: PRAGMA journal_mode = WAL;
✅ Set PRAGMA foreign_keys = ON; on every connection
✅ Add PRAGMA busy_timeout so brief locks don't error out
✅ Use .backup (not a file copy) to back up a live database
✅ Prefer :memory: databases for fast, isolated tests

Conclusion: SQLite isn't the database you use until you get a real one — for an enormous range of problems, it is the real one. It's the most deployed database on the planet precisely because it disappears: no server, no setup, no maintenance, just a single reliable file that does exactly what a database should. The next time you're about to spin up a server for a project that doesn't need one, or invent a file format for data that's obviously relational, remember there's a trillion-copy-strong, aviation-grade SQL engine already sitting on your machine, waiting.