query-api

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

Partial field selection with columns parameter

The columns parameter in relational queries lets you include or omit specific columns. You can use 'true' to include a column or 'false' to exclude it. Drizzle performs partial selects on the query level with no additional data transferred from the database, and outputs a single SQL statement.

Mixing true and false in columns selection

When both 'true' and 'false' select options are present in the columns parameter, all 'false' options are ignored. If you include one field as 'true', all other fields are automatically excluded.

Partial select with nested relations in columns

You can include or exclude columns of nested relations using the columns parameter in nested 'with' queries. This allows fine-grained control over which fields are returned from related tables.

Select filters with operators in relational queries

Relational queries support the same filters and operators as the SQL-like query builder. Operators can be imported from 'drizzle-orm' or used from the callback syntax. Available operators include: OR, AND, NOT, RAW, eq, ne, gt, gte, lt, lte, in, notIn, like, notLike, isNull, isNotNull.

Relations filtering in relational queries

You can filter by any table included in the query using the relations filtering syntax. For example, filter users who have posts with specific content, or filter users only if they have at least one post by setting the relation filter to 'true'.

Limit and offset in relational queries

Drizzle provides limit and offset APIs for both the main query and nested relations. The offset parameter can now be used in nested tables ('with' clauses) as well as the main query. Both can be used together to paginate results.

Order by in relational queries

Relational queries support ordering using either the object syntax (with 'asc' or 'desc' strings) or callback syntax. When multiple orderBy statements are used in the same table, they are included in the query in the same order they were added. Custom SQL can be used with orderBy by passing a callback with sql template.

Custom fields with extras parameter

The extras parameter in relational queries lets you add custom additional fields by applying functions to data. This is useful when you need to retrieve data and apply transformations like lowercasing, concatenation, or length calculation. The key name you provide becomes a field in the returned object.

Aggregations not supported in extras

Aggregations are not currently supported in the extras parameter of relational queries. Use core queries instead for aggregation operations.

SQL aliases in extras are ignored

If you specify .as('<alias>') on any extras field, Drizzle will ignore it. The key name used in the extras object becomes the field name in the result.

Subqueries in relational queries with extras

You can use subqueries within the extras parameter of relational queries. Use db.$count() to count records matching a condition. Example: totalPostsCount: (table) => db.$count(posts, eq(posts.authorId, table.id)). This allows calculating aggregate values for each record without using traditional aggregation functions.

Prepared statements in relational queries

Relational queries support prepared statements for performance optimization. Use sql.placeholder() to define placeholders in where conditions, limit, and offset parameters. Call .prepare() on the query and then execute() with an object containing the parameter values.

Single SQL statement output in relational queries

A single SQL statement is outputted by Drizzle for relational queries, even with partial field selection, nested relations, and custom fields. This is important for understanding query performance characteristics.

Example: Partial field selection

const posts = await db.query.posts.findMany({ columns: { id: true, content: true, }, with: { comments: true, } }); This retrieves only id and content from posts, but includes all fields from related comments.

Example: Nested partial field selection

const posts = await db.query.posts.findMany({ columns: { id: true, content: true, }, with: { comments: { columns: { authorId: false } } } }); This retrieves specific fields from posts and excludes authorId from nested comments.

Example: Filter by column with equals

const users = await db.query.users.findMany({ where: { id: 1 } }); This filters users where id equals 1.

INTERSECT operation in Drizzle MySQL

INTERSECT combines only those rows which are present in both query blocks, omitting duplicates. It can be used with the import-pattern using the intersect() function from 'drizzle-orm/mysql-core' or with the builder-pattern using the .intersect() method. Example using builder-pattern: db.select({ courseName: depA.courseName }).from(depA).intersect(db.select({ courseName: depB.courseName }).from(depB)); generates SQL: (select `course_name` from `department_a_courses`) intersect (select `course_name` from `department_b_courses`)

INTERSECT ALL operation in Drizzle MySQL

