Skip to main content

SQLite: A Practical Guide to the Database You Do Not Run

Why a single file is often all the database you need, and the four settings to switch on before you trust it

Updated 9 min readMarkdown
SQLite: A Practical Guide to the Database You Do Not Run

Pick up your phone. Your messages, your contacts, your browser history and the settings of most apps you have installed are sitting in SQLite databases right now. There is no server process, nobody administers it, and you have never had to restart it.

That is the whole idea. SQLite is a database you do not run. You just open a file.

It is also far more capable than its "lite" name suggests, provided you know a few settings that are off by default. This guide walks through them.

What SQLite is

SQLite is a relational database engine packaged as a small library. Your program links to it and calls it directly, in the same process. There is no network connection, no port, no username or password. The entire database, tables, indexes and all, is a single ordinary file on disk.

The code is in the public domain, so anyone can use it for anything without a licence. Its authors describe it as a replacement for fopen(), the function that opens a file, more than a replacement for a client-server database.

It supports most of the SQL you already know: joins, transactions, views, triggers, window functions, common table expressions, JSON functions and full-text search.

Why people choose it

Nothing to set up

A database is created the moment you open a file name that does not exist yet:

sqlite3 app.db

Most languages ship with SQLite support built in or one package away. There is no install step for your users and nothing to keep running.

It is fast for local work

Because there is no network hop, a simple query costs about as much as a function call. Running many small queries in a row, which would be wasteful against a server, is perfectly reasonable in SQLite.

One file is easy to move

You can copy a database to another machine, attach it to a bug report, or ship it inside an application. The file format is stable and works across operating systems.

It is dependable

SQLite transactions are fully ACID: a change either happens completely or not at all, even if the power fails halfway. The project is known for unusually thorough testing.

Where SQLite fits, and where it does not

A strong choice for:

  • Mobile and desktop applications
  • Devices and embedded systems
  • Command-line tools and local caches
  • Small and medium websites running on one server
  • Analysing a data set on your laptop
  • Tests and prototypes
  • An application's own file format (a document that is really a database)

Look elsewhere when:

  • Many processes write at the same moment. SQLite allows any number of readers but only one writer at a time. Short writes queue up nicely; a write-heavy workload with long transactions will not.
  • Several servers need the same database. The file lives on one machine's disk. If your application runs on many machines, use a client-server database such as PostgreSQL or MySQL, or a hosted SQLite-compatible service such as Turso or Cloudflare D1.
  • The file would sit on a network share. File locking over network file systems is unreliable, and that can corrupt the database.
  • You need users and permissions inside the database. SQLite has none. Whoever can read the file can read everything.

Using it the right way

Switch on the settings you want

SQLite keeps old defaults for backward compatibility. Run these each time you open a connection:

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;

What each one does:

  • journal_mode = WAL turns on write-ahead logging. Readers no longer block the writer and the writer no longer blocks readers. This one setting fixes most "database is locked" complaints. It is remembered in the file, so you only strictly need to set it once.
  • foreign_keys = ON makes SQLite enforce foreign keys. Without it, they are parsed and then ignored. It must be set on every connection.
  • busy_timeout = 5000 tells a connection to wait up to five seconds for a lock instead of failing immediately.
  • synchronous = NORMAL is a common pairing with WAL: much faster writes, and the database stays consistent after a crash, though the last few transactions may be lost on power failure. Keep the default FULL if you cannot accept that.

Make the types strict

By default SQLite uses "type affinity": a column declared INTEGER will happily store the text 'hello'. A STRICT table refuses values of the wrong type.

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  status      TEXT NOT NULL DEFAULT 'pending',
  details     TEXT,
  created_at  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;

Two things to notice:

  • INTEGER PRIMARY KEY is special. It becomes the table's built-in row id, so lookups by it are as quick as SQLite gets, and it fills itself in when you insert without a value.
  • There is no date type. Store times as ISO 8601 text in UTC, as above, or as an integer count of seconds. Pick one and use it everywhere. The text form sorts correctly and is readable.

Group your writes in a transaction

Each write outside a transaction is its own transaction, with its own trip to disk. Wrapping a batch makes it dramatically faster:

BEGIN;
INSERT INTO orders (customer_id, total_cents) VALUES (1, 1250);
INSERT INTO orders (customer_id, total_cents) VALUES (2, 899);
-- ... thousands more
COMMIT;

