Reference / SQL API for Bun and Node
SQL API for Bun and Node
Every public class, function and type of the SQL engine in @tinyshed/tinystore, generated from its source. The SQL guide explains how to use them, and the Python, Go and Rust pages list the same API.
Columns
type Columns<T> = {
[K in keyof T]: T[K][]
}A row type's columns: an array of each of its fields.
Row
type Row = Record<string, SqlValue>A row as SQLite gave it, by its columns' names.
Migrations
type Migrations = string | Record<string, string> | readonly {
name: string
sql: string
}[]A database's migrations: a directory of .sql files named 0001_notes.sql, 0002_tags.sql, or the files themselves by name, for a program bundled into one file. A relative directory is the working directory's.
DatabaseOptions
interface DatabaseOptions {
/** applied when the file has not, and checked at every open; without them the file opens as it is */
migrations?: Migrations
/**
* How far this database's commits go before they return, where the
* store's own is not this file's: `'os'` for what may be lost to a power
* loss, beside a store of what may not. Its buckets and queues commit
* with it. A database open already keeps what it was opened with.
*/
durability?: Durability
}Done
interface Done {
changes: number
lastInsertRowid: number | bigint
}What a write changed: the rows, and SQLite's rowid of the row it inserted, 0 when none.
Statement
type Statement = [text: string | TemplateStringsArray | Sql, ...values: unknown[]]A statement in any of the ways a call takes it: a template whose values are
its values, the text and the values of its ?s, the text and one object of
named values, or a piece of SQL from sql.
db.all`select * from notes where id = ${id}`
db.all('select * from notes where id = ?', id)
db.all('select * from notes where id = :id', { id })Sql
class Sql {
readonly text: string
readonly values: readonly SqlArg[]
readonly bare: boolean
}A piece of SQL and the values of its ?s, which are always values and never
SQL. sql makes one; a call, a batch and a table's query take it.
SqlTag
interface SqlTag {
(strings: TemplateStringsArray, ...values: unknown[]): Sql
(text: string, ...values: unknown[]): Sql
/** conditions joined with or, in parentheses; an empty or finds nothing */
or(...conditions: Condition[]): Sql
/** conditions joined with and, in parentheses; an empty and finds everything */
and(...conditions: Condition[]): Sql
/** a list for in (…), as one value SQLite reads with json_each: any length, the same statement */
list(values: readonly unknown[]): Sql
/** a value kept as its JSON */
json(value: unknown): Sql
/** a name, quoted, never SQL: a column a request chooses */
ident(name: string): Sql
/** a JSON list in column `name` holding `value` */
has(name: string, value: unknown): Sql
}A piece of SQL: sql`total >= ${min}`, or sql('total >= ?', min).
Its helpers build the pieces a query's conditions are made of.
sql
const sql: SqlTagDatabase
class Database implements Runner, Home {
readonly name: string
readonly describe: string
}An application's database: await store.database('app', { migrations }).
The verb says what comes back: all the rows, one or none, one value, or
what a write changed. A write with returning goes through a read's verb
and runs on the writer, its rows given once it is durable.
Database.handle
handle(connection: Connection): Promise<number>Database.keep
keep(method: number, bytes: Uint8Array, make: (connection: Connection) => Promise<Uint8Array>): OpenDatabase.via
via(what: string): ViaDatabase.bucket
bucket<T = unknown>(name: string, options?: BucketOptions): Bucket<T>A bucket of values by key kept in this database's file, beside its
tables, as store.bucket keeps one in kv.db: the same calls, and its
writes commit with the rows in db.tx, through tx.with(bucket).
const sessions = db.bucket<Session>('sessions', { idle: '30d' })Database.queue
queue<S extends StandardSchemaV1>(name: string, options: QueueOptions & {
schema: S
}): Queue<StandardSchemaV1.InferOutput<S>>
queue<T = unknown>(name: string, options?: QueueOptions): Queue<T>A queue of jobs kept in this database's file, as store.queue keeps one
in jobs.db: the same calls and workers, and its jobs commit with the
rows in db.tx, through tx.with(queue). It shares the database's
writer, which is the point and its cost.
const emails = db.queue<Email>('emails', { attempts: 5 })Database.searchIndex
searchIndex<T = Row>(name: string, options: TableIndexOptions): TableIndex<T>An index of a table of this database, kept in its file: its rows found
again by their words, vectors or both, each hit's value its row as
all reads it. The table owns the rows and the index follows them:
every change of a row, by any statement, trigger or migration, reaches
it in the commit that makes it, and one by a process beside the store
within a second.
const articles = db.searchIndex<Article>('articles_search', { table: 'articles', fields: { title: 2, body: 1 } })Database.dropSearchIndex
dropSearchIndex(name: string): Promise<boolean>Drops a table's index this database keeps, its triggers off the table; says whether there was one.
Database.all
all<T = Row>(...statement: Statement): Promise<T[]>Every row a statement gives: db.all<Note>\select * from notes where author_id = ${id}``
Database.one
one<T = Row>(...statement: Statement): Promise<T | undefined>The one row a statement gives, or undefined; two are InvalidError, and a write that gave them rolls back.
Database.scalar
scalar<T = SqlValue>(...statement: Statement): Promise<T>The one value of the one row a statement gives, as count(*) does; null as SQL answers it.
Database.columns
columns<T = Row>(...statement: Statement): Promise<Columns<T>>What all gives, by its columns: an array a column where all gives
an object a row, for rows by the thousand, a chart's points. It comes
faster, and a chart takes it as it is.
const { ts, cpu } = await db.columns<Sample>`select ts, cpu from samples where host = ${host} order by ts`Database.each
each<T = Row>(...statement: Statement): AsyncGenerator<T>The rows a statement gives, one at a time as their parts arrive: an export that holds one part, not every row. The server reads them from one snapshot, at most 64 MiB.
Database.table
table<S extends StandardSchemaV1>(name: string, options: TableOptions & {
schema: S
}): Table<StandardSchemaV1.InferOutput<S>>
table<T = Row>(name: string, options?: TableOptions): Table<T>A table of the database, typed where it opens: its rows' inserts and upserts, and queries built without SQL text. It costs nothing until its first call; open each once, in the module that owns it.
const notes = db.table<Note>('notes', { types: { done: 'bool', created_at: 'time' } })Database.batch
batch(statements: readonly Sql[]): Promise<Done[]>Statements known before they run, written as one: all of them or none, in a shared commit.
Database.tx
tx<T>(fn: (tx: SqlTx) => T | Promise<T>): Promise<T>Runs fn in a transaction that holds the writer, for writes that depend on what its reads found: returning commits, a throw rolls back. It runs once, five seconds at most, past which the server rolls it back; inside it, call nothing that waits on the world.
await db.tx(async tx => {
const left = await tx.scalar<number>`select stock from items where sku = ${sku}`
if (left < 1) throw new SoldOut()
await tx.exec`update items set stock = stock - 1 where sku = ${sku}`
})Database.rows
rows(statement: Sql, want: Want): Promise<Answered>Database.exec
exec(...statement: Statement): Promise<Done>Runs a write once it is durable; writes from many callers share a commit, each failing alone.
SqlTx
class SqlTx implements Runner {
readonly describe: string
}A database's transaction, from db.tx: its reads see its writes, and a
call that fails leaves it as it was before the call. Its calls go one at a
time, in order.
SqlTx.with
with<T>(bucket: Bucket<T>): Bucket<T>
with<T>(queue: Queue<T>): TxQueue<T>
with<T>(channel: Channel<T>): TxChannel<T>A bucket or a queue opened from this database, inside the transaction: what it writes commits with the rows or not at all, and what it reads sees what the transaction wrote.
await db.tx(async tx => {
const order = await tx.table<Order>('orders').insert({ user_id, total })
await tx.with(emails).add({ order: order.id, to })
})SqlTx.all
all<T = Row>(...statement: Statement): Promise<T[]>SqlTx.one
one<T = Row>(...statement: Statement): Promise<T | undefined>SqlTx.scalar
scalar<T = SqlValue>(...statement: Statement): Promise<T>SqlTx.exec
exec(...statement: Statement): Promise<Done>SqlTx.table
table<T = Row>(name: string, options?: TableOptions): Table<T>A table inside the transaction: its calls are the transaction's.
SqlTx.rows
rows(statement: Sql, want: Want): Promise<Answered>SqlTx.end
end(commit: boolean): Promise<void>Sends the last call: commit, or roll back, and waits for the server's word.
ColumnType
type ColumnType = 'bool' | 'time' | 'json' | 'bigint'How a column SQLite keeps otherwise reads in JavaScript.
TableOptions
interface TableOptions {
/** the columns SQLite keeps as 0 or 1, unix milliseconds, JSON text or an integer past 2^53 */
types?: Record<string, ColumnType>
/** a Standard Schema, zod's or valibot's, that checks each row read */
schema?: StandardSchemaV1
}UpsertOptions
interface UpsertOptions<T> {
/** the columns whose value, taken, makes the insert an update: the key */
conflict: string | readonly string[]
/** the columns the update sets from the row; none other changes */
update: readonly (keyof T & string)[]
/** the condition the row that holds the key must meet, its owner's, or it stays as it is */
where?: Condition | undefined
}What an upsert does when the row's key is taken.
RowPage
interface RowPage<T> {
rows: T[]
next?: string
}A page of a query's rows, and the cursor that reads the next; none after the last.
Query
class Query<T = Row> implements Embeddable {}A query on a table: a value, each call returning a new one, so that the
first stays as it was. all, one, scalar, count, each and list
read; update and delete write.
const urgent = await orders
.where({ status: 'pending', deleted: false })
.where(sql.or(sql`amount >= ${1000}`, { priority: 'high' }))
.orderBy('created_at', 'desc')
.limit(50)
.all()Query.where
where(condition: Condition | string, ...values: unknown[]): Query<T>Adds a condition with and: equal values, null as is null and a list
as in (…); a piece of SQL from sql; or text and the values of its ?s.
undefined is InvalidError, never a filter left out.
Query.select
select<R = Row>(columns: string | Sql): Query<R>The columns a row holds, as SQL text, and the shape of the row they make.
Query.include
include<N extends string, C>(name: N, query: Query<C>): Query<T & Record<N, C[]>>Gives each row the rows of another query as a list under name, from
the same statement: the query names the row it belongs to in its
where, by this table's alias, names its columns with select, and has
a limit, which counts for each row. Every value comes back as it is
kept, bytes and integers past 2^53 among them.
const latest = db.table<Post>('posts as p').select<{ id: string; title: string }>('p.id, p.title')
.where('p.author_id = u.id').orderBy('p.id', 'desc').limit(3)
const authors = await db.table<User>('users as u').include('posts', latest).all()Query.join
join(table: string, on: string | Sql): Query<T>Joins a table, 'users as u', on a condition as SQL text or a piece of SQL.
Query.leftJoin
leftJoin(table: string, on: string | Sql): Query<T>Joins a table whose columns may be null where no row of it meets the condition.
Query.groupBy
groupBy(...columns: string[]): Query<T>Groups the rows by columns, quoted as names.
Query.having
having(condition: Condition | string, ...values: unknown[]): Query<T>A condition on the groups, as where takes one.
Query.orderBy
orderBy(column: string | Sql, direction?: Direction): Query<T>Orders the rows by a column, quoted as a name, so that a sort a request
chooses cannot be SQL, or by a piece of SQL: orderBy(sql`lower(title)`).
Query.limit
limit(rows: number): Query<T>Query.offset
offset(rows: number): Query<T>Query.toSQL
toSQL(): {
text: string
values: SqlArg[]
}The text and the values the query sends, to read or to log.
Query.embedded
embedded(): SqlThe query inside another piece of SQL: a subquery, in parentheses.
Query.all
all(): Promise<T[]>Query.one
one(): Promise<T | undefined>Query.scalar
scalar<V = SqlValue>(): Promise<V>Query.count
count(): Promise<number>Every row the conditions match, without the order and the limit.
Query.each
each(): AsyncGenerator<T>The rows one at a time, as their parts arrive.
Query.list
list(options?: {
limit?: number | undefined
after?: string | undefined
}): Promise<RowPage<T>>A page after the last row of the one before, by the query's order,
which ends in a unique column so that no row is read twice or skipped.
next reads the next page, and only of this query.
const first = await orders.orderBy('created_at', 'desc').orderBy('id', 'desc').list({ limit: 50 })
const second = await orders.orderBy('created_at', 'desc').orderBy('id', 'desc').list({ limit: 50, after: first.next })Query.explain
explain(): Promise<string[]>SQLite's plan for the query, a line a step.
Query.update
update(values: {
[K in keyof T]?: T[K] | Sql
} & Record<string, unknown>): Promise<Done>Sets columns of the rows the conditions match, and says how many changed.
Query.delete
delete(): Promise<Done>Deletes the rows the conditions match, and says how many it deleted.
Table
class Table<T = Row> extends Query<T> {
readonly name: string
}A table: its queries, and its rows' inserts and upserts.
const notes = db.table<Note>('notes', { types: { done: 'bool', tags: 'json', created_at: 'time' } })
const note = await notes.insert({ id: uuidv7(), author_id: userId, title: 'Buy milk', done: false, tags: [], created_at: new Date() })Table.insert
insert(row: Partial<T>): Promise<T>
insert(rows: readonly Partial<T>[]): Promise<T[]>Inserts a row and gives it back as the file keeps it; a list of rows is one write, all of them or none.
Table.upsert
upsert(row: Partial<T>, options: UpsertOptions<T>): Promise<T | undefined>Inserts a row, or, when its key is taken, sets the columns update
names on the row that holds it, if that row meets where. It gives
back the row as the file keeps it, or undefined when where kept it.