BaseQueryBuilder

Defined in: packages/db/src/query/builder/index.ts:138

Type Parameters

TContext

TContext extends Context = Context

Constructors

Constructor

new BaseQueryBuilder<TContext>(
   query, 
   resolveCollection?, 
owned?): BaseQueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:141

Parameters

query

Partial<QueryIR> = {}

resolveCollection?

CollectionResolver

owned?

boolean = false

Internal

Whether the builder may keep query without copying it.

Returns

BaseQueryBuilder<TContext>

Accessors

fn

Get Signature

get fn(): object;

Defined in: packages/db/src/query/builder/index.ts:956

Functional variants of the query builder These are imperative function that are called for ery row. Warning: that these cannot be optimized by the query compiler, and may prevent some type of optimizations being possible.

Example

q.fn.select((row) => ({
  name: row.user.name.toUpperCase(),
  age: row.user.age + 1,
}))

Returns

having()

having(callback): QueryBuilder<TContext>;

Filter grouped rows using a function that operates on each aggregated row Warning: This cannot be optimized by the query compiler

Parameters

callback

(row) => any

A function that receives an aggregated row (with $selected when select() was called) and returns a boolean

Returns

QueryBuilder<TContext>

A QueryBuilder with functional having filter applied

Example

// Functional having (not optimized)
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .select(({posts}) => ({ userId: posts.userId, count: count(posts.id) }))
  .fn.having(({ $selected }) => $selected.count > 5)

select()

select<TFuncSelectResult>(callback): FnSelectQueryResult<TContext, TFuncSelectResult>;

Select fields using a function that operates on each row Warning: This cannot be optimized by the query compiler

Type Parameters

TFuncSelectResult

TFuncSelectResult

Parameters

callback

(row) => TFuncSelectResult

A function that receives a row and returns the selected value

Returns

FnSelectQueryResult<TContext, TFuncSelectResult>

A QueryBuilder with functional selection applied

Example

// Functional select (not optimized)
query
  .from({ users: usersCollection })
  .fn.select(row => ({
    name: row.users.name.toUpperCase(),
    age: row.users.age + 1,
  }))

Child query builders, query expressions, and helpers such as eq(), toArray(), and materialize() cannot be returned from fn.select(). Use them as fields in select() so the compiler can add them to the query graph.

Compiled Collection-valued includes cannot be inputs to fn.select(), including nested descendants. Use toArray() or materialize() in the upstream select(), or do parent-only functional work before adding live Collection includes with select().

where()

where(callback): QueryBuilder<TContext>;

Filter rows using a function that operates on each row Warning: This cannot be optimized by the query compiler

Parameters

callback

(row) => any

A function that receives a row and returns a boolean

Returns

QueryBuilder<TContext>

A QueryBuilder with functional filtering applied

Example

// Functional where (not optimized)
query
  .from({ users: usersCollection })
  .fn.where(row => row.users.name.startsWith('A'))

Methods

_getQuery()

_getQuery(): QueryIR;

Defined in: packages/db/src/query/builder/index.ts:1053

Returns

QueryIR

distinct()

distinct(): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:873

Specify that the query should return distinct rows. Deduplicates rows based on the selected columns.

Returns

QueryBuilder<TContext>

A QueryBuilder with distinct enabled

Example

// Get countries our users are from
query
  .from({ users: usersCollection })
  .select(({users}) => ({ country: users.country }))
  .distinct()

findOne()

findOne(): QueryBuilder<TContext & SingleResult>;

Defined in: packages/db/src/query/builder/index.ts:893

Specify that the query should return a single result

Returns

QueryBuilder<TContext & SingleResult>

A QueryBuilder that returns the first result

Example

// Get the user matching the query
query
  .from({ users: usersCollection })
  .where(({users}) => eq(users.id, 1))
  .findOne()

from()

from<TSource>(source): QueryBuilder<ContextFromSource<TSource>>;

Defined in: packages/db/src/query/builder/index.ts:260

Specify the source table or subquery for the query

Type Parameters

TSource

TSource extends Source

