A one-page reference for SQLite: the sqlite3 shell’s dot-commands, the SQL you write every day, and the PRAGMAs that matter. Each section links to a full tested guide.
Open a database with sqlite3 shop.db, list tables with .tables, see a table’s definition with .schema products, and leave with .quit. Everything else on this page is plain SQL that you can run in the shell or from any program.
Every command on this page was run on 6 October 2026 with SQLite 3.51.0, top to bottom in a single sqlite3 session, with no errors. The exported CSV, the import, the dump and the VACUUM INTO backup were then checked.
Download the printable PDF (A4, 2 pages, the same commands as this page).
sqlite3 shell
| Task | Command |
|---|---|
| Open or create a database file | .open shop.db |
| Turn on column headers | .headers on |
| Aligned column output | .mode column |
| List tables | .tables |
| Show a table’s CREATE statement | .schema products |
Export a query to CSV, then import it into a new table (the first row becomes the column names):
.mode csv
.output products.csv
SELECT * FROM products;
.output stdout
.import --csv products.csv products_copyBack up the whole database as SQL text, and restore it into another database with .read:
.output dump.sql
.dump
.output stdout
-- later, in a new database:
.read dump.sql| Task | Command |
|---|---|
| Run SQL from a file | .read dump.sql |
| Quit | .quit |
Create and change tables
| Task | SQL |
|---|---|
| Create a table | CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, price REAL NOT NULL CHECK (price >= 0), added TEXT DEFAULT CURRENT_TIMESTAMP); |
| Create only if missing | CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL REFERENCES products(id), qty INTEGER NOT NULL DEFAULT 1); |
| Create an index | CREATE INDEX IF NOT EXISTS idx_orders_product ON orders(product_id); |
| Add a column | ALTER TABLE products ADD COLUMN stock INTEGER NOT NULL DEFAULT 0; |
| Rename a column | ALTER TABLE products RENAME COLUMN stock TO in_stock; |
More: CREATE TABLE, AUTOINCREMENT.
Inspect a database
| Task | SQL |
|---|---|
| List tables with SQL | SELECT name FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite\_%' ESCAPE '\'; |
| List a table’s columns | PRAGMA table_info(products); |
More: show tables, describe a table.
Insert, update and delete
| Task | SQL |
|---|---|
| Insert rows | INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Notebook', 4.25), ('Stapler', 9.99); |
| Insert and return the new row | INSERT INTO orders (product_id, qty) VALUES (1, 3) RETURNING id, product_id, qty; |
| Insert or update (upsert) | INSERT INTO products (name, price) VALUES ('Pen', 1.75) ON CONFLICT(name) DO UPDATE SET price = excluded.price; |
| Insert, ignoring duplicates | INSERT OR IGNORE INTO products (name, price) VALUES ('Pen', 99); |
| Update rows | UPDATE products SET in_stock = in_stock + 10 WHERE name = 'Pen'; |
| Delete rows | DELETE FROM products WHERE name = 'Stapler'; |
More: upsert.
Query
| Task | SQL |
|---|---|
| Filter, sort and limit | SELECT name, price FROM products WHERE price < 5 ORDER BY price DESC LIMIT 10; |
| Pattern match (case-insensitive for ASCII) | SELECT name FROM products WHERE name LIKE 'n%'; |
| Group and aggregate | SELECT product_id, count(*) AS orders, sum(qty) AS units FROM orders GROUP BY product_id HAVING sum(qty) > 0; |
| Join two tables | SELECT o.id, p.name, o.qty FROM orders AS o JOIN products AS p ON p.id = o.product_id; |
| Conditional value | SELECT name, iif(price > 2, 'pricey', 'cheap') AS band FROM products; |
| Replace NULL | SELECT name, coalesce(NULL, 'n/a') AS note FROM products LIMIT 1; |
| Common table expression | WITH cheap AS (SELECT * FROM products WHERE price < 5) SELECT count(*) FROM cheap; |
| Window function | SELECT name, price, rank() OVER (ORDER BY price DESC) AS price_rank FROM products; |
More: SELECT, window functions.
Strings
| Task | SQL |
|---|---|
| Concatenate | SELECT 'SQL' || 'ite' AS joined; |
| Substring, upper, length | SELECT substr('sqldocs', 1, 3) AS part, upper('sql') AS up, length('sqldocs') AS len; |
| Trim and replace | SELECT trim(' hi ') AS trimmed, replace('a-b-c', '-', '/') AS replaced; |
More: string concatenation.
Dates and times
| Task | SQL |
|---|---|
| Current UTC date and time | SELECT datetime('now') IS NOT NULL AS has_now; |
| Add an interval | SELECT date('2026-10-06', '+7 days') AS next_week; |
| Start of month | SELECT date('2026-10-06', 'start of month') AS month_start; |
| Format a date | SELECT strftime('%Y-%m', '2026-10-06') AS year_month; |
| Days between dates | SELECT julianday('2026-12-25') - julianday('2026-10-06') AS days; |
| Unix seconds to text | SELECT datetime(1791297015, 'unixepoch') AS from_unix; |
More: SQLite date and time.
JSON
| Task | SQL |
|---|---|
| Read a JSON field | SELECT '{"a": {"b": 2}}' ->> '$.a.b' AS value; |
| Set a JSON field | SELECT json_set('{"a": 1}', '$.b', 2) AS updated; |
| Expand a JSON array | SELECT value FROM json_each('[10, 20, 30]'); |
More: SQLite JSON.
Types
| Task | SQL |
|---|---|
| Check a value’s type | SELECT typeof(42), typeof('42'), typeof(4.2), typeof(NULL); |
| Convert a type | SELECT CAST('42' AS INTEGER) + 1 AS n; |
More: SQLite data types.
Transactions
| Task | SQL |
|---|---|
| Start, commit | BEGIN IMMEDIATE; UPDATE products SET price = price * 1.1 WHERE id = 2; COMMIT; |
| Undo uncommitted changes | BEGIN; DELETE FROM orders; ROLLBACK; |
More: database is locked.
PRAGMAs
| Task | SQL |
|---|---|
| Enforce foreign keys (per connection) | PRAGMA foreign_keys = ON; |
| Switch to WAL mode (persistent) | PRAGMA journal_mode = WAL; |
| Wait up to 5 s for locks | PRAGMA busy_timeout = 5000; |
| Check database integrity | PRAGMA integrity_check; |
More: WAL mode, foreign keys.
Maintenance
| Task | SQL |
|---|---|
| Analyze query plan | EXPLAIN QUERY PLAN SELECT * FROM orders WHERE product_id = 1; |
| Reclaim free space | VACUUM; |
| Copy a live database to a file | VACUUM INTO 'backup.db'; |
More: CREATE INDEX.
Official references: the sqlite3 shell, SQL as understood by SQLite, PRAGMA statements.