pg-core/query-api

254 notes in this subject, read out of this brain and free to use. This is page 3 of 5.

Relational queries findMany and findFirst methods

Drizzle provides .findMany() and .findFirst() APIs for relational queries. findMany() returns an array of results. findFirst() returns a single result and adds LIMIT 1 to the query.

Include relations with 'with' operator in relational queries

The 'with' operator lets you combine data from multiple related tables and properly aggregate results. You can chain nested with statements as much as necessary. Drizzle will infer types using Core Type API.

Partial fields select with 'columns' parameter

The 'columns' parameter lets you include or omit columns from the database result. You can specify columns as true to include them or false to exclude them. Drizzle performs partial selects on the query level with no additional data transferred from the database. When both true and false select options are present, all false options are ignored.

Nested partial fields select in relational queries

Just like with partial select, you can include or exclude columns of nested relations using the columns parameter within the with clause.

Filter operators in relational queries where clause

Relational queries support the following filter operators: OR, AND, NOT, RAW (for custom SQL), relation filtering, column filtering with eq, ne, gt, gte, lt, lte, in, notIn, like, ilike, notLike, notIlike, isNull, isNotNull, arrayOverlaps, arrayContained, and arrayContains.

Relations filtering in relational queries

You can filter not only by the table you're querying but also by any table you include in the with clause. For example, you can get all users whose ID>10 and who have at least one post with content starting with 'M'. You can also filter to get users with posts only if the user has at least 1 post by specifying posts: true in the where clause.

Limit and offset in relational queries

Drizzle ORM provides limit and offset API for both the main query and nested entities within the with clause. offset can be used in both main queries and with tables.

Order by in relational queries

Drizzle provides API for ordering in the relational query builder. You can use the same ordering core API or use orderBy operator from the callback with no imports. When you use multiple orderBy statements in the same table, they will be included in the query in the same order in which you added them. You can use custom sql in orderBy statements.

Subqueries in relational queries using extras

You can use subqueries within Relational Queries using the extras parameter. For example, you can use db.$count() to get a count of related records for each row.

Prepared statements in relational queries

Prepared statements are designed to massively improve query performance. You can define placeholders using sql.placeholder('name') in where, limit, and offset clauses and execute prepared statements using the .prepare('query_name').execute({}) pattern.

Relational queries initialization with defineRelations

Relational queries are an extension to Drizzle's original query builder. You need to provide all tables and relations from your schema file/files upon drizzle() initialization and then use the db.query API. Relations are defined using defineRelations() which takes a schema object and a callback that defines one-to-many and one-to-one relationships.

findMany example with nested with and columns

Example: Get all users with posts. Each post should contain a list of comments: ```typescript const users = await db.query.users.findMany({ with: { posts: { with: { comments: true, }, }, }, }); ```

Partial select example excluding columns

Example: Get all posts without content: ```typescript const posts = await db.query.posts.findMany({ columns: { content: false, }, }); ```

Where clause with RAW SQL example

Example: Filter users with complex conditions using RAW SQL: ```ts const response = await db.query.users.findMany({ where: { AND: [ { OR: [ { RAW: (table) => sql`LOWER(${table.name}) LIKE 'john%'` }, { name: { ilike: "jane%" } }, ], }, { OR: [ { RAW: (table) => sql`${table.preferences}->>'theme' = 'dark'` }, { RAW: (table) => sql`${table.preferences}->>'theme' IS NULL` }, ], }, { RAW: (table) => sql`${table.age} BETWEEN 25 AND 35` }, ], }, }); ```

Order by with callback example

Example: Order posts by id ascending and nested comments by id descending: ```typescript await db.query.posts.findMany({ orderBy: (t) => sql`${t.id} asc`, with: { comments: { orderBy: (t, { desc }) => desc(t.id), }, }, }); ```

Extras with SQL function example

Example: Add custom field with SQL function: ```typescript await db.query.users.findMany({ extras: { loweredName: (table) => sql`lower(${table.name})`, }, }); ```

Subquery with db.$count example

Example: Get users with posts and total posts count for each user: ```ts import { posts } from './schema'; import { eq } from 'drizzle-orm'; await db.query.users.findMany({ with: { posts: true }, extras: { totalPostsCount: (table) => db.$count(posts, eq(posts.authorId, table.id)), } }); ```

Prepared statement with placeholder example

Example: Prepared statement with placeholder in where clause: ```ts const prepared = db.query.users.findMany({ where: { id: { eq: sql.placeholder("id") } }, with: { posts: { where: { id: 1 }, }, }, }).prepare("query_name"); const usersWithPosts = await prepared.execute({ id: 1 }); ```

