Virtual Tables and the Extension Architecture

Learning outcomes

Chapter 14 begins with the mechanism underneath both FTS5 and R*Tree. A virtual table looks table-like to SQL, but a registered module implements how rows are produced, searched, and sometimes changed. That distinction explains why specialized indexes can participate in ordinary SQL without being ordinary B-tree tables.

One SQL surface, several kinds of database object

When SQL says FROM something, that “something” does not always mean rows stored in an ordinary SQLite table B-tree. Chapter 12 introduced views; Chapter 13 used json_each(), a table-valued interface. Virtual tables extend the same idea further: a module can present specialized storage, an external source, or computed information through the relational query interface.

Inventory the current connection before depending on a module

SQLite is embedded into many hosts, and those hosts may compile different optional features. The safest course habit is to ask the runtime what it actually provides. PRAGMA module_list reports virtual-table modules currently registered on this connection; PRAGMA compile_options reports compile-time options. Some modules can be registered lazily, so absence from an early list is not always a universal statement about every possible later extension.

SELECT sqlite_version() AS sqlite_version;PRAGMA module_list;SELECT compile_optionsFROM pragma_compile_optionsWHERE compile_options LIKE '%FTS%'   OR compile_options LIKE '%RTREE%'   OR compile_options LIKE '%DBSTAT%'   OR compile_options LIKE '%SESSION%'   OR compile_options LIKE '%LOAD_EXTENSION%'ORDER BY compile_options;

CREATE VIRTUAL TABLE binds a name to a module

An ordinary CREATE TABLE asks SQLite core to create the normal table structure. CREATE VIRTUAL TABLE instead names a module that has already been registered on the connection. Everything inside the module argument list is passed to that implementation to interpret. FTS5 chooses to interpret many arguments as columns and tokenizer options; R*Tree interprets them as an id plus coordinate bounds.

CREATE VIRTUAL TABLE note_searchUSING fts5(title, body, tokenize='unicode61');CREATE VIRTUAL TABLE device_boundsUSING rtree(device_id, min_x, max_x, min_y, max_y);

These objects are queried with familiar SQL, but that does not make their internal storage or maintenance identical to ordinary tables. In particular, SQLite's virtual-table documentation notes that ordinary CREATE INDEX, triggers directly on a virtual table, and ALTER TABLE ... ADD COLUMN are not generally available in the same way.

The planner still needs a cost conversation

Chapter 10 taught that SQLite chooses plans based on available access paths. For a virtual table, the SQLite core cannot know the module's specialized search structure. Conceptually, the planner asks the module: “Given these WHERE constraints and this ORDER BY, what can you use and roughly how expensive is it?” The virtual-table method associated with that negotiation is called xBestIndex.

SQL query   |   vSQLite query planner   |   |  usable constraints / ordering needs   vvirtual-table module: xBestIndex   |   |  chosen strategy + estimated cost/order capability   vmodule cursor reads matching rows   |   vSQLite finishes joins, filters, projection, result delivery

You do not need the C structure fields yet. The useful practitioner insight is that FTS5 and R*Tree are not magical exceptions to planning: each module tells SQLite which specialized constraints it can exploit.

Eponymous virtual tables and table-valued behavior

Some modules can be queried by their module name without first running CREATE VIRTUAL TABLE. SQLite calls these eponymous virtual tables. dbstat is a documented example when the library was built with SQLITE_ENABLE_DBSTAT_VTAB. Eponymous-only modules are also a foundation for table-valued functions.

-- First check whether dbstat is registered in this build.SELECT nameFROM pragma_module_listWHERE name='dbstat';-- If present, inspect the first few pages without modifying anything.SELECT name, pageno, pagetype, ncell, payload, unusedFROM dbstatWHERE name IN ('device','maintenance_note')ORDER BY name, pagenoLIMIT 12;

If dbstat is unavailable, do not download a random binary merely to complete the lesson. Use PRAGMA table_list, sqlite_schema, and the storage concepts from Chapter 11; then repeat the optional dbstat observation on a trusted build later.

Virtual tables can be writable, read-only, or something in between

“Virtual” does not mean “read-only.” FTS5 accepts writes that update its full-text structures; R*Tree accepts rows that update its spatial index; dbstat is observational. A custom module may support only a subset of INSERT/UPDATE/DELETE. The module contract, not the word “table,” determines what is valid.

Trust boundaries: module registration versus native code loading

A built-in module compiled into the SQLite library is part of the library your application already chose to execute. A loadable extension is different: it is a native shared library brought into the application's process at runtime. Loading one is operationally closer to loading a plugin DLL than to opening a data file.

Lab: map the module surface of your SQLite runtime

Run the following in a disposable database. The objective is not to obtain a particular universal list. It is to produce a capability record for the exact runtime that will execute your application.

SELECT sqlite_version() AS library_version;SELECT nameFROM pragma_module_listORDER BY name;SELECT compile_optionsFROM pragma_compile_optionsORDER BY compile_options;PRAGMA table_list;

On the generation environment's SQLite 3.46.1 library, the registered module list includes fts5, fts5vocab, rtree, rtree_i32, dbstat, json_each, and json_tree. Your list is evidence about your build—not a promise made by the SQLite file format.

Failure diagnosis: “no such module” is a capability problem first

-- 1. Confirm the library actually executing the SQL.SELECT sqlite_version();-- 2. Inspect registered modules.SELECT name FROM pragma_module_list ORDER BY name;-- 3. Inspect relevant compile options.SELECT compile_options FROM pragma_compile_optionsWHERE compile_options LIKE '%FTS%'   OR compile_options LIKE '%RTREE%';-- 4. Only then decide whether the application must use a different--    SQLite build, statically include an approved extension, or safely load one.

Do not confuse the version printed by a separately installed sqlite3 CLI with the SQLite library bundled into Python, Node.js, a mobile runtime, or another application. Chapter 15 will make that driver/library boundary explicit.

Verification checkpoint

Production judgment and bridge

Virtual tables are an extensibility contract, not a shortcut around data modeling. In Lesson 2, FTS5 will use that contract to expose an inverted full-text index through ordinary SQL while applying search-specific tokenization and query syntax.

Authoritative references

• SQLite Virtual Table Mechanism
• SQLite PRAGMA module_list and compile_options
• SQLite dbstat Virtual Table
• SQLite Run-Time Loadable Extensions
• SQLite 3.53.4 release history