Skip to content

SQLite Cheat Sheet

SQLite is a lightweight SQL database engine and sqlite3 is its command-line interface. This SQLite cheatsheet provides an advanced, scan-friendly reference for installing SQLite, creating databases and tables, querying data, controlling transactions, using PRAGMA settings, inspecting schema, and exporting/importing data.

Install sqlite3

Install the SQLite CLI on common platforms.

# Debian/Ubuntu
sudo apt-get update
sudo apt-get install -y sqlite3

# Fedora
sudo dnf install -y sqlite

# macOS (Homebrew)
brew install sqlite

# Windows (PowerShell, example with winget if available)
winget install SQLite.SQLite

sqlite3 --version
# 3.xx.x 202x-xx-xx ...

Create or open a database file

Opening a database file creates it if it does not exist.

sqlite3 path/to/app.db

SQLite version 3.xx.x
Enter ".help" for usage hints.
sqlite>

Open an in-memory database

The special name :memory: creates a database that lives only in memory for the life of the connection.

sqlite3 :memory:

sqlite> .databases
main: "" r/w

Exit sqlite3

Exit the interactive shell.

.quit
.exit

sqlite> .quit

Show help for sqlite3 meta-commands

List dot-commands supported by the CLI.

.help

sqlite> .help
.archive ...     .databases ...   .tables ...

List databases and attachments

Display attached databases and file paths.

.databases

sqlite> .databases
main: /tmp/app.db r/w

Open a different database in the same session

Switch the active database file.

.open FILENAME

sqlite> .open other.db
sqlite> .databases
main: /tmp/other.db r/w

Attach and detach databases

Attach additional database files under a schema name.

ATTACH DATABASE 'analytics.db' AS analytics;
DETACH DATABASE analytics;

ATTACH DATABASE 'analytics.db' AS analytics;
SELECT name FROM analytics.sqlite_master WHERE type='table';
DETACH DATABASE analytics;

Configure output to table mode

Table mode renders results in a grid format.

.mode table
.headers on

sqlite> .mode table
sqlite> .headers on
sqlite> SELECT 1 AS id, 'ok' AS status;
id  status
--  ------
1   ok

Configure output to CSV mode

CSV mode is useful for exporting query results.

.mode csv
.separator ,

sqlite> .mode csv
sqlite> .separator ,
sqlite> SELECT 1,'a';
1,a

Redirect output to a file

Send subsequent query output to a file, then restore to stdout.

.output FILENAME
.output stdout

sqlite> .output out.csv
sqlite> SELECT 'id','name' UNION ALL SELECT 1,'Alice';
sqlite> .output stdout

Execute SQL from a file

Run a .sql file inside the current session.

.read FILENAME.sql

sqlite> .read schema.sql

Dump a database as SQL

Dump schema and data as SQL statements.

.dump
.dump table_name

sqlite> .output backup.sql
sqlite> .dump
sqlite> .output stdout

Backup a live database

Create a consistent backup from within sqlite3.

.backup FILENAME

sqlite> .backup backup.db

Restore a database from a backup

Replace the current database contents with a backup file.

.restore FILENAME

sqlite> .restore backup.db

Import CSV into a table

Import a CSV file into an existing table.

.mode csv
.import FILE.csv table_name

sqlite> .mode csv
sqlite> .import users.csv users

SQLite storage classes and type affinity

SQLite uses storage classes for values and type affinity for columns.

Storage classes: NULL, INTEGER, REAL, TEXT, BLOB
Type affinities: TEXT, NUMERIC, INTEGER, REAL, BLOB

SELECT typeof(123) AS t1, typeof(123.4) AS t2, typeof('x') AS t3, typeof(x'00') AS t4, typeof(NULL) AS t5;

t1       t2     t3    t4    t5
-------  -----  ----  ----  ----
integer  real   text  blob  null

Data types table

Common declared type names map to affinities; values are stored using storage classes.

Declared Type Examples Affinity Typical Use
INT, INTEGER, BIGINT, TINYINT INTEGER Integers and row identifiers
REAL, DOUBLE, FLOAT REAL Floating-point numbers
NUMERIC, DECIMAL, BOOLEAN, DATE, DATETIME NUMERIC Numbers/dates stored with numeric conversion rules
CHAR(n), VARCHAR(n), TEXT, CLOB TEXT Text data
BLOB BLOB Binary data

