Aggregations not supported in extras parameter
As of now, aggregations are not supported in the 'extras' parameter of relational queries. To use aggregations, you must use core queries instead.
query-builder
273 notes in this subject, read out of this brain and free to use. This is page 2 of 5.
Type inference for nested relational queries
For nested 'with' queries in relational queries, Drizzle automatically infers types using the Core Type API, ensuring type safety throughout nested relation structures.
Placeholder in relational query limit parameter
Example of using a placeholder in the limit parameter of a relational query: const prepared = db._query.users.findMany({ with: { posts: { limit: placeholder('limit') } } }).prepare('query_name'); const usersWithPosts = await prepared.execute({ limit: 1 });
Placeholder in relational query where clause
Example of using a placeholder in the where clause of a relational query: const prepared = db._query.users.findMany({ where: (users, { eq }) => eq(users.id, placeholder('id')), with: { posts: { where: (users, { eq }) => eq(users.id, placeholder('pid')) } } }).prepare('query_name'); const usersWithPosts = await prepared.execute({ id: 1, pid: 1 });
Select filters in relational queries
Relational queries support filters and conditions using operators like eq, lt, and others, the same as the SQL-like query builder. You can import operators from 'drizzle-orm' or use callback syntax: where: (users, { eq }) => eq(users.id, 1). Filters can be applied to nested relations as well.
Partial field selection behavior with mixed true and false
When both true and false select options are present in the columns parameter, all false options are ignored. If you include any field with true, all fields not explicitly set to true are excluded. For example, columns: { name: true, id: false } results in only the name field being selected because id: false is redundant.
Relational query API entry point
After initializing drizzle with a schema, relational queries are accessed via the db._query API. For example: await db._query.users.findMany() or await db._query.posts.findFirst().
Example relational query with custom fields using sql
Example showing how to add custom computed fields using sql in extras: const res = await db._query.posts.findMany({ extras: (table, { sql }) => ({ contentLength: sql<number>`length(${table.content})`.as('content_length') }), with: { comments: { extras: { commentSize: sql<number>`length(${comments.content})`.as('comment_size') } } } }); This retrieves posts and comments with additional calculated fields for content length.
Placeholder in relational query offset parameter
Example of using a placeholder in the offset parameter of a relational query: const prepared = db._query.users.findMany({ offset: placeholder('offset'), with: { posts: true } }).prepare('query_name'); const usersWithPosts = await prepared.execute({ offset: 1 });
Raw parametrized SQL queries with db.execute
Use `db.execute()` with a sql template tag to run raw parametrized SQL queries: `const statement = sql\`select * from \${users} where \${users.id} = \${userId}\`; const result = await db.execute(statement);`
Print generated SQL from query with toSQL()
Call `.toSQL()` on a query to get the generated SQL without executing it. For example: `const query = db.select({ id: users.id, name: users.name }).from(users).groupBy(users.id).toSQL();`
Standalone CockroachDB query builder without database instance
Use the QueryBuilder class from 'drizzle-orm/cockroach-core' to build queries without a database connection: `import { QueryBuilder } from 'drizzle-orm/cockroach-core'; const qb = new QueryBuilder(); const query = qb.select().from(users).where(eq(users.name, 'Dan')); const { sql, params } = query.toSQL();`
Example: EXCEPT with import-pattern and builder-pattern
Import-pattern example: ```typescript import { except } from 'drizzle-orm/cockroach-core' import { depA, depB } from './schema' const departmentACourses = db.select({ courseName: depA.projectsName }).from(depA); const departmentBCourses = db.select({ courseName: depB.projectsName }).from(depB); const result = await except(departmentACourses, departmentBCourses); ``` Builder-pattern example: ```typescript import { depA, depB } from './schema' const result = await db .select({ courseName: depA.projectsName }) .from(depA) .except(db.select({ courseName: depB.projectsName }).from(depB)); ```
Example: INTERSECT with import-pattern and builder-pattern
Import-pattern example: ```typescript import { intersect } from 'drizzle-orm/cockroach-core' import { depA, depB } from './schema' const departmentACourses = db.select({ courseName: depA.courseName }).from(depA); const departmentBCourses = db.select({ courseName: depB.courseName }).from(depB); const result = await intersect(departmentACourses, departmentBCourses); ``` Builder-pattern example: ```typescript import { depA, depB } from './schema' const result = await db .select({ courseName: depA.courseName }) .from(depA) .intersect(db.select({ courseName: depB.courseName }).from(depB)); ```
Example: UNION with import-pattern and builder-pattern
Import-pattern example: ```typescript import { union } from 'drizzle-orm/cockroach-core' import { users, customers } from './schema' const allNamesForUserQuery = db.select({ name: users.name }).from(users); const result = await union( allNamesForUserQuery, db.select({ name: customers.name }).from(customers) ).limit(10); ``` Builder-pattern example: ```typescript import { users, customers } from './schema' const result = await db .select({ name: users.name }) .from(users) .union(db.select({ name: customers.name }).from(customers)) .limit(10); ```
INTERSECT ALL returns common rows from two query blocks with duplicates
The INTERSECT ALL set operation combines only those rows which the results of two query blocks have in common, retaining duplicate rows. It can be used with the import-pattern via the intersectAll() function or with the builder-pattern via the .intersectAll() method.
UNION ALL combines results from two query blocks with duplicates
The UNION ALL set operation combines all results from two query blocks into a single result, retaining duplicate rows. It can be used with the import-pattern via the unionAll() function or with the builder-pattern via the .unionAll() method.
EXCEPT ALL returns rows from first query not in second query with duplicates
The EXCEPT ALL set operation returns all results from the first query block which are not also present in the second query block, retaining duplicate rows. It can be used with the import-pattern via the exceptAll() function or with the builder-pattern via the .exceptAll() method.
Example: INTERSECT ALL with import-pattern and builder-pattern
Import-pattern example: ```typescript import { intersectAll } from 'drizzle-orm/cockroach-core' import { regularCustomerOrders, vipCustomerOrders } from './schema' const regularOrders = db.select({ productId: regularCustomerOrders.productId, quantityOrdered: regularCustomerOrders.quantityOrdered } ).from(regularCustomerOrders); const vipOrders = db.select({ productId: vipCustomerOrders.productId, quantityOrdered: vipCustomerOrders.quantityOrdered } ).from(vipCustomerOrders); const result = await intersectAll(regularOrders, vipOrders); ``` Builder-pattern example: ```typescript import { regularCustomerOrders, vipCustomerOrders } from './schema' const result = await db .select({ productId: regularCustomerOrders.productId, quantityOrdered: regularCustomerOrders.quantityOrdered, }) .from(regularCustomerOrders) .intersectAll( db .select({ productId: vipCustomerOrders.productId, quantityOrdered: vipCustomerOrders.quantityOrdered, }) .from(vipCustomerOrders) ); ```
Example: UNION ALL with import-pattern and builder-pattern
Import-pattern example: ```typescript import { unionAll } from 'drizzle-orm/cockroach-core' import { onlineSales, inStoreSales } from './schema' const onlineTransactions = db.select({ transaction: onlineSales.transactionId }).from(onlineSales); const inStoreTransactions = db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales); const result = await unionAll(onlineTransactions, inStoreTransactions); ``` Builder-pattern example: ```typescript import { onlineSales, inStoreSales } from './schema' const result = await db .select({ transaction: onlineSales.transactionId }) .from(onlineSales) .unionAll( db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales) ); ```
Example: EXCEPT ALL with import-pattern and builder-pattern
Import-pattern example: ```typescript import { exceptAll } from 'drizzle-orm/cockroach-core' import { regularCustomerOrders, vipCustomerOrders } from './schema' const regularOrders = db.select({ productId: regularCustomerOrders.productId, quantityOrdered: regularCustomerOrders.quantityOrdered } ).from(regularCustomerOrders); const vipOrders = db.select({ productId: vipCustomerOrders.productId, quantityOrdered: vipCustomerOrders.quantityOrdered } ).from(vipCustomerOrders); const result = await exceptAll(regularOrders, vipOrders); ``` Builder-pattern example: ```typescript import { regularCustomerOrders, vipCustomerOrders } from './schema' const result = await db .select({ productId: regularCustomerOrders.productId, quantityOrdered: regularCustomerOrders.quantityOrdered, }) .from(regularCustomerOrders) .exceptAll( db .select({ productId: vipCustomerOrders.productId, quantityOrdered: vipCustomerOrders.quantityOrdered, }) .from(vipCustomerOrders) ); ```
Cursor-based pagination pattern
Use a cursor column (usually id) with a comparison operator to implement cursor pagination. Example: `db.select().from(users).where(cursor ? gt(users.id, cursor) : undefined).limit(pageSize).orderBy(asc(users.id))`. Pass the cursor of the last row from the previous page.
Custom filter operators with sql function
Create custom filter operators using the `sql` function. Example: `function equals42(col: CockroachColumn) { return sql`${col} = 42`; }` then use `db.select().from(users).where(equals42(users.id))`.
WITH clause for common table expressions
Use `db.$with('name').as(query)` to create a CTE, then reference it with `db.with(cte).select().from(cte)`. CTEs help simplify complex queries by splitting them into smaller subqueries.
avgDistinct aggregation function
Use `avgDistinct(column)` from 'drizzle-orm' to calculate the average of distinct non-null values. Returns a string type. Example: `db.select({ value: avgDistinct(users.id) }).from(users)`
getColumns helper for advanced selection
Use `getColumns(table)` from 'drizzle-orm' to get all columns of a table as an object. You can spread it into a select object to include all columns, or destructure and exclude specific columns. Example: `const { content, ...rest } = getColumns(posts); await db.select({ ...rest }).from(posts);`
selectDistinct for unique rows
Use `.selectDistinct()` instead of `.select()` to retrieve only unique rows from a dataset.
Limit offset pagination pattern
Combine `.orderBy()`, `.limit()`, and `.offset()` for pagination. Example: `db.select().from(users).orderBy(asc(users.id)).limit(pageSize).offset((page - 1) * pageSize)`. Order by is mandatory for consistent pagination.
Partial select with column mapping
Use `.select({ fieldName: table.column }).from(table)` to select only specific columns. You can rename columns in the result object. You can also use arbitrary SQL expressions as selection fields with `sql<Type>` template tags.
WITH clause with insert statement
Create a CTE with an insert statement: `db.$with('sq').as(db.insert(users).values({ name: 'John' }).returning())`. Then use it in a select: `db.with(sq).select().from(sq)`
sum aggregation function
Use `sum(column)` from 'drizzle-orm' to calculate the sum of non-null values. Returns a string type. Example: `db.select({ value: sum(users.id) }).from(users)`
selectDistinctOn for CockroachDB specific uniqueness
In CockroachDB, use `.selectDistinctOn([columns])` to specify which columns determine uniqueness. Example: `db.selectDistinctOn([users.id]).from(users).orderBy(users.id)` selects rows distinct on the id column.
offset method for pagination
Use `.offset(n)` to skip the first n rows. Example: `db.select().from(users).limit(10).offset(10)` returns rows 11-20.
orderBy for sorting results
Use `.orderBy()` to sort results. Pass column(s) directly for ascending order, or wrap with `asc()` or `desc()` for explicit direction. Example: `db.select().from(users).orderBy(users.name)` or `db.select().from(users).orderBy(desc(users.name))`
avg aggregation function
Use `avg(column)` from 'drizzle-orm' to calculate the average of non-null values. Returns a string type (use `.mapWith(Number)` to convert to number at runtime). Example: `db.select({ value: avg(users.id) }).from(users)`
orderBy multiple columns with mixed directions
Pass multiple columns to `.orderBy()` with different directions using `asc()` and `desc()`. Example: `db.select().from(users).orderBy(asc(users.name), desc(users.name2))`
WITH clause with update statement
Create a CTE with an update statement: `db.$with('sq').as(db.update(users).set({ age: 25 }).where(eq(users.name, 'John')).returning())`. Then use it in a select: `db.with(sq).select().from(sq)`
sumDistinct aggregation function
Use `sumDistinct(column)` from 'drizzle-orm' to calculate the sum of distinct non-null values. Returns a string type. Example: `db.select({ value: sumDistinct(users.id) }).from(users)`
groupBy for aggregations
Use `.groupBy(column)` to group rows for aggregation functions. Example: `db.select({ age: users.age, count: sql<number>\`cast(count(${users.id}) as int)\` }).from(users).groupBy(users.age)`
Conditional filtering with undefined
Pass `undefined` to a `.where()` clause to conditionally apply filters. Example: `db.select().from(posts).where(term ? ilike(posts.title, term) : undefined)` applies the filter only if term is truthy.
having clause for filtering groups
Use `.having()` to filter groups after aggregation. Pass a function that receives the aggregated fields. Example: `.having(({ count }) => gt(count, 1))` filters groups where count is greater than 1.
Array of filters with and operator
Collect filters in an array and pass them to `and()` to build dynamic filter combinations. Example: `const filters: SQL[] = []; filters.push(ilike(...)); filters.push(inArray(...)); db.select().from(posts).where(and(...filters))`
Subqueries in joins
Subqueries can be used anywhere a table can be used, including in joins. Example: `const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); db.select().from(users).leftJoin(sq, eq(users.id, sq.id))`
count aggregation function
Use `count()` from 'drizzle-orm' to count rows. `count()` counts all rows, `count(column)` counts non-null values in the column. Returns a number. Example: `db.select({ value: count() }).from(users)` or `db.select({ value: count(users.id) }).from(users)`
min aggregation function
Use `min(column)` from 'drizzle-orm' to find the minimum value. Example: `db.select({ value: min(users.id) }).from(users)`
WITH clause with delete statement
Create a CTE with a delete statement: `db.$with('sq').as(db.delete(users).where(eq(users.name, 'John')).returning())`. Then use it in a select: `db.with(sq).select().from(sq)`
CTE fields must have aliases
When selecting arbitrary SQL values in a CTE, you must add aliases with `.as('name')`. Example: `sql<string>\`upper(${users.name})\`.as('name')`. Without aliases, the field type becomes DrizzleTypeError and cannot be referenced.
countDistinct aggregation function
Use `countDistinct(column)` from 'drizzle-orm' to count unique non-null values. Example: `db.select({ value: countDistinct(users.id) }).from(users)`
limit method for result count
Use `.limit(n)` to limit the number of rows returned. Example: `db.select().from(users).limit(10)`
Values in filters are automatically parameterized
All values provided to filter operators and the `sql` function are automatically parameterized. Example: `db.select().from(users).where(eq(users.id, 42))` becomes `select ... where "users"."id" = $1; -- params: [42]`.
Select from subquery
Convert a query to a subquery with `.as('name')` and select from it: `const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(sq);`
max aggregation function
Use `max(column)` from 'drizzle-orm' to find the maximum value. Example: `db.select({ value: max(users.id) }).from(users)`
Relational query API for column selection
Use the relational query API with `db.query.table.findMany()` and pass a `columns` object. Set `true` to include a column or `false` to exclude it. Example: `db.query.posts.findMany({ columns: { title: true } })` or `db.query.posts.findMany({ columns: { content: false } })`
Conditional select with spread operator
Use the spread operator with a ternary to conditionally include columns in the selection object: `{ id: users.id, ...(condition ? { name: users.name } : {}) }`.
update() with WITH clause (CTE)
The WITH clause can be used with UPDATE to create common table expressions (CTEs) that simplify complex queries. Define a CTE using `db.$with('name').as(subquery)`, then reference it in the update using `db.with(cte).update(table).set(...)`.
update() - object keys must match column names
The object passed to update() must have keys that match column names in the database schema. Values of undefined are ignored; to set a column to null, pass null explicitly.
update().from() clause in CockroachDB
CockroachDB supports UPDATE...FROM syntax to allow columns from other tables to appear in WHERE conditions and update expressions. Use `.from(table)` after .set() to join another table: `await db.update(users).set({ cityId: cities.id }).from(cities).where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John')));`
update().set() with parameterized values
All values provided to .set() are parameterized automatically for SQL injection prevention. For example, the query `await db.update(users).set({ name: "Mr. Dan" }).where(eq(users.name, "Dan"));` translates to the SQL `update "users" set "name" = $1 where "users"."name" = $2;` with parameters ['Mr. Dan', 'Dan'].
update().returning() in CockroachDB
CockroachDB supports returning columns after an update operation using .returning(). The returning method accepts an object specifying which columns to return with aliases. For example: `await db.update(users).set({ name: "Mr. Dan" }).where(eq(users.name, "Dan")).returning({ updatedId: users.id });` returns an array of objects with the specified columns.
update().from() with table aliases
Tables can be aliased in UPDATE...FROM queries using the alias() function. Both the joined table and the table being updated can be aliased: `const c = alias(cities, 'c'); await db.update(users).set({ cityId: c.id }).from(c);`