SQLite Cheat Sheet: sqlite3 Commands and SQL Syntax (Tested)

This cheat sheet puts the everyday sqlite3 shell commands and SQL statements on one page, grouped by task, for when you need the exact syntax without a full guide. I tested every line, and in this article I’ll walk you through the groups one by one, with a link to the full guide for each.

Download the printable PDF (A4, 2 pages, the same commands as this page).

How to use this page

Find the task in the left column of a table and copy the command next to it. Every line on this page ran top to bottom in a single sqlite3 session against one shop.db database, with no errors, so the statements also work in that order as a quick tour. I also checked the files it writes separately, from the exported CSV to the VACUUM INTO backup.

sqlite3 shell

These dot-commands only work in the sqlite3 shell. They control the session, not the data.

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_copy

Back 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

These statements define tables and indexes. IF NOT EXISTS makes them safe to run again.

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

Use these to see what a database contains, from code or from the shell.

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

These statements change rows. RETURNING and ON CONFLICT save a second query.

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

These are the SELECT patterns for filtering, grouping and joining.

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

SQLite joins strings with ||, and its string functions count characters, not bytes.

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

SQLite has no date type. These functions work on ISO-8601 text and Unix seconds.

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

JSON is stored as text, and these functions and operators read and change it.

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

SQLite stores five kinds of values. typeof() shows which one a value has.

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

Group changes so they all apply or none do.

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

PRAGMAs read and change SQLite settings. Some last only for the current connection.

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

Use these to check query plans and keep the file compact.

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.

Common mistakes

Watch for these when you copy from this page:

Dot-commands are shell-only. .tables and .schema work in the sqlite3 shell, not from Python or Node.js. Use the SQL forms in “Inspect a database” from code.
Per-connection PRAGMAs. foreign_keys and busy_timeout reset on every new connection, while journal_mode = WAL is stored in the file. See SQLite PRAGMA.

INSERT OR REPLACE isn’t an upsert. It deletes the old row. Use ON CONFLICT … DO UPDATE as shown above.