SQLite Practical Guide — CLI · Schema · Backup · Python Integration

#
sudo apt install -y sqlite3   # Ubuntu
brew install sqlite           # macOS

#
sqlite3 myapp.db

#
sqlite> .help

.tables              list tables
.schema users        inspect table schema
.mode column         column-aligned output
.headers on          show headers
.width 20 10 30      set column widths
.output result.txt   write output to a file
.read script.sql     execute an SQL file
.quit                quit

--
CREATE TABLE IF NOT EXISTS users (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  name      TEXT    NOT NULL,
  email     TEXT    NOT NULL UNIQUE,
  age       INTEGER CHECK(age >= 0),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS posts (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title     TEXT    NOT NULL,
  body      TEXT,
  published INTEGER DEFAULT 0
);

--
INSERT INTO users (name, email, age) VALUES
  ('Alice', 'alice@example.com', 30),
  ('Bob', 'bob@example.com', 25);

--
SELECT * FROM users WHERE age > 20 ORDER BY name;

SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id
ORDER BY post_count DESC;

--
UPDATE users SET age = 31 WHERE email = 'alice@example.com';

--
DELETE FROM users WHERE id = 2;

--
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user ON posts(user_id, published);

-- when
ALTER TABLE users ADD COLUMN updated_at DATETIME;

CREATE TRIGGER users_updated_at
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
END;

-- list
.indexes users

-- run
EXPLAIN QUERY PLAN
SELECT * FROM posts WHERE user_id = 1 AND published = 1;

# (SQL )
sqlite3 myapp.db .dump > backup_$(date +%Y%m%d).sql

#
sqlite3 restored.db < backup_20250527.sql

# (DB check after)
sqlite3 myapp.db "VACUUM INTO 'backup.db'"

# WAL backup
sqlite3 myapp.db ".backup backup.db"

import sqlite3
from contextlib import contextmanager

@contextmanager
def get_db(path="myapp.db"):
    conn = sqlite3.connect(path)
 conn.row_factory = sqlite3.Row #
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA journal_mode = WAL")
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

# example
with get_db() as db:
 #
    db.execute(
        "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
        ("Carol", "carol@example.com", 35)
    )

 #
    rows = db.execute("SELECT * FROM users WHERE age > ?", (20,)).fetchall()
    for row in rows:
        print(dict(row))

 # executemany
    data = [
        ("Dave", "dave@example.com", 28),
        ("Eve", "eve@example.com", 32),
    ]
    db.executemany(
        "INSERT OR IGNORE INTO users (name, email, age) VALUES (?, ?, ?)",
        data
    )