SQLite Database Comprehensive Cheatsheet

Comprehensive SQLite cheatsheet covering database management, CRUD, indexes, joins, transactions, FTS5, JSON, date functions, PRAGMA settings, performance tuning, and command-line tools. Includes 21 sections with syntax examples, best practices, and quick reference for beginners to advanced users.

Table of Contents

1. Basic Concepts
2. Database Management
3. Data Types
4. Table Operations
5. CRUD Operations
6. Querying Data
7. Constraints
8. Indexes
9. Views
10. Transactions
11. Functions
12. Joins
13. Subqueries & CTEs
14. Triggers
15. PRAGMA Statements
16. Date & Time Functions
17. Full-Text Search (FTS5)
18. JSON Support
19. Backup & Recovery
20. Performance Tuning
21. Command Line (.commands)

---

1. Basic Concepts

Concept	Description
SQLite	Lightweight, serverless, zero-configuration, transactional SQL database engine
Database	Single file (.db, .sqlite, .sqlite3)
Connection	Multiple processes can read, only one can write at a time
Rowid	Implicit 64-bit signed integer primary key (unless WITHOUT ROWID)

---

2. Database Management

Create/Open Database

-- Command line
sqlite3 database_name.db

-- In SQL
.open database_name.db

-- Attach another database
ATTACH DATABASE 'other.db' AS other;

-- Detach
DETACH DATABASE other;

Show Databases

PRAGMA database_list;

Get Database Info

-- Schema version
PRAGMA schema_version;

-- User version
PRAGMA user_version;

-- Integrity check
PRAGMA integrity_check;

-- Foreign key check
PRAGMA foreign_key_check;

---

3. Data Types

SQLite uses dynamic typing with 5 storage classes:

Type	Description
NULL	Missing value
INTEGER	Signed integer (1,2,3,4,6,8 bytes)
REAL	Floating point (8 bytes)
TEXT	UTF-8, UTF-16
BLOB	Binary data

Type Affinities (Column types you can declare)

-- Text affinities
TEXT, CHARACTER, VARCHAR, CLOB

-- Numeric affinities
NUMERIC, DECIMAL, BOOLEAN, DATE, DATETIME

-- Integer affinities
INT, INTEGER, TINYINT, SMALLINT, BIGINT, UNSIGNED BIG INT

-- Real affinities
REAL, DOUBLE, FLOAT

-- Blob affinities
BLOB, NONE

---

4. Table Operations

Create Table

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Without rowid (explicit primary key)
CREATE TABLE products (
    product_code TEXT PRIMARY KEY,
    name TEXT,
    price REAL
) WITHOUT ROWID;

-- Create from query
CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

Alter Table

-- Add column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Rename table
ALTER TABLE users RENAME TO customers;

-- Rename column (SQLite 3.25+)
ALTER TABLE users RENAME COLUMN username TO user_name;

-- Add column with check
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive'));

Drop Table

DROP TABLE IF EXISTS users;
DROP TABLE users;  -- Without check

List Tables

.tables                    -- Command line
SELECT name FROM sqlite_master WHERE type='table';
SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';

Table Info

PRAGMA table_info(users);
.schema users              -- Command line

Copy Table Structure

-- Structure only
CREATE TABLE users_backup AS SELECT * FROM users WHERE 0;

-- Structure + data
CREATE TABLE users_backup AS SELECT * FROM users;

---

5. CRUD Operations

INSERT

-- Single row
INSERT INTO users (username, email, age) VALUES ('john', 'john@mail.com', 25);

-- Multiple rows
INSERT INTO users (username, email, age) VALUES 
    ('jane', 'jane@mail.com', 30),
    ('bob', 'bob@mail.com', 28);

-- Insert with default
INSERT INTO users (username, email) VALUES ('alice', 'alice@mail.com');

-- Insert or replace (conflict handling)
INSERT OR REPLACE INTO users (id, username, email) VALUES (1, 'john_new', 'new@mail.com');

-- Insert or ignore
INSERT OR IGNORE INTO users (id, username) VALUES (2, 'existing_user');

-- Insert from select
INSERT INTO premium_users SELECT * FROM users WHERE age > 30;

