SQLite Mastery: Automating Edge Database Design

JSON to SQLite Schema: Type Affinity, JSON1 Functions, and Embedded Database Patterns

SQLite is the most widely deployed database engine in existence — every Android phone, iOS device, Mac, and most web browsers contain a copy. Its defining characteristic is serverless operation: the entire database lives in a single file that your application opens directly, with no network round-trips and no server process to manage. Converting your JSON to SQLite schema is about understanding what's different from server databases: five dynamic type affinities instead of strict types, the JSON1 extension for in-column JSON querying, WAL mode for concurrent reads, and the specific constraints (and freedoms) that come from a file-based embedded database.

Live Example: Task Manager Schema

-- Input JSON
{
  "task_id": "T-500",
  "title": "Implement JSON converter",
  "priority": 2,
  "completed": false,
  "due_date": "2024-02-15",
  "assignee": "alice",
  "metadata": {
    "estimated_hours": 4,
    "tags": ["backend", "tooling"]
  }
}

-- Generated SQLite Schema
CREATE TABLE tasks (
    task_id   TEXT    NOT NULL PRIMARY KEY,
    title     TEXT    NOT NULL,
    priority  INTEGER NOT NULL DEFAULT 0,  -- 0=low, 1=medium, 2=high, 3=urgent
    completed INTEGER NOT NULL DEFAULT 0
                      CHECK (completed IN (0, 1)),   -- SQLite has no BOOLEAN
    due_date  TEXT,                                   -- ISO 8601: "2024-02-15"
    assignee  TEXT,
    metadata  TEXT,   -- JSON stored as TEXT, queried via json_extract()
    created_at TEXT   NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);

CREATE INDEX idx_tasks_priority    ON tasks (priority DESC) WHERE completed = 0;
CREATE INDEX idx_tasks_assignee    ON tasks (assignee)      WHERE completed = 0;
CREATE INDEX idx_tasks_due_date    ON tasks (due_date)      WHERE due_date IS NOT NULL;

Partial indexes (WHERE completed = 0) index only the rows that match the predicate — keeping index size small and lookups fast for the common case of querying active tasks. For a task manager, this matters: the completed = 1 rows grow indefinitely, but partial indexes only track the working set.

SQLite's Type System: Five Affinities, Not Types

SQLite uses type affinity — a preference, not a constraint. Understanding this is critical for correct schema design:

-- SQLite's 5 type affinities:
-- TEXT: 'hello', '123', 'true'
-- INTEGER: 1, -42, 0 (also used for booleans: 0/1)
-- REAL: 3.14, 1.0e10 (64-bit IEEE 754 float)
-- BLOB: raw binary data
-- NUMERIC: integer if possible, real otherwise

-- SQLite is lenient — you CAN store any type in any column:
CREATE TABLE demo (col INTEGER);
INSERT INTO demo VALUES ('hello');  -- works, stores as TEXT
INSERT INTO demo VALUES (3.14);     -- works, stores as REAL
INSERT INTO demo VALUES (42);       -- stores as INTEGER (affinity match)

-- This is why CHECK constraints matter:
CREATE TABLE users (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    email       TEXT    NOT NULL UNIQUE,
    age         INTEGER CHECK (age >= 0 AND age < 150),
    score       REAL    DEFAULT 0.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:%SZ', 'now'))
);

JSON1 Extension: Query JSON Columns

-- Store JSON as TEXT, query with json_extract()
CREATE TABLE events (
    id        INTEGER PRIMARY KEY,
    type      TEXT    NOT NULL,
    payload   TEXT    NOT NULL,  -- JSON
    occurred_at TEXT  NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);

INSERT INTO events (type, payload) VALUES
  ('purchase', '{"user_id": "u1", "amount": 4999, "currency": "USD", "items": ["SKU-001"]}'),
  ('view',     '{"user_id": "u2", "page": "/pricing", "duration_ms": 3400}');

-- Extract a scalar value
SELECT json_extract(payload, '$.amount') AS amount
FROM events
WHERE type = 'purchase';
-- → 4999

-- Filter on JSON content
SELECT id, json_extract(payload, '$.user_id') AS user_id
FROM events
WHERE type = 'purchase'
  AND CAST(json_extract(payload, '$.amount') AS INTEGER) > 1000;

-- Array access
SELECT json_extract(payload, '$.items[0]') AS first_item
FROM events WHERE type = 'purchase';
-- → "SKU-001"

-- json_each() — expand JSON array into rows
SELECT e.id, item.value AS sku
FROM events e,
     json_each(json_extract(e.payload, '$.items')) AS item
WHERE e.type = 'purchase';

-- Generated column (SQLite 3.31+) — index a JSON path
ALTER TABLE events ADD COLUMN user_id TEXT
  GENERATED ALWAYS AS (json_extract(payload, '$.user_id')) VIRTUAL;

