SQLite Cheat Sheet

A comprehensive quick-reference for SQLite commands and features. Search by keyword or filter by category to find exactly what you need.

🗄️ What is SQLite?

SQLite is a self-contained, serverless, zero-configuration SQL database engine. Unlike client-server databases, the entire database lives in a single file on disk. It is the most widely deployed database engine in the world — found in every smartphone, browser, and countless applications.

Key Characteristics

  • Serverless: No daemon or setup needed
  • Single file: Entire DB in one .db file
  • Zero config: Works out of the box
  • ACID compliant: Full transaction support
  • Cross-platform: Same file on any OS
  • Public domain: No license restrictions

Common Use Cases

  • Mobile apps (iOS, Android)
  • Desktop applications (Electron, etc.)
  • Embedded systems and IoT
  • Prototyping and local development
  • Browser storage (Web SQL, local cache)
  • CLI tools and scripts

SQLite vs Other Databases

Use SQLite when:

  • Single user or low concurrency
  • Embedded in an application
  • Portability is important
  • Simplicity over scalability

Avoid SQLite when:

  • High write concurrency needed
  • Multi-server / distributed setup
  • Very large datasets (> a few GB)
  • Fine-grained user permissions

Notable defaults:

  • Foreign keys OFF by default
  • Dynamic typing (type affinity)
  • WAL mode off by default
  • No RIGHT / FULL OUTER JOIN
19 commands found
Filter:

SELECT

Queries

Retrieve rows from one or more tables

Syntax:

SELECT column1, column2 FROM table_name WHERE condition ORDER BY column LIMIT n;

Examples:

SELECT * FROM users;
Select all columns from users
SELECT name, email FROM users WHERE active = 1;
Filter rows by condition
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
Latest 10 rows
SELECT DISTINCT country FROM users;
Unique values only

Notes:

SQLite supports all standard SELECT clauses: WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET.

INSERT

Queries

Add new rows to a table

Syntax:

INSERT INTO table_name (col1, col2) VALUES (val1, val2);

Examples:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
Insert single row
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com');
Insert multiple rows
INSERT OR REPLACE INTO settings (key, value) VALUES ('theme', 'dark');
Upsert — replace if key conflict
INSERT OR IGNORE INTO tags (name) VALUES ('sqlite');
Skip insert if row already exists

Notes:

INSERT OR REPLACE, INSERT OR IGNORE, and INSERT OR FAIL are SQLite-specific conflict handlers.

UPDATE

Queries

Modify existing rows in a table

Syntax:

UPDATE table_name SET col1 = val1 WHERE condition;

Examples:

UPDATE users SET email = 'new@example.com' WHERE id = 1;
Update one row
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
Increase price by 10%
UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = 42;
Set timestamp to now

Notes:

Always include a WHERE clause — omitting it updates every row in the table.

DELETE

Queries

Remove rows from a table

Syntax:

DELETE FROM table_name WHERE condition;

Examples:

DELETE FROM users WHERE id = 5;
Delete a specific row
DELETE FROM logs WHERE created_at < date('now', '-30 days');
Delete rows older than 30 days
DELETE FROM users;
Delete all rows (use with caution)

Notes:

To reset an auto-increment counter, also run: DELETE FROM sqlite_sequence WHERE name = "table_name";

CREATE TABLE

DDL

Define a new table with columns and constraints

Syntax:

CREATE TABLE table_name (col1 TYPE CONSTRAINT, col2 TYPE, ...);

Examples:

CREATE TABLE users (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  name    TEXT    NOT NULL,
  email   TEXT    UNIQUE NOT NULL,
  active  INTEGER DEFAULT 1,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
Typical users table
CREATE TABLE IF NOT EXISTS settings (
  key   TEXT PRIMARY KEY,
  value TEXT
);
Create only if it does not exist
CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id),
  total      REAL    NOT NULL CHECK (total >= 0)
);
Table with foreign key and check constraint

Notes:

SQLite uses dynamic typing. Column types are hints (type affinity), not strict enforcement unless STRICT is added (SQLite 3.37+).

DROP TABLE

DDL

Permanently remove a table and all its data

Syntax:

DROP TABLE [IF EXISTS] table_name;

Examples:

DROP TABLE old_logs;
Remove a table
DROP TABLE IF EXISTS temp_cache;
Remove only if exists (no error if missing)

Notes:

This is irreversible. Wrap in a transaction if you want a safety net.

ALTER TABLE

DDL

Modify an existing table structure

Syntax:

