← All posts
ExtensionsSpatiaLitesqlite-vecFTS5sqleanExplainer

SQLite extensions: what they unlock

SQLite ships deliberately small, then lets you bolt on the rest. Loadable extensions add full-text search, spatial queries, vector search, regular expressions, cryptography, and statistics — the pieces other databases bundle by default. Here's what they unlock, why most GUI tools hide them, and what changes when one doesn't.

Chwilio13 min read

You are almost certainly running SQLite right now. It’s in every iPhone and Android phone, every Mac and Windows machine, every Firefox, Chrome, and Safari. It’s in Dropbox, in Skype, in cars and TVs and embedded gear you’d never think of as computers. By most counts it’s the single most deployed database engine on Earth — over a trillion active databases, more than all other engines combined. Among software libraries of any kind, only zlib comes close.

And yet SQLite is intentionally tiny. The full amalgamation source (the single-file sqlite3.c you can drop into any project) clocks in around 200 000 lines of C. It omits features that other databases ship by default: no trig functions, no regular expressions, no geospatial types, no UUID generation, no hashing, no vector search. That’s deliberate. SQLite stays small on purpose, because it was built to be extended.

The extension mechanism has been there from the start: a way to load shared libraries (.dylib on macOS, .so on Linux, .dll on Windows) at runtime that register new SQL functions, collating sequences, virtual tables, or even entirely new filesystem layers. But most developers have never used it, because most tools that put a GUI on SQLite hide it entirely. This post is about what lives behind that wall, and what becomes possible once you can load it.

What actually is an extension?

A loadable extension is a shared library compiled from C (or any language that can produce a C-compatible dynamic library) with a single entry point, a function SQLite calls after dlopen(). That entry point receives a database handle and registers callbacks: “here is a new scalar function,” “here is a virtual table constructor,” “here is a collation.” Once registered, those new capabilities are indistinguishable from SQLite’s built-ins. They appear in schemas, work in WHERE clauses and JOIN conditions and GROUP BY aggregations, show up in EXPLAIN plans, and participate in transactions. From the SQL side, loading one is one statement:

SELECT load_extension('/path/to/my_extension');

The entry point convention is straightforward: a function named sqlite3_X_init where X derives from the filename. A library named spellfixext.dylib gets sqlite3_spellfixext_init. A library named libmathfunc.so gets sqlite3_mathfunc_init. SQLite auto-detects the entry point from the filename, or you can specify one explicitly. The template is under a hundred lines of boilerplate.

The philosophy is exactly “browser plus extensions” or “editor plus plugins”: keep the core lean, let the ecosystem supply domain-specific capability. SQLite’s authors intentionally architected around this split. Extensions can be developed and tested independently from the application, loaded only when needed, and unloaded when the connection closes, or kept permanently resident for capabilities that need to survive across connections, like custom filesystem layers.

What extensions unlock

Here are the categories that matter, with real SQL to ground each one. None of these queries run on a stock SQLite without the named extension loaded, but each works the moment it is.

Full-text search with custom tokenizers

SQLite ships with FTS5, a full-text search engine that gives you phrase queries, prefix matching, boolean operators, column weighting, and relevance-ranked results. It is technically implemented as a loadable extension, just one that happens to be compiled into the standard distribution.

CREATE VIRTUAL TABLE docs USING fts5(title, body);
INSERT INTO docs VALUES ('Extensions in SQLite', 'SQLite can load shared libraries...');
SELECT title, snippet(docs, 1, '<mark>', '</mark>', '...', 32)
FROM docs WHERE docs MATCH 'load* extensions';

But the FTS5 tokenizer interface is itself an extension API. The built-in tokenizer splits on whitespace and folds case, which is adequate for English but useless for languages without word boundaries (CJK) or for tasks that need stemming, synonym expansion, or trigram indexing. A custom tokenizer (a C module that implements xCreate, xTokenize, xDelete) can do any of those. SpatiaLite ships a geonames tokenizer that normalises place names for fuzzy geographic search. The porter tokenizer handles stemming so “running” matches “run.” A trigram tokenizer enables substring search without full scans. The interface is public, stable, and entirely undocumented in the GUI, because the GUI never exposes it.

Geospatial: SpatiaLite

SpatiaLite, by Alessandro Furieri, bundles GEOS (the geometry engine) and PROJ (coordinate reprojection) into a single loadable library and registers over 300 spatial functions. It adds geometry types (POINT, LINESTRING, POLYGON), an R*Tree spatial index, on-the-fly coordinate-system transforms, and the full ST_* function family familiar to PostGIS users.

SELECT name, country,
       ROUND(ST_Distance(geom, MakePoint(4.3517, 50.8503, 4326), 1) / 1000.0, 1) AS km
FROM cities
ORDER BY ST_Distance(geom, MakePoint(4.3517, 50.8503, 4326), 1)
LIMIT 5;

