Introduction

VTTP is a SQLite runtime extension that lets you query an HTTP JSON endpoint as a read-only table. Let's write a query.

Link the Extension

Build the extension with make from the repo root, then open sqlite3 and load it:

.load ./libvttp
.mode box

.mode box just makes the output easier to read. You have now linked the vttp virtual table library with SQLite.

Write your Queries

Create a virtual table with the endpoint and the columns you want from its payload:

CREATE VIRTUAL TABLE todos USING vttp (
    URL ('https://jsonplaceholder.typicode.com/todos'),
    HEADERS ('Accept', 'application/json'),
    id INTEGER,
    "userId" INTEGER,
    title TEXT,
    completed INTEGER
);

SELECT id, title FROM todos WHERE completed = 1 LIMIT 5;

Here, URL tells VTTP where to fetch the todos, and HEADERS adds an HTTP header. The columns read the JSON keys with the same names. JSON keys are case-sensitive, so userId and userid aren't interchangeable, even with unquoted column names.

completed INTEGER turns JSON's true and false into 1 and 0, so the query above gives us five completed todos. If you'd prefer the strings true and false, declare it as TEXT instead.

If you only care about id and title, simply omit the other columns. For nested values, you can add a PATH to the column; we'll get to that in Read JSON. Declaring columns yourself also means VTTP won't send a request until you query it.

Let VTTP work out the Columns

If you want all the top-level fields, leave the columns out entirely:

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

This sends a GET request when the table is created and works out the schema from all the returned objects. PRAGMA table_info(users) shows you what it found. Nested objects and arrays stay as JSON text.

VTTP saves the inferred schema, but each query fetches fresh data. Reopening the database keeps those same columns. If the API adds a field and you want to include it, drop and recreate the table.

If the API returns no rows, there's nothing to infer, so you'll get:

vttp can't infer columns from an empty response. please try again later when the api is up or provide explicit columns yourself

Try again when the endpoint has data, or declare the columns yourself. Explicit columns work fine with an empty response. Network failures, HTTP errors, invalid JSON, non-object rows, and objects without any fields have their own errors.

A few things to keep in mind

URL(...) and HEADERS(...) belong in the declaration. Their names aren't case-sensitive, and they can go before, between, or after the columns. Whitespace before ( is fine too. If you repeat a header name, the last value wins. To change the endpoint or credentials, drop and recreate the table.

Each scan makes a fresh GET request, and a join can scan the table more than once. Filtering, sorting, and limits happen in SQLite. Even LIMIT 1 fetches and validates the whole response first, so don't use it to limit how much data the API sends.

VTTP doesn't follow redirects or pagination. Requests have 10 seconds to connect, 60 seconds total, and a 64 MiB limit on the decoded response.

See JSON mapping and configuration.