ALTER TABLE table_name ADD COLUMN col_name TYPE;
ALTER TABLE table_name RENAME TO new_name;
ALTER TABLE table_name RENAME COLUMN old TO new;

Examples:

ALTER TABLE users ADD COLUMN phone TEXT;
Add a new column
ALTER TABLE users RENAME TO customers;
Rename the table
ALTER TABLE users RENAME COLUMN phone TO mobile;
Rename a column (SQLite 3.25+)

Notes:

SQLite ALTER TABLE is limited — you cannot drop columns (before 3.35) or change column types. For complex changes, use the recommended 12-step migration.

CREATE INDEX

DDL

Create an index to speed up queries on a column

Syntax:

CREATE [UNIQUE] INDEX index_name ON table_name (col1, col2);

Examples:

CREATE INDEX idx_users_email ON users (email);
Index for faster email lookups
CREATE UNIQUE INDEX idx_users_username ON users (username);
Unique index enforces uniqueness
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
Composite index
DROP INDEX IF EXISTS idx_users_email;
Remove an index

Notes:

Indexes speed up SELECT but slow down INSERT/UPDATE/DELETE. Add them based on actual query patterns.

CREATE VIEW

DDL

Save a query as a virtual table

Syntax:

CREATE VIEW view_name AS SELECT ...;

Examples:

CREATE VIEW active_users AS
  SELECT id, name, email
  FROM users
  WHERE active = 1;
View of active users only
DROP VIEW IF EXISTS active_users;
Remove a view

Notes:

Views in SQLite are read-only by default. Use INSTEAD OF triggers for writable views.

JOIN

Joins

Combine rows from two or more tables

Syntax:

SELECT cols FROM t1 [INNER|LEFT|CROSS] JOIN t2 ON t1.id = t2.fk;

Examples:

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
INNER JOIN — only matching rows
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
LEFT JOIN — all users, NULL if no orders
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
Count orders per user

Notes:

SQLite supports INNER JOIN, LEFT JOIN, and CROSS JOIN. RIGHT and FULL OUTER JOINs are not supported (simulate with UNION).

GROUP BY / Aggregate Functions

Aggregate

Summarize data using aggregate functions

Syntax:

SELECT col, AGG(col2) FROM table GROUP BY col HAVING condition;

Examples:

SELECT COUNT(*) FROM users;
Total row count
SELECT country, COUNT(*) AS total FROM users GROUP BY country;
Count by group
SELECT category, AVG(price), MIN(price), MAX(price) FROM products GROUP BY category;
Price stats per category
SELECT user_id, SUM(total) FROM orders GROUP BY user_id HAVING SUM(total) > 100;
Filter groups with HAVING
SELECT GROUP_CONCAT(name, ', ') FROM users WHERE active = 1;
Concatenate values into a string

Notes:

GROUP_CONCAT is SQLite-specific. Use it to aggregate text values within a group.

WITH (CTE)

Subqueries & CTEs

Define a named temporary result set for reuse in a query

Syntax:

WITH cte_name AS (SELECT ...) SELECT * FROM cte_name;

Examples:

WITH top_users AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
  HAVING spent > 500
)
SELECT u.name, t.spent
FROM users u
JOIN top_users t ON u.id = t.user_id;
Top spenders using a CTE
WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;
Recursive CTE — generate a sequence

Notes:

Recursive CTEs are great for hierarchical data (trees, parent-child relationships).

Subquery

Subqueries & CTEs

Use a nested SELECT as a value, list, or table

Syntax:

SELECT * FROM table WHERE col IN (SELECT col FROM other_table);

Examples:

SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE country = 'US');
IN subquery
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
Scalar subquery comparison
SELECT u.name, sub.total
FROM users u
JOIN (SELECT user_id, SUM(total) AS total FROM orders GROUP BY user_id) sub
  ON u.id = sub.user_id;
Derived table subquery

BEGIN / COMMIT / ROLLBACK

Transactions

Group multiple statements into an atomic unit

Syntax:

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

Examples:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
Transfer funds atomically
BEGIN;
  DELETE FROM users WHERE id = 99;
ROLLBACK;
Undo changes with ROLLBACK
SAVEPOINT sp1;
  UPDATE users SET name = 'Test' WHERE id = 1;
ROLLBACK TO sp1;
RELEASE sp1;
Partial rollback with SAVEPOINT

Notes:

SQLite defaults to autocommit. Use BEGIN...COMMIT for batch writes — it is dramatically faster than individual statements.

PRAGMA

Pragma

Query or configure SQLite internal settings

Syntax:

PRAGMA pragma_name;
PRAGMA pragma_name = value;

Examples:

