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.
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
- table() on
DB, then from(), getTableName(), getConnectionName(), getConnection()
Select columns
- select(), addSelect(), distinct()
- selectCount(), addSelectCount(), selectSum(), addSelectSum(), selectAvg(), addSelectAvg(), selectMin(), addSelectMin(), selectMax(), addSelectMax()
- selectSub(), addSelectSub(), unsafeSelect(), addUnsafeSelect()
Filter rows
- where(), orWhere(), whereNot(), orWhereNot()
- whereNull(), orWhereNull(), whereNotNull(), orWhereNotNull(), withoutWhereNull(), withoutWhereNotNull()
- whereIn(), orWhereIn(), whereNotIn(), orWhereNotIn(), whereBetween(), orWhereBetween(), whereNotBetween(), orWhereNotBetween()
- whereBetweenColumns(), orWhereBetweenColumns(), whereNotBetweenColumns(), orWhereNotBetweenColumns(), whereValueBetween(), orWhereValueBetween(), whereValueNotBetween(), orWhereValueNotBetween()
- whereLike(), orWhereLike(), whereNotLike(), orWhereNotLike(), whereColumn(), orWhereColumn()
- whereAny(), orWhereAny(), whereAll(), orWhereAll(), whereNone(), orWhereNone()
- whereDate(), orWhereDate(), whereMonth(), orWhereMonth(), whereDay(), orWhereDay(), whereYear(), orWhereYear(), whereTime(), orWhereTime()
- wherePast(), orWherePast(), whereNowOrPast(), orWhereNowOrPast(), whereFuture(), orWhereFuture(), whereNowOrFuture(), orWhereNowOrFuture(), whereToday(), orWhereToday(), whereBeforeToday(), orWhereBeforeToday(), whereTodayOrBefore(), orWhereTodayOrBefore(), whereAfterToday(), orWhereAfterToday(), whereTodayOrAfter(), orWhereTodayOrAfter()
- whereJson(), orWhereJson(), whereJsonContains(), orWhereJsonContains(), whereJsonLength(), orWhereJsonLength(), whereJsonDoesntContain(), orWhereJsonDoesntContain(), whereJsonContainsKey(), orWhereJsonContainsKey(), whereJsonDoesntContainKey(), orWhereJsonDoesntContainKey()
- whereFullText(), orWhereFullText(), whereVectorSimilarTo(), orWhereVectorSimilarTo()
- whereExists(), orWhereExists(), whereNotExists(), orWhereNotExists()
- whereSub(), orWhereSub(), whereInSub(), whereNotInSub()
- unsafeWhere(), orUnsafeWhere()
- when(), unless()
Join and combine queries
- join(), leftJoin(), rightJoin(), crossJoin()
- joinSub(), leftJoinSub(), rightJoinSub()
- joinLateral(), leftJoinLateral()
- union(), unionAll()
Group, order, and limit
- groupBy(), having(), orHaving(), havingBetween(), orHavingBetween(), havingNotBetween(), orHavingNotBetween(), havingNull(), orHavingNull(), havingNotNull(), orHavingNotNull()
- orderBy(), orderByDesc(), latest(), oldest(), inRandomOrder(), reorder(), orderByVectorSimilarity(), unsafeOrderBy()
- limit(), offset(), skip(), take(), forPage()
- lock(), lockForUpdate(), sharedLock()
Read rows
- get(), first(), find(), sole()
- value(), valueOrFail(), soleValue(), pluck(), implode()
- exists(), doesntExist(), existsOr(), doesntExistOr()
- count(), sum(), avg(), average(), min(), max()
- paginate(), simplePaginate(), cursorPaginate()
- chunk(), chunkById(), chunkByIdDesc(), each(), eachById(), lazy(), lazyById(), lazyByIdDesc(), cursor()
Write rows
- insert(), insertOrIgnore(), insertGetId(), insertUsing(), insertOrIgnoreUsing(), upsert(), updateOrInsert()
- update(), updateJson(), increment(), decrement(), incrementEach(), decrementEach(), delete(), truncate()
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.
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:
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.
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.
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:
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.
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.
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.
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.
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.
const email = await DB.table('users')
.where('id', 7)
.value('email')valueOrFail()
valueOrFail() returns the column value, or throws when no row matches.
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.
const plan = await DB.table('subscriptions')
.where('id', 'sub_123')
.soleValue('plan')| Method | Returns | When nothing matches |
|---|---|---|
value(column) | the column value | undefined |
valueOrFail(column) | the column value | throws |
soleValue(column) | the column value of the only row | throws (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.
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.
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.
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.
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.
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.
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.
const userCount = await DB.table('users').count()sum()
sum() adds up one numeric column across the matching rows.
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.
const averageOrder = await DB.table('orders').avg('total')average()
average() is another name for avg().
const averageOrder = await DB.table('orders').average('total')min()
min() returns the lowest value of one column. It returns null when no rows match.
const cheapest = await DB.table('products').min('price')max()
max() returns the highest value of one column. It returns null when no rows match.
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.
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:
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:
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.
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.
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.
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.
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.
const categories = await DB.table('categories')
.orderBy('name')
.cache(300)
.get()Reading methods reference
| Method | Returns |
|---|---|
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 |