Modern SQLite: Features You Didn't Know It Had

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 );

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" );

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;

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;

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 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.