# 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:

```bash
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:

```sql
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.

```sql
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:

```sql
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

```sql
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:

```sql
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:

```bash
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:

```sql
PRAGMA integrity_check;
```

### Import and export from the shell

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

```text
.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](https://www.creamdb.com) 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.