Multiple placeholders in prepared statement example

Example: Prepared statement with multiple placeholders in where, limit, and offset: ```ts const prepared = db.query.users.findMany({ limit: sql.placeholder("uLimit"), offset: sql.placeholder("uOffset"), where: { OR: [{ id: { eq: sql.placeholder("id") } }, { id: 3 }], }, with: { posts: { where: { id: { eq: sql.placeholder("pid") } }, limit: sql.placeholder("pLimit"), }, }, }).prepare("query_name"); const usersWithPosts = await prepared.execute({ pLimit: 1, uLimit: 3, uOffset: 1, id: 2, pid: 6 }); ```

UNION ALL operation - import and builder patterns

UNION ALL combines all results from two query blocks into a single result, including duplicates. In import-pattern, use unionAll() from 'drizzle-orm/pg-core': const result = await unionAll(firstQuery, secondQuery). In builder-pattern, chain .unionAll(secondQuery) on a select query: db.select({...}).from(table1).unionAll(db.select({...}).from(table2)). Both patterns generate (select ...) union all (select ...) SQL.

INTERSECT ALL operation - import and builder patterns

INTERSECT ALL combines only rows which are common to both query blocks, including duplicates. In import-pattern, use intersectAll() from 'drizzle-orm/pg-core': const result = await intersectAll(firstQuery, secondQuery). In builder-pattern, chain .intersectAll(secondQuery) on a select query: db.select({...}).from(table1).intersectAll(db.select({...}).from(table2)). Both patterns generate (select ...) intersect all (select ...) SQL.

EXCEPT ALL operation - import and builder patterns

EXCEPT ALL returns all results from the first query block which are not present in the second query block, including duplicates. In import-pattern, use exceptAll() from 'drizzle-orm/pg-core': const result = await exceptAll(firstQuery, secondQuery). In builder-pattern, chain .exceptAll(secondQuery) on a select query: db.select({...}).from(table1).exceptAll(db.select({...}).from(table2)). Both patterns generate (select ...) except all (select ...) SQL.

Set operations support limit() method

Set operations like union() can be chained with .limit() to restrict the number of results returned: await union(firstQuery, secondQuery).limit(10) generates (select ...) union (select ...) limit $1.

UNION example - combining names from multiple tables

Example using builder-pattern to get all names from users and customers tables without duplicates: const result = await db .select({ name: users.name }) .from(users) .union(db.select({ name: customers.name }).from(customers)) .limit(10); This generates: (select "name" from "sellers") union (select "name" from "customers") limit $1

UNION ALL example - combining transaction IDs from online and in-store sales

Example using builder-pattern to combine transaction data from online and in-store sales with duplicates: const result = await db .select({ transaction: onlineSales.transactionId }) .from(onlineSales) .unionAll( db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales) ); This generates: (select "transaction_id" from "online_sales") union all (select "transaction_id" from "in_store_sales")

INTERSECT example - finding common courses between departments

Example using builder-pattern to find courses common to two departments without duplicates: const result = await db .select({ courseName: depA.courseName }) .from(depA) .intersect(db.select({ courseName: depB.courseName }).from(depB)); This generates: (select "course_name" from "department_a_courses") intersect (select "course_name" from "department_b_courses")

INTERSECT ALL example - finding common products ordered by regular and VIP customers

Example using builder-pattern to find products ordered by both regular and VIP customers, retaining quantity information: const result = await db .select({ productId: regularCustomerOrders.productId, quantityOrdered: regularCustomerOrders.quantityOrdered, }) .from(regularCustomerOrders) .intersectAll( db .select({ productId: vipCustomerOrders.productId, quantityOrdered: vipCustomerOrders.quantityOrdered, }) .from(vipCustomerOrders) ); This generates: (select "product_id", "quantity_ordered" from "regular_customer_orders") intersect all (select "product_id", "quantity_ordered" from "vip_customer_orders")

EXCEPT example - finding unique projects in one department

Example using builder-pattern to find projects unique to one department, excluding duplicates: const result = await db .select({ courseName: depA.projectsName }) .from(depA) .except(db.select({ courseName: depB.projectsName }).from(depB)); This generates: (select "projects_name" from "department_a_projects") except (select "projects_name" from "department_b_projects")

EXCEPT ALL example - finding products ordered only by regular customers