Parameters

source

SingleSource<TSource>

An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery

Returns

QueryBuilder<ContextFromSource<TSource>>

A QueryBuilder with the specified source

Example

// Query from a collection
query.from({ users: usersCollection })

// Query from a subquery
const activeUsers = query.from({ u: usersCollection }).where(({u}) => u.active)
query.from({ activeUsers })

fullJoin()

fullJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "full">>;

Defined in: packages/db/src/query/builder/index.ts:478

Perform a FULL JOIN with another table or subquery

Type Parameters

TSource

TSource extends Source

Parameters

source

TSource

An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery

onCallback

JoinOnCallback<MergeContextForJoinCallback<TContext, { [K in string | number | symbol]: { [K in string | number | symbol]: TSource[K] extends CollectionImpl<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends CollectionOptionsIdentity<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends QueryBuilder<TContext> ? ResultValue<TContext> : never }[K] }>>

A function that receives table references and returns the join condition

Returns

QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "full">>

A QueryBuilder with the full joined table available

Example

// Full join users with posts
query
  .from({ users: usersCollection })
  .fullJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

groupBy()

groupBy(callback): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:792

Group rows by one or more columns for aggregation

Parameters

callback

GroupByCallback<TContext>

A function that receives table references and returns the field(s) to group by

Returns

QueryBuilder<TContext>

A QueryBuilder with grouping applied (enables aggregate functions in SELECT and HAVING)

Example

// Group by a single column
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .select(({posts, count}) => ({
    userId: posts.userId,
    postCount: count()
  }))

// Group by multiple columns
query
  .from({ sales: salesCollection })
  .groupBy(({sales}) => [sales.region, sales.category])
  .select(({sales, sum}) => ({
    region: sales.region,
    category: sales.category,
    totalSales: sum(sales.amount)
  }))

having()

having(callback): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:575

Filter grouped rows based on aggregate conditions

Parameters

callback

WhereCallback<TContext>

A function that receives table references and returns an expression

Returns

QueryBuilder<TContext>

A QueryBuilder with the having condition applied

Example

// Filter groups by count
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .having(({posts}) => gt(count(posts.id), 5))

// Filter by average
query
  .from({ orders: ordersCollection })
  .groupBy(({orders}) => orders.customerId)
  .having(({orders}) => gt(avg(orders.total), 100))

// Multiple having calls are ANDed together
query
  .from({ orders: ordersCollection })
  .groupBy(({orders}) => orders.customerId)
  .having(({orders}) => gt(count(orders.id), 5))
  .having(({orders}) => gt(avg(orders.total), 100))

innerJoin()

innerJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "inner">>;

Defined in: packages/db/src/query/builder/index.ts:452

Perform an INNER JOIN with another table or subquery

Type Parameters

TSource

TSource extends Source

Parameters

source

TSource

An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery

onCallback

JoinOnCallback<MergeContextForJoinCallback<TContext, { [K in string | number | symbol]: { [K in string | number | symbol]: TSource[K] extends CollectionImpl<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends CollectionOptionsIdentity<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends QueryBuilder<TContext> ? ResultValue<TContext> : never }[K] }>>

A function that receives table references and returns the join condition

Returns

QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "inner">>

A QueryBuilder with the inner joined table available

Example

// Inner join users with posts
query
  .from({ users: usersCollection })
  .innerJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

join()

join<TSource, TJoinType>(
   source, 
   onCallback, 
type): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, TJoinType>>;

Defined in: packages/db/src/query/builder/index.ts:347

Join another table or subquery to the current query

Type Parameters

TSource

TSource extends Source

TJoinType

TJoinType extends "inner" | "left" | "right" | "full" = "left"

Parameters

source

TSource

An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery

onCallback

JoinOnCallback<MergeContextForJoinCallback<TContext, { [K in string | number | symbol]: { [K in string | number | symbol]: TSource[K] extends CollectionImpl<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends CollectionOptionsIdentity<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends QueryBuilder<TContext> ? ResultValue<TContext> : never }[K] }>>

