A thin abstraction over SQLite, to work with tables from vals. All methods async.

new SQLiteTable("myTable")
  .create({
    id: "unique primary key",
    title: "text",
    body: "text",
    created: "integer",
  })

Basics

Start an instance of SQLiteTable.

Create this table in your VT SQLite storage. scheme is a JSON with column names as keys, and definitions as values (see above for example). Run this only once per table.

Drop this table from your VT SQLite storage.

Returns whether this table exists in storage.

Table operations

Add a new row to your table. rowData is a JSON with column names as keys, and row values as values.

table.insertRow({
  id: 1234,
  title: 'Post title',
  body: 'Post body',
  created: 12345678
})

Returns the first row matching the given conditions.

conditions: Either a JSON object of column conditions, or a raw SQL WHERE clause as a string.

options (optional):

json (default false): Return a plain JS object instead of raw DB output.

table.getRow({
  id: 1234,
  title: 'Post title'
})

Same, except returns all matching rows.

Returns a count of all rows in the table that match the condition.

Deletes all rows in the table that match the condition.

Modifies all rows in the table that match the condition. rowData is a JSON of key-value pairs to change; it does not affect any columns not mentioned.

table.updateRow(
  {id: 1234},
  {title: 'New title! The existing body text is not mentioned here so it is unaffected'}
)

Iterate over all rows in batches of 1000, passing each batch to onBatch.

await table.streamRowBatches((batch) => {
  for (const row of batch) {
    console.log(row);
  }
});

Exposes read operations (getRow, getRows, countRows) as an HTTP handler, so your table can be queried directly over the network. Useful for building lightweight read APIs on top of your SQLite data without writing custom routing logic.

Pass it as the handler in an HTTP val, bound to the table instance:

import { SQLiteTable } from "https://esm.town/v/peterqliu/SQLiteTable/main.tsx";

const table = new SQLiteTable("posts");

export default table.http.bind(table);

Then query it via URL. The last path segment selects the operation, and query params become row conditions:

GET /getRows?title=Hello
GET /getRow?id=1234
GET /countRows?created=12345678

Use _columns to limit which columns are returned:

GET /getRows?_columns=id,title

Utilities

Use AI to generate a suggested SQLite command for a given text description of your intent.

const cmd = await table.AISuggestCmd("show me all active users sorted by join date");