Skip to content

Query Builder ​

The query builder lets you write SQL queries in a fluent, chainable way. Use it when you want plain rows back — reports, dashboards, exports, background jobs — instead of models. Every value you pass is sent as a bound parameter, so you never build SQL strings by hand.

ts
import { DB } from '@holo-js/db'

export async function recentPublishedPosts() {
  return await DB.table('posts')
    .select('id', 'title', 'published_at')
    .where('status', 'published')
    .orderBy('published_at', 'desc')
    .limit(10)
    .get()
}

DB uses the connections from your config/database.ts. See Database: Getting Started for setup.

Available methods ​

Every query builder method, grouped by what it does. Each name links to its entry.

Start and choose a connection

Select columns

Filter rows

Join and combine queries

Group, order, and limit

Read rows

Write rows

Debug, cache, and raw SQL

Start a query ​

Call DB.table() with a table name. Each chained method returns a new query, so you can build a base query and reuse it safely.

ts
import { DB } from '@holo-js/db'

const posts = DB.table('posts')

const drafts = await posts.where('status', 'draft').get()
const published = await posts.where('status', 'published').get()

When a table has no definition in the generated schema, rows keep the database driver's values. For example, PostgreSQL returns bigint columns as strings, and SQLite and MySQL return booleans as 1 and 0.

On the default connection, a table name resolves through the Generated schema, the table definitions holo migrate and holo build write from your database. When the table is in it, Holo uses its columns and primary key to check the columns you name and to prepare the values you write, compare and read. The Generated schema describes only the default connection, so a table name on another connection uses no definition. A table that isn't in the Generated schema, such as one created earlier in the same script, uses no definition until the Generated schema is regenerated. Pass a table definition to DB.table() to use its columns right away.

You can alias the table with as:

ts
const rows = await DB.table('posts as p')
  .select('p.id', 'p.title')
  .get()

To run the query on a connection other than the default, pass its name as the second argument. See Multiple Connections.

ts
const events = await DB.table('events', 'analytics').get()

from() ​

from() switches the query to a different table while keeping the connection and any clauses you already added.

ts
const archived = await DB.table('posts').from('archived_posts').get()

Booleans ​

Pass JavaScript booleans directly in filters, inserts, updates, and upserts, including queries started with a string table name:

ts
import { DB } from '@holo-js/db'

await DB.table('users').where('id', 7).update({ active: true })
const activeUsers = await DB.table('users').where('active', true).get()

As Laravel does, Holo binds scalar true as 1 and false as 0 on every driver, so a filter matches what an insert stored, and you don't need to register a table schema for this conversion. A column of a table definition you pass to DB.table() gets the value its type stores, so a PostgreSQL boolean column binds true and false. Query-builder reads use the table definition, including one resolved from the generated schema, to convert boolean columns to booleans. Without a definition, rows keep the driver's values. Booleans inside JSON values remain JSON booleans.

Get all rows ​

get() ​

get() runs the query and returns an array of rows.

ts
const users = await DB.table('users')
  .where('active', true)
  .get()

for (const user of users) {
  console.log(user.email)
}

Get a single row ​

first() ​

first() returns the first matching row, or undefined when nothing matches.

ts
const user = await DB.table('users')
  .where('email', '[email protected]')
  .first()

if (!user) {
  throw new Error('User not found')
}

find() ​

find() looks up a row by its id column. Pass a second argument to search a different column.

ts
const post = await DB.table('posts').find(42)
const bySlug = await DB.table('posts').find('hello-world', 'slug')

sole() ​

sole() returns the only matching row. It throws if there are zero rows or more than one.

ts
const invoice = await DB.table('invoices')
  .where('number', 'INV-1001')
  .sole()

Get a single value ​

value() ​

value() returns one column from the first matching row, or undefined.

ts
const email = await DB.table('users')
  .where('id', 7)
  .value('email')

valueOrFail() ​

valueOrFail() returns the column value, or throws when no row matches.

ts
const apiKey = await DB.table('api_keys')
  .where('user_id', 7)
  .valueOrFail('secret')

soleValue() ​

soleValue() returns the column value of the only matching row. It throws when zero rows or more than one row match.

ts
const plan = await DB.table('subscriptions')
  .where('id', 'sub_123')
  .soleValue('plan')
MethodReturnsWhen nothing matches
value(column)the column valueundefined
valueOrFail(column)the column valuethrows
soleValue(column)the column value of the only rowthrows (also throws if more than one row matches)

Get a list of column values ​

pluck() ​

pluck() returns one column from every matching row as an array.

ts
const titles = await DB.table('posts')
  .where('status', 'published')
  .pluck('title')
// ['Hello World', 'Second Post', ...]

implode() ​

implode(column, glue = '') joins one column from every matching row into a string.

ts
const tags = await DB.table('tags').orderBy('name').implode('name', ', ')
// 'news, release, tips'

Check if rows exist ​

exists() ​

exists() returns true when at least one row matches.

ts
const taken = await DB.table('users')
  .where('email', '[email protected]')
  .exists()

doesntExist() ​

doesntExist() is the opposite of exists(): it returns true when no row matches.

ts
const isFree = await DB.table('users')
  .where('email', '[email protected]')
  .doesntExist()

existsOr() ​