PRAGMA journal_mode = WAL;
Enable WAL mode for better concurrency
PRAGMA foreign_keys = ON;
Enforce foreign key constraints (off by default!)
PRAGMA synchronous = NORMAL;
Balance durability vs. write speed
PRAGMA table_info(users);
List columns of a table
PRAGMA index_list(users);
List indexes on a table
PRAGMA integrity_check;
Verify database file integrity
PRAGMA cache_size = -64000;
Set page cache to 64 MB
VACUUM;
Rebuild the database file, reclaiming unused space

Notes:

PRAGMA foreign_keys must be enabled each connection — it is not persisted. Always set it in your app startup code.

Date Functions

Date & Time

Built-in functions for date and time manipulation

Syntax:

date(timestring, modifier, ...)
datetime(timestring, modifier, ...)
strftime(format, timestring, ...)

Examples:

SELECT date('now');
Today's date: 2025-03-27
SELECT datetime('now', 'localtime');
Current local date and time
SELECT date('now', '-7 days');
Date 7 days ago
SELECT strftime('%Y-%m', created_at) AS month, COUNT(*) FROM orders GROUP BY month;
Group orders by month
SELECT * FROM events WHERE date(start) BETWEEN date('now') AND date('now', '+30 days');
Events in next 30 days

Notes:

SQLite has no native DATE type. Store dates as TEXT (ISO 8601: 'YYYY-MM-DD'), INTEGER (Unix timestamp), or REAL (Julian day).

JSON Functions

JSON

Extract and manipulate JSON data stored in TEXT columns (SQLite 3.38+)

Syntax:

json_extract(json, '$.path')
json_object(key, value, ...)
json_array(val, ...)

Examples:

SELECT json_extract(data, '$.name') FROM records;
Extract a field from JSON
SELECT * FROM records WHERE json_extract(data, '$.active') = 1;
Filter by JSON field
SELECT json_object('id', id, 'name', name) FROM users;
Build JSON from columns
UPDATE records SET data = json_set(data, '$.score', 99) WHERE id = 1;
Update a JSON field
SELECT value FROM records, json_each(data, '$.tags');
Expand JSON array into rows

Notes:

The -> and ->> operators (SQLite 3.38+) are shorthand for json_extract: data->>'$.name' returns plain text.

FTS5

Full-Text Search

Full-text search virtual table for fast text search

Syntax:

CREATE VIRTUAL TABLE t USING fts5(col1, col2);
SELECT * FROM t WHERE t MATCH 'query';

Examples:

CREATE VIRTUAL TABLE docs USING fts5(title, body);
INSERT INTO docs VALUES ('SQLite Guide', 'SQLite is a lightweight database.');
SELECT * FROM docs WHERE docs MATCH 'lightweight';
Create and search an FTS5 table
SELECT * FROM docs WHERE docs MATCH 'sqlite AND database';
Boolean AND search
SELECT * FROM docs WHERE docs MATCH 'title:sqlite';
Search in a specific column
SELECT *, rank FROM docs WHERE docs MATCH 'database' ORDER BY rank;
Order by relevance rank

Notes:

FTS5 is the modern full-text search extension. Prefer it over the older FTS3/FTS4.

Utility Statements

Utility

Inspect schema, list tables, and other meta commands

Syntax:

.tables
.schema [table]
SELECT * FROM sqlite_master;

Examples:

SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;
List all tables
SELECT sql FROM sqlite_master WHERE name = 'users';
Show CREATE statement for a table
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'a@b.com';
Show query execution plan
SELECT sqlite_version();
Current SQLite version
SELECT * FROM sqlite_master WHERE type = 'index';
List all indexes

Notes:

In the sqlite3 CLI, use .tables, .schema, .indexes, and .mode column for interactive exploration.

SQLite Best Practices

Performance

  • Enable PRAGMA journal_mode = WAL for concurrent reads
  • Wrap bulk inserts in BEGIN...COMMIT — 10–100× faster
  • Use PRAGMA synchronous = NORMAL for a speed/safety balance
  • Add indexes on columns used in WHERE, JOIN, and ORDER BY
  • Use EXPLAIN QUERY PLAN to spot full-table scans
  • Increase cache_size for read-heavy workloads

Safety & Correctness

  • Always set PRAGMA foreign_keys = ON at connection start
  • Use parameterized queries to prevent SQL injection
  • Include WHERE in every UPDATE and DELETE
  • Run PRAGMA integrity_check after migrations
  • Back up with sqlite3 db.sqlite ".backup backup.sqlite"
  • Store dates as ISO 8601 TEXT for portability