This skill should be used when working on SQLite databases, writing SQL schemas, configuring PRAGMAs, using FTS5, or designing embedded database schemas.
These are comprehensive conventions for SQLite database development, covering PRAGMA configuration, type affinity, STRICT tables, schema design, and SQLite-specific features. Following these conventions ensures optimal performance, data integrity, and portability across SQLite 3.40+ environments.
When working with existing SQLite databases and projects, always respect established conventions and patterns before applying these preferences.
PRAGMAs configure SQLite behavior. Some are per-database (persisted), most are per-connection (must be set on every new connection).
-- CORRECT: Complete per-connection PRAGMA setup
PRAGMA journal_mode = WAL; -- Per-database, persisted after first set
PRAGMA foreign_keys = ON; -- Per-connection, OFF by default
PRAGMA busy_timeout = 5000; -- Per-connection, 0ms by default
PRAGMA synchronous = NORMAL; -- Per-connection, FULL by default (safe with WAL)
PRAGMA cache_size = -64000; -- Per-connection, negative = KB (64MB)
PRAGMA journal_size_limit = 67108864; -- Per-database, limit WAL growth (64MB)
PRAGMA temp_store = MEMORY; -- Per-connection, use memory for temp tables
-- WRONG: Opening connection without PRAGMAs
-- Defaults: DELETE journal mode, foreign_keys OFF, busy_timeout 0,
-- synchronous FULL, small cache. Every default is suboptimal.
Per-database (persisted, set once):
journal_mode: WAL vs DELETE. Once set to WAL, persists until changed.page_size: Set before first table creation. Cannot change without VACUUM.auto_vacuum: FULL, INCREMENTAL, or NONE. Set before first table.journal_size_limit: Limits WAL file growth.Per-connection (must set on every new connection):
foreign_keys: OFF by default — always set to ON.busy_timeout: 0 by default — always set to at least 5000ms.synchronous: FULL by default — NORMAL is safe with WAL and faster.cache_size: Varies — increase for read-heavy workloads.temp_store: DEFAULT (file) — MEMORY is faster.WAL (Write-Ahead Logging) is essential for concurrent access:
-- CORRECT: Enable WAL mode
PRAGMA journal_mode = WAL;
-- CORRECT: Manual checkpoint (rarely needed — auto-checkpoint handles this)
PRAGMA wal_checkpoint(PASSIVE); -- Non-blocking checkpoint
PRAGMA wal_checkpoint(TRUNCATE); -- Checkpoint and reset WAL file
-- WRONG: Using DELETE journal mode for concurrent access
PRAGMA journal_mode = DELETE;
-- DELETE mode blocks all readers during writes and is significantly slower
-- for concurrent workloads. Only use for single-connection scenarios or
-- network filesystem constraints.
-- CORRECT: Always enable foreign key enforcement
PRAGMA foreign_keys = ON;
-- Verify it's enabled
PRAGMA foreign_keys; -- Should return 1
-- Check for existing violations
PRAGMA foreign_key_check;
-- WRONG: Relying on default (OFF)
-- SQLite defaults to foreign_keys = OFF for backwards compatibility.
-- Without this PRAGMA, foreign key constraints are parsed but NOT enforced.
-- This means invalid references will be silently accepted.
SQLite uses type affinity rather than strict typing. Any column can store any type unless STRICT is used.
| Affinity | Stores as | Column Type Keywords |
|---|---|---|
| TEXT | Text string | CHAR, CLOB, TEXT, VARCHAR |
| NUMERIC | Integer, real, text | NUMERIC, DECIMAL, BOOLEAN, DATE |
| INTEGER | Integer | INT, INTEGER, TINYINT, BIGINT |
| REAL | Floating point | REAL, DOUBLE, FLOAT |
| BLOB | Raw bytes | BLOB, or no type specified |
STRICT tables enforce column types at insertion time. Allowed types: INTEGER, REAL, TEXT, BLOB, ANY.
-- CORRECT: STRICT table with enforced types
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
notes TEXT,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;
-- WRONG: Non-STRICT table where type safety matters
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
total_cents INTEGER, -- Without STRICT, text '100' would be stored as text
customer_id INTEGER -- '42' (text) would be stored without conversion
);
-- INSERT INTO orders VALUES (1, 'not a number', 'not an id');
-- This succeeds silently in non-STRICT mode!
When to use STRICT:
When STRICT may not be appropriate:
SQLite has no native date/time type. Use TEXT in ISO-8601 format:
-- CORRECT: ISO-8601 text dates
CREATE TABLE events (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
event_date TEXT NOT NULL, -- '2024-03-15'
starts_at TEXT NOT NULL, -- '2024-03-15T09:00:00.000Z'
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;
-- Date arithmetic using SQLite date functions
SELECT title FROM events
WHERE date(event_date) BETWEEN date('now') AND date('now', '+7 days');
-- Formatting
SELECT title, strftime('%Y-%m-%d %H:%M', starts_at) AS formatted
FROM events;
-- WRONG: Unix timestamps
CREATE TABLE events (
id INTEGER PRIMARY KEY,
event_date INTEGER -- 1710460800 — not human-readable, harder to debug
);
-- Unix timestamps work but are harder to read in queries, require conversion
-- for display, and don't sort naturally as text. ISO-8601 TEXT is preferred.
-- WRONG: Non-standard date formats
CREATE TABLE events (
id INTEGER PRIMARY KEY,
event_date TEXT -- '03/15/2024' — ambiguous (US) or '15/03/2024' (EU)
);
-- Non-ISO formats are ambiguous, don't sort correctly, and don't work with
-- SQLite date functions. Always use ISO-8601.
-- CORRECT: INTEGER booleans with CHECK constraint
CREATE TABLE features (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
is_enabled INTEGER NOT NULL DEFAULT 0 CHECK (is_enabled IN (0, 1))
) STRICT;
-- Query with boolean logic
SELECT name FROM features WHERE is_enabled = 1;
SELECT name FROM features WHERE NOT is_enabled; -- SQLite treats 0 as false
-- WRONG: TEXT booleans
CREATE TABLE features (
id INTEGER PRIMARY KEY,
name TEXT,
is_enabled TEXT -- 'true'/'false', 'yes'/'no' — inconsistent, no type safety
);
-- CORRECT: TEXT UUID (human-readable, debuggable)
CREATE TABLE resources (
id TEXT PRIMARY KEY NOT NULL CHECK (
length(id) = 36 AND
id GLOB '????????-????-????-????-????????????'
),
name TEXT NOT NULL
) STRICT;
-- CORRECT: BLOB UUID (compact, 16 bytes)
CREATE TABLE resources (
id BLOB PRIMARY KEY NOT NULL CHECK (length(id) = 16),
name TEXT NOT NULL
) STRICT;
Choose TEXT when: Debugging and readability matter, IDs are logged or displayed. Choose BLOB when: Storage efficiency matters, large tables, IDs are opaque.
-- CORRECT: INTEGER cents for monetary values
CREATE TABLE line_items (
id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0),
total_cents INTEGER GENERATED ALWAYS AS (quantity * unit_price_cents) STORED
) STRICT;
-- Display as dollars in application code: total_cents / 100.0
-- WRONG: REAL for money
CREATE TABLE line_items (
id INTEGER PRIMARY KEY,
price REAL -- 19.99 + 0.01 might equal 20.000000000000004
);
-- IEEE 754 floating point cannot exactly represent most decimal fractions.
-- Use INTEGER cents or store as TEXT with application-level parsing.
-- CORRECT: INTEGER PRIMARY KEY (rowid alias, auto-increments)
CREATE TABLE items (
id INTEGER PRIMARY KEY, -- Alias for rowid, auto-assigned on INSERT
name TEXT NOT NULL
) STRICT;
-- CORRECT: Plain INTEGER PRIMARY KEY
CREATE TABLE logs (
id INTEGER PRIMARY KEY,
message TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;
-- WRONG: Unnecessary AUTOINCREMENT
CREATE TABLE logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
message TEXT NOT NULL
) STRICT;
-- AUTOINCREMENT prevents rowid reuse but adds overhead:
-- - Creates sqlite_sequence tracking table
-- - Slightly slower inserts
-- - IDs never decrease (even after DELETE)
-- Only use when rowid reuse would cause actual problems (audit trails, external references).
snake_case, lowercase, plural (e.g., users, order_items)snake_case, lowercase (e.g., first_name, created_at)idx_ prefix with table and columns (e.g., idx_users_email)_id (e.g., user_id, category_id)users_updated_at)order, group, index, key, value, etc.-- CORRECT: NOT NULL with defaults where possible
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
description TEXT, -- Nullable is intentional and documented
price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;
-- WRONG: Everything nullable
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT, -- Should be NOT NULL
price_cents INTEGER -- Should be NOT NULL with CHECK constraint
);
-- CORRECT: Domain validation with CHECK constraints
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL CHECK (email LIKE '%@%.%'),
age INTEGER CHECK (age >= 0 AND age <= 150),
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'banned')),
role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('admin', 'moderator', 'user'))
) STRICT;
-- CORRECT: JSON with generated columns for indexing
CREATE TABLE events (
id INTEGER PRIMARY KEY,
payload TEXT NOT NULL CHECK (json_valid(payload)),
event_type TEXT GENERATED ALWAYS AS (json_extract(payload, '$.type')) STORED,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;
CREATE INDEX idx_events_type ON events(event_type);
-- WRONG: json_extract in WHERE without generated column
SELECT * FROM events WHERE json_extract(payload, '$.type') = 'purchase';
-- Full table scan + per-row JSON parsing. Use generated columns for indexed queries.
-- CORRECT: External content FTS5 with sync triggers
CREATE VIRTUAL TABLE docs_fts USING fts5(
title, body,
content='documents',
content_rowid='id',
tokenize='porter unicode61'
);
-- WRONG: LIKE with leading wildcard for text search
SELECT * FROM documents WHERE body LIKE '%search term%';
-- Requires full table scan. Use FTS5 for text search.
-- CORRECT: WITHOUT ROWID for small lookup tables
CREATE TABLE settings (
key TEXT PRIMARY KEY NOT NULL,
value TEXT NOT NULL
) STRICT, WITHOUT ROWID;
CREATE TABLE tag_assignments (
item_id INTEGER NOT NULL REFERENCES items(id),
tag_id INTEGER NOT NULL REFERENCES tags(id),
PRIMARY KEY (item_id, tag_id)
) STRICT, WITHOUT ROWID;
-- WRONG: WITHOUT ROWID on large table with INTEGER PRIMARY KEY
CREATE TABLE events (
id INTEGER PRIMARY KEY,
data TEXT NOT NULL
) STRICT, WITHOUT ROWID;
-- INTEGER PRIMARY KEY is already a rowid alias — WITHOUT ROWID adds overhead
-- by storing data in a B-tree indexed by PK instead of using the optimized rowid.
-- CORRECT: Idempotent upsert
INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = excluded.value;
-- CORRECT: Window functions for analytics
SELECT
name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
-- CORRECT: Get inserted data back
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')
RETURNING id, created_at;
-- CORRECT: Stored generated column for indexing
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
data TEXT NOT NULL CHECK (json_valid(data)),
customer TEXT GENERATED ALWAYS AS (json_extract(data, '$.customer')) STORED
) STRICT;
CREATE INDEX idx_orders_customer ON orders(customer);
-- CORRECT: Acquire write lock immediately
BEGIN IMMEDIATE;
-- ... write operations ...
COMMIT;
-- WRONG: Deferred transaction (default)
BEGIN;
-- ... read operations ...
INSERT INTO ...; -- May get SQLITE_BUSY here if another writer started
COMMIT;
-- Deferred transactions don't acquire the write lock until the first write.
-- This can cause unexpected SQLITE_BUSY errors mid-transaction.
CREATE INDEX ... WHERE conditionCREATE INDEX ... ON table(lower(col))EXPLAIN QUERY PLAN to verify index usage| Feature | Minimum Version |
|---|---|
| WAL mode | 3.7.0 |
| FTS5 | 3.9.0 |
| UPSERT | 3.24.0 |
| Window functions | 3.25.0 |
| Generated columns | 3.31.0 |
| RETURNING clause | 3.35.0 |
| STRICT tables | 3.37.0 |
| Built-in JSON | 3.38.0 |
| RIGHT/FULL OUTER JOIN | 3.39.0 |
| Math functions | 3.35.0 |
Always check the SQLite version in the target environment before using newer features. Mobile platforms and embedded systems may ship older versions.