UPDATE

-- Basic update
UPDATE users SET age = 26 WHERE username = 'john';

-- Multiple columns
UPDATE users SET email = 'new@mail.com', age = age + 1 WHERE id = 1;

-- Update all rows
UPDATE users SET status = 'active';

-- Update with join
UPDATE users SET age = users.age + 1
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

DELETE

-- Delete specific rows
DELETE FROM users WHERE username = 'bob';

-- Delete all rows
DELETE FROM users;

-- Truncate (faster)
DELETE FROM users; VACUUM;

-- Delete with limit/order (SQLite 3.25+)
DELETE FROM logs WHERE timestamp < '2024-01-01' ORDER BY timestamp LIMIT 1000;

SELECT (Basic)

-- All columns
SELECT * FROM users;

-- Specific columns
SELECT username, email FROM users;

-- Distinct
SELECT DISTINCT age FROM users;

-- With limit
SELECT * FROM users LIMIT 10 OFFSET 5;

-- Alias
SELECT username AS name, email AS contact FROM users;

---

6. Querying Data

WHERE Clause

-- Comparisons
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE username = 'john';

-- Logical operators
SELECT * FROM users WHERE age > 18 AND status = 'active';
SELECT * FROM users WHERE age < 18 OR age > 65;
SELECT * FROM users WHERE NOT status = 'inactive';

-- IN operator
SELECT * FROM users WHERE age IN (25, 30, 35);
SELECT * FROM users WHERE username IN ('john', 'jane');

-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- LIKE (case-insensitive by default)
SELECT * FROM users WHERE username LIKE 'j%';   -- Starts with j
SELECT * FROM users WHERE username LIKE '%hn';  -- Ends with hn
SELECT * FROM users WHERE username LIKE '%oh%'; -- Contains oh

-- GLOB (case-sensitive, wildcards: * ?)
SELECT * FROM users WHERE username GLOB 'J*';

-- NULL checks
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

ORDER BY

-- Ascending (default)
SELECT * FROM users ORDER BY age;

-- Descending
SELECT * FROM users ORDER BY age DESC;

-- Multiple columns
SELECT * FROM users ORDER BY age DESC, username ASC;

LIMIT & OFFSET (Pagination)

-- First 10 rows
SELECT * FROM users LIMIT 10;

-- Next 10 rows (offset 10)
SELECT * FROM users LIMIT 10 OFFSET 10;

-- Alternative syntax
SELECT * FROM users LIMIT 10, 10;

GROUP BY & HAVING

-- Count by age
SELECT age, COUNT(*) as count FROM users GROUP BY age;

-- With aggregate functions
SELECT status, AVG(age) as avg_age, MAX(age) as max_age
FROM users GROUP BY status;

-- Having filter
SELECT age, COUNT(*) as count
FROM users
GROUP BY age
HAVING COUNT(*) > 1;

-- With WHERE (executes before GROUP BY)
SELECT status, COUNT(*) as count
FROM users
WHERE age > 18
GROUP BY status
HAVING COUNT(*) >= 2;

---

7. Constraints

Constraint	Description
NOT NULL	Column cannot be NULL
UNIQUE	All values must be different
PRIMARY KEY	Unique identifier, automatically indexed
FOREIGN KEY	References column in another table
CHECK	Validates values against expression
DEFAULT	Default value when not specified

Examples

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    sku TEXT UNIQUE,
    price REAL CHECK (price > 0),
    category_id INTEGER,
    status TEXT DEFAULT 'draft',
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- Composite unique constraint
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    UNIQUE(first_name, last_name)
);

-- Foreign key with cascade
CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Enable Foreign Keys

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;  -- Check status

---

8. Indexes

Create Index

-- Basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index
CREATE INDEX idx_users_age_status ON users(age, status);

-- Descending index
CREATE INDEX idx_users_age_desc ON users(age DESC);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Index with collation
CREATE INDEX idx_users_names ON users(last_name COLLATE NOCASE);

Drop Index

DROP INDEX IF EXISTS idx_users_email;

List Indexes

.indices users              -- Command line
SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='users';
PRAGMA index_list(users);
PRAGMA index_info(idx_users_email);

