SQLite § virtual-tables

Virtual tables

A virtual table is an object that looks like an SQL table to a query — it can be the target of SELECT, INSERT, UPDATE, DELETE — but whose rows are produced by code rather than read from a B-tree on disk. The virtual-table mechanism is one of SQLite’s distinctive features: it is the extension point through which the engine integrates with arbitrary external data, including the file system, in-memory data structures, network services, full-text indexes, R-tree spatial indexes, and JSON. The mechanism is exposed both as a set of pre-built modules shipped with SQLite and as a C API for application authors to register their own.

This page covers the conceptual model of virtual tables, the syntax of CREATE VIRTUAL TABLE, the pre-built modules shipped with SQLite (fts5, rtree, json_each, json_tree, dbstat, csv, series via generate_series), the table-valued function form, and a high-level summary of the C API for authoring new modules.

The conceptual model

An ordinary SQLite table is a B-tree on disk. The engine handles INSERT by inserting a key into the B-tree, SELECT by traversing it. A virtual table replaces the B-tree with a module — a collection of C functions registered through the API — that responds to the same operations. The module can compute its rows on demand (the way generate_series produces an integer sequence), can read them from another data source (the way the csv module reads from a CSV file), or can maintain its own data structure separate from the SQLite database (the way fts5 maintains a full-text index).

To a query, the virtual table is indistinguishable from an ordinary table:

SELECT value FROM generate_series(1, 10);
SELECT * FROM csv WHERE name = 'Ada';
SELECT rowid, rank FROM article_fts WHERE article_fts MATCH 'sqlite';

The query planner consults the module to find the most efficient access strategy — whether the module supports indexed lookups, range scans, or only a full scan — and the join order is chosen accordingly. The full surface of the API permits the module to participate in query planning at the same level of detail as the engine’s own B-trees.

CREATE VIRTUAL TABLE

A virtual table is created with CREATE VIRTUAL TABLE:

CREATE VIRTUAL TABLE name USING module(arg1, arg2, …);

The module name selects the registered module; the arguments are passed to the module’s xCreate function. Each module defines its own argument grammar; the syntax is module-specific and is documented under the module.

CREATE VIRTUAL TABLE IF NOT EXISTS suppresses the error if the table exists. DROP TABLE removes a virtual table as it does an ordinary table.

The system schema records the CREATE VIRTUAL TABLE statement in sqlite_master so that the virtual table is restored on reopening the database.

The bundled modules

The SQLite distribution includes a small set of virtual-table modules. The most-used:

fts5 — full-text search

fts5 implements full-text search over text-heavy columns. The module is documented in detail under Full-text search. The skeletal form:

CREATE VIRTUAL TABLE article_fts USING fts5(title, body);

INSERT INTO article_fts (title, body) VALUES ('SQLite', 'SQLite is …');

SELECT title FROM article_fts WHERE article_fts MATCH 'sqlite';

rtree — spatial index

rtree implements the R-tree spatial index, suitable for two-dimensional geometric queries (bounding-box overlap, nearest-neighbour). The module supports any number of dimensions; rectangles in k dimensions are stored as 2k coordinates.

CREATE VIRTUAL TABLE area_idx USING rtree(
    id,
    minX, maxX,
    minY, maxY
);

INSERT INTO area_idx VALUES (1,  0.0,  10.0,  0.0,  10.0);
INSERT INTO area_idx VALUES (2,  5.0,  15.0,  5.0,  15.0);

-- Areas overlapping the box (3,3)-(7,7)
SELECT id FROM area_idx
WHERE minX <= 7 AND maxX >= 3
  AND minY <= 7 AND maxY >= 3;

R-tree queries are evaluated in O(log n) expected time, far faster than the equivalent linear scan over a column-stored geometry. The module is the standard tool for SQLite-based geospatial work; the SpatiaLite extension builds further on it.

json_each and json_tree — JSON expansion

These two table-valued functions expand a JSON value into rows: one row per top-level element (json_each) or one row per element at any depth (json_tree):

SELECT key, value
FROM json_each('{"a":1,"b":2,"c":3}');
-- a|1
-- b|2
-- c|3

SELECT key, value, type, path
FROM json_tree('{"a":1,"b":[2,3],"c":{"d":4}}');

The functions are the canonical mechanism for querying JSON payloads in SQL; they are documented in detail under JSON.

dbstat — database introspection

dbstat is a read-only virtual table that exposes the storage layout of the database. It reports, per page, which table or index it belongs to, how many rows or entries are stored, and how full the page is. The module is useful for diagnosing storage inefficiency:

SELECT name, SUM(payload) AS bytes_used, SUM(unused) AS bytes_wasted
FROM dbstat
GROUP BY name
ORDER BY bytes_wasted DESC;

The output is the conventional starting point for VACUUM decisions and for understanding the on-disk size of a particular table.