CREATE INDEX idx_events_user ON events (user_id);

-- Now this uses the index:
SELECT * FROM events WHERE user_id = 'u1';

WAL Mode and PRAGMA Configuration

The default journal mode (DELETE) locks the database during writes, blocking all readers. WAL mode allows concurrent reads while a write is in progress:

-- Execute once per connection, before any queries
PRAGMA journal_mode = WAL;          -- enable WAL mode (persists)
PRAGMA synchronous = NORMAL;        -- balance between safety and speed
PRAGMA cache_size = -64000;         -- 64MB page cache (negative = KB)
PRAGMA foreign_keys = ON;           -- NOT enabled by default!
PRAGMA busy_timeout = 5000;         -- wait 5s before failing on lock

-- Check current settings
PRAGMA journal_mode;      -- → wal
PRAGMA foreign_keys;      -- → 1 (if enabled)

PRAGMA foreign_keys = ON is the most common SQLite gotcha — foreign key constraints are disabled by default. You must enable them for every connection. In most drivers, this is done via a connection hook or post-connect callback.

Full-Text Search with FTS5

-- FTS5 virtual table for full-text search
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    body,
    content='articles',    -- keeps FTS in sync with the real table
    content_rowid='id'
);

-- Populate FTS from existing data
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

-- Trigger to keep FTS in sync with inserts/updates/deletes
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;

-- Full-text search with relevance ranking
SELECT a.id, a.title, rank
FROM articles_fts
JOIN articles a ON a.id = articles_fts.rowid
WHERE articles_fts MATCH 'sqlite json'
ORDER BY rank;

-- Phrase search and prefix matching
SELECT title FROM articles_fts WHERE articles_fts MATCH '"json schema" OR sqlite*';

Integration: better-sqlite3 (Node.js) and Python sqlite3

// Node.js with better-sqlite3 (synchronous API — ideal for SQLite)
import Database from 'better-sqlite3';

const db = new Database('app.db');

// One-time setup
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

// Prepared statements are cached and reused
const insertTask = db.prepare(`
  INSERT INTO tasks (task_id, title, priority, metadata)
  VALUES (@taskId, @title, @priority, @metadata)
`);

const getActiveTasks = db.prepare(`
  SELECT task_id, title, priority,
         json_extract(metadata, '$.estimated_hours') AS estimated_hours
  FROM tasks
  WHERE completed = 0
  ORDER BY priority DESC, due_date ASC
  LIMIT ?
`);

// Transaction for bulk inserts
const insertMany = db.transaction((tasks: Task[]) => {
  for (const task of tasks) {
    insertTask.run({
      taskId:   task.id,
      title:    task.title,
      priority: task.priority,
      metadata: JSON.stringify(task.metadata),
    });
  }
});

insertMany(taskArray);  // atomic — all or nothing

// Read
const tasks = getActiveTasks.all(20);

Best Practices for Production

• Enable WAL mode on every connection: PRAGMA journal_mode = WAL is the single most impactful performance improvement for SQLite apps with concurrent access. Without it, writes block all readers.
• Always enable foreign keys: PRAGMA foreign_keys = ON is off by default for backward compatibility. Enable it in your connection setup — a missing foreign key enforcement is a data integrity gap.
• Use ISO 8601 TEXT for dates: Store dates as "2024-01-15T08:30:00Z" strings. SQLite's date functions (strftime, date, datetime) work natively with this format, and ISO 8601 lexicographic order matches chronological order — indexes on TEXT dates work correctly.
• Prefer generated columns over computed queries for hot JSON paths: If you query json_extract(payload, '$.user_id') in every WHERE clause, add a generated column and index it. The generated column is free to query and the index makes it fast.

FAQ

Q: Can SQLite handle concurrent writes from multiple processes?
A: Yes, with WAL mode and PRAGMA busy_timeout. WAL allows one writer and multiple readers simultaneously. True parallel writes are serialized — if you need multi-writer concurrency at scale, use PostgreSQL or MySQL instead.

Q: Is SQLite suitable for production web apps?
A: For read-heavy single-server web apps, yes — SQLite with WAL mode performs excellently. Products like Litestream (continuous replication) and LiteFS (distributed SQLite) make it viable for serious production use. For high-write-concurrency or multi-server deployments, PostgreSQL is the better choice.

Q: How do I store booleans?
A: Use INTEGER NOT NULL CHECK (col IN (0, 1)). SQLite has no native BOOLEAN type. The CHECK constraint enforces the 0/1 invariant; application code maps 0 → false and 1 → true.

Q: What is the maximum database size?
A: 281 TB (2^48 bytes) with the default page size. In practice, SQLite is suitable for single-server databases up to hundreds of gigabytes — beyond that, the lack of multi-server distribution becomes a constraint.