Index Usage

-- Query to check index usage (EXPLAIN QUERY PLAN)
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'john@mail.com';

---

9. Views

Create View

-- Basic view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE status = 'active';

-- View with join
CREATE VIEW user_orders AS
SELECT u.username, o.order_date, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Temporary view (session only)
CREATE TEMP VIEW temp_view AS SELECT * FROM users;

Drop View

DROP VIEW IF EXISTS active_users;

List Views

SELECT name FROM sqlite_master WHERE type='view';

Updatable Views (limited)

-- View that can be updated (simple, no joins, no aggregates)
CREATE VIEW simple_users AS SELECT id, username, email FROM users;
INSERT INTO simple_users (username, email) VALUES ('new', 'new@mail.com');

---

10. Transactions

Basic Transaction

BEGIN; -- or BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or END TRANSACTION;

-- Rollback on error
BEGIN;
    DELETE FROM users WHERE id = 100;
    -- Something wrong?
    ROLLBACK;  -- Undo the delete

Savepoints

BEGIN;
    SAVEPOINT sp1;
        UPDATE users SET age = 30 WHERE id = 1;
    ROLLBACK TO sp1;  -- Undo only last change
    
    SAVEPOINT sp2;
        INSERT INTO logs VALUES ('action');
    RELEASE sp2;  -- Make permanent
COMMIT;

Transaction Types

-- DEFERRED (default): starts when first read/write occurs
BEGIN DEFERRED;

-- IMMEDIATE: acquires write lock immediately
BEGIN IMMEDIATE;

-- EXCLUSIVE: prevents all other access
BEGIN EXCLUSIVE;

---

11. Functions

Aggregate Functions

SELECT 
    COUNT(*) as total_rows,
    COUNT(email) as non_null_emails,
    COUNT(DISTINCT age) as unique_ages,
    SUM(age) as age_sum,
    AVG(age) as age_avg,
    MIN(age) as youngest,
    MAX(age) as oldest,
    TOTAL(age) as age_total_float,  -- Returns 0.0 instead of NULL
    GROUP_CONCAT(username) as usernames,  -- Comma-separated
    GROUP_CONCAT(username, '; ') as usernames_semicolon
FROM users;

Scalar Functions

-- String
SELECT 
    LENGTH('hello') as len,                    -- 5
    UPPER('hello') as upper,                   -- HELLO
    LOWER('HELLO') as lower,                   -- hello
    SUBSTR('hello world', 7) as substr,        -- world
    SUBSTR('hello world', 7, 3) as substr_lim, -- wor
    TRIM('  hello  ') as trimmed,              -- hello
    LTRIM('  hello'), RTRIM('hello  '),
    REPLACE('hello world', 'world', 'sqlite'),
    INSTR('hello', 'e') as position,           -- 2
    LIKE('%lo%', 'hello') as like_match,
    GLOB('*lo*', 'hello') as glob_match,
    'Hello ' || 'World' as concat;

-- Numeric
SELECT 
    ABS(-5) as abs,                            -- 5
    ROUND(3.14159, 2) as rounded,             -- 3.14
    CEIL(3.2) as ceil,                         -- 4
    FLOOR(3.8) as floor,                       -- 3
    RANDOM() as random,
    RANDOM() % 100 as random_0_99,
    POWER(2, 3) as power,                      -- 8 (SQLite 3.35+)
    SQRT(16) as sqrt;                          -- 4

-- Type conversion
SELECT 
    CAST('123' AS INTEGER) as int_val,
    CAST('3.14' AS REAL) as float_val,
    TYPEOF(123) as type;                       -- integer

Conditional Functions

-- CASE expression
SELECT 
    username,
    CASE age
        WHEN 18 THEN 'Just adult'
        WHEN 21 THEN 'Legal drinking'
        ELSE 'Other'
    END as age_status,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 65 THEN 'Adult'
        ELSE 'Senior'
    END as age_group
FROM users;

-- IIF (ternary)
SELECT IIF(age >= 18, 'Adult', 'Minor') as status FROM users;

-- COALESCE (first non-NULL)
SELECT COALESCE(phone, email, 'No contact') as contact FROM users;

