A thin abstraction over SQLite, to work with tables via JSON. Basic functionality for

• Simple CRUD operations, intra-table
• HTTP response handler for web reads
• Streaming reads to iterate over large tables in batches
• Store vector embeddings alongside data and do similarity search by nearest neighbour
• Indices to optimize querying, and similarity search with ANN vector indices

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

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

---

SQLiteTable supports vector columns backed by libSQL's native F32_BLOB type, enabling similarity search directly in your SQLite database — no external vector store required.

In object-form schema, set type: "vector" and provide dimensions:

const table = new SQLiteTable("articles", {
  id: { type: "integer", primaryKey: true },
  content: { type: "text" },
  embedding: { type: "vector", dimensions: 1536 },
});

String form also works and is passed through verbatim:

{ embedding: "F32_BLOB(1536)" }

Pass a plain number[] for vector columns. insertRow, batchInsert, and batchUpsert all handle the wrapping automatically:

await table.insertRow({
  id: 1,
  content: "Hello world",
  embedding: [0.1, 0.2, 0.3, /* ... */],
});

Creates an ANN (Approximate Nearest Neighbour) index on a vector column using libSQL's libsql_vector_idx. The index is named ${tableName}_${column}_vidx and is auto-discovered by vectorSearch() — you never need to reference the name directly.

await table.addVectorIndex("embedding");

Call this once after create(). Calling it again with the same column will throw.

Returns the nearest neighbours to a query vector, sorted by ascending distance. Each result includes a distance field alongside the row's normal columns.

const results = await table.vectorSearch({
  queryVector: [0.1, 0.2, 0.3, /* ... */],
  column: "embedding",
  limit: 5,
});
// [{ id: 1, content: "Hello world", embedding: ..., distance: 0.002 }, ...]

Option	Type	Default	Description
queryVector	number[]	—	The query to search against
column	string	—	The vector column to search
limit	number	10	Maximum number of results to return
forceFullScan	boolean	false	Skip the ANN index and use a full table scan instead

If a vector index exists for the column (created via addVectorIndex), it is used automatically for ANN search via vector_top_k. Otherwise the query falls back to a full table scan using vector_distance_cos, which is accurate but slower for large tables (>10k rows).

---

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" | "vector"	Column type (defaults to "text")
dimensions	number	Number of dimensions — required when type is "vector"
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");