That query, five cities nearest to Brussels with great-circle distances in kilometres, runs against an ordinary .sqlite file. No server, no PostGIS, no Docker. ST_Within answers “which region contains this point?” with a spatial join. AsGeoJSON serialises geometry you can drop straight onto a map. Transform reprojects between any two coordinate systems. The spatial index, activated with CreateSpatialIndex, answers bounding-box queries in logarithmic time via an R*Tree virtual table.

GIS has historically meant servers. SpatiaLite makes it a file you can email.

Vector search: sqlite-vec

Alex Garcia’s sqlite-vec adds vector storage and K-nearest-neighbour search to SQLite. It defines a vec0 virtual table: each row is a vector column plus metadata columns you choose, with distance computation happening inside the extension.

CREATE VIRTUAL TABLE docs USING vec0(
  id       INTEGER PRIMARY KEY,
  title    TEXT,
  category TEXT,
  embedding float[768] distance_metric=cosine
);

-- Find the 10 documents most similar in meaning to a query embedding
SELECT title, distance
FROM docs
WHERE embedding MATCH '[...]'   -- 768 floats as JSON array
  AND k = 10
  AND category = 'legal'        -- metadata filter applied during search
ORDER BY distance;

Swap the 768-number sentence embedding for a 3-number RGB vector and the same query finds the nearest colours. Swap it for a 1 536-number image embedding and you have visual search. The metadata filter (AND category = 'legal') is applied inside the KNN traversal, not as a post-filter, so you get the nearest vectors that also match the condition, even if the globally nearest vectors are in a different category.

This means semantic search, recommendation, and RAG retrieval run in-process, in the same file as the rest of your data, without Elasticsearch, Pinecone, or any external service. The vector database is a .sqlite file.

Regular expressions

SQLite has LIKE and GLOB but no REGEXP. There is a REGEXP operator, but it is only a placeholder. It calls a user-defined function named regexp() that does not exist unless you register one. Without an extension implementing it, WHERE email REGEXP '...' silently returns no rows or throws an error. With one, it works:

-- Requires a regex extension (sqlean, sqlite-regex, or equivalent)
SELECT email FROM users
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

This is the extension mechanism in microcosm: the hook is wired into the SQL grammar, waiting for someone to plug in an implementation. SQLite’s authors deliberately left it empty. Better to let the ecosystem provide the regex engine (PCRE, RE2, Oniguruma) than embed one and bloat the core for every user who never writes a regex.

Mathematics and statistics

Core SQLite ships ABS, ROUND, RANDOM, MAX, MIN, AVG, SUM, TOTAL, COUNT, and GROUP_CONCAT. No SIN, COS, TAN, LOG, EXP, POW, SQRT. No STDDEV, MEDIAN, PERCENTILE. No linear regression, no random sampling, no factorial.

Liam Healy’s extension-functions.c (in SQLite’s own ext/misc/ contrib directory) filled the trig gap years ago. Anton Zhiyanov’s sqlean project bundles statistics, additional math, and much more into domain modules (math, stats, crypto, text, uuid, regexp, time, fileio, ipaddr, fuzzy) compiled for Linux, macOS, and Windows, tested and documented. Together they form what amounts to a standard library for SQLite.

-- Requires a stats extension (sqlean stats or equivalent)
SELECT
  percentile_25(population),
  median(population),
  percentile_75(population),
  stddev(population)
FROM cities
WHERE country = 'US';

Cryptography and hashing

SQLite has no built-in hashing. Extensions add MD5, SHA1, SHA256, SHA512, BLAKE2, BCRYPT, and base64/hex encode/decode as native SQL functions. Password hashing, checksum verification, and binary-to-text encoding all happen in-database:

-- Requires a crypto extension (sqlean crypto or equivalent)
SELECT username, sha256(password || salt) AS pw_hash FROM users;

UUID generation

SQLite has no native UUID type or generator. Extensions add uuid4() (random), uuid7() (time-ordered, index-friendly), and ulid() (sortable unique identifiers). Generating identifiers at the database level rather than in application code means your IDs are consistent across every client, every language binding, every script, and your indexes stay compact because time-ordered UUIDs insert sequentially into the B-tree:

-- Requires a uuid extension (sqlean uuid or equivalent)
INSERT INTO events (id, payload) VALUES (uuid7(), '...');

Querying external data: virtual tables

SQLite’s virtual table interface lets an extension present any data source as if it were a SQL table. CSV files, JSON documents, spreadsheet ranges, HTTP API responses, in-memory data structures: write a virtual table module once, and SELECT, JOIN, WHERE, and GROUP BY all work across it automatically.

SQLite ships json_each() and json_tree(), table-valued functions that expand a JSON string into rows, built on the exact same extension interface. The CSV virtual table from sqlean reads a file directly:

-- Requires a CSV virtual table extension (sqlean vsv or equivalent)
CREATE VIRTUAL TABLE temp.orders USING csv(filename='/data/orders.csv');
SELECT category, SUM(quantity * price) AS revenue
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY category
ORDER BY revenue DESC;

No import step. The CSV is the table. Change the file, re-run the query, the results update. The same pattern works for spreadsheets, log files, and, with sqlite-http, live API responses.

Custom filesystem layers (VFS)