-- NULLIF (NULL if equal)
SELECT NULLIF(age, 0) as valid_age FROM users;

---

12. Joins

INNER JOIN

SELECT u.username, o.order_date, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

LEFT OUTER JOIN

SELECT u.username, o.order_date, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Users without orders will have NULL for order columns

CROSS JOIN

SELECT colors.name, sizes.name
FROM colors
CROSS JOIN sizes;

NATURAL JOIN (joins on same column names)

SELECT * FROM users NATURAL JOIN profiles;

Join with USING

SELECT * FROM users JOIN orders USING (user_id);

Self Join

SELECT a.username as employee, b.username as manager
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.id;

Multi-table Join

SELECT u.username, o.order_date, p.name as product_name, oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

---

13. Subqueries & CTEs

Subqueries in WHERE

-- Scalar subquery
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- IN subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- EXISTS subquery
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- ANY/ALL (SQLite 3.25+)
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'electronics');

Subqueries in SELECT

SELECT 
    username,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count,
    (SELECT SUM(total) FROM orders WHERE user_id = users.id) as total_spent
FROM users;

Subqueries in FROM (Derived Tables)

SELECT category, avg_price
FROM (
    SELECT category, AVG(price) as avg_price
    FROM products
    GROUP BY category
) WHERE avg_price > 50;

Common Table Expressions (CTE)

-- Basic CTE
WITH high_value_orders AS (
    SELECT user_id, SUM(total) as total_spent
    FROM orders
    GROUP BY user_id
    HAVING SUM(total) > 1000
)
SELECT u.username, h.total_spent
FROM users u
JOIN high_value_orders h ON u.id = h.user_id;

Recursive CTE (Hierarchical queries)

