1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# Open SQLite shell
sqlite3 database.db

# Execute SQL from command line
sqlite3 database.db "SELECT * FROM users;"

# Open in read-only mode
sqlite3 -readonly database.db

# Useful dot-commands inside the shell
.tables          # List tables
.schema users    # Show CREATE statement
.mode box        # Pretty-print results
.headers on
.timer on

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import sqlite3

# Context manager handles commit/rollback on the transaction
with sqlite3.connect('database.db') as conn:
    conn.row_factory = sqlite3.Row          # dict-like rows
    conn.execute('PRAGMA foreign_keys = ON')

    # Always use parameterized queries (never f-strings)
    rows = conn.execute(
        'SELECT id, username FROM users WHERE created_at > ?',
        ('2026-01-01',),
    ).fetchall()

# Python 3.12+: autocommit attribute replaces the legacy
# isolation_level dance for explicit transaction control.
conn = sqlite3.connect('database.db', autocommit=False)

1
2
3
4
5
6
7
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync('database.db');
const stmt = db.prepare('SELECT * FROM users WHERE id = ?');
const user = stmt.get(1);
db.close();

1
2
3
4
5
6
7
8
9
10
11
import Database from 'better-sqlite3';
const db = new Database('database.db');

const users = db.prepare('SELECT * FROM users').all();

const insert = db.prepare('INSERT INTO users (name) VALUES (?)');
const insertMany = db.transaction((rows) => {
  for (const r of rows) insert.run(r.name);
});
insertMany([{ name: 'alice' }, { name: 'bob' }]);

1
2
3
4
import { Database } from 'bun:sqlite';
const db = new Database('database.db', { strict: true });
const rows = db.query('SELECT * FROM users').all();

1
2
3
# Creates a new database file (or opens if exists)
sqlite3 newdatabase.db

1
2
3
4
5
6
7
8
9
10
11
-- Attach another database
ATTACH DATABASE 'secondary.db' AS secondary;

-- Query across databases
SELECT *
FROM main.users u
JOIN secondary.orders o ON u.id = o.user_id;

-- Detach database
DETACH DATABASE secondary;

1
2
3
4
5
6
7
CREATE TABLE users (
    id         INTEGER PRIMARY KEY,           -- rowid alias; AUTOINCREMENT rarely needed
    username   TEXT    NOT NULL UNIQUE,
    email      TEXT,
    created_at TEXT    NOT NULL DEFAULT (datetime('now'))
);

1
2
3
4
5
6
7
-- Enforces declared column types instead of relying on type affinity.
CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance INTEGER NOT NULL,
    note    TEXT
) STRICT;

1
2
3
4
5
ALTER TABLE users ADD COLUMN last_login TEXT;
ALTER TABLE users RENAME COLUMN email TO email_address;  -- 3.25+
ALTER TABLE users DROP COLUMN email_address;             -- 3.35+
ALTER TABLE users RENAME TO app_users;

1
2
DROP TABLE IF EXISTS users;

1
2
3
4
5
6
7
CREATE TABLE invoices (
    id       INTEGER PRIMARY KEY,
    qty      INTEGER NOT NULL,
    unit     REAL    NOT NULL,
    total    REAL GENERATED ALWAYS AS (qty * unit) STORED
);

1
2
3
4
5
6
7
INSERT INTO users (username, email)
VALUES ('johndoe', 'john@example.com');

INSERT INTO users (username, email) VALUES
    ('alice', 'alice@example.com'),
    ('bob',   'bob@example.com');

1
2
3
4
5
6
INSERT INTO users (username, email)
VALUES ('alice', 'alice@new.com')
ON CONFLICT(username) DO UPDATE
    SET email = excluded.email
    WHERE excluded.email <> users.email;

1
2
3
4
5
6
7
8
9
10
INSERT INTO users (username, email)
VALUES ('carol', 'carol@example.com')
RETURNING id, created_at;

UPDATE users SET last_login = datetime('now')
WHERE username = 'carol'
RETURNING id, last_login;

DELETE FROM users WHERE id = 5 RETURNING *;

1
2
3
4
5
6
7
8
9
10
SELECT * FROM users;

SELECT username, email
FROM users
WHERE created_at > date('now', '-30 days');

-- Pagination (prefer keyset paging for large tables)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
SELECT * FROM users WHERE id > ? ORDER BY id LIMIT 10;  -- keyset

1
2
3
4
5
6
UPDATE users
SET last_login = datetime('now')
WHERE username = 'johndoe';

DELETE FROM users WHERE id = 5;

1
2
3
4
5
6
7
8
SELECT u.username, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH recent AS (
    SELECT * FROM orders WHERE created_at > date('now', '-7 days')
)
SELECT u.username, COUNT(*) AS recent_orders
FROM users u JOIN recent r ON r.user_id = u.id
GROUP BY u.id;

-- Recursive CTE
WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

1
2
3
4
5
6
7
SELECT
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders;

1
2
3
4
5
6
7
8
9
10
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX idx_email ON users(email);

