Defined in: packages/db/src/query/builder/index.ts:46
TContext extends Context = Context
new BaseQueryBuilder<TContext>(query): BaseQueryBuilder<TContext>;
new BaseQueryBuilder<TContext>(query): BaseQueryBuilder<TContext>;
Defined in: packages/db/src/query/builder/index.ts:49
Partial<QueryIR> = {}
BaseQueryBuilder<TContext>
get fn(): object;
get fn(): object;
Defined in: packages/db/src/query/builder/index.ts:672
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.
q.fn.select((row) => ({
name: row.user.name.toUpperCase(),
age: row.user.age + 1,
}))
q.fn.select((row) => ({
name: row.user.name.toUpperCase(),
age: row.user.age + 1,
}))
having(callback): QueryBuilder<TContext>;
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
(row) => any
A function that receives an aggregated row and returns a boolean
QueryBuilder<TContext>
A QueryBuilder with functional having filter applied
// Functional having (not optimized)
query
.from({ posts: postsCollection })
.groupBy(({posts}) => posts.userId)
.fn.having(row => row.count > 5)
// Functional having (not optimized)
query
.from({ posts: postsCollection })
.groupBy(({posts}) => posts.userId)
.fn.having(row => row.count > 5)
select<TFuncSelectResult>(callback): QueryBuilder<WithResult<TContext, TFuncSelectResult>>;
select<TFuncSelectResult>(callback): QueryBuilder<WithResult<TContext, TFuncSelectResult>>;
Select fields using a function that operates on each row Warning: This cannot be optimized by the query compiler
TFuncSelectResult
(row) => TFuncSelectResult
A function that receives a row and returns the selected value
QueryBuilder<WithResult<TContext, TFuncSelectResult>>
A QueryBuilder with functional selection applied
// Functional select (not optimized)
query
.from({ users: usersCollection })
.fn.select(row => ({
name: row.users.name.toUpperCase(),
age: row.users.age + 1,
}))
// Functional select (not optimized)
query
.from({ users: usersCollection })
.fn.select(row => ({
name: row.users.name.toUpperCase(),
age: row.users.age + 1,
}))
where(callback): QueryBuilder<TContext>;
where(callback): QueryBuilder<TContext>;
Filter rows using a function that operates on each row Warning: This cannot be optimized by the query compiler
(row) => any
A function that receives a row and returns a boolean
QueryBuilder<TContext>
A QueryBuilder with functional filtering applied
// Functional where (not optimized)
query
.from({ users: usersCollection })
.fn.where(row => row.users.name.startsWith('A'))
// Functional where (not optimized)
query
.from({ users: usersCollection })
.fn.where(row => row.users.name.startsWith('A'))
_getQuery(): QueryIR;
_getQuery(): QueryIR;
Defined in: packages/db/src/query/builder/index.ts:758
distinct(): QueryBuilder<TContext>;
distinct(): QueryBuilder<TContext>;
Defined in: packages/db/src/query/builder/index.ts:611
Specify that the query should return distinct rows. Deduplicates rows based on the selected columns.
QueryBuilder<TContext>
A QueryBuilder with distinct enabled
// Get countries our users are from
query
.from({ users: usersCollection })
.select(({users}) => users.country)
.distinct()
// Get countries our users are from
query
.from({ users: usersCollection })
.select(({users}) => users.country)
.distinct()
findOne(): QueryBuilder<TContext & SingleResult>;
findOne(): QueryBuilder<TContext & SingleResult>;
Defined in: packages/db/src/query/builder/index.ts:631
Specify that the query should return a single result
QueryBuilder<TContext & SingleResult>
A QueryBuilder that returns the first result
// Get the user matching the query
query
.from({ users: usersCollection })
.where(({users}) => eq(users.id, 1))
.findOne()
// Get the user matching the query
query
.from({ users: usersCollection })
.where(({users}) => eq(users.id, 1))
.findOne()
from<TSource>(source): QueryBuilder<{
baseSchema: SchemaFromSource<TSource>;
fromSourceName: keyof TSource & string;
hasJoins: false;
schema: SchemaFromSource<TSource>;
}>;
from<TSource>(source): QueryBuilder<{
baseSchema: SchemaFromSource<TSource>;
fromSourceName: keyof TSource & string;
hasJoins: false;
schema: SchemaFromSource<TSource>;
}>;
Defined in: packages/db/src/query/builder/index.ts:103
Specify the source table or subquery for the query
TSource extends 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
QueryBuilder<{ baseSchema: SchemaFromSource<TSource>; fromSourceName: keyof TSource & string; hasJoins: false; schema: SchemaFromSource<TSource>; }>
A QueryBuilder with the specified source
// 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 })
// 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<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "full">>;
fullJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "full">>;
Defined in: packages/db/src/query/builder/index.ts:294
Perform a FULL JOIN with another table or subquery
TSource extends 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
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 QueryBuilder<TContext> ? { [K in string | number | symbol]: ((...)[(...)] extends object ? any[any] : (...) extends (...) ? (...) : (...))[K] } : never }[K] }>>
A function that receives table references and returns the join condition
QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "full">>
A QueryBuilder with the full joined table available
// Full join users with posts
query
.from({ users: usersCollection })
.fullJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))
// Full join users with posts
query
.from({ users: usersCollection })
.fullJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))
groupBy(callback): QueryBuilder<TContext>;
groupBy(callback): QueryBuilder<TContext>;
Defined in: packages/db/src/query/builder/index.ts:533
Group rows by one or more columns for aggregation
GroupByCallback<TContext>
A function that receives table references and returns the field(s) to group by
QueryBuilder<TContext>
A QueryBuilder with grouping applied (enables aggregate functions in SELECT and HAVING)
// 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)
}))
// 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(callback): QueryBuilder<TContext>;
having(callback): QueryBuilder<TContext>;
Defined in: packages/db/src/query/builder/index.ts:374
Filter grouped rows based on aggregate conditions
WhereCallback<TContext>
A function that receives table references and returns an expression
QueryBuilder<TContext>
A QueryBuilder with the having condition applied
// 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))
// 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<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "inner">>;
innerJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "inner">>;
Defined in: packages/db/src/query/builder/index.ts:268
Perform an INNER JOIN with another table or subquery
TSource extends 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
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 QueryBuilder<TContext> ? { [K in string | number | symbol]: ((...)[(...)] extends object ? any[any] : (...) extends (...) ? (...) : (...))[K] } : never }[K] }>>
A function that receives table references and returns the join condition
QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "inner">>
A QueryBuilder with the inner joined table available
// Inner join users with posts
query
.from({ users: usersCollection })
.innerJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))
// Inner join users with posts
query
.from({ users: usersCollection })
.innerJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))
join<TSource, TJoinType>(
source,
onCallback,
type): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, TJoinType>>;
join<TSource, TJoinType>(
source,
onCallback,
type): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, TJoinType>>;
Defined in: packages/db/src/query/builder/index.ts:146
Join another table or subquery to the current query
TSource extends Source
TJoinType extends "inner" | "left" | "right" | "full" = "left"
TSource
An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery
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 QueryBuilder<TContext> ? { [K in string | number | symbol]: ((...)[(...)] extends object ? any[any] : (...) extends (...) ? (...) : (...))[K] } : never }[K] }>>
A function that receives table references and returns the join condition
TJoinType = ...
The type of join: 'inner', 'left', 'right', or 'full' (defaults to 'left')
QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, TJoinType>>
A QueryBuilder with the joined table available
// 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')
// 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<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "left">>;
leftJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "left">>;
Defined in: packages/db/src/query/builder/index.ts:216
Perform a LEFT JOIN with another table or subquery
TSource extends 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
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 QueryBuilder<TContext> ? { [K in string | number | symbol]: ((...)[(...)] extends object ? any[any] : (...) extends (...) ? (...) : (...))[K] } : never }[K] }>>
A function that receives table references and returns the join condition
QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "left">>
A QueryBuilder with the left joined table available
// Left join users with posts
query
.from({ users: usersCollection })
.leftJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))
// Left join users with posts
query
.from({ users: usersCollection })
.leftJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))
limit(count): QueryBuilder<TContext>;
limit(count): QueryBuilder<TContext>;
Defined in: packages/db/src/query/builder/index.ts:566
Limit the number of rows returned by the query orderBy is required for limit
number
Maximum number of rows to return
QueryBuilder<TContext>
A QueryBuilder with the limit applied
// Get top 5 posts by likes
query
.from({ posts: postsCollection })
.orderBy(({posts}) => posts.likes, 'desc')
.limit(5)
// Get top 5 posts by likes
query
.from({ posts: postsCollection })
.orderBy(({posts}) => posts.likes, 'desc')
.limit(5)
offset(count): QueryBuilder<TContext>;
offset(count): QueryBuilder<TContext>;
Defined in: packages/db/src/query/builder/index.ts:590
Skip a number of rows before returning results orderBy is required for offset
number
Number of rows to skip
QueryBuilder<TContext>
A QueryBuilder with the offset applied
// Get second page of results
query
.from({ posts: postsCollection })
.orderBy(({posts}) => posts.createdAt, 'desc')
.offset(page * pageSize)
.limit(pageSize)
// Get second page of results
query
.from({ posts: postsCollection })
.orderBy(({posts}) => posts.createdAt, 'desc')
.offset(page * pageSize)
.limit(pageSize)
orderBy(callback, options): QueryBuilder<TContext>;
orderBy(callback, options): QueryBuilder<TContext>;
Defined in: packages/db/src/query/builder/index.ts:462
Sort the query results by one or more columns
OrderByCallback<TContext>
A function that receives table references and returns the field to sort by
OrderByDirection | OrderByOptions
QueryBuilder<TContext>
A QueryBuilder with the ordering applied
// 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)
// 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<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "right">>;
rightJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "right">>;
Defined in: packages/db/src/query/builder/index.ts:242
Perform a RIGHT JOIN with another table or subquery
TSource extends 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
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 QueryBuilder<TContext> ? { [K in string | number | symbol]: ((...)[(...)] extends object ? any[any] : (...) extends (...) ? (...) : (...))[K] } : never }[K] }>>
A function that receives table references and returns the join condition
QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "right">>
A QueryBuilder with the right joined table available
// Right join users with posts
query
.from({ users: usersCollection })
.rightJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))
// Right join users with posts
query
.from({ users: usersCollection })
.rightJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))
select<TSelectObject>(callback): QueryBuilder<WithResult<TContext, ResultTypeFromSelect<TSelectObject>>>;
select<TSelectObject>(callback): QueryBuilder<WithResult<TContext, ResultTypeFromSelect<TSelectObject>>>;
Defined in: packages/db/src/query/builder/index.ts:421
Select specific columns or computed values from the query
TSelectObject extends SelectShape
(refs) => TSelectObject
A function that receives table references and returns an object with selected fields or expressions
QueryBuilder<WithResult<TContext, ResultTypeFromSelect<TSelectObject>>>
A QueryBuilder that returns only the selected fields
// 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)
}))
// 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)
}))
where(callback): QueryBuilder<TContext>;
where(callback): QueryBuilder<TContext>;
Defined in: packages/db/src/query/builder/index.ts:333
Filter rows based on a condition
WhereCallback<TContext>
A function that receives table references and returns an expression
QueryBuilder<TContext>
A QueryBuilder with the where condition applied
// 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))
// 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))