If a transaction will write, start it with BEGIN IMMEDIATE. It takes the write lock up front, so the busy timeout can do its job, instead of failing partway through when it tries to upgrade from reading to writing.

Index and check the plan

CREATE INDEX orders_customer_created_idx
  ON orders (customer_id, created_at);

EXPLAIN QUERY PLAN
SELECT id, total_cents FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

SEARCH ... USING INDEX is what you hope to see. SCAN orders means SQLite is reading the whole table.

Query JSON in place

JSON is stored as text and queried with built-in functions and operators:

SELECT id, details ->> '$.coupon' AS coupon
FROM orders
WHERE details ->> '$.gift_wrap' = 1;

Back up safely

Do not copy a database file with cp while an application is writing to it. You can catch it mid-change, and with WAL mode some of the data lives in a separate -wal file. Use one of the safe methods:

sqlite3 app.db ".backup backup.db"
sqlite3 app.db "VACUUM INTO 'backup-compact.db'"

Both produce a consistent copy while the database is in use. For continuous backup of a production file, look at a streaming tool such as Litestream.

To check a file for damage:

PRAGMA integrity_check;

Import and export from the shell

The sqlite3 shell has "dot commands" that are handy for quick data work:

.mode csv
.import --skip 1 customers.csv customers
.headers on
.once report.csv
SELECT status, count(*) FROM orders GROUP BY status;

Common mistakes

  • Assuming foreign keys are enforced. They are not until you run PRAGMA foreign_keys = ON on that connection.
  • Staying on the default journal mode for a web app. Readers and the writer block each other. Use WAL.
  • Holding a write transaction open while doing slow work. Calling an API or waiting for user input inside a transaction locks out every other writer. Keep write transactions short.
  • Putting the file on a network drive or a synced folder. Locking breaks and sync tools may copy a half-written file.
  • Deleting the -wal and -shm files. They are part of the database while it is open. Removing them can lose recent changes.
  • Mixing date formats. With no date type, nothing stops one row holding 2026-03-01 and another 01/03/2026. Comparisons then quietly go wrong.
  • Expecting ALTER TABLE to do everything. You can add, rename and drop columns, but changing a column's type or constraints means creating a new table and copying the data across.
  • Treating it as a toy. Many "we need a database server" problems on a single machine are handled comfortably by SQLite in WAL mode.

Quick reference

Task Command
Open or create a database sqlite3 app.db
List tables .tables
Show a table's definition .schema orders
Readable output .mode box
Query plan EXPLAIN QUERY PLAN SELECT ...;
Enable WAL PRAGMA journal_mode = WAL;
Enforce foreign keys PRAGMA foreign_keys = ON;
Safe backup .backup backup.db
Check for corruption PRAGMA integrity_check;
Reclaim space VACUUM;
SQLite version SELECT sqlite_version();

SQLite asks for very little: four pragmas, strict tables, short write transactions and a proper backup command. In return you get a real SQL database with no server to look after.

Exploring SQLite with CreamDB

One honest limit first. CreamDB is a hosted workspace that runs in your browser, so it cannot open a SQLite file sitting on your laptop. For a local file, the sqlite3 shell or a desktop viewer is the right tool.

Where CreamDB helps is with SQLite-compatible databases that live on the internet:

  • libSQL and Turso, a fork of SQLite offered as a hosted service. You connect with the database URL and an auth token.
  • Cloudflare D1, SQLite running on Cloudflare's network.

For those you get the same workspace as any other SQL database: a tree of tables, a grid with paging, sorting and filtering, an editor with autocomplete, formatting and history, and export to CSV, JSON or Excel. You can also ask in plain English and get the SQL, a result table and a chart.

The assistant only reads on its own, and any change waits as an approval card for you to answer.

It is free to start, with nothing to install.

About the author

  • CreamDB is a web-based database explorer with an AI assistant. Connect your databases, ask questions in plain English and get answers, tables and charts from any browser. Product news and tips here.

The four pragma block is the most useful part for me. Foreign keys being parsed but ignored until you turn them on is the kind of default that quietly causes real damage, and the same goes for staying off WAL in a web app. I also appreciated the backup warning: copying a live file with cp feels safe until you learn what the wal file holds.