SQLite Database Comprehensive Cheatsheet

Table of Contents

1. Basic Concepts

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

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', '[email protected]', 25);

-- Multiple rows
INSERT INTO users (username, email, age) VALUES 
    ('jane', '[email protected]', 30),
    ('bob', '[email protected]', 28);

-- Insert with default
INSERT INTO users (username, email) VALUES ('alice', '[email protected]');

-- Insert or replace (conflict handling)
INSERT OR REPLACE INTO users (id, username, email) VALUES (1, 'john_new', '[email protected]');

-- 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 = '[email protected]', 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

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 = '[email protected]';

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', '[email protected]');

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

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

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 = '[email protected]';

-- Force index usage
SELECT * FROM users INDEXED BY idx_users_email WHERE email = '[email protected]';

-- Disable index usage
SELECT * FROM users NOT INDEXED WHERE email = '[email protected]';

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