WITH RECURSIVE subordinates AS (
    -- Base case: direct subordinates of manager 1
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id = 1
    
    UNION ALL
    
    -- Recursive case
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

-- Generate numbers
WITH RECURSIVE numbers(n) AS (
    VALUES(1)
    UNION ALL
    SELECT n+1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers;

Materialized CTEs (SQLite 3.35+)

WITH MATERIALIZED expensive_computation AS (
    SELECT complex_query
)
SELECT * FROM expensive_computation;

---

14. Triggers

Create Trigger

-- Before insert
CREATE TRIGGER validate_email_before_insert 
BEFORE INSERT ON users
BEGIN
    SELECT CASE
        WHEN NEW.email NOT LIKE '%@%' THEN
            RAISE(ABORT, 'Invalid email address')
    END;
END;

-- After update
CREATE TRIGGER log_user_update
AFTER UPDATE ON users
BEGIN
    INSERT INTO audit_log (table_name, action, old_value, new_value, timestamp)
    VALUES ('users', 'UPDATE', OLD.email, NEW.email, CURRENT_TIMESTAMP);
END;

-- Instead of (for views)
CREATE TRIGGER update_active_users
INSTANT OF UPDATE ON active_users_view
BEGIN
    UPDATE users SET username = NEW.username WHERE id = OLD.id;
END;

Trigger Events

-- DELETE trigger
CREATE TRIGGER prevent_user_delete
BEFORE DELETE ON users
BEGIN
    SELECT CASE
        WHEN (SELECT COUNT(*) FROM orders WHERE user_id = OLD.id) > 0 THEN
            RAISE(ABORT, 'Cannot delete user with orders')
    END;
END;

-- UPDATE of specific columns
CREATE TRIGGER track_email_changes
AFTER UPDATE OF email ON users
BEGIN
    INSERT INTO email_history (user_id, old_email, new_email, changed_at)
    VALUES (OLD.id, OLD.email, NEW.email, CURRENT_TIMESTAMP);
END;

Drop Trigger

DROP TRIGGER IF EXISTS validate_email_before_insert;

List Triggers

SELECT name FROM sqlite_master WHERE type='trigger';
SELECT * FROM sqlite_master WHERE type='trigger' AND tbl_name='users';

---

15. PRAGMA Statements

Database Configuration

-- Foreign keys
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

-- Auto-vacuum
PRAGMA auto_vacuum = FULL;  -- 0=NONE, 1=FULL, 2=INCREMENTAL

-- Cache size (pages)
PRAGMA cache_size = 10000;

-- Page size (must be set before creating tables)
PRAGMA page_size = 4096;

-- Journal mode
PRAGMA journal_mode = WAL;  -- DELETE, TRUNCATE, PERSIST, MEMORY, WAL, OFF
PRAGMA journal_mode;        -- Returns current mode

-- Synchronous
PRAGMA synchronous = NORMAL;  -- 0=OFF, 1=NORMAL, 2=FULL, 3=EXTRA

-- Encoding
PRAGMA encoding = 'UTF-8';

-- Temp store
PRAGMA temp_store = MEMORY;  -- 0=DEFAULT, 1=FILE, 2=MEMORY

Introspection

-- Table info
PRAGMA table_info(users);
PRAGMA table_xinfo(users);  -- Hidden columns

-- Index info
PRAGMA index_list(users);
PRAGMA index_info(idx_users_email);

-- Foreign key info
PRAGMA foreign_key_list(users);

-- Schema version
PRAGMA schema_version;
PRAGMA user_version = 123;  -- Set user version

-- Compile options
PRAGMA compile_options;

-- Database limits
PRAGMA max_page_count;
PRAGMA soft_heap_limit = 67108864;  -- 64MB

-- Optimize
PRAGMA optimize;  -- Run at shutdown
PRAGMA analysis_limit = 1000;

Debug & Performance

-- Query profiling
PRAGMA profile = ON;
PRAGMA profile;

-- Lock status
PRAGMA lock_status;

-- Integrity & optimization
PRAGMA integrity_check;
PRAGMA quick_check;
PRAGMA foreign_key_check;
PRAGMA optimize(0x10002);  -- Full optimization

---

16. Date & Time Functions

SQLite has no native date type; stores as TEXT, REAL, or INTEGER.

Date/Time Format Functions

-- Current time
SELECT DATE('now');           -- YYYY-MM-DD
SELECT TIME('now');           -- HH:MM:SS
SELECT DATETIME('now');       -- YYYY-MM-DD HH:MM:SS
SELECT JULIANDAY('now');      -- Julian day number
SELECT STRFTIME('%s', 'now'); -- Unix timestamp (seconds)

Date Arithmetic

-- Modifiers
SELECT DATE('now', '+1 day');
SELECT DATE('now', '-1 month');
SELECT DATE('now', '+1 year', '-1 day');
SELECT DATE('2024-01-15', '+7 days');
SELECT DATETIME('now', 'start of month');
SELECT DATETIME('now', 'localtime');
SELECT DATETIME('now', 'utc');

-- Time arithmetic
SELECT TIME('12:00:00', '+30 minutes');
SELECT TIME('12:00:00', '-2 hours');
SELECT TIME('now', 'start of day');

Extract Parts

SELECT STRFTIME('%Y', 'now') as year;       -- 2024
SELECT STRFTIME('%m', 'now') as month;      -- 01-12
SELECT STRFTIME('%d', 'now') as day;        -- 01-31
SELECT STRFTIME('%H', 'now') as hour;       -- 00-23
SELECT STRFTIME('%M', 'now') as minute;     -- 00-59
SELECT STRFTIME('%S', 'now') as second;     -- 00-59
SELECT STRFTIME('%w', 'now') as weekday;    -- 0=Sunday, 6=Saturday
SELECT STRFTIME('%W', 'now') as week;       -- 00-53
SELECT STRFTIME('%j', 'now') as day_of_year;-- 001-366
SELECT STRFTIME('%s', 'now') as timestamp;  -- Unix timestamp

Formatting

SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now');
SELECT STRFTIME('%d/%m/%Y', '2024-01-15');  -- 15/01/2024
SELECT STRFTIME('%H:%M', '2024-01-15 14:30:00');  -- 14:30

Calculations

-- Difference between dates
SELECT JULIANDAY('2024-12-31') - JULIANDAY('2024-01-01') as days;
SELECT (STRFTIME('%s', '2024-12-31') - STRFTIME('%s', '2024-01-01')) / 86400 as days;

-- Age calculation
SELECT (STRFTIME('%s', 'now') - STRFTIME('%s', birth_date)) / 31536000 as age FROM users;

---

17. Full-Text Search (FTS5)

Create FTS Table

-- Basic FTS5 table
CREATE VIRTUAL TABLE posts_fts USING fts5(
    title, 
    content, 
    author
);

-- With options
CREATE VIRTUAL TABLE posts_fts USING fts5(
    title UNINDEXED,     -- Stored but not indexed
    content,
    author,
    content='posts',     -- External content table
    tokenize='porter unicode61'
);

-- External content mode
CREATE VIRTUAL TABLE posts_fts USING fts5(content, content='posts');

Insert/Update

-- Direct insert
INSERT INTO posts_fts (title, content, author) 
VALUES ('SQLite Tutorial', 'Learn SQLite FTS5', 'John');

-- For external content, sync with source table
INSERT INTO posts_fts (rowid, title, content, author) 
SELECT rowid, title, content, author FROM posts;

-- Update after content changes
INSERT OR REPLACE INTO posts_fts (rowid, title, content, author)
VALUES (1, 'Updated Title', 'New content', 'John');

Query with FTS5

-- Basic MATCH
SELECT * FROM posts_fts WHERE posts_fts MATCH 'sqlite';

-- Column-specific
SELECT * FROM posts_fts WHERE title MATCH 'tutorial';

-- Phrase search
SELECT * FROM posts_fts WHERE content MATCH '"learn sqlite"';

-- Prefix search
SELECT * FROM posts_fts WHERE posts_fts MATCH 'learn*';

-- NEAR operator
SELECT * FROM posts_fts WHERE content MATCH 'NEAR("sqlite" "fts5", 5)';

-- Boolean operators
SELECT * FROM posts_fts WHERE posts_fts MATCH 'sqlite AND tutorial';
SELECT * FROM posts_fts WHERE posts_fts MATCH 'sqlite NOT fts5';
SELECT * FROM posts_fts WHERE posts_fts MATCH 'sqlite OR fts5';

-- Highlight and snippet
SELECT 
    highlight(posts_fts, 1, '<b>', '</b>') as title_highlighted,
    snippet(posts_fts, 2, '<mark>', '</mark>', '...', 15) as content_snippet
FROM posts_fts 
WHERE posts_fts MATCH 'sqlite';

FTS5 Auxiliary Functions

-- BM25 ranking
SELECT *, bm25(posts_fts) as rank 
FROM posts_fts 
WHERE posts_fts MATCH 'sqlite' 
ORDER BY rank;

-- Debug info
SELECT * FROM posts_fts('sqlite');

Maintain FTS Tables

-- Rebuild index
INSERT INTO posts_fts(posts_fts) VALUES('optimize');

-- Delete from external content table
DELETE FROM posts_fts WHERE rowid IN (SELECT rowid FROM posts WHERE deleted = 1);

---

18. JSON Support

JSON Functions (SQLite 3.9+)

-- Create table with JSON
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES 
    ('Laptop', '{"brand": "Dell", "ram": 16, "storage": 512, "colors": ["black", "silver"]}'),
    ('Phone', '{"brand": "Apple", "ram": 8, "storage": 256, "colors": ["black", "white", "gold"]}');

Extract JSON Data

-- Extract value (returns JSON)
SELECT json_extract(attributes, '$.brand') as brand FROM products;

-- Extract as text
SELECT attributes->>'$.brand' as brand FROM products;

-- Extract as integer
SELECT attributes->>'$.ram' as ram FROM products;

-- Nested extraction
SELECT json_extract(attributes, '$.colors[0]') as first_color FROM products;

-- Multiple extractions
SELECT 
    json_extract(attributes, '$.brand'),
    json_extract(attributes, '$.ram')
FROM products;

Query JSON Arrays

-- Check if array contains value
SELECT * FROM products 
WHERE json_extract(attributes, '$.colors') LIKE '%"gold"%';

-- JSON each (3.37+)
SELECT 
    p.name,
    value as color
FROM products p, json_each(p.attributes, '$.colors');

Modify JSON

-- Insert/update JSON value
UPDATE products 
SET attributes = json_insert(attributes, '$.cpu', 'Intel i7')
WHERE name = 'Laptop';

-- Replace value
UPDATE products 
SET attributes = json_replace(attributes, '$.ram', 32)
WHERE name = 'Laptop';

-- Set (insert or replace)
UPDATE products 
SET attributes = json_set(attributes, '$.storage', 1024, '$.ssd', true)
WHERE name = 'Laptop';

-- Remove key
UPDATE products 
SET attributes = json_remove(attributes, '$.colors')
WHERE name = 'Phone';

Create JSON

-- Build JSON object
SELECT json_object(
    'name', name,
    'attributes', json(attributes)
) as product_json FROM products;

-- Build JSON array
SELECT json_group_array(name) as product_names FROM products;

-- Validate JSON
SELECT json_valid('{"key": "value"}');  -- 1 (valid)

JSON Functions Summary

Function	Description
json()	Validates and minifies JSON
json_extract()	Extracts value from JSON
json_array()	Creates JSON array
json_object()	Creates JSON object
json_insert()	Inserts new values
json_replace()	Replaces existing values
json_set()	Inserts or replaces
json_remove()	Removes key/value
json_each()	Table-valued function for array elements
json_tree()	Table-valued function for recursive extraction
json_valid()	Validates JSON format
json_quote()	Escapes string as JSON
json_group_array()	Aggregates rows into JSON array
json_group_object()	Aggregates rows into JSON object

---

19. Backup & Recovery

Command Line Backup

# Simple copy
cp database.db database_backup.db

# Using .backup (creates consistent backup)
sqlite3 database.db ".backup database_backup.db"

# Backup specific table
sqlite3 database.db ".dump users" > users.sql

# Full database dump
sqlite3 database.db ".dump" > database.sql

# Restore from dump
sqlite3 new_database.db < database.sql

SQL Backup Commands

-- Backup to file (within SQLite)
BACKUP TO 'backup.db';

-- Backup with encryption (SQLite Encryption Extension)
BACKUP TO 'encrypted.db' KEY 'password';

-- Vacuum to rebuild and shrink
VACUUM;

-- Vacuum into new file
VACUUM INTO 'compacted.db';

Programmatic Backup (C API)

sqlite3_backup *pBackup;
sqlite3_backup_init(pDest, "main", pSource, "main");
sqlite3_backup_step(pBackup, -1);
sqlite3_backup_finish(pBackup);

Auto Backup Schedule

-- Create backup trigger
CREATE TRIGGER auto_backup
AFTER INSERT ON critical_table
BEGIN
    SELECT backup('auto_backup.db');
END;

Recovery Methods

-- Recover from corrupt database
PRAGMA integrity_check;
PRAGMA quick_check;

-- Dump and restore
.output recovery.sql
.dump
.output stdout

-- Try to recover data from corrupt DB
sqlite3 corrupt.db ".recover" | sqlite3 recovered.db

---

20. Performance Tuning

Query Optimization

-- Analyze to update statistics
ANALYZE;
ANALYZE users;  -- Specific table
ANALYZE sqlite_schema;  -- System tables

-- Query plan analysis
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@mail.com';

-- Force index usage
SELECT * FROM users INDEXED BY idx_users_email WHERE email = 'test@mail.com';

-- Disable index usage
SELECT * FROM users NOT INDEXED WHERE email = 'test@mail.com';

Optimize Performance Settings

-- Use WAL mode for better concurrency
PRAGMA journal_mode=WAL;

-- Increase cache size
PRAGMA cache_size = -20000;  -- 20,000 pages (negative = KB)

-- Memory-mapped I/O
PRAGMA mmap_size = 268435456;  -- 256MB

-- Optimize synchronous (trade safety for speed)
PRAGMA synchronous = OFF;  -- Not recommended for critical data

-- Use temp store in memory
PRAGMA temp_store = MEMORY;

-- Disable schema locking (read-only)
PRAGMA query_only = ON;

-- Compile-time optimizations
PRAGMA trusted_schema = ON;  -- Disable bytecode verification

Bulk Insert Optimization

-- Wrap in transaction
BEGIN;
    INSERT INTO users SELECT * FROM temp_users;
COMMIT;

-- Use prepared statements
-- (in application code)

-- Disable indexes temporarily (rebuild later)
PRAGMA writable_schema = ON;
-- Drop indexes, insert data, re-create indexes

-- Increase page cache
PRAGMA cache_size = 100000;

Memory Usage

-- Hard memory limit
PRAGMA soft_heap_limit = 2147483648;  -- 2GB

-- Page cache size
PRAGMA cache_size = 5000;  -- Pages

-- Memory-mapped I/O threshold
PRAGMA mmap_size = 0;  -- Disable

Monitoring

-- Check status
PRAGMA status;
PRAGMA cache_status;

-- Trace execution
PRAGMA vdbe_trace = ON;
PRAGMA vdbe_listing = ON;

-- Profile queries
PRAGMA profile;

---

21. Command Line (.commands)

Basic Commands

.help                    -- Show all commands
.quit / .exit            -- Exit sqlite3
.databases               -- List attached databases
.tables ?PATTERN?        -- List tables
.schema ?TABLE?          -- Show CREATE statements
.indices ?TABLE?         -- List indexes
.dump ?TABLE?            -- Dump database as SQL
.output FILE             -- Send output to file
.output stdout           -- Send output to console
.mode MODE               -- Set output mode
.headers ON|OFF          -- Toggle headers for column mode
.timer ON|OFF            -- Toggle query timings

Output Modes

.mode list               -- Separated by |
.mode column             -- Table format (aligned)
.mode csv                -- Comma-separated values
.mode tab                -- Tab-separated
.mode insert             -- SQL INSERT statements
.mode line               -- Each column on separate line
.mode quote              -- SQL quoted strings
.mode json               -- JSON format (3.33+)
.mode markdown           -- Markdown tables (3.37+)
.mode table              -- ASCII table (3.36+)
.mode box                -- Box-drawing table (3.36+)

Import/Export

-- Import CSV
.mode csv
.import data.csv table_name

-- Import CSV with header
.import --csv --skip 1 data.csv table_name

-- Export to CSV
.headers ON
.mode csv
.output data.csv
SELECT * FROM table_name;
.output stdout

-- Export to JSON
.mode json
.output data.json
SELECT * FROM table_name;

-- Script execution
.read script.sql
.once output.csv         -- Output for next query only

Database Maintenance

.backup FILE             -- Backup database
.restore FILE            -- Restore from backup
.vacuum                  -- Rebuild database file
.recover                 -- Recover data from corrupt DB
.check ?DB?              -- Check database integrity
.sha3sum ?FILE?          -- Compute SHA3 hash

Configuration

.echo ON|OFF             -- Echo commands
.stats ON|OFF            -- Show statistics
.nullvalue STRING        -- String for NULL values
.separator CHAR          -- Change separator for list mode
.cd DIR                  -- Change directory
.system CMD              -- Run system command
.shell CMD               -- Run shell command
.parameter list          -- List bound parameters
.parameter set NAME VALUE
.parameter clear

Debugging

.show                    -- Show current settings
.trace FILE|OFF          -- Enable SQL tracing
.log FILE|OFF            -- Enable logging
.eqp ON|OFF              -- Explain query plan
.expert                  -- Index recommendations (experimental)

---

Quick Reference Card

Most Used Commands

-- Connect
sqlite3 database.db

-- Create table
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

-- CRUD
INSERT INTO users (name) VALUES ('John');
SELECT * FROM users WHERE name='John';
UPDATE users SET name='Jane' WHERE id=1;
DELETE FROM users WHERE id=1;

-- Schema
.schema users
.tables

-- Backup
.backup backup.db

-- Exit
.quit

Best Practices

1. Always enable foreign keys: PRAGMA foreign_keys = ON;
2. Use WAL mode for better concurrency
3. Use parameterized queries for security
4. Create indexes on columns used in WHERE, JOIN, ORDER BY
5. Avoid SELECT * in production
6. Use transactions for multiple operations
7. Regularly run ANALYZE and VACUUM
8. Backup before major schema changes