An HTTP backed virtual table for SQLite.

Give VTTP a JSON endpoint, tell it which columns you want, and query it like any other table:

.load ./libvttp
 
CREATE VIRTUAL TABLE links USING vttp (
    URL ('https://api.example.com/links'),
    HEADERS ('Authorization', 'Bearer TOKEN'),
    HEADERS ('Accept', 'application/json'),
    id INTEGER PATH '$.id',
    url TEXT PATH '$.url',
    title TEXT PATH '$.title'
);
 
SELECT url, title FROM links WHERE id > 10;

Don't feel like writing out the columns? Just give it a URL:

CREATE VIRTUAL TABLE todos USING vttp (
    URL ('https://jsonplaceholder.typicode.com/todos')
);
SELECT * FROM todos LIMIT 5;

And that's it! VTTP makes an initial request, works out the columns from the JSON keys, and saves the schema. Each query fetches fresh data. Reopening the database keeps the same columns; drop and recreate the table if you want to infer them again.

Write your Queries

URL(...) sets the endpoint. You need exactly one per table. Add a ‘HEADERS('name’, 'value')` for each request header you need. These can go anywhere in the declaration, and their names aren't case-sensitive. If you repeat a header, the last value wins.

Columns read matching JSON keys by default. Use ‘PATH ’$.address.city'` to reach into nested objects, or leave the columns out to infer all top-level keys. Objects and arrays come back as JSON text; missing fields come back as SQL NULL. If the endpoint has no rows yet, declare the columns yourself so VTTP has something to work with.

A JSON object becomes one row, and an array of objects becomes multiple rows. For arrays inside an object, use SQLite's json_each to pull out the rows. WHERE, ORDER BY, and LIMIT work on the response in SQLite; they don't get sent to the API. Even LIMIT 1 fetches and validates the full response.

Requests use GET, with 10 seconds to connect, 60 seconds total, and a 64 MiB limit on the decoded response. VTTP doesn't follow redirects or pagination, so use the final endpoint directly.

The endpoint and headers belong to the table. Drop and recreate it to change them. Header values, including tokens, are saved in the database's schema SQL.

Building

To build using the Makefile, you'll need these libraries installed onto your system:

SQLite headers (3.45+ recommended).
libcurl (7.62+) to make the HTTP requests.
yyjson to work with JSONs in C without going insane.

On Ubuntu, for example:

sudo apt install build-essential cmake sqlite3 libsqlite3-dev libcurl4-openssl-dev

If your package manager doesn't have yyjson, build it from source:

git clone https://github.com/ibireme/yyjson
cmake -S yyjson -B yyjson/build
cmake --build yyjson/build
sudo cmake --install yyjson/build
sudo ldconfig

Then cd back into the repo root and build the extension file libvttp.so:

make

Linux x86_64 is the tested platform.

Tests

make test

# or: pnpm test

make test builds the extension and runs the tests against a local HTTP server. You'll need Python 3 with SQLite extension loading enabled. No API keys or public endpoints to worry about, and no Node dependencies for this one.

Migration

If you've used an older version, you'll need to drop and recreate your VTTP tables. The declaration syntax has changed:

Replace ‘url TEXT DEFAULT ’...'and query-time endpoints withURL('...').
Replace customGENERATED ALWAYS AS (...)mappings withTEXT PATH '$.field' (or whichever SQL type you need).
DeclareHEADERS('name', 'value')at creation time.
Treaturl,headers, andbodyas ordinary payload column names.
Expand nested arrays with SQLitejson_eachrather thanWHERE body = ...`.

See the introduction, JSON mapping, and configuration for the full interface.

Standalone C helpers

The vapi.h stream helpers are still available for standalone C code. The SQLite extension now has its own request implementation, so it doesn't use these helpers. To build and install libvapi.so, you'll also need yajl and OpenSSL:

sudo apt install libyajl-dev libssl-dev
make all
sudo make install
# sudo make uninstall

Code documentation uses Doxygen, and the tutorial website uses Docusaurus.