Create a table

Create a basic table with common constraints.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (email, display_name) VALUES ('a@example.com', 'Alice');
SELECT id, email, display_name, created_at FROM users;

1|a@example.com|Alice|2025-12-27 00:00:00

INTEGER PRIMARY KEY and rowid

INTEGER PRIMARY KEY is an alias for the internal rowid and auto-assigns values when omitted.

CREATE TABLE t (
  id INTEGER PRIMARY KEY,
  v  TEXT
);

INSERT INTO t (v) VALUES ('x'), ('y');
SELECT rowid, id, v FROM t;

1|1|x
2|2|y

WITHOUT ROWID tables

WITHOUT ROWID stores rows as a B-tree keyed by the PRIMARY KEY instead of an implicit rowid.

CREATE TABLE kv (
  k TEXT PRIMARY KEY,
  v BLOB
) WITHOUT ROWID;

INSERT INTO kv (k, v) VALUES ('a', x'0102');
SELECT k, length(v) FROM kv;

a|2

Composite primary keys and UNIQUE constraints

Define multi-column keys and separate uniqueness constraints.

CREATE TABLE enrollment (
  course_code TEXT NOT NULL,
  student_id  TEXT NOT NULL,
  enrolled_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (course_code, student_id),
  UNIQUE (student_id, enrolled_at)
);

INSERT INTO enrollment(course_code, student_id) VALUES ('CS101','s1');
SELECT course_code, student_id FROM enrollment;

CS101|s1

NOT NULL constraint

Prevent inserting NULL in required columns.

CREATE TABLE items (
  sku TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO items (sku, price) VALUES ('X1', 9.99);
SELECT sku, price FROM items;

X1|9.99

Foreign keys (enable and verify)

Foreign key enforcement must be enabled per connection.

PRAGMA foreign_keys = ON;
PRAGMA foreign_key_check;

PRAGMA foreign_keys = ON;

CREATE TABLE parent (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE child (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE SET NULL
);

PRAGMA foreign_key_check;

-- (no rows means no violations)

Insert rows

Insert with explicit column list or defaulted columns.

INSERT INTO users (email, display_name) VALUES (?, ?);
INSERT INTO users (email) VALUES ('b@example.com');

INSERT INTO users (email, display_name) VALUES ('c@example.com', 'Carol');
SELECT id, email, display_name FROM users ORDER BY id;

1|a@example.com|Alice
2|b@example.com|
3|c@example.com|Carol

Update rows

Update with computed expressions and filtered targets.

UPDATE users
SET display_name = upper(display_name)
WHERE display_name IS NOT NULL;

SELECT id, display_name FROM users ORDER BY id;

1|ALICE
2|
3|CAROL

Delete rows

Delete by condition or clear a table.

DELETE FROM users WHERE id = 2;
DELETE FROM users; -- removes all rows

DELETE FROM users WHERE id = 2;
SELECT COUNT(*) FROM users;

2

Select query structure

Standard SELECT clause ordering in SQLite.

SELECT select_list
FROM table_or_subquery
WHERE predicate
GROUP BY group_list
HAVING group_predicate
ORDER BY order_list
LIMIT count OFFSET offset;

SELECT email
FROM users
WHERE email LIKE '%@example.com'
ORDER BY email
LIMIT 10 OFFSET 0;

a@example.com
c@example.com

Join tables

Join tables with explicit ON conditions.

SELECT c.id, c.parent_id, p.name
FROM child AS c
JOIN parent AS p
  ON p.id = c.parent_id;

SELECT c.id, p.name
FROM child c
JOIN parent p ON p.id = c.parent_id;

-- (result depends on inserted data)

Left join and NULL extension

Rows with no match in the right table produce NULLs.

SELECT p.id, p.name, c.id AS child_id
FROM parent p
LEFT JOIN child c ON c.parent_id = p.id
ORDER BY p.id;

SELECT p.id, p.name, c.id AS child_id
FROM parent p
LEFT JOIN child c ON c.parent_id = p.id;

1|ParentName|NULL

Grouping and aggregates

Aggregate functions compute per-group values.

SELECT parent_id, COUNT(*) AS child_count
FROM child
GROUP BY parent_id
HAVING COUNT(*) >= 1
ORDER BY child_count DESC;

SELECT parent_id, COUNT(*) AS child_count
FROM child
GROUP BY parent_id;

1|3
2|1

Sorting and pagination

Sort and page with ORDER BY, LIMIT, and OFFSET.

SELECT id, email
FROM users
ORDER BY id
LIMIT 20 OFFSET 40;

SELECT id, email
FROM users
ORDER BY id
LIMIT 2 OFFSET 0;

1|a@example.com
3|c@example.com

Common Table Expressions (CTE)

CTEs define named subqueries for a single statement.

WITH active_users AS (
  SELECT id, email
  FROM users
  WHERE display_name IS NOT NULL
)
SELECT * FROM active_users ORDER BY id;

WITH u AS (SELECT id, email FROM users)
SELECT COUNT(*) FROM u;

2

Recursive CTE

Recursive CTEs iterate over hierarchical data.

WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5
)
SELECT n FROM seq;

WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 3
)
SELECT group_concat(n, ',') FROM seq;