existsOr(callback) returns true when at least one row matches. Otherwise it returns the result of the callback.

ts
const found = await DB.table('invoices')
  .where('number', number)
  .existsOr(() => {
    throw new Error(`Invoice ${number} not found`)
  })

doesntExistOr() ​

doesntExistOr(callback) returns true when no row matches. Otherwise it returns the result of the callback.

ts
const free = await DB.table('users')
  .where('email', email)
  .doesntExistOr(() => 'taken')

Aggregates ​

The builder has methods for the common aggregate functions. They respect your where clauses.

count() ​

count() returns the number of matching rows.

ts
const userCount = await DB.table('users').count()

sum() ​

sum() adds up one numeric column across the matching rows.

ts
const revenue = await DB.table('orders').where('status', 'paid').sum('total')

avg() ​

avg() returns the average of one column. It returns null when no rows match.

ts
const averageOrder = await DB.table('orders').avg('total')

average() ​

average() is another name for avg().

ts
const averageOrder = await DB.table('orders').average('total')

min() ​

min() returns the lowest value of one column. It returns null when no rows match.

ts
const cheapest = await DB.table('products').min('price')

max() ​

max() returns the highest value of one column. It returns null when no rows match.

ts
const priciest = await DB.table('products').max('price')

count() and sum() return a number. avg(), average(), min(), and max() return number | null.

To compute aggregates per group, select them as columns instead. See Grouping.

ts
const postsPerUser = await DB.table('posts')
  .select('user_id')
  .addSelectCount('post_count')
  .groupBy('user_id')
  .get()
// [{ user_id: 1, post_count: 12 }, ...]

Typed results ​

When you pass a table name, rows are typed as Record<string, unknown>. You have two ways to get precise types.

Pass a table definition. Holo generates a definition for every table in your database into .holo-js/generated/schema.generated.ts (Holo regenerates this file; don't edit it). Each table is exported in camelCase (posts, postTags, users). Pass one to DB.table() and column names autocomplete everywhere, and results carry the real column types:

ts
import { DB } from '@holo-js/db'
import { posts } from '../../.holo-js/generated/schema.generated'

const rows = await DB.table(posts)
  .select('id', 'title as heading')
  .where('status', 'published')
  .get()
// rows: { id: number, heading: string }[]

With a table definition, select(), where(), orderBy(), groupBy(), pluck(), and value() only accept real column names, and select('title as heading') renames the key in the result type.

A table definition also sets how integer columns are read. id(), integer(), bigInteger(), and foreignId() columns come back as numbers on every driver, the same values a model returns, including columns of a definition you pass to join(). Name-only queries use the same conversions when the generated schema contains the table; otherwise they keep the driver's values, such as PostgreSQL bigint text. A value above Number.MAX_SAFE_INTEGER throws a HydrationError on every database, MySQL included. Raw SQL selections keep the driver's values.

Or annotate the row type. get(), first(), and sole() accept a row type:

ts
type PostRow = { id: number, title: string }

const rows = await DB.table('posts').select('id', 'title').get<PostRow>()

TIP

For queries that belong to a single domain record — with relationships, casts, and events — use a model instead. Models are typed automatically.

Debug a query ​

toSQL() ​

toSQL() returns the compiled SQL and bindings without running the query.

ts
const query = DB.table('users').where('active', true).orderBy('name')

console.log(query.toSQL())
// { sql: 'SELECT * FROM "users" WHERE "active" = ? ORDER BY "name" ASC', bindings: [true], ... }

toRawSql() ​

toRawSql() returns the SQL with the bindings written into it as literals. Use it to read or copy a query while debugging. Don't run the output; run the query itself, which sends the bindings separately.

ts
console.log(DB.table('users').where('name', 'Ava').toRawSql())
// SELECT * FROM "users" WHERE "name" = 'Ava'

debug() ​

debug() returns the same output as toSQL(), plus the connection name and scheduling details. Sensitive bindings are redacted.

ts
const details = DB.table('users').where('active', true).debug()
console.log(details.connectionName)

dump() ​

dump() logs the debug() output and returns the same query, so you can keep chaining.

ts
const users = await DB.table('users').where('active', true).dump().get()

Cache query results ​

cache() ​

cache() stores the result in the cache. Pass a TTL in seconds, a Date to expire at that time, or an options object with ttl or flexible, plus optional key, driver, and invalidate. See Query Caching for keys, drivers, and invalidation.

ts
const categories = await DB.table('categories')
  .orderBy('name')
  .cache(300)
  .get()

Reading methods reference ​

MethodReturns
get()all rows
first()first row or undefined
find(value, column = 'id')matching row or undefined
sole()the only row; throws otherwise
value(column) / valueOrFail(column) / soleValue(column)one column value
pluck(column)array of one column's values
implode(column, glue = '')one column's values joined into a string
exists() / doesntExist()boolean
existsOr(callback) / doesntExistOr(callback)true, or the callback's result
count() / sum(column)number
avg(column) / average(column) / min(column) / max(column)number | null
paginate() / simplePaginate() / cursorPaginate()a page of rows — see Pagination
chunk() / chunkById() / each() / eachById() / lazy() / lazyById() / lazyByIdDesc() / cursor()batches or a stream — see Chunking

Next steps ​

Released under the MIT License.