SQLite — Practical Notes#

CSV / JSON          SQLite .db file              PostgreSQL
simple files   →    real queryable DB      →     big server DB
weak search         local + portable             multi-user production

scraping data
course projects
local APIs
small dashboards
RAG metadata
logs / cache
searchable notes
quick demos

sudo apt install sqlite3          # Linux
brew install sqlite               # macOS

uv tool install sqlite-utils
uv tool install datasette

sqlite3 --version
sqlite-utils --version
datasette --version

uv tool uninstall sqlite-utils
uv tool uninstall datasette

sqlite-utils insert app.db users users.csv --csv
sqlite-utils tables app.db --counts
sqlite-utils schema app.db
sqlite-utils app.db "select * from users limit 5"

app.db     = database file
users      = table
columns    = fields
rows       = records

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  done INTEGER DEFAULT 0,
  due_date TEXT,
  created_at TEXT DEFAULT (datetime('now'))
);

INSERT INTO tasks (title, due_date)
VALUES ('Read SQLite notes', '2026-06-15');

SELECT * FROM tasks;

SELECT * FROM tasks
WHERE done = 0;

SELECT * FROM tasks
ORDER BY due_date;

UPDATE tasks
SET done = 1
WHERE id = 1;

DELETE FROM tasks
WHERE id = 1;

SELECT due_date, COUNT(*) AS total
FROM tasks
GROUP BY due_date
ORDER BY due_date;

SELECT  → read
WHERE   → filter
ORDER BY → sort
GROUP BY → summarize
JOIN    → connect tables

orders(id, user_name, user_email, product_name, price)

users
 ├─ id
 ├─ name
 └─ email

products
 ├─ id
 ├─ name
 └─ price

orders
 ├─ id
 ├─ user_id     → users.id
 ├─ product_id  → products.id
 └─ ordered_at

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  ordered_at TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

PRAGMA foreign_keys = ON;

One real-world thing  → one table
Repeated data         → separate table
Connection            → foreign key

SELECT
  orders.id,
  users.name,
  products.name AS product,
  products.price,
  orders.ordered_at
FROM orders
JOIN users ON users.id = orders.user_id
JOIN products ON products.id = orders.product_id;

orders.user_id     ─────→ users.id
orders.product_id  ─────→ products.id

JOIN       → matching rows only
LEFT JOIN  → keep left table rows even if match missing

import sqlite3

con = sqlite3.connect("app.db")
con.row_factory = sqlite3.Row

con.execute("PRAGMA journal_mode = WAL")
con.execute("PRAGMA foreign_keys = ON")

rows = con.execute(
    "SELECT * FROM tasks WHERE done = ?",
    (0,)
)

for row in rows:
    print(row["title"])

con.close()

con.execute(f"SELECT * FROM users WHERE name = '{name}'")

con.execute("SELECT * FROM users WHERE name = ?", (name,))

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;

WAL = Write-Ahead Log

Better for:
- local apps
- readers + writers
- safer database writes

app.db
app.db-wal
app.db-shm

for row in rows:
    con.execute("INSERT INTO logs VALUES (?, ?)", row)
    con.commit()

with con:
    con.executemany(
        "INSERT INTO logs VALUES (?, ?)",
        rows
    )

BEGIN
  many inserts / updates
COMMIT

scraping
bulk import
ETL
logs
dataset building

CREATE INDEX idx_tasks_due_date
ON tasks(due_date);

WHERE
JOIN
ORDER BY
GROUP BY

EXPLAIN QUERY PLAN
SELECT * FROM tasks
WHERE due_date > '2026-06-01';

Without index → SQLite scans many rows
With index    → SQLite jumps directly to useful rows

SELECT * FROM notes
WHERE body LIKE '%sqlite%';

sqlite-utils enable-fts notes.db notes title body --create-triggers
sqlite-utils search notes.db notes "database"

word search      database
phrase search    "full text search"
prefix search    data*
ranked results   best match first