Example using builder-pattern to find products ordered exclusively by regular customers, retaining quantity information: const result = await db .select({ productId: regularCustomerOrders.productId, quantityOrdered: regularCustomerOrders.quantityOrdered, }) .from(regularCustomerOrders) .exceptAll( db .select({ productId: vipCustomerOrders.productId, quantityOrdered: vipCustomerOrders.quantityOrdered, }) .from(vipCustomerOrders) ); This generates: (select "product_id", "quantity_ordered" from "regular_customer_orders") except all (select "product_id", "quantity_ordered" from "vip_customer_orders")

avg() aggregation helper returns string type

Import avg from 'drizzle-orm'. db.select({ value: avg(users.id) }).from(users) generates 'select avg("id") from "users"'. This is equivalent to sql`avg(${users.id})`.mapWith(String).

Partial select with custom field names

To select only specific columns, pass a selection object to .select() with the desired fields. For example, db.select({ field1: users.id, field2: users.name }).from(users) selects only id and name columns and aliases them as field1 and field2 in the result.

Use arbitrary SQL expressions in select fields

You can use any expression, not just table columns, in the selection object. For example, db.select({ id: users.id, lowerName: sql<string>`lower(${users.name})` }).from(users) applies the SQL lower() function to the name column.

Basic select returns all columns with inferred types

To select all rows and columns from a table, use db.select().from(table). The result type is automatically inferred based on the table definition, including column nullability. For example, selecting from a users table with id (serial, not null), name (text, not null), and age (integer, nullable) returns an array of objects with types {id: number, name: string, age: number | null}[].

sql<Type> generic parameter specifies expected type, not runtime conversion

When using sql<Type>, you are telling Drizzle the expected type of the field at runtime. If you specify the type incorrectly (for example, sql<number> for a field that returns a string), the runtime value will not match the expected type. Drizzle cannot perform type casts based on the generic type because that information is not available at runtime. Use .mapWith() for runtime transformations.

Conditional select with dynamic fields

Use the spread operator with conditional expressions to build dynamic selection objects. For example, const result = db.select({ id: users.id, ...(withName ? { name: users.name } : {}) }).from(users) conditionally includes the name field based on a parameter.

selectDistinct() returns only unique rows

Use .selectDistinct() instead of .select() to retrieve only unique rows from a dataset. For example, db.selectDistinct().from(users) removes duplicate rows.

PostgreSQL distinct on clause syntax

PostgreSQL supports DISTINCT ON to specify which columns determine uniqueness. Use db.selectDistinctOn([users.id]).from(users) to mark only the id column for distinctness. Example: db.selectDistinctOn([users.id]).from(users).orderBy(users.id) generates 'select distinct on ("users"."id") "id", "name", "age" from "users" order by "users"."id"'.

getColumns helper for advanced partial select

Use import { getColumns } from 'drizzle-orm' to retrieve all columns from a table as an object. You can then spread them into a selection object, optionally excluding specific columns. For example, const { content, ...rest } = getColumns(posts); await db.select({ ...rest }).from(posts) selects all columns except content.

Filter operators: eq, lt, gte, ne and others

Use filter operators in the .where() clause. Common operators include eq() for equality, lt() for less than, gte() for greater than or equal, and ne() for not equal. Example: db.select().from(users).where(eq(users.id, 42)) generates 'select "id", "name", "age" from "users" where "users"."id" = $1' with parameterized values.

having() filters aggregated groups

Use .having() to filter groups after aggregation. For example, db.select({ age: users.age, count: sql<number>`cast(count(${users.id}) as int)` }).from(users).groupBy(users.age).having(({ count }) => gt(count, 1)) filters groups with more than one user.

count() returns bigint/string in PostgreSQL, use cast or mapWith

The count() function in PostgreSQL returns bigint (which Drizzle represents as string). To get a number type, use cast(count(...) as int) or use .mapWith(Number) after the sql expression. For example, sql<number>`cast(count(${users.id}) as int)` or sql`count(*)`.mapWith(Number).

count() aggregation helper

Import count from 'drizzle-orm' to use the helper function. db.select({ value: count() }).from(users) generates 'select count(*) from "users"' and db.select({ value: count(users.id) }).from(users) generates 'select count("id") from "users"'. This is equivalent to using sql`count(*)`.mapWith(Number) or sql`count(${users.id})`.mapWith(Number).

Drizzle always explicitly lists columns in SELECT, never uses SELECT *

Drizzle generates SQL with explicit column names in the SELECT clause instead of using SELECT *. This is required internally to guarantee the order of fields in the query result and is also considered a good practice.

avgDistinct() aggregation helper

Import avgDistinct from 'drizzle-orm'. db.select({ value: avgDistinct(users.id) }).from(users) generates 'select avg(distinct "id") from "users"'. This is equivalent to sql`avg(distinct ${users.id})`.mapWith(String).