INTERSECT ALL combines only those rows which are present in both query blocks, retaining duplicates. It can be used with the import-pattern using the intersectAll() function from 'drizzle-orm/mysql-core' or with the builder-pattern using the .intersectAll() method. Example using builder-pattern: db.select({ productId: regularCustomerOrders.productId, quantityOrdered: regularCustomerOrders.quantityOrdered }).from(regularCustomerOrders).intersectAll(db.select({ productId: vipCustomerOrders.productId, quantityOrdered: vipCustomerOrders.quantityOrdered }).from(vipCustomerOrders)); generates SQL: (select `product_id`, `quantity_ordered` from `regular_customer_orders`) intersect all (select `product_id`, `quantity_ordered` from `vip_customer_orders`)

EXCEPT operation in Drizzle MySQL

EXCEPT returns all results from the first query block that are not present in the second query block, omitting duplicates. It can be used with the import-pattern using the except() function from 'drizzle-orm/mysql-core' or with the builder-pattern using the .except() method. Example using builder-pattern: db.select({ courseName: depA.projectsName }).from(depA).except(db.select({ courseName: depB.projectsName }).from(depB)); generates SQL: (select `projects_name` from `department_a_projects`) except (select `projects_name` from `department_b_projects`)

EXCEPT ALL operation in Drizzle MySQL

EXCEPT ALL returns all results from the first query block that are not present in the second query block, retaining duplicates. It can be used with the import-pattern using the exceptAll() function from 'drizzle-orm/mysql-core' or with the builder-pattern using the .exceptAll() method. Example using builder-pattern: db.select({ productId: regularCustomerOrders.productId, quantityOrdered: regularCustomerOrders.quantityOrdered }).from(regularCustomerOrders).exceptAll(db.select({ productId: vipCustomerOrders.productId, quantityOrdered: vipCustomerOrders.quantityOrdered }).from(vipCustomerOrders)); generates SQL: (select `product_id`, `quantity_ordered` from `regular_customer_orders`) except all (select `product_id`, `quantity_ordered` from `vip_customer_orders`)

SQL set operations in Drizzle

Drizzle supports six SQL set operations: UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, and EXCEPT ALL. These operations combine results from multiple query blocks into a single result. Each operation can be used in two patterns: import-pattern (importing the function and calling it directly) or builder-pattern (using methods on the query builder).

UNION ALL operation in Drizzle MySQL

UNION ALL combines results from two query blocks into a single result, retaining duplicates. It can be used with the import-pattern using the unionAll() function from 'drizzle-orm/mysql-core' or with the builder-pattern using the .unionAll() method. Example using builder-pattern: db.select({ transaction: onlineSales.transactionId }).from(onlineSales).unionAll(db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)); generates SQL: (select `transaction_id` from `online_sales`) union all (select `transaction_id` from `in_store_sales`)

UNION operation in Drizzle MySQL

UNION combines results from two query blocks into a single result, omitting duplicates. It can be used with the import-pattern using the union() function from 'drizzle-orm/mysql-core' or with the builder-pattern using the .union() method on a query. Example using builder-pattern: db.select({ name: users.name }).from(users).union(db.select({ name: customers.name }).from(customers)).limit(10); generates SQL: (select `name` from `sellers`) union (select `name` from `customers`) limit 10

comment() value encoding in sqlcommenter format

When passing an object to comment(), values including special characters like forward slashes are URL-encoded. For example, { trace: true, route: '/api/users', version: 2 } produces /*route='%2Fapi%2Fusers',trace='true',version='2'*/ with the forward slash encoded as %2F.

comment() method adds sqlcommenter-formatted metadata to queries

The .comment() method appends sqlcommenter-formatted comments to select, insert, update, and delete queries. These tags help with query tracking, debugging, and database traffic control.

comment() before prepare() correct pattern

To use comment() with prepared statements, call it before prepare(): db.select().from(users).comment({ key: "val" }).prepare()

