SQLite Cheat Sheet

Every example below uses one small shop database, shop.db, with three tables: users, orders and events.

Opening and Exiting sqlite3

sqlite3 opens a file and creates it if it does not exist. Build a connection string for your app with the SQLite connection string generator.

In 1bench: open a SQLite file on a remote server over SSH, either live in place or as a local snapshot you sync back. See the SQLite GUI.

Example: run a migration script that stops on the first error and returns a non-zero exit code for CI.

sqlite3 -bail shop.db < migrations/004_add_orders_index.sql
echo "exit code: $?"   # 1 if any statement failed

Dot Commands

Dot commands start with a period, take no semicolon and only work in the sqlite3 shell.

Example: find the tables in an unfamiliar database file and describe the one you need.

sqlite3 shop.db '.tables'
sqlite3 shop.db '.schema orders'
sqlite3 -box shop.db "SELECT name, type, \"notnull\", pk FROM pragma_table_info('orders');"

Output Modes

Interactive sessions print boxes by default since 3.53. Scripts and pipes get the plain list format, so set a mode when the output feeds another tool.

Example: write the paid orders to a JSON file from a script.

sqlite3 shop.db <<'SQL'
.mode json
.once paid_orders.json
SELECT id, user_id, total, created_at FROM orders WHERE status = 'paid';
SQL

Tables

INTEGER PRIMARY KEY already auto-assigns ids. Add AUTOINCREMENT only when deleted ids must never come back.

Example: the users and orders tables every example on this page uses.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status TEXT NOT NULL DEFAULT 'pending',
  total REAL NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Writing Data

Wrap bulk writes in BEGIN and COMMIT. Each statement outside a transaction is its own commit and syncs to disk.

Example: upsert a user by email, keep the original id and created_at, and return the row.

INSERT INTO users (email, name)
VALUES ('[email protected]', 'Ada Lovelace')
ON CONFLICT(email) DO UPDATE SET name = excluded.name
RETURNING id, email, name, created_at;

Select and Filtering

SQLite has no TOP. Use LIMIT, with OFFSET for paging.

Example: each user's most recent order, using a window function.

SELECT id, user_id, status, total, created_at
FROM (
  SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
)
WHERE rn = 1;

Joins

SQLite supports INNER, LEFT, RIGHT, FULL OUTER and CROSS joins. RIGHT and FULL need 3.39 or later.

Example: order count and lifetime spend per user, including users who never ordered.

SELECT u.email,
       count(o.id)                         AS orders,
       round(coalesce(sum(o.total), 0), 2) AS spend
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'
GROUP BY u.id
ORDER BY spend DESC;

Import, Export and Backup

.import creates the table from the CSV header when the table does not exist yet.

In 1bench: export a SQLite table to SQL, CSV, JSON or NDJSON, and import those formats back with an option to empty the table first. See the SQLite GUI.

Example: load a CSV export into a staging table, then upsert it into users in one transaction.

sqlite3 -bail shop.db <<'SQL'
CREATE TEMP TABLE staging (email TEXT, name TEXT);
.import --csv --skip 1 new_users.csv staging
BEGIN;
INSERT INTO users (email, name) SELECT email, name FROM staging WHERE true
ON CONFLICT(email) DO UPDATE SET name = excluded.name;
COMMIT;
SQL

PRAGMAs

Most PRAGMAs apply to the current connection only. journal_mode = WAL and user_version are stored in the file.

In 1bench: the SQLite GUI overview shows the file size with page count and page size, table and index counts, encoding, journal mode and SQLite version, without running these PRAGMAs by hand.

Example: the settings most apps run on every new connection.

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

Dates and Times

SQLite has no date type. Store ISO 8601 text or Unix seconds, and the functions below read both. 'now' is UTC.

Example: revenue per month from the orders table.

SELECT strftime('%Y-%m', created_at) AS month,
       count(*)                    AS orders,
       round(sum(total), 2)        AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY month
ORDER BY month;

JSON

JSON is stored in TEXT columns and the functions are built in. ->> returns a SQL value, -> returns JSON. Check a payload with the JSON formatter.

Example: index a JSON field and count events by type, so the filter uses the index.

CREATE INDEX idx_events_type ON events (payload ->> '$.type');

SELECT payload ->> '$.type' AS type, count(*) AS n
FROM events
WHERE payload ->> '$.type' IN ('login', 'purchase')
GROUP BY type;

Indexes and Query Plan

Check the plan before adding an index. SCAN reads every row, SEARCH uses an index.

Example: confirm a lookup goes from a full scan to an index search.

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
-- `--SCAN orders

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
-- `--SEARCH orders USING INDEX idx_orders_user_status (user_id=? AND status=?)

Turso and libSQL

Turso hosts libSQL, a fork of SQLite, so the SQL above runs unchanged. The turso CLI manages databases and opens a shell with the common dot commands.

Example: move a local SQLite file to Turso and get the URL and token for your app.

sqlite3 shop.db 'PRAGMA journal_mode = WAL;'
turso db import shop.db
turso db show shop --url        # libsql://... URL for the client
turso db tokens create shop     # pass as the auth token

Gotchas

• Foreign keys are off by default and the setting does not persist. Run PRAGMA foreign_keys = ON; on every connection, or ON DELETE CASCADE and REFERENCES checks silently do nothing.
• INSERT OR REPLACE deletes the clashing row and inserts a new one. Columns you leave out reset to their defaults, and with foreign keys on, ON DELETE CASCADE removes the child rows. In testing, replacing a user wiped all of their orders. Use ON CONFLICT ... DO UPDATE instead.
• Column types are suggestions unless the table is STRICT. An INTEGER column happily stores 'abc' as text. Store money as integer cents too, since REAL sums give results like 62.489999999999995.
• .import into a table that already exists treats the header line as data. Add --skip 1.
• ALTER TABLE ... ADD COLUMN cannot add a UNIQUE or PRIMARY KEY column ("Cannot add a UNIQUE column"). Add the plain column, then CREATE UNIQUE INDEX on it.
• "database is locked" means another connection holds the write lock. Turn on WAL, set busy_timeout and keep write transactions short.