A thin abstraction over SQLite, to work with tables via JSON. All methods async.

const table = new SQLiteTable("myTable", {
  id: "unique primary key",
  title: "text",
  body: "text",
  created: "integer",
});

await table.create();

---

Start an instance of SQLiteTable. schema is a required object with column names as keys and definitions as values — it is stored on the instance and used by .create(). See TableScheme below for how to define columns.

options.scope controls which SQLite database the table lives in:

Value	Database	Use when
"user" (default)	Shared across all your vals	Data should persist and be accessible from multiple vals
"val"	Scoped to this val only	Data is private to this val, or you want isolation between vals

// User-scoped (default) — table is shared across your account
const table = new SQLiteTable("posts", {
  id: "unique primary key",
  title: "text",
});

// Val-scoped — table is isolated to this val
const table = new SQLiteTable("posts", { id: "unique primary key" }, {
  scope: "val",
});

Create this table in your VT SQLite storage using the schema passed to the constructor. Run this only once per table.

Drop this table from your VT SQLite storage.

Returns whether this table exists in storage.

---

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

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

---

Returns metadata about the table:

• columns: array of { name, type } for each column
• rowCount: total number of rows
• pageSize, pageCount: SQLite page-level storage info
• estimatedTableBytes, humanReadableSize: estimated table size on disk

const info = await table.getInfo();
// {
//   columns: [{ name: "id", type: "INTEGER" }, { name: "title", type: "TEXT" }],
//   rowCount: 42,
//   humanReadableSize: "4 KB",
//   ...
// }

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

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

Returns the first row matching the given conditions.

options is an optional SelectOutputOptionsBase object.

table.getRow({ id: 1234 }, { json: true, columns: ["id", "title"] });

Returns all rows matching the given conditions.

options is an optional SelectOutputOptions object, which extends SelectOutputOptionsBase with ordering.

// Basic fetch
table.getRows({ status: "active" }, { json: true });

// Sorted descending, limited
table.getRows({ status: "active" }, { json: true, descendBy: "created", limit: 10 });

// Ascending, specific columns only
table.getRows(undefined, { json: true, ascendBy: "score", columns: ["id", "score"] });

---

Shared options accepted by both .getRow() and .getRows():

Field	Type	Default	Description
json	boolean	false	Return plain JS objects instead of raw DB output
columns	string[]	—	Limit which columns are returned (defaults to all)

Extends SelectOutputOptionsBase with ordering options, accepted by .getRows(). ascendBy and descendBy are mutually exclusive.

Field	Type	Default	Description
ascendBy	string	—	Column name to sort by, ascending (ASC)
descendBy	string	—	Column name to sort by, descending (DESC)
limit	number	—	Maximum number of rows to return

Returns rows sorted by by in descending order, optionally capped at limit rows.

options (optional):

• excludedValue: Exclude rows where by equals this value.
• json (default false): Return plain JS objects instead of raw DB output.

table.getDescending({ by: "created", limit: 10 });

Same, but sorted in ascending order.

table.getAscending({ by: "created", limit: 10 });

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

---

Returns all indices on the table as an array of { name, sql } objects.

const indices = await table.getIndices();
// [{ name: "idx_title", sql: "CREATE INDEX idx_title ON posts (title)" }]

Creates an index on a single column. Note: there is no IF NOT EXISTS guard — calling this twice with the same name will throw.

await table.addIndex({ name: "idx_title", column: "title" });

---

Exposes read operations (getRow, getRows, countRows, getInfo) 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", {
  id: "unique primary key",
  title: "text",
});

export default (req: Request) => table.http(req);

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

Note: Operators in object form are not available when using .http(). HTTP query params only support simple equality conditions.

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

---

A TableScheme is a plain object where each key is a column name and each value is a column definition. Definitions can be provided in two forms:

Pass a raw SQLite column definition string. The value is used verbatim, so you have full control over type and constraints.

{
  id: "integer primary key",
  title: "text not null",
  score: "real default 0",
}

Pass an object with the following optional fields:

Field	Type	Description
type	"text" | "integer" | "number" | "string"	Column type (defaults to "text")
primaryKey	boolean	Adds PRIMARY KEY constraint
unique	boolean	Adds UNIQUE constraint

{
  id: { type: "integer", primaryKey: true },
  email: { type: "text", unique: true },
  score: { type: "number" },
  bio: {}, // defaults to TEXT, no constraints
}

Both forms can be mixed freely in the same schema:

const table = new SQLiteTable("users", {
  id: { type: "integer", primaryKey: true },
  email: { type: "text", unique: true },
  created: "integer not null",
});

---

Some methods use a conditions argument to filter which rows are matched. Conditions can be provided in two forms:

Pass a plain JS object where each key is a column name. Multiple keys are joined with AND.

Equality (primitive value): matches rows where the column equals the value. A null value uses IS NULL.

table.getRows({ status: "active", score: 42 });
// WHERE status = 'active' AND score = 42

table.getRows({ deleted_at: null });
// WHERE deleted_at IS NULL

Operator object: for non-equality comparisons, set the value to an object with one or more of the following operators. Multiple operators on the same key are also joined with AND.

Operator	SQL equivalent	Example
greaterThan	>	{ score: { greaterThan: 10 } }
atLeast	>=	{ score: { atLeast: 10 } }
lessThan	<	{ score: { lessThan: 100 } }
atMost	<=	{ score: { atMost: 100 } }
notEqual	!=	{ status: { notEqual: "banned" } }
like	LIKE	{ title: { like: "%hello%" } }
in	IN (...)	{ id: { in: [1, 2, 3] } }

// Rows where score is between 10 and 100 (inclusive), and status is not "banned"
table.getRows({
  score: { atLeast: 10, atMost: 100 },
  status: { notEqual: "banned" },
});

// Rows where title contains "hello" (case-insensitive depends on collation)
table.getRows({ title: { like: "%hello%" } });

// Rows where id is one of a set of values
table.getRows({ id: { in: [1, 2, 3] } });

Pass a string to use a raw SQL WHERE clause directly. This gives you full SQLite expressiveness, but you are responsible for safe formatting.

table.getRows("created > 1700000000 OR pinned = 1");