A function that receives table references and returns the join condition

type

TJoinType = ...

The type of join: 'inner', 'left', 'right', or 'full' (defaults to 'left')

Returns

QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, TJoinType>>

A QueryBuilder with the joined table available

Example

// Left join users with posts
query
  .from({ users: usersCollection })
  .join({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

// Inner join with explicit type
query
  .from({ u: usersCollection })
  .join({ p: postsCollection }, ({u, p}) => eq(u.id, p.userId), 'inner')

// Join with a subquery const activeUsers = query.from({ u: usersCollection }).where(({u}) => u.active) query .from({ activeUsers }) .join({ p: postsCollection }, ({u, p}) => eq(u.id, p.userId))

leftJoin()

leftJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "left">>;

Defined in: packages/db/src/query/builder/index.ts:400

Perform a LEFT JOIN with another table or subquery

Type Parameters

TSource

TSource extends Source

Parameters

source

TSource

An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery

onCallback

JoinOnCallback<MergeContextForJoinCallback<TContext, { [K in string | number | symbol]: { [K in string | number | symbol]: TSource[K] extends CollectionImpl<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends CollectionOptionsIdentity<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends QueryBuilder<TContext> ? ResultValue<TContext> : never }[K] }>>

A function that receives table references and returns the join condition

Returns

QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "left">>

A QueryBuilder with the left joined table available

Example

// Left join users with posts
query
  .from({ users: usersCollection })
  .leftJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

limit()

limit(count): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:828

Limit the number of rows returned by the query orderBy is required for limit

Parameters

count

number

Maximum number of rows to return

Returns

QueryBuilder<TContext>

A QueryBuilder with the limit applied

Example

// Get top 5 posts by likes
query
  .from({ posts: postsCollection })
  .orderBy(({posts}) => posts.likes, 'desc')
  .limit(5)

offset()

offset(count): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:852

Skip a number of rows before returning results orderBy is required for offset

Parameters

count

number

Number of rows to skip

Returns

QueryBuilder<TContext>

A QueryBuilder with the offset applied

Example

// Get second page of results
query
  .from({ posts: postsCollection })
  .orderBy(({posts}) => posts.createdAt, 'desc')
  .offset(page * pageSize)
  .limit(pageSize)

orderBy()

orderBy(callback, options): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:707

Sort the query results by one or more columns

Parameters

callback

OrderByCallback<TContext>

A function that receives table references and returns the field to sort by

options

OrderByDirection | OrderByOptions

Returns

QueryBuilder<TContext>

A QueryBuilder with the ordering applied

Example

// Sort by a single column
query
  .from({ users: usersCollection })
  .orderBy(({users}) => users.name)

// Sort descending
query
  .from({ users: usersCollection })
  .orderBy(({users}) => users.createdAt, 'desc')

// Multiple sorts (chain orderBy calls)
query
  .from({ users: usersCollection })
  .orderBy(({users}) => users.lastName)
  .orderBy(({users}) => users.firstName)

rightJoin()

rightJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "right">>;

Defined in: packages/db/src/query/builder/index.ts:426

Perform a RIGHT JOIN with another table or subquery

Type Parameters

TSource

TSource extends Source

Parameters

source

TSource

An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery

onCallback

JoinOnCallback<MergeContextForJoinCallback<TContext, { [K in string | number | symbol]: { [K in string | number | symbol]: TSource[K] extends CollectionImpl<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends CollectionOptionsIdentity<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends QueryBuilder<TContext> ? ResultValue<TContext> : never }[K] }>>

A function that receives table references and returns the join condition

Returns

QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "right">>

A QueryBuilder with the right joined table available

Example

// Right join users with posts
query
  .from({ users: usersCollection })
  .rightJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

select()

Call Signature

select<TSelectObject>(callback): QueryBuilder<WithResult<TContext, ResultTypeFromSelect<TSelectObject>>>;

Defined in: packages/db/src/query/builder/index.ts:641

Select specific columns or computed values from the query

Type Parameters

TSelectObject

TSelectObject extends SelectShape

Parameters

callback

(refs) => NonScalarSelectObject<TSelectObject>

A function that receives table references and returns an object with selected fields or expressions

Returns

QueryBuilder<WithResult<TContext, ResultTypeFromSelect<TSelectObject>>>

A QueryBuilder that returns only the selected fields

Example

// Select specific columns
query
  .from({ users: usersCollection })
  .select(({users}) => ({
    name: users.name,
    email: users.email
  }))

// Select with computed values
query
  .from({ users: usersCollection })
  .select(({users}) => ({
    fullName: concat(users.firstName, ' ', users.lastName),
    ageInMonths: mul(users.age, 12)
  }))

// Select with aggregates (requires GROUP BY)
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .select(({posts, count}) => ({
    userId: posts.userId,
    postCount: count(posts.id)
  }))

Call Signature

select<TSelectValue>(callback): QueryBuilder<WithResult<TContext, ResultTypeFromSelectValue<TSelectValue>>>;

Defined in: packages/db/src/query/builder/index.ts:646

Select specific columns or computed values from the query

Type Parameters

TSelectValue

TSelectValue extends ScalarSelectValue

Parameters

callback

(refs) => TSelectValue

A function that receives table references and returns an object with selected fields or expressions

Returns

QueryBuilder<WithResult<TContext, ResultTypeFromSelectValue<TSelectValue>>>

A QueryBuilder that returns only the selected fields

Example

// Select specific columns
query
  .from({ users: usersCollection })
  .select(({users}) => ({
    name: users.name,
    email: users.email
  }))

// Select with computed values
query
  .from({ users: usersCollection })
  .select(({users}) => ({
    fullName: concat(users.firstName, ' ', users.lastName),
    ageInMonths: mul(users.age, 12)
  }))

// Select with aggregates (requires GROUP BY)
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .select(({posts, count}) => ({
    userId: posts.userId,
    postCount: count(posts.id)
  }))

unionAll()

Call Signature

unionAll<TSource>(source): QueryBuilder<ContextFromUnionSource<TSource>>;

Defined in: packages/db/src/query/builder/index.ts:286

Union multiple independent source streams in one query.

Type Parameters

TSource

TSource extends Source

Parameters

source

TSource

An object with one or more aliases mapped to collections or subqueries

Returns

QueryBuilder<ContextFromUnionSource<TSource>>

A QueryBuilder with the unioned sources available

Example

query
  .unionAll({ message: messagesCollection, toolCall: toolCallsCollection })
  .orderBy(({ message, toolCall }) =>
    coalesce(message.timestamp, toolCall.timestamp)
  )

Call Signature

unionAll<TBranches>(...branches): QueryBuilder<ContextFromUnionBranches<TBranches>>;

Defined in: packages/db/src/query/builder/index.ts:289

Union multiple independent source streams in one query.

Type Parameters

TBranches

TBranches extends readonly [QueryBuilder<any>, QueryBuilder<any>]

Parameters

branches

...TBranches

Returns

QueryBuilder<ContextFromUnionBranches<TBranches>>

A QueryBuilder with the unioned sources available

Example

query
  .unionAll({ message: messagesCollection, toolCall: toolCallsCollection })
  .orderBy(({ message, toolCall }) =>
    coalesce(message.timestamp, toolCall.timestamp)
  )

where()

where(callback): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:517

Filter rows based on a condition

Parameters

callback

WhereCallback<TContext>

A function that receives table references and returns an expression

Returns

QueryBuilder<TContext>

A QueryBuilder with the where condition applied

Example

// Simple condition
query
  .from({ users: usersCollection })
  .where(({users}) => gt(users.age, 18))

// Multiple conditions
query
  .from({ users: usersCollection })
  .where(({users}) => and(
    gt(users.age, 18),
    eq(users.active, true)
  ))

// Multiple where calls are ANDed together
query
  .from({ users: usersCollection })
  .where(({users}) => gt(users.age, 18))
  .where(({users}) => eq(users.active, true))