-- Partial index
CREATE INDEX idx_active_users ON users(last_login)
WHERE last_login IS NOT NULL;

-- Expression index
CREATE INDEX idx_email_lower ON users(lower(email));

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE products (
    id       INTEGER PRIMARY KEY,
    name     TEXT NOT NULL,
    price    REAL CHECK (price > 0),
    category TEXT DEFAULT 'Misc',
    UNIQUE(name, category)
);

-- Foreign keys are OFF by default — enable per connection:
PRAGMA foreign_keys = ON;

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    name || ' ' || lastname AS full_name,
    substr(name, 1, 3)      AS first_three,
    length(name)            AS name_length,
    upper(name), lower(name),
    instr(email, '@')       AS at_pos,
    replace(email, '@', ' at ') AS masked
FROM users;

-- printf / format (alias added in 3.38) for formatted strings
SELECT format('User #%d: %s', id, username) FROM users;

1
2
SELECT pi(), sqrt(2), pow(2, 10), ln(10), log10(1000), ceil(1.2), floor(1.8);

1
2
3
4
5
6
SELECT datetime('now');              -- UTC by default
SELECT datetime('now', 'localtime');
SELECT date('now', '+1 month');
SELECT unixepoch('now');             -- 3.38+
SELECT strftime('%Y-%m', created_at) AS month FROM orders;

1
2
3
4
5
6
7
8
9
10
11
12
-- Extract values
SELECT json_extract(data, '$.email') FROM users;
SELECT data ->> '$.email'  FROM users;   -- text result
SELECT data ->  '$.tags'   FROM users;   -- JSON result

-- Build JSON
SELECT json_object('id', id, 'name', username) FROM users;

-- JSONB (binary representation, faster to query)
INSERT INTO users (data) VALUES (jsonb('{"email":"a@b.c","tags":["x","y"]}'));
SELECT jsonb_extract(data, '$.tags[0]') FROM users;

1
2
3
4
5
6
7
8
9
10
CREATE VIRTUAL TABLE docs USING fts5(title, content, tokenize = 'porter unicode61');

INSERT INTO docs(title, content) VALUES ('SQLite', 'A small, fast database engine');

-- Match query + ranking + snippet/highlight
SELECT title, snippet(docs, 1, '<b>', '</b>', '…', 10) AS preview
FROM docs
WHERE docs MATCH 'sqlite OR database'
ORDER BY rank;

1
2
3
4
5
6
7
8
9
CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users
    SET updated_at = datetime('now')
    WHERE id = NEW.id;
END;

1
2
3
CREATE VIEW active_users AS
SELECT * FROM users WHERE last_login > date('now', '-30 days');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Concurrency / durability
PRAGMA journal_mode = WAL;       -- Recommended for most apps
PRAGMA synchronous  = NORMAL;    -- Safe and fast with WAL
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;      -- ms; avoids SQLITE_BUSY in concurrent writers

-- Memory / I/O
PRAGMA cache_size  = -20000;     -- ~20 MiB
PRAGMA temp_store  = MEMORY;
PRAGMA mmap_size   = 268435456;  -- 256 MiB memory-mapped I/O

-- Maintenance
PRAGMA optimize;                 -- Run periodically (e.g. on close)
PRAGMA integrity_check;
PRAGMA wal_checkpoint(TRUNCATE);

1
2
3
4
5
6
7
PRAGMA journal_mode = WAL;
PRAGMA synchronous  = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size   = -20000;
PRAGMA temp_store   = MEMORY;

1
2
3
4
BEGIN IMMEDIATE;        -- acquire write lock up front
-- many INSERT/UPDATE/DELETE
COMMIT;

1
2
3
4
5
6
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC;

ANALYZE;                -- refresh statistics
PRAGMA optimize;        -- automatic, lightweight ANALYZE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# Python: short, retried write transactions
import sqlite3, time

def write_with_retry(conn, fn, retries=5):
    for attempt in range(retries):
        try:
            with conn:                       # BEGIN/COMMIT/ROLLBACK
                conn.execute('BEGIN IMMEDIATE')
                return fn(conn)
        except sqlite3.OperationalError as e:
            if 'locked' in str(e) and attempt < retries - 1:
                time.sleep(0.05 * (2 ** attempt))
                continue
            raise

1
2
3
4
5
6
# CLI dot-command — uses the SQLite Online Backup API
sqlite3 source.db ".backup 'destination.db'"

# Or, with the live DB open, use VACUUM INTO (3.27+) for a consistent snapshot
sqlite3 source.db "VACUUM INTO 'snapshot.db';"

1
2
3
4
5
6
7
8
# Python: programmatic online backup
import sqlite3
src = sqlite3.connect('source.db')
dst = sqlite3.connect('destination.db')
with dst:
    src.backup(dst)
src.close(); dst.close()

1
2
3
4
-- Use user_version to track schema state
PRAGMA user_version;          -- read
PRAGMA user_version = 7;      -- write after applying migration #7