sum() aggregation helper returns string type

Import sum from 'drizzle-orm'. db.select({ value: sum(users.id) }).from(users) generates 'select sum("id") from "users"'. This is equivalent to sql`sum(${users.id})`.mapWith(String).

sumDistinct() aggregation helper

Import sumDistinct from 'drizzle-orm'. db.select({ value: sumDistinct(users.id) }).from(users) generates 'select sum(distinct "id") from "users"'. This is equivalent to sql`sum(distinct ${users.id})`.mapWith(String).

max() aggregation helper

Import max from 'drizzle-orm'. db.select({ value: max(users.id) }).from(users) generates 'select max("id") from "users"'. This is equivalent to sql`max(${users.id})`.mapWith(users.id).

min() aggregation helper

Import min from 'drizzle-orm'. db.select({ value: min(users.id) }).from(users) generates 'select min("id") from "users"'. This is equivalent to sql`min(${users.id})`.mapWith(users.id).

Use GROUP BY when selecting aggregating functions with other columns

When selecting aggregation functions like count(), sum() together with other columns, you must include a .groupBy() clause. For example, db.select({ age: users.age, count: count() }).from(users).groupBy(users.age) groups by age and counts rows in each group.

All filter values are automatically parameterized

Values passed to filter operators and the sql function are automatically parameterized. For example, db.select().from(users).where(eq(users.id, 42)) becomes 'select "id", "name", "age" from "users" where "users"."id" = $1' with params: [42], preventing SQL injection.

Write arbitrary SQL filters with sql operator

Use the sql operator to write custom SQL filters. For example, db.select().from(users).where(sql`${users.id} < 42`) or db.select().from(users).where(sql`lower(${users.name}) = 'aaron'`). All filter operators in Drizzle are implemented using sql internally.

not() operator inverts a condition

Use not() to invert a filter condition. For example, db.select().from(users).where(not(eq(users.id, 42))) generates 'select "id", "name", "age" from "users" where not ("users"."id" = 42)'.

and() combines multiple filter conditions

Use and() to logically combine multiple filter conditions. For example, db.select().from(users).where(and(eq(users.id, 42), eq(users.name, 'Dan'))) generates 'select "id", "name", "age" from "users" where (("users"."id" = 42) and ("users"."name" = 'Dan'))'.

or() combines multiple filter conditions with logical OR

Use or() to logically combine filter conditions with OR. For example, db.select().from(users).where(or(eq(users.id, 42), eq(users.name, 'Dan'))) generates 'select "id", "name", "age" from "users" where (("users"."id" = 42) or ("users"."name" = 'Dan'))'.

Passing undefined to where() skips the filter

You can pass undefined to .where() to conditionally apply filters. For example, db.select().from(posts).where(term ? ilike(posts.title, term) : undefined) applies the ilike filter only when term is provided, otherwise the filter is omitted.

limit() and offset() for pagination

Use .limit(n) to limit the number of rows returned and .offset(n) to skip n rows. For example, db.select().from(users).limit(10).offset(10) generates 'select "id", "name", "age" from "users" limit 10 offset 10'.

orderBy() with asc() and desc()

Use .orderBy() to sort results. Import asc and desc to control sort direction. For example, db.select().from(users).orderBy(desc(users.name)) generates 'select "id", "name", "age" from "users" order by "users"."name" desc'. Multiple fields can be specified: .orderBy(asc(users.name), desc(users.name2)).

WITH clause for common table expressions (CTEs)

Use db.$with('name').as(subquery) to define a CTE, then db.with(cte).select().from(cte) to use it. Example: const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42))); const result = await db.with(sq).select().from(sq) generates 'with "sq" as (select "id", "name", "age" from "users" where "users"."id" = 42) select "id", "name", "age" from "sq"'.

CTEs can contain INSERT, UPDATE, or DELETE statements

You can use insert, update, or delete statements inside a WITH clause. For example, const sq = db.$with('sq').as(db.insert(users).values({ name: 'John' }).returning()); const result = await db.with(sq).select().from(sq) creates a CTE that inserts a row and returns it.

Alias arbitrary SQL expressions in CTEs with .as()

To select arbitrary SQL expressions in a CTE and reference them in other queries, add aliases using .as(). For example, const sq = db.$with('sq').as(db.select({ name: sql<string>`upper(${users.name})`.as('name') }).from(users)); const result = await db.with(sq).select({ name: sq.name }).from(sq). Without an alias, the field type becomes DrizzleTypeError and cannot be referenced.

Give your agent this brain