The deepest extension interface is the Virtual File System. By implementing xOpen, xRead, xWrite, xClose, and a handful of other methods, an extension becomes SQLite’s entire I/O subsystem. This is how SQLCipher encrypts databases transparently: every page is encrypted before xWrite and decrypted after xRead, with no change to the SQL above. It is how SQLite databases stored in iCloud or an S3 bucket work: the VFS translates xRead into an HTTP range request. Firefox uses a custom VFS for its bookmarks and history storage. Signal uses SQLCipher, a VFS-level encryption extension, for encrypted local message storage on every platform it ships on.

The VFS interface is the ultimate proof that SQLite’s extension model is not a bolt-on: it is architectural. The entire storage layer is swappable at runtime.

Why nobody uses them

Extension loading is off by default in SQLite. Before any shared library can be loaded, the application must call:

sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, 1, NULL);

This is a deliberate security fence. Loading a malicious extension can compromise the process, so SQLite makes you opt in explicitly, and most applications never do. The sqlite3 command-line shell does enable it (the .load dot-command works out of the box), but that still means every session you manually re-load every extension you need. There is no persistence across sessions, no autocomplete for extension functions, and no GUI to browse the results of a spatial or full-text query.

GUI SQLite tools compound the problem. Most disable extension loading entirely: the load_extension() SQL function is either removed at compile time or restricted at runtime. They cannot afford to let users load arbitrary native code into their process space.

On macOS, the App Store sandbox makes it structurally impossible. A sandboxed app cannot call dlopen(); the system denies it. This means any SQLite browser distributed through the Mac App Store cannot load extensions even if it wanted to. The extension ecosystem has been CLI-only on macOS since the sandbox was introduced, because no sandboxed app can touch it.

The result: roughly a trillion SQLite databases in active use, and the richest part of the ecosystem (spatial search, full-text tokenizers, vector similarity, regex, statistics, cryptography, UUID generation, custom filesystems) has been invisible from every desktop GUI for two decades.

What changes when the barrier comes down

When a tool loads extensions as a normal, remembered step instead of a hidden trick, the whole experience changes. The line between “what SQLite can do” and “what a specialised database server does” blurs into irrelevance.

You load an extension once, not per session. The tool remembers it. Every database connection you open from then on has the extension available: ST_Distance, vec_distance_cosine, regexp_like, median, sha256, uuid7 all appear in the autocomplete dropdown alongside SELECT and WHERE.

Results from extension queries render in the same data grid as any other query. You can scroll through ST_AsGeoJSON output, copy it as JSON, export it as CSV or Excel. You can browse a vector table and inspect individual embeddings in a cell viewer. You can run EXPLAIN QUERY PLAN on a spatial query and see SCAN SpatialIndex VIRTUAL TABLE INDEX, confirmation that the R*Tree is doing its job, without leaving the editor.

Safe editing, the reviewable, preview-first change-set workflow, works on data touched by extension queries. Edit a geometry, preview the SQL, commit it as a transaction. No raw UPDATE against spatial columns, no risk of corrupting the R*Tree.

The workflow stops being “SQLite plus some hacks to make it do spatial” and becomes “a single tool that opens any SQLite database and loads whatever extensions that database needs.” You get spatial queries, full-text search, and vector similarity in the same file, with the same tool, in the same session. No server. No Docker. No API key. Just a .sqlite file and a desktop app.

SQLite as a platform

SQLite’s authors describe it as “an application file format,” not merely a database. Their essay on the subject, one of the best pieces of technical writing on the SQLite site, argues that an SQLite database can replace custom file formats, pile-of-files formats, and ZIP-wrapped file formats, and do it better on every axis: simpler application code, atomic transactions, incremental updates, cross-platform portability, and a schema that documents itself.

The extension mechanism makes that argument even stronger. An application that ships an SQLite database can also ship the extensions that database needs (spatial, full-text, vector, crypto, custom VFS), and the combination is a complete domain-specific platform with no external dependencies beyond the SQLite library itself. This is already happening. Firefox ships SQLite with a custom VFS for bookmarks and history. iMessage stores its messages in SQLite. Signal uses SQLCipher, a VFS-level encryption extension, for encrypted local storage on every platform. Audacity stores project data in SQLite. Fossil, the version control system written by SQLite’s creator, uses SQLite as its repository format.

Extensions close most of the gap between “SQLite as a lightweight embedded database” and “SQLite as a complete application platform.” You don’t need PostGIS to answer “what’s near me?” — SpatiaLite does it. You don’t need Elasticsearch to find similar documents — sqlite-vec does it. A median, a regex, a hash: an extension function covers each one. What you’re left with is a single self-contained file, portable across every operating system and self-documenting, that any tool able to load extensions can open and explore.

You can try this right now, for free. Chwilio is in private beta, and the beta build unlocks every feature, including loading your own extensions, for the whole beta period at no cost. Grab a beta invite, then load any of the extensions discussed above from the Extension Manager (⇧⌘E). When the beta period ends, access reverts to the free trial, and keeping these features needs a license. Questions about a specific extension? Get in touch.

Sources