SQLite

# Debian/Ubuntu
sudo apt install -y sqlite3

# RHEL/Rocky/Alma
sudo dnf install -y sqlite

# Arch Linux
sudo pacman -S sqlite

sqlite3 --version

# Create or open a database (creates file if it doesn't exist)
sqlite3 <database_name>.db

# Open in read-only mode
sqlite3 -readonly <database_name>.db

# Execute a command and exit
sqlite3 <database_name>.db "SELECT * FROM users;"

rm <database_name>.db
# Also remove WAL and SHM files if they exist
rm -f <database_name>.db-wal <database_name>.db-shm

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at TEXT DEFAULT (datetime('now')),
    status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'banned'))
);

-- Without AUTOINCREMENT (reuses deleted rowids, faster)
CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    message TEXT NOT NULL,
    level TEXT NOT NULL,
    timestamp TEXT DEFAULT (datetime('now'))
);

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

-- Rename a column (SQLite 3.25+)
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- Rename a table
ALTER TABLE users RENAME TO app_users;

-- Drop a column (SQLite 3.35+)
ALTER TABLE users DROP COLUMN phone_number;

BEGIN TRANSACTION;
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    age INTEGER CHECK (age > 0)
);
INSERT INTO users_new (id, name, email) SELECT id, name, email FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
COMMIT;

DROP TABLE IF EXISTS users;

CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

DROP VIEW IF EXISTS active_users;

-- Single row
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Multiple rows
INSERT INTO users (name, email) VALUES
    ('Bob', 'bob@example.com'),
    ('Carol', 'carol@example.com'),
    ('Dave', 'dave@example.com');

-- Insert or replace on conflict
INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'Alice Updated', 'alice@example.com');

-- Upsert (SQLite 3.24+)
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;

-- Basic select
SELECT * FROM users;

-- With conditions and ordering
SELECT name, email FROM users
WHERE status = 'active'
ORDER BY name ASC
LIMIT 10 OFFSET 20;

-- Aggregation
SELECT status, COUNT(*) as count
FROM users
GROUP BY status
HAVING count > 5;

-- Window functions (SQLite 3.25+)
SELECT name, email,
       ROW_NUMBER() OVER (ORDER BY created_at) as row_num
FROM users;

-- JSON functions (SQLite 3.38+)
SELECT json_extract(metadata, '$.role') as role
FROM users
WHERE json_extract(metadata, '$.active') = 1;

-- Common Table Expressions
WITH recent AS (
    SELECT * FROM users WHERE created_at > datetime('now', '-7 days')
)
SELECT * FROM recent WHERE status = 'active';

UPDATE users SET status = 'inactive'
WHERE created_at < datetime('now', '-1 year');

DELETE FROM users WHERE status = 'banned';

-- Delete all rows (faster than DELETE without WHERE)
DELETE FROM users;

BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('Eve', 'eve@example.com');
INSERT INTO logs (message, level) VALUES ('User created: Eve', 'INFO');
COMMIT;

-- Or rollback on error
BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('Eve', 'eve@example.com');
-- Something went wrong
ROLLBACK;

-- DEFERRED (default): acquires lock only when needed
BEGIN DEFERRED TRANSACTION;

-- IMMEDIATE: acquires RESERVED lock immediately (prevents other writers)
BEGIN IMMEDIATE TRANSACTION;

-- EXCLUSIVE: acquires EXCLUSIVE lock immediately (prevents all other access)
BEGIN EXCLUSIVE TRANSACTION;

BEGIN IMMEDIATE;
UPDATE users SET status = 'inactive' WHERE id = 42;
COMMIT;

BEGIN TRANSACTION;

INSERT INTO users (name, email) VALUES ('Frank', 'frank@example.com');

SAVEPOINT before_logs;
INSERT INTO logs (message, level) VALUES ('Test entry', 'DEBUG');
-- Undo just the log entry
ROLLBACK TO SAVEPOINT before_logs;

-- Frank's insert is still staged
COMMIT;

PRAGMA journal_mode=WAL;

-- Automatic checkpointing threshold (default 1000 pages)
PRAGMA wal_autocheckpoint=1000;

