SQLite Reference

Zero-config file-based database. No server, no authentication — just a file and the sqlite3 CLI.

CLI Basics

Open or create a database

sqlite3 myapp.db

If the file does not exist, SQLite creates it on first write. No server process, no configuration, no authentication — just a file.

Useful dot-commands inside the sqlite3 shell

sqlite3 myapp.db <<'SQL'
.tables                    -- list all tables
.schema devices            -- show CREATE TABLE for 'devices'
.headers on                -- show column names in output
.mode column               -- aligned column output (vs csv, json, etc.)
.mode json                 -- JSON array output (3.33+)
.quit
SQL

Run SQL directly from the command line

sqlite3 myapp.db "SELECT COUNT(*) FROM users;"

# Pipe a script file
sqlite3 myapp.db < schema.sql

Export to CSV

sqlite3 -header -csv myapp.db "SELECT * FROM devices;" > devices.csv

Import CSV into a table

sqlite3 myapp.db <<'SQL'
.mode csv
.import devices.csv devices
SQL

Output Modes

Switch output formats for different consumers

sqlite3 myapp.db <<'SQL'
.mode column
.headers on
SELECT hostname, ip_address, device_type FROM devices LIMIT 5;

.mode json
SELECT hostname, ip_address FROM devices LIMIT 3;

.mode csv
.output report.csv
SELECT * FROM devices;
.output stdout
SQL

.mode json is particularly useful for piping into jq for further processing.

Schema and Types

CREATE TABLE — SQLite type affinity

CREATE TABLE IF NOT EXISTS devices (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    hostname    TEXT NOT NULL UNIQUE,
    ip_address  TEXT,
    vlan_id     INTEGER DEFAULT 10,
    active      INTEGER DEFAULT 1,           -- boolean: 0/1
    created_at  TEXT DEFAULT (datetime('now'))
);

SQLite uses type affinity, not strict types. INTEGER, TEXT, REAL, BLOB, and NULL. Any column can hold any type unless you use STRICT tables (3.37+).

STRICT mode — enforce column types (3.37+)

CREATE TABLE configs (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
) STRICT;

Date and Time Functions

SQLite has no native date type. Dates are stored as TEXT (ISO 8601), INTEGER (Unix epoch), or REAL (Julian day).

Date functions — all return TEXT

-- Current timestamp
SELECT datetime('now');                        -- 2026-04-10 15:30:00
SELECT datetime('now', 'localtime');           -- local timezone

-- Date arithmetic
SELECT datetime('now', '-7 days');             -- 7 days ago
SELECT datetime('now', '+1 month');            -- next month
SELECT date('now', 'start of month');          -- first of current month
SELECT date('now', 'start of year', '+6 months', '-1 day');  -- Jun 30

-- Extract parts
SELECT strftime('%Y-%m', created_at) AS month FROM events;

Backup and Maintenance

Backup a live database safely

sqlite3 myapp.db ".backup backup_$(date +%F).db"

.backup uses SQLite’s online backup API — safe even while the database is being written to. Never cp a busy database file.

Dump as SQL (for version control or migration)

sqlite3 myapp.db .dump > schema_and_data.sql

VACUUM — reclaim space after deletes

sqlite3 myapp.db "VACUUM;"

Integrity check

sqlite3 myapp.db "PRAGMA integrity_check;"

PRAGMAs — Runtime Configuration

Essential PRAGMAs for application databases

-- Write-Ahead Logging: concurrent reads during writes, better performance
PRAGMA journal_mode = WAL;

-- Wait up to 5 seconds for locks instead of failing immediately
PRAGMA busy_timeout = 5000;

-- Enable foreign key enforcement (OFF by default!)
PRAGMA foreign_keys = ON;

-- Sync less aggressively (safe with WAL, significant speedup)
PRAGMA synchronous = NORMAL;

PRAGMA foreign_keys = ON must be set per-connection. It does not persist. Every application connecting to SQLite should set this at startup.

Check current PRAGMA values

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA table_info(devices);

JSON Support (3.38+)

Query JSON fields

-- Extract value from JSON column
SELECT json_extract(config, '$.max_retries') AS max_retries
FROM settings;

-- Use -> and ->> operators (3.38+)
SELECT config->>'$.hostname' AS hostname FROM devices;

-- Filter on JSON values
SELECT * FROM events
WHERE json_extract(metadata, '$.severity') = 'critical';

Full-Text Search (FTS5)

Create and query an FTS index

-- Create virtual table
CREATE VIRTUAL TABLE docs_fts USING fts5(title, body, content=docs);

-- Populate
INSERT INTO docs_fts(rowid, title, body)
SELECT id, title, body FROM docs;

-- Search
SELECT * FROM docs_fts WHERE docs_fts MATCH 'network AND firewall';
SELECT * FROM docs_fts WHERE docs_fts MATCH '"exact phrase"';

FTS5 provides ranked full-text search without external dependencies. Useful for building searchable notes or log databases.

Python Integration

sqlite3 module — standard library, no install needed

import sqlite3
from pathlib import Path

db = Path("myapp.db")
conn = sqlite3.connect(db)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA foreign_keys=ON")
conn.row_factory = sqlite3.Row  # access columns by name

cursor = conn.execute("SELECT * FROM devices WHERE active = ?", (1,))
for row in cursor:
    print(f"{row['hostname']}: {row['ip_address']}")

conn.close()

Always use parameterized queries (? placeholders). Never f-string or format SQL — that is how SQL injection happens.