comment() accepts string or object with key-value pairs

The .comment() method can accept either a raw string like "my_first_tag" or an object with key-value pairs like { priority: 'high', category: 'analytics' }. String comments are appended as-is, while object values are URL-encoded in the SQL comment.

comment() with string example

db.select().from(users).comment("my_first_tag") produces select `id`, `name` from `users` /*my_first_tag*/

comment() cannot be used after prepare()

The .comment() method must be called before prepare() on a query. Prepared statements compile the SQL query once and reuse it, so the query string is fixed at preparation time and cannot be modified afterwards. Calling comment() after prepare() will not work.

comment() with object example

db.select().from(users).comment({ priority: 'high', category: 'analytics' }) produces select `id`, `name` from `users` /*priority='high',category='analytics'*/

comment() with insert example

db.insert(users).values({ name: 'Dan' }).comment({ operation: 'seed' }) produces insert into `users` (`name`) values ('Dan') /*operation='seed'*/

comment() with update example

db.update(users).set({ name: 'Dan' }).where(eq(users.id, 1)).comment({ operation: 'update' }) produces update `users` set `name` = 'Dan' where `users`.`id` = 1 /*operation='update'*/

comment() with delete example

db.delete(users).where(eq(users.id, 1)).comment({ operation: 'cleanup' }) produces delete from `users` where `users`.`id` = 1 /*operation='cleanup'*/

Conditional SELECT with dynamic fields

Create dynamic selection objects based on conditions using spread operator: `db.select({ id: users.id, ...(withName ? { name: users.name } : {}) }).from(users)`.

Column aliases in SELECT

Use `.as()` method to specify column aliases: `db.select({ id: users.id, lowerName: users.name.as('lower') }).from(users)` generates SQL: `select id, name as lower from users`.

Partial SELECT with column subset

To select only a subset of columns, provide a selection object to `.select()` method: `db.select({ field1: users.id, field2: users.name }).from(users)`. You can also use arbitrary expressions as selection fields, not just table columns.

Basic SELECT all columns

To select all rows from a table including all columns, use `await db.select().from(users)`. Drizzle always explicitly lists columns in the SELECT clause instead of using SELECT *, which guarantees field order in the query result. Result types are automatically inferred from the table definition, including column nullability.

sql type generic and runtime mapping

When using `sql<string>` you are telling Drizzle the expected type of the field. If specified incorrectly, the runtime value won't match the expected type because Drizzle cannot perform type casts at runtime. Use `.mapWith()` method if you need to apply runtime transformations to returned values.

DISTINCT SELECT

Use `.selectDistinct()` instead of `.select()` to retrieve only unique rows: `await db.selectDistinct().from(users).orderBy(users.id, users.name)` or `await db.selectDistinct({ id: users.id }).from(users)`.

getColumns helper for column selection

Use `import { getColumns } from 'drizzle-orm'` to get all columns from a table. You can spread them: `await db.select({ ...getColumns(posts), titleLength: sql<number>`length(${posts.title})` }).from(posts)`. You can also exclude columns: `const { content, ...rest } = getColumns(posts); await db.select({ ...rest }).from(posts)`.

Relational query API column selection

The relational query API provides simpler column selection: `await db.query.posts.findMany({ columns: { title: true } })` selects only the title column. Use `columns: { content: false }` to select all columns except content.

WHERE clause with filter operators

Use filter operators in `.where()` method: `eq(users.id, 42)`, `lt(users.id, 42)`, `gte(users.id, 42)`, `ne(users.id, 42)`. All filter operators are implemented using the `sql` function. Values provided to filter operators are parameterized automatically.

Custom filter operators with sql

Build custom filter operators using the `sql` function: `function equals42(col: Column) { return sql`${col} = 42` }`. For example: `await db.select().from(users).where(sql`${users.id} < 42`)` or `await db.select().from(users).where(sql`lower(${users.name}) = 'aaron'`)`. All values are parameterized automatically.