1,2,3

Create an index

Indexes speed lookup and sorting for predicates and ORDER BY expressions.

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email_uq ON users(email);
DROP INDEX idx_users_email;

CREATE INDEX idx_child_parent ON child(parent_id);
PRAGMA index_list('child');

0|idx_child_parent|0|c|0

Expression index

Index an expression used in predicates.

CREATE INDEX idx_users_email_lower ON users(lower(email));

SELECT id FROM users WHERE lower(email) = 'a@example.com';
EXPLAIN QUERY PLAN SELECT id FROM users WHERE lower(email) = 'a@example.com';

SEARCH users USING INDEX idx_users_email_lower (<expr>=?)

Create a view

Views store a named SELECT for reuse.

CREATE VIEW v_user_emails AS
SELECT id, email
FROM users
WHERE email IS NOT NULL;

SELECT * FROM v_user_emails ORDER BY id;

1|a@example.com
3|c@example.com

Create a trigger

Triggers execute SQL automatically on INSERT/UPDATE/DELETE events.

CREATE TABLE audit_log (
  id INTEGER PRIMARY KEY,
  table_name TEXT NOT NULL,
  action TEXT NOT NULL,
  row_id INTEGER,
  at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TRIGGER users_ai
AFTER INSERT ON users
BEGIN
  INSERT INTO audit_log(table_name, action, row_id)
  VALUES ('users', 'INSERT', new.id);
END;

INSERT INTO users (email, display_name) VALUES ('d@example.com','Dan');
SELECT table_name, action, row_id FROM audit_log ORDER BY id DESC LIMIT 1;

users|INSERT|4

Begin a transaction

Group statements into an atomic unit of work.

BEGIN;
-- statements
COMMIT;
-- or:
ROLLBACK;

BEGIN;
UPDATE users SET display_name = 'X' WHERE id = 9999;
ROLLBACK;

-- ROLLBACK completed

Savepoints

Savepoints allow partial rollback within a transaction.

BEGIN;
SAVEPOINT sp1;
-- statements
ROLLBACK TO sp1;
RELEASE sp1;
COMMIT;

BEGIN;
SAVEPOINT sp1;
INSERT INTO users (email) VALUES ('e@example.com');
ROLLBACK TO sp1;
RELEASE sp1;
COMMIT;

-- Insert rolled back to savepoint

EXPLAIN QUERY PLAN

Show how SQLite intends to execute a query.

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@example.com';

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@example.com';

SEARCH users USING INDEX idx_users_email (email=?)

ANALYZE

Collect statistics to help the query planner choose indexes.

ANALYZE;
ANALYZE table_name;

ANALYZE;
SELECT name FROM sqlite_master WHERE type='table';

users
child
parent

VACUUM

Rebuild the database file to reclaim free space.

VACUUM;

VACUUM;
PRAGMA page_count;

-- page_count is engine- and DB-dependent

PRAGMA journal_mode

Query or set journaling mode.

PRAGMA journal_mode;
PRAGMA journal_mode = WAL;

PRAGMA journal_mode;
PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

delete
wal

PRAGMA synchronous

Control the synchronization level for transactions.

PRAGMA synchronous;
PRAGMA synchronous = OFF|NORMAL|FULL|EXTRA;

PRAGMA synchronous;
PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

2
1

PRAGMA busy_timeout

Set how long SQLite waits when a table is locked.

PRAGMA busy_timeout;
PRAGMA busy_timeout = 5000;

PRAGMA busy_timeout = 2000;
PRAGMA busy_timeout;

2000

PRAGMA cache_size

Configure page cache size (units depend on sign and build).

PRAGMA cache_size;
PRAGMA cache_size = 2000;

PRAGMA cache_size = 4000;
PRAGMA cache_size;

4000

PRAGMA temp_store

Control where temporary tables and indices are stored.

PRAGMA temp_store;
PRAGMA temp_store = DEFAULT|FILE|MEMORY;

PRAGMA temp_store = MEMORY;
PRAGMA temp_store;

2

PRAGMA mmap_size

Control the memory-mapped I/O region size.

PRAGMA mmap_size;
PRAGMA mmap_size = 268435456;

PRAGMA mmap_size = 134217728;
PRAGMA mmap_size;

134217728

PRAGMA wal_checkpoint

Checkpoint WAL content back into the main database file.

PRAGMA wal_checkpoint;
PRAGMA wal_checkpoint(FULL);
PRAGMA wal_checkpoint(TRUNCATE);

PRAGMA wal_checkpoint;

0|0|0

PRAGMA integrity_check and quick_check

Validate database integrity; quick_check skips some validations.

PRAGMA integrity_check;
PRAGMA quick_check;

PRAGMA quick_check;

ok

PRAGMA optimize

Run optimizer maintenance tasks.

PRAGMA optimize;
PRAGMA optimize(-1);

PRAGMA optimize;

-- (no output on success in many builds)

Schema introspection via PRAGMA

Inspect columns, indexes, and foreign keys.

PRAGMA table_info('users');
PRAGMA index_list('users');
PRAGMA index_info('idx_users_email');
PRAGMA foreign_key_list('child');

PRAGMA table_info('users');

0|id|INTEGER|0||1
1|email|TEXT|1||0
2|display_name|TEXT|0||0
3|created_at|TEXT|1|datetime('now')|0

sqlite_master catalog query

Use sqlite_master to list schema objects and their SQL definitions.

SELECT type, name, tbl_name, sql
FROM sqlite_master
WHERE type IN ('table','index','view','trigger')
ORDER BY type, name;

SELECT type, name
FROM sqlite_master
ORDER BY type, name;

index|idx_users_email
table|users
view|v_user_emails

FTS5 virtual table

Full-text search using the FTS5 extension.

CREATE VIRTUAL TABLE docs USING fts5(title, body);
INSERT INTO docs(title, body) VALUES ('Intro', 'SQLite full text search');
SELECT rowid, title FROM docs WHERE docs MATCH 'text';

1|Intro

JSON functions (JSON1)

Query JSON content stored in TEXT columns when JSON1 is available.

SELECT json_extract(data, '$.user.id') AS user_id
FROM events
WHERE json_type(data, '$.user.id') IS NOT NULL;

CREATE TABLE events (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO events(data) VALUES ('{"user":{"id":42}}');
SELECT json_extract(data, '$.user.id') FROM events;

42

STRICT tables

STRICT tables enforce type constraints more strictly (SQLite feature).

CREATE TABLE strict_users (
  id INTEGER PRIMARY KEY,
  age INTEGER NOT NULL
) STRICT;

INSERT INTO strict_users(age) VALUES (30);
SELECT id, age FROM strict_users;

1|30

Date and time functions

SQLite provides built-in date/time functions that return TEXT by default.

SELECT date('now'), time('now'), datetime('now');
SELECT datetime('now','-7 days'), datetime('now','start of month');

SELECT datetime('now') AS now_utc;

2025-12-27 00:00:00

Common CLI schema browsing

List tables and show CREATE statements quickly in sqlite3.

.tables
.schema table_name

```text sqlite> .tables audit_log child docs enrollment items parent strict_users users sqlite> .schema users CREATE TABLE users( id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, display_name TEXT, created_at TEXT NOT NULL DEFAULT (datetime('now')) );