csv — read CSV files

The csv module exposes a CSV file as a queryable table:

CREATE VIRTUAL TABLE customer_import USING csv(
    filename = 'customers.csv',
    header   = 1
);

SELECT * FROM customer_import WHERE country = 'GB';

The module is read-only; combining it with INSERT INTO target SELECT … is the conventional bulk-load idiom. csv is bundled in the SQLite source but is not always linked into the standard sqlite3 shell; the .import shell command provides a more portable alternative for CSV ingest.

generate_series

generate_series is a table-valued function: a virtual-table-like construct invoked by name in the FROM clause without an intervening CREATE. It returns the integers in a range:

SELECT value FROM generate_series(1, 10);
-- 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

SELECT value FROM generate_series(0, 100, 10);   -- step 10
-- 0, 10, 20, … 100

The function is useful for constructing dense sequences without an auxiliary table — for date series in reports that need a row per day even on days with no activity, for parameterised number ranges in tests, and for the recursive-CTE alternative when the iteration is a simple count. generate_series is bundled in the SQLite source but is not always available in standard shells; a recursive CTE (see CTEs and recursion) is the universal substitute.

Table-valued functions

A table-valued function is a virtual table whose rows are determined entirely by its arguments — the constructor accepts arguments and the table is consumed in the same statement. The function is invoked directly in FROM:

SELECT key, value FROM json_each('[1,2,3]');
SELECT value FROM generate_series(1, 5);

Table-valued functions are the convenient form for short-lived virtual tables; persistent virtual tables are created with CREATE VIRTUAL TABLE and remain across connections.

When a virtual table is the right answer

The virtual-table mechanism is powerful and can be over-used. The decision rule:

Yes, virtual table: the data lives somewhere outside the SQLite database (a CSV file, an HTTP service, a memory-mapped binary format, a hardware sensor) and an SQL query is the appropriate access shape.
Yes, virtual table: the index has structure (full-text inverted index, R-tree, prefix tree) that an ordinary B-tree cannot efficiently represent.
Yes, virtual table: the rows are computed (generate-series, regex matches, JSON expansion) and storage is unnecessary.
No, virtual table: the data is structured row-and-column data that fits naturally in an SQLite table; an ordinary table with appropriate indexes is simpler, faster, and more durable.
No, virtual table: the application’s query needs are simple lookups or filters; the engine’s normal mechanisms suffice.

The virtual-table mechanism is the right answer for integration with existing data sources or acceleration with specialised indexes; it is not a general-purpose performance lever.

Authoring a virtual table

A virtual-table module is a C structure containing function pointers — the xMethods — that the engine calls in response to operations on the table. The principal methods:

Method	Purpose
`xCreate`	Called for `CREATE VIRTUAL TABLE`. Initialises the module’s state.
`xConnect`	Called when the database is opened and the virtual table is referenced.
`xBestIndex`	Called by the planner. Reports the cost of a proposed access strategy.
`xFilter`	Called to begin a query. Receives the strategy chosen by `xBestIndex`.
`xNext`	Advances to the next row.
`xColumn`	Returns the value of a column for the current row.
`xEof`	Reports whether iteration has reached the end.
`xUpdate`	Handles `INSERT`, `UPDATE`, `DELETE` (for writable tables).
`xDestroy`	Called for `DROP TABLE`. Releases the module’s state.

The module is registered through sqlite3_create_module (or sqlite3_create_module_v2). Loadable extensions — shared libraries that register their modules — can be loaded at runtime through sqlite3_load_extension or the .load shell command, provided the host application has enabled extension loading.

The full surface of xBestIndex is the most intricate of the API: the planner provides a list of constraints (WHERE col = ?, WHERE col > ?, ORDER BY col) and asks the module to return a cost estimate and indicate which constraints it can use. A well-designed xBestIndex permits the module to participate in query planning as efficiently as the engine’s own indexes; a naive xBestIndex simply reports a high cost and accepts only a full-scan strategy, which is acceptable for small or rarely-queried tables.

The complete reference is at sqlite.org/vtab.html. The conventional starting point for new module authors is to copy one of the bundled modules (fts5, csv, series) and adapt the methods to the new data source; the bundled modules are well-commented and handle the common cases.

Inspection

The PRAGMA module_list enumerates the registered modules:

PRAGMA module_list;
-- pragma, generate_series, fts5, rtree, dbstat, json_each, json_tree, csv, ...

The list is connection-specific and reflects the modules registered by the host application and the loaded extensions. The sqlite3 shell registers a useful default set; embedded applications register only the modules they need.

The sqlite_master system table records virtual-table definitions:

SELECT name, sql FROM sqlite_master WHERE type = 'table' AND sql LIKE 'CREATE VIRTUAL%';

Virtual tables are otherwise indistinguishable from ordinary tables in queries and in introspection.