-- Manual checkpoint
PRAGMA wal_checkpoint(PASSIVE);    -- Non-blocking, checkpoint what's possible
PRAGMA wal_checkpoint(FULL);       -- Block until all frames checkpointed
PRAGMA wal_checkpoint(TRUNCATE);   -- Full + truncate WAL file to zero bytes

-- Wait up to 5 seconds for locks to clear
PRAGMA busy_timeout=5000;

sqlite3 mydb.db "PRAGMA busy_timeout=5000;"

-- Performance
PRAGMA journal_mode=WAL;           -- Enable WAL mode
PRAGMA synchronous=NORMAL;         -- NORMAL is safe with WAL; FULL is safest
PRAGMA cache_size=-64000;          -- 64 MB page cache (negative = KB)
PRAGMA mmap_size=268435456;        -- 256 MB memory-mapped I/O
PRAGMA temp_store=MEMORY;          -- Store temporary tables in memory

-- Integrity
PRAGMA foreign_keys=ON;            -- Enforce foreign key constraints (off by default)
PRAGMA integrity_check;            -- Full database integrity check
PRAGMA quick_check;                -- Faster, less thorough check

-- Information
PRAGMA table_info(<table>);        -- Column information
PRAGMA index_list(<table>);        -- Indexes on a table
PRAGMA compile_options;            -- SQLite compile-time options
PRAGMA database_list;              -- Attached databases

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA busy_timeout=5000;
PRAGMA cache_size=-64000;
PRAGMA foreign_keys=ON;
PRAGMA temp_store=MEMORY;
PRAGMA mmap_size=268435456;

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Compound index
CREATE INDEX idx_logs_level_time ON logs(level, timestamp);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index (SQLite 3.8.0+)
CREATE INDEX idx_active_users ON users(name) WHERE status = 'active';

-- Expression index (SQLite 3.9.0+)
CREATE INDEX idx_users_lower_name ON users(lower(name));

-- Show the query plan
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'alice@example.com';

-- Full bytecode explanation
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- Enable automatic query plan display
.eqp on

-- Update query planner statistics
ANALYZE;

-- Analyze a specific table
ANALYZE users;

-- View statistics
SELECT * FROM sqlite_stat1;

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

-- Query across databases
SELECT u.name, o.order_date
FROM main.users u
JOIN other.orders o ON u.id = o.user_id;

-- List attached databases
PRAGMA database_list;

-- Detach
DETACH DATABASE other;

# Full dump (portable, human-readable)
sqlite3 <database>.db .dump > backup.sql

# Restore from dump
sqlite3 <database_new>.db < backup.sql

# Backup to file
sqlite3 <database>.db ".backup /backup/db-$(date +%Y%m%d).db"

# Checkpoint first to flush WAL to main database
sqlite3 <database>.db "PRAGMA wal_checkpoint(TRUNCATE);"

# Then copy the database file
cp <database>.db /backup/<database>-$(date +%Y%m%d).db

cp <database>.db <database>.db-wal <database>.db-shm /backup/

#!/bin/bash
# /usr/local/bin/sqlite-backup.sh
DB_PATH="<database>.db"
BACKUP_DIR="/backup/sqlite"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d-%H%M%S)

mkdir -p "${BACKUP_DIR}"
sqlite3 "${DB_PATH}" ".backup ${BACKUP_DIR}/backup-${DATE}.db"

if [ $? -eq 0 ]; then
    echo "Backup successful"
    find "${BACKUP_DIR}" -name "*.db" -mtime +${RETENTION_DAYS} -delete
else
    echo "Backup FAILED" >&2
    exit 1
fi

VACUUM INTO '/backup/compacted.db';

-- Wrap bulk inserts in a transaction (50x+ faster)
PRAGMA synchronous=OFF;  -- Only during initial load
BEGIN TRANSACTION;
INSERT INTO data VALUES (1, 'a');
INSERT INTO data VALUES (2, 'b');
-- ... thousands more
COMMIT;
PRAGMA synchronous=NORMAL;

-- Map 256 MB of database into memory
PRAGMA mmap_size=268435456;

-- Full vacuum (rebuilds entire database, requires 2x disk space temporarily)
VACUUM;

-- Auto-vacuum (reclaims space automatically, slight overhead)
PRAGMA auto_vacuum=INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- Free up to 100 pages