SQLite Cheat-Sheet

General SQL and engine differences: SQL.

SQLite is a relational database contained in a C library. In contrast to many other databases, SQLite is not a client-server database engine. Rather, it’s embedded into an end program.

SQLite generally follows the PostgreSQL (PostgreSQL Cheat-Sheet) syntax but does not enforce type checking (columns use type affinity; STRICT tables are available since 3.37).

You can open a SQLite Database with sqlite3 <filename> directly.

Shell Commands (dot-commands) #

Command Line #

Useful PRAGMAs #

Examples #

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (name) VALUES ('alice'), ('bob');
SELECT name, datetime(created_at, 'localtime') FROM users;
ALTER TABLE users ADD COLUMN email TEXT;
CREATE INDEX idx_users_name ON users(name);
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'alice';

Users and Access #

SQLite has no users, roles or passwords. Access is controlled by file permissions on the database file and its directory (SQLite also creates -wal, -shm or -journal files next to it, so the directory must be writable by the same user):

chmod 600 app.db
chown appuser:appuser app.db

Open read-only with sqlite3 -readonly app.db or file:app.db?mode=ro URIs. For encryption use SQLCipher or an encrypted file system.

Indexes #

EXPLAIN QUERY PLAN #

EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'alice';

In the shell, .eqp on shows the plan for each query, and .scanstats on adds scan statistics.

Vacuum and Maintenance #

Performance Settings #

Set per connection when opening the database (WAL persists in the file, the rest do not):

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;      -- safe with WAL, much faster than FULL
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -64000;       -- negative = KiB, here 64 MB
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 268435456;

Group many writes in one transaction (BEGIN; ... COMMIT;), it is the largest single speed-up. One writer at a time: use WAL and a busy timeout for concurrent readers. Do not put SQLite database files on NFS or other network file systems (NFS), file locking is unreliable.

Extensions and Features #

Backup #

• sqlite3 app.db ".backup 'backup.db'" or VACUUM INTO are consistent while the app is running.
• .dump gives portable SQL, slower to restore.
• Copying the file is only safe when no process writes. With WAL copy the -wal and -shm files too, or checkpoint first.
• Litestream streams WAL changes to S3-compatible storage for continuous backup.

Notes #

• sqlite_master lists all schema objects: SELECT name, type FROM sqlite_master;
• Do not copy a live database file with cp while it is being written. Use .backup.
• Browse with DB Browser for SQLite.