NOT operator for inverting conditions

Use `not()` operator to invert conditions: `await db.select().from(users).where(not(eq(users.id, 42)))` or `await db.select().from(users).where(sql`not ${users.id} = 42`)`. Both generate: `select id, name, age from users where not (users.id = 42)`.

OR operator for combining filters

Combine multiple filter conditions with `or()`: `await 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'))`.

Conditional filtering with undefined

Pass `undefined` to `.where()` for conditional filtering: `await db.select().from(posts).where(term ? like(posts.title, term) : undefined)` works when term is not provided.

Dynamic filter array with and operator

Build dynamic filter arrays and combine them: `const filters: SQL[] = []; filters.push(like(posts.title, 'AI')); filters.push(inArray(posts.category, ['Tech', 'Art'])); await db.select().from(posts).where(and(...filters))`.

LIMIT clause

Use `.limit()` to add limit clause: `await db.select().from(users).limit(10)` generates: `select id, name, age from users limit 10`.

OFFSET clause for pagination

Use `.offset()` to add offset clause for pagination: `await db.select().from(users).limit(10).offset(10)` generates: `select id, name, age from users limit 10 offset 10`.

ORDER BY with ascending/descending

Use `.orderBy()` to sort results: `await db.select().from(users).orderBy(users.name)` sorts ascending. Use `desc()`: `await db.select().from(users).orderBy(desc(users.name))` sorts descending. Order by multiple fields: `await db.select().from(users).orderBy(users.name, users.name2)` or `await db.select().from(users).orderBy(asc(users.name), desc(users.name2))`.

Limit-offset pagination pattern

Implement limit-offset pagination: `await db.select().from(users).orderBy(asc(users.id)).limit(4).offset(4)`. The `.orderBy()` is mandatory. Calculate offset as: `offset: (page - 1) * pageSize`.

Relational API pagination

Using relational API for pagination: `const getUsers = async (page = 1, pageSize = 3) => { return await db.query.users.findMany({ orderBy: (users, { asc }) => asc(users.id), limit: pageSize, offset: (page - 1) * pageSize }); }`.

Cursor-based pagination pattern

Implement cursor-based pagination: `const nextUserPage = async (cursor?: number, pageSize = 3) => { return await 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.

Subquery pagination with join

Advanced pagination using subquery: `const sq = db.select({ id: users.id }).from(users).orderBy(users.id).limit(pageSize).offset((page - 1) * pageSize).as('subquery'); return await db.select().from(users).innerJoin(sq, eq(users.id, sq.id)).orderBy(users.id)`.

WITH clause for CTEs

Use Common Table Expressions (CTEs) with `with` clause to simplify complex queries: `const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42))); const result = await db.with(sq).select().from(sq)`. This generates: `with sq as (select id, name, age from users where users.id = 42) select id, name, age from sq`.

CTE with arbitrary SQL expressions

When selecting arbitrary SQL values as fields in a CTE, add aliases to them: `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 in other queries, causing runtime errors.

MySQL RETURNING limitation with CTEs

MySQL doesn't support native `RETURNING`, so `insert`, `update`, and `delete` statements that depend on returning rows are not available inside `with` for MySQL dialect.

Subquery SELECT

Embed queries into other queries using `.as()` to create a subquery: `const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(sq)`. This generates: `select id, name, age from (select id, name, age from users where users.id = 42) sq`.

Subquery in JOIN

Subqueries can be used anywhere a table can be used, including in joins: `const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id))`. This generates: `select users.id, users.name, users.age, sq.id, sq.name, sq.age from users left join (select id, name, age from users where users.id = 42) sq on users.id = sq.id`.

GROUP BY clause

Use `.groupBy()` to group query results: `await db.select({ age: users.age, count: sql<number>`cast(count(${users.id}) as signed)` }).from(users).groupBy(users.age)`. When selecting aggregating functions and other columns in one query, use the `.groupBy` clause.

Give your agent this brain