SQLite: Core SQL, Data Types & Pragmas

SQLite is a serverless, file-based relational database embedded directly in the application. No separate server process — the entire database is a single .db file. Used in mobile apps, browsers, Electron apps, edge computing, and embedded systems.

When to Use SQLite

• Embedded / local-first apps: mobile (iOS, Android), desktop (Electron), IoT devices

• Development and testing: replace PostgreSQL locally — faster setup, no Docker needed

• Read-heavy apps with low concurrency: SQLite handles thousands of reads/second, but concurrent writes are serialized

• Edge computing: Cloudflare D1, Turso, LiteFS — SQLite at the edge

• NOT for: high-concurrency write workloads, multi-server setups, or datasets >1TB

Data Types (Type Affinity)

SQLite uses dynamic typing with 5 storage classes. Column types are suggestions, not enforced — any column can store any type.

-- SQLite storage classes:
-- NULL, INTEGER, REAL, TEXT, BLOB

-- Type affinity (column type → storage class):
-- INTEGER affinity:  INT, INTEGER, TINYINT, BIGINT, BOOLEAN
-- REAL affinity:     REAL, FLOAT, DOUBLE, DECIMAL
-- TEXT affinity:     TEXT, VARCHAR, CHAR, CLOB, DATE, DATETIME
-- BLOB affinity:     BLOB, none
-- NUMERIC affinity:  NUMERIC, DECIMAL (stores as INT if possible, else REAL, else TEXT)

-- Dates stored as TEXT (ISO 8601), REAL (Julian day), or INTEGER (Unix time)
CREATE TABLE events (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  name    TEXT NOT NULL,
  date    TEXT NOT NULL DEFAULT (date('now')),   -- '2026-05-07'
  ts      INTEGER DEFAULT (unixepoch()),          -- Unix timestamp
  price   REAL,
  data    BLOB
);

Essential SQL

-- Create and insert
CREATE TABLE users (
  id       INTEGER PRIMARY KEY,
  email    TEXT UNIQUE NOT NULL,
  name     TEXT,
  created  TEXT DEFAULT (datetime('now'))
);

INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');
INSERT OR REPLACE INTO users (id, email, name) VALUES (1, 'alice@example.com', 'Alice Updated');
INSERT OR IGNORE INTO users (email, name) VALUES ('alice@example.com', 'Duplicate');

-- Query
SELECT * FROM users WHERE email LIKE '%@example.com' ORDER BY created DESC LIMIT 10;

-- Update and delete
UPDATE users SET name = 'Bob' WHERE id = 2;
DELETE FROM users WHERE created < date('now', '-1 year');

-- Upsert (SQLite 3.24+)
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;

Pragmas

Pragmas are SQLite-specific configuration commands. Run them after opening the connection.

-- Performance (run on every connection open)
PRAGMA journal_mode = WAL;       -- Write-Ahead Logging: better concurrency, faster writes
PRAGMA synchronous = NORMAL;     -- Safe + fast (vs FULL which is slower, OFF which is unsafe)
PRAGMA cache_size = -64000;      -- 64MB page cache (negative = KB)
PRAGMA temp_store = MEMORY;      -- Temp tables in memory
PRAGMA mmap_size = 268435456;    -- 256MB memory-mapped I/O

-- Foreign keys (disabled by default!)
PRAGMA foreign_keys = ON;

-- Info
PRAGMA table_info(users);         -- columns: cid, name, type, notnull, dflt_value, pk
PRAGMA index_list(users);         -- indexes on a table
PRAGMA integrity_check;           -- check for corruption
PRAGMA page_count;                -- database size in pages
PRAGMA page_size;                 -- bytes per page (default 4096)

Indexes & Performance

-- Create indexes for frequent query columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_events_date ON events(date DESC);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index (only index subset of rows)
CREATE INDEX idx_active_users ON users(email) WHERE active = 1;

-- Covering index (include extra columns to avoid table lookup)
CREATE INDEX idx_users_name_email ON users(name, email);

-- Explain query plan
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'alice@example.com';
-- Should show: SEARCH users USING INDEX (not SCAN)

-- VACUUM: reclaim space after deletes, defragment
VACUUM;
-- Auto-vacuum
PRAGMA auto_vacuum = INCREMENTAL;