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);
  }
});

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");