The database in a file, and the sqlite3 CLI

One file, no server, dynamic typing — plus the CLI commands and pragmas that make it usable for real work.

A database in a single file

SQLite is a library, not a server. Your process links it and reads the file directly, so there is no port, no daemon, no user accounts and no network protocol. That is why it is in almost every phone, browser and desktop application, and why it is a poor fit when many machines need the same database.

sqlite3 app.db

SQLite version 3.45.0
sqlite> .tables
sqlite> .schema orders
sqlite> .mode box          -- pretty output instead of pipe separated
sqlite> .headers on
sqlite> SELECT id, status FROM orders LIMIT 5;
sqlite> .import data.csv measurements --csv
sqlite> .dump orders > orders.sql
sqlite> .backup backup.db
sqlite> .quit

Types, rowid and flexibility

SQLite has dynamic typing. A column has a declared type affinity and a value keeps whatever storage class it was given, so an INTEGER column can legally hold text. Since version 3.37 you can opt into real enforcement with STRICT tables; for new schemas that is usually the right choice.

CREATE TABLE measurements (
  id     INTEGER PRIMARY KEY,     -- alias for rowid: fast, auto-assigned
  sensor TEXT    NOT NULL,
  value  REAL,
  taken  TEXT    NOT NULL DEFAULT (datetime('now'))
) STRICT;

INSERT INTO measurements (sensor, value) VALUES ('s1', 21.4)
RETURNING id, taken;

-- upsert requires a unique constraint to detect the conflict
INSERT INTO measurements (id, sensor, value) VALUES (1, 's1', 22.0)
ON CONFLICT (id) DO UPDATE SET value = excluded.value;

SELECT sensor,
       count(*)          AS n,
       round(avg(value), 2) AS avg_value
FROM   measurements
GROUP  BY sensor
HAVING n > 2
ORDER  BY avg_value DESC;

• INTEGER PRIMARY KEY is an alias for the internal rowid, which makes lookups by it the fastest access path.
• A table declared WITHOUT ROWID stores rows in primary-key order — good for narrow tables with a natural text key.
• STRICT tables allow only INT, INTEGER, REAL, TEXT, BLOB and ANY, and reject values that do not fit.
• Dates are stored as text, numbers or integers depending on the function you use; pick one convention and keep it.

Pragmas worth setting

PRAGMA journal_mode = WAL;      -- better reader/writer concurrency
PRAGMA synchronous  = NORMAL;   -- the usual pairing with WAL
PRAGMA foreign_keys = ON;       -- per connection, off by default
PRAGMA busy_timeout = 5000;     -- wait instead of failing immediately
PRAGMA cache_size   = -64000;   -- negative means kibibytes, so about 64 MB
PRAGMA user_version = 3;        -- your own migration counter
PRAGMA optimize;                -- run before closing a long-lived connection
PRAGMA integrity_check;         -- full structural check (quick_check is faster)

FAQ

Related

Using SQLite from application code WAL mode, backups, and when not to use SQLite

Last refreshed 2026-09-18.