SQLite Features You Didn’t Know It Had: JSON, text search, CTE, STRICT, generated columns, WAL

Working with JSON data

SQLite ships with a JSON extension that lets you store and query JSON documents directly in tables. You can keep your schema flexible while still using SQL to slice and dice structured data.

Example: extracting fields from a JSON column:

CREATE TABLE events (
  id      INTEGER PRIMARY KEY,
  payload TEXT NOT NULL -- JSON
);

SELECT
  json_extract(payload, '$.user.id')   AS user_id,
  json_extract(payload, '$.action')    AS action,
  json_extract(payload, '$.metadata')  AS metadata
FROM events
WHERE json_extract(payload, '$.action') = 'login';

You can also create indexes on JSON expressions, making queries over semi-structured data surprisingly fast.

Full-text search with FTS5

SQLite’s FTS5 extension turns it into a capable full-text search engine. Instead of bolting on an external search service, you can keep everything in a single database file.

Example: building a simple search index:

CREATE VIRTUAL TABLE docs USING fts5(
  title,
  body,
  tokenize = "porter"
);

INSERT INTO docs (title, body) VALUES
  ('SQLite Guide', 'Learn how to use SQLite effectively.'),
  ('Local-first Apps', 'Why local storage and sync matter.');

SELECT rowid, title
FROM docs
WHERE docs MATCH 'local NEAR/5 storage';

You get ranking, phrase queries, prefix searches, and more—without leaving SQLite or managing a separate service.

Analytics with window functions and CTEs

SQLite supports common table expressions (CTEs) and window functions, which unlock a whole class of analytical queries that used to require heavier databases.

Example: computing running totals with a window function:

SELECT
  user_id,
  created_at,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM payments
ORDER BY user_id, created_at;

Combine this with CTEs and you can build surprisingly rich reports and dashboards on top of a single SQLite file.

Strict tables and better typing

SQLite is famous (or infamous) for its flexible typing model. Modern SQLite adds STRICT tables, which enforce type constraints much more like PostgreSQL or other traditional databases.

Example: defining a strict table:

CREATE TABLE users (
  id        INTEGER PRIMARY KEY,
  email     TEXT NOT NULL,
  is_active INTEGER NOT NULL DEFAULT 1
) STRICT;

With strict tables, invalid types are rejected at insert time, making schemas more predictable and reducing subtle bugs—especially in larger codebases.

Generated columns for derived data

Generated columns let you store expressions as virtual or stored columns, keeping derived data close to the source without duplicating logic across your application.

Example: a normalized search field:

CREATE TABLE contacts (
  id          INTEGER PRIMARY KEY,
  first_name  TEXT NOT NULL,
  last_name   TEXT NOT NULL,
  full_name   TEXT GENERATED ALWAYS AS (
    trim(first_name || ' ' || last_name)
  ) STORED
);

CREATE INDEX idx_contacts_full_name ON contacts(full_name);

Now every insert or update keeps full_name in sync automatically, and you can index and query it efficiently.

Write-ahead logging and concurrency

Write-ahead logging (WAL) is a journaling mode that improves concurrency and performance for many workloads. Readers don’t block writers, and writers don’t block readers in the common case.

Enabling WAL is a single pragma call:

PRAGMA journal_mode = WAL;

For desktop apps, local-first tools, and small services, WAL mode can dramatically improve perceived performance while keeping SQLite’s simplicity and reliability.