searchable notes
document search
logs search
RAG keyword search
course material search
support chatbot search

SQLite FTS5      → keyword search
Vector database  → semantic search
SQL filters      → metadata filtering

{"user":"alice","score":10,"tags":["sql","tds"]}

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  ts TEXT,
  payload TEXT CHECK (json_valid(payload))
);

SELECT
  payload ->> 'user' AS user,
  payload ->> 'score' AS score
FROM events;

API response
scraped raw data
LLM output
metadata
event logs

Search/filter often?  → make it a column
Store extra details?  → JSON is fine

datasette serve app.db

http://localhost:8001

table browser
SQL editor
filters
CSV export
JSON API
quick demo UI

scrape/API/CSV
    ↓
SQLite
    ↓
Datasette
    ↓
shareable mini data app

# Import CSV
sqlite-utils insert app.db users users.csv --csv

# Import JSON
sqlite-utils insert app.db events events.json

# See tables
sqlite-utils tables app.db --counts

# See schema
sqlite-utils schema app.db

# Query
sqlite-utils app.db "select * from users limit 5"

# Add index
sqlite-utils create-index app.db users email

# Enable search
sqlite-utils enable-fts app.db notes title body --create-triggers

# Search
sqlite-utils search app.db notes "sqlite"

# Serve
datasette serve app.db

# Open SQLite shell
sqlite3 app.db

.tables
.schema
.headers on
.mode table
SELECT * FROM users LIMIT 5;
.quit

title,body,week
SQLite Basics,SQLite is a single-file database,1
FTS5 Search,FTS5 enables full-text search,1
Datasette,Datasette serves SQLite as a web UI,1

sqlite-utils insert notes.db notes notes.csv --csv
sqlite-utils tables notes.db --counts
sqlite-utils schema notes.db

sqlite-utils enable-fts notes.db notes title body --create-triggers
sqlite-utils search notes.db notes "single-file database"

datasette serve notes.db

CSV import
SQL database
schema inspection
full-text search
web publishing

Data analysis:
CSV → SQLite → SQL queries → charts/reports

Scraping:
Website/API → SQLite → clean data → export

Backend:
FastAPI/Flask → SQLite → local app database

RAG:
documents → chunks table → FTS5 search → metadata filter

Dashboard:
SQLite → Datasette → quick web UI

Testing:
SQLite → simple local DB before PostgreSQL

❌ No primary key
✅ Use id INTEGER PRIMARY KEY

❌ Dates like 15/06/26
✅ Use 2026-06-15

❌ SQL with f-strings
✅ Use ? parameters

❌ No index on filter columns
✅ Index WHERE/JOIN columns

❌ One huge messy table
✅ Separate repeated entities

❌ PostgreSQL too early
✅ Start with SQLite for local projects

❌ SQLite for heavy multi-user writes
✅ Move to PostgreSQL

[ ] I can use `sqlite-utils` to import CSV or JSON data into a local DB.
[ ] I know the basic SQL commands: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `JOIN`.
[ ] I understand why parameterized queries (using `?`) are essential for security.
[ ] I know how to create tables with a primary key and foreign keys.
[ ] I can set `PRAGMA journal_mode = WAL` for better performance.
[ ] I understand when to use SQLite vs a large server database like PostgreSQL.

SQLite = local data engine

sqlite-utils  → import/query/manage DB from terminal
sqlite3       → raw SQLite shell
Python sqlite3 → use DB inside code
FTS5          → full-text search
JSON1         → flexible metadata
Indexes       → speed
Transactions  → safe + fast writes
Datasette     → instant web UI/API
PostgreSQL    → when SQLite becomes too small

1. Import CSV/JSON
2. SELECT, WHERE, ORDER BY, GROUP BY
3. CREATE, INSERT, UPDATE, DELETE
4. Keys and JOINs
5. Python sqlite3
6. Indexes and transactions
7. FTS5 search
8. JSON metadata
9. Datasette publishing