query-api

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

HAVING clause with aggregations

Use `.having()` to filter grouped results: `await db.select({ age: users.age, count: sql<number>`cast(count(${users.id}) as signed)` }).from(users).groupBy(users.age).having(({ count }) => gt(count, 1))`. This filters groups where count is greater than 1.

count aggregation function

Use `count()` helper from drizzle-orm: `await db.select({ value: count() }).from(users)` generates `select count(*) from users` or `await db.select({ value: count(users.id) }).from(users)` generates `select count(id) from users`. Equivalent to `sql`count(*)`.mapWith(Number)`.

countDistinct aggregation function

Use `countDistinct()` to count unique values: `await db.select({ value: countDistinct(users.id) }).from(users)` generates `select count(distinct id) from users`. Equivalent to `sql`count(distinct ${users.id})`.mapWith(Number)`.

avg aggregation function

Use `avg()` to calculate average: `await db.select({ value: avg(users.id) }).from(users)` generates `select avg(id) from users`. Equivalent to `sql`avg(${users.id})`.mapWith(String)`.

avgDistinct aggregation function

Use `avgDistinct()` to calculate average of distinct values: `await db.select({ value: avgDistinct(users.id) }).from(users)` generates `select avg(distinct id) from users`. Equivalent to `sql`avg(distinct ${users.id})`.mapWith(String)`.

sum aggregation function

Use `sum()` to calculate sum: `await db.select({ value: sum(users.id) }).from(users)` generates `select sum(id) from users`. Equivalent to `sql`sum(${users.id})`.mapWith(String)`.

sumDistinct aggregation function

Use `sumDistinct()` to sum distinct values: `await db.select({ value: sumDistinct(users.id) }).from(users)` generates `select sum(distinct id) from users`. Equivalent to `sql`sum(distinct ${users.id})`.mapWith(String)`.

max aggregation function

Use `max()` to find maximum value: `await db.select({ value: max(users.id) }).from(users)` generates `select max(id) from users`. Equivalent to `sql`max(${users.id})`.mapWith(users.id)`.

min aggregation function

Use `min()` to find minimum value: `await db.select({ value: min(users.id) }).from(users)` generates `select min(id) from users`. Equivalent to `sql`min(${users.id})`.mapWith(users.id)`.

Advanced aggregation example with joins

Example aggregation with LEFT JOIN and GROUP BY: `await db.select({ id: orders.id, shippedDate: orders.shippedDate, shipName: orders.shipName, shipCity: orders.shipCity, shipCountry: orders.shipCountry, productsCount: sql<number>`cast(count(${details.productId}) as signed)`, quantitySum: sql<number>`sum(${details.quantity})`, totalPrice: sql<number>`sum(${details.quantity} * ${details.unitPrice})` }).from(orders).leftJoin(details, eq(orders.id, details.orderId)).groupBy(orders.id).orderBy(asc(orders.id))`.

Iterator for large result sets

Use `.iterator()` to convert query into async iterator for large result sets: `const iterator = db.select().from(users).iterator(); for await (const row of iterator) { console.log(row) }`. This loads rows into memory incrementally instead of loading all at once. Also works with prepared statements: `const query = db.select().from(users).prepare(); const iterator = query.iterator()`.

USE INDEX hint

The `USE INDEX` hint suggests indexes to the optimizer without forcing them. Define an index: `const usersTableNameIndex = index('users_name_index').on(users.name)`. Use in query: `await db.select().from(users, { useIndex: usersTableNameIndex }).where(eq(users.name, 'David'))`. Also works on joins: `await db.select().from(users).leftJoin(posts, eq(posts.userId, users.id), { useIndex: usersTableNameIndex }).where(eq(users.name, 'David'))`.

IGNORE INDEX hint

The `IGNORE INDEX` hint tells the optimizer to avoid using specific indexes. Define an index: `const usersTableNameIndex = index('users_name_index').on(users.name)`. Use in query: `await db.select().from(users, { ignoreIndex: usersTableNameIndex }).where(eq(users.name, 'David'))`. Also works on joins: `await db.select().from(users).leftJoin(posts, eq(posts.userId, users.id), { ignoreIndex: usersTableNameIndex }).where(eq(users.name, 'David'))`.

FORCE INDEX hint

The `FORCE INDEX` hint forces the optimizer to use specified indexes. If the index cannot be used, MySQL will not fall back to other indexes. Define an index: `const usersTableNameIndex = index('users_name_index').on(users.name)`. Use in query: `await db.select().from(users, { forceIndex: usersTableNameIndex }).where(eq(users.name, 'David'))`. Also works on joins: `await db.select().from(users).leftJoin(posts, eq(posts.userId, users.id), { forceIndex: usersTableNameIndex }).where(eq(users.name, 'David'))`.

schema inference and type safety

Result types from queries are automatically inferred from table definitions, including column nullability. For example, a column defined as `age: int()` without `.notNull()` will have type `number | null` in query results.

Template interpolation prevents schema coupling

You can safely alter schema, rename tables and columns, and it will be automatically reflected in your queries because of template interpolation with the `sql` function, as opposed to hardcoding column or table names when writing raw SQL.

sql template execution example

Example: `import { sql } from 'drizzle-orm'; const id = 69; await db.execute(sql`select * from ${usersTable} where ${usersTable.id} = ${id}`)` generates the query `select * from `users` where `users`.`id` = ?;` with parameters [69].

sql template for parameterized queries

Drizzle's sql template allows you to write type-safe and parameterized queries. You import sql from 'drizzle-orm' and use it as a template literal. Tables and columns are automatically mapped to escaped SQL syntax, and dynamic parameters like ${id} are converted to ? placeholders with values passed separately to the database. This prevents SQL injection vulnerabilities.

sql<T> generic type for custom return types

You can define a custom type using sql<T> to specify the TypeScript type of the result. This is purely a compile-time helper for Drizzle and does not perform runtime mapping. For example, `sql<string>`lower(${usersTable.name})`` will type the result as a string instead of unknown. Runtime type determination is not feasible because SQL queries are highly versatile and customizable.

sql<T> example for typed partial select

Without sql<T>: `const response: { lowerName: unknown }[] = await db.select({ lowerName: sql`lower(${usersTable.id})` }).from(usersTable);` With sql<T>: `const response: { lowerName: string }[] = await db.select({ lowerName: sql<string>`lower(${usersTable.id})` }).from(usersTable);`

sql.mapWith() for runtime value mapping

The .mapWith() method allows runtime mapping for values passed from the database driver to Drizzle. It accepts a Column interface implementation or a custom DriverValueDecoder implementation. You can replicate a specific column's mapping strategy by passing that column, or provide custom mapping logic via an object with a mapFromDriverValue function, or pass built-in constructors like Number.

sql.mapWith() example with column

Example: `import { mysqlTable, int, text } from 'drizzle-orm/mysql-core'; const usersTable = mysqlTable('users', { id: int().primaryKey().autoincrement(), name: text().notNull() }); sql`...`.mapWith(usersTable.name);` This maps runtime values the same way the text column is mapped.

sql.mapWith() example with custom decoder

Example with custom DriverValueDecoder: `sql``.mapWith({ mapFromDriverValue: (value: any) => { const mappedValue = value; return mappedValue; } });` Example with constructor: `sql``.mapWith(Number);`

sql.as() for field aliasing

Use .as('alias_name') to explicitly specify an alias for a custom field in a select query. Example: `sql`lower(${usersTable.name})`.as('lower_name')` generates SQL `... `users`.`name` as lower_name ...`

sql.raw() for unescaped raw SQL

The sql.raw() function allows including raw SQL statements without additional processing, escaping, or parameterization. Example: `sql.raw(`select * from users where id = ${12}`)` generates `select * from users where id = 12;` whereas `sql`select * from users where id = ${12}`` generates `select * from users where id = ?; --> [12]`

sql.raw() nested inside sql template

You can use sql.raw() inside the sql template function to include unescaped raw strings. Example: `sql`select * from ${usersTable} where id = ${sql.raw(12)}`` generates `select * from `users` where id = 12;` instead of `select * from `users` where id = ?;`

sql.fromList() for aggregating SQL chunks

The sql.fromList() function combines multiple SQL chunks (arrays of SQL parts) into a single SQL statement that can be passed to the database. It allows you to aggregate chunks according to custom business logic before concatenating them into a unified query.

sql.fromList() example

Example: `const sqlChunks: SQL[] = []; sqlChunks.push(sql`select * from users`); sqlChunks.push(sql` where `); for (let i = 0; i < 5; i++) { sqlChunks.push(sql`id = ${i}`); if (i === 4) continue; sqlChunks.push(sql` or `); } const finalSql: SQL = sql.fromList(sqlChunks)` generates `select * from users where id = ? or id = ? or id = ? or id = ? or id = ?; --> [0, 1, 2, 3, 4]`

sql.join() for concatenating chunks with custom separator

The sql.join() function concatenates SQL chunks using a specified separator string or character. It provides additional flexibility compared to fromList for handling spaces between chunks or specifying custom delimiters.

sql.join() example with custom separator

Example: `const sqlChunks: SQL[] = []; sqlChunks.push(sql`select * from users`); sqlChunks.push(sql`where`); for (let i = 0; i < 5; i++) { sqlChunks.push(sql`id = ${i}`); if (i === 4) continue; sqlChunks.push(sql`or`); } const finalSql: SQL = sql.join(sqlChunks, sql.raw(' '));` generates `select * from users where id = ? or id = ? or id = ? or id = ? or id = ?; --> [0, 1, 2, 3, 4]`

sql.append() for dynamically adding SQL chunks

The append() method allows you to dynamically add new SQL chunks to an existing SQL object, achieving the same behavior as fromList. This enables incremental construction of SQL queries with custom logic applied to each chunk.

sql.empty() for building incremental queries

The sql.empty() function creates a blank SQL object that you can dynamically append SQL chunks to using .append(). This allows incremental construction of SQL queries applying custom logic or conditions to determine the contents of each chunk. The resulting SQL object supports all sql template features like parameterization, composition, and escaping.

sql.empty() example

Example: `const finalSql = sql.empty(); finalSql.append(sql`select * from users`); finalSql.append(sql` where `); for (let i = 0; i < 5; i++) { finalSql.append(sql`id = ${i}`); if (i === 4) continue; finalSql.append(sql` or `); }` generates `select * from users where id = ? or id = ? or id = ? or id = ? or id = ?; --> [0, 1, 2, 3, 4]`

Converting sql template to string and params with dialect

To obtain the query string and corresponding parameters from an sql template, you must specify the database dialect. Different databases have varying syntax for parameterization and escaping. Use the dialect's sqlToQuery method to convert the SQL template into the desired query string and parameter format.

Converting sql to string and params example with MySQL

Example: `import { MySqlDialect } from 'drizzle-orm/mysql-core'; const mysqlDialect = new MySqlDialect(); mysqlDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`);` generates `select * from `users` where `users`.`id` = ?; --> [ 12 ]`

sql in partial select queries

The sql functionality can be used in partial select queries to retrieve specific fields or columns from a table. This allows combining Drizzle's query builder syntax with custom SQL expressions for specific fields.

sql in partial select example

Example: `import { sql } from 'drizzle-orm'; import { usersTable } from 'schema'; await db.select({ id: usersTable.id, lowerName: sql<string>`lower(${usersTable.name})`, aliasedName: sql<string>`lower(${usersTable.name})`.as('aliased_column'), count: sql<number>`count(*)`.mapWith(Number) }).from(usersTable)` generates `select `id`, lower(`name`), lower(`name`) as `aliased_column`, count(*) from `users`;`

sql in WHERE clause for custom filtering

You can use the sql template directly in the WHERE clause to utilize any database expressions not natively supported by Drizzle. This provides flexibility to leverage the full power of SQL while maintaining type safety and parameterization.

sql in WHERE clause example

Example: `import { sql } from 'drizzle-orm'; import { usersTable } from 'schema'; const id = 77; await db.select().from(usersTable).where(sql`${usersTable.id} = ${id}`)` generates `select * from `users` where `users`.`id` = ?; --> [ 77 ]`

sql in WHERE clause for advanced fulltext search

Example of advanced fulltext search: `import { sql } from 'drizzle-orm'; import { usersTable } from 'schema'; const searchPattern = "%Ale%"; await db.select().from(usersTable).where(sql`lower(${usersTable.name}) like lower(${searchPattern})`)` generates `select * from `users` where lower(`users`.`name`) like lower(?); --> [ "%Ale%" ]`

sql in ORDER BY clause

The sql template can be used in the ORDER BY clause to implement specific ordering functionality not available in Drizzle while avoiding raw SQL. This allows custom ordering expressions like DESC NULLS FIRST.

sql in ORDER BY example

Example: `import { sql } from 'drizzle-orm'; import { usersTable } from 'schema'; await db.select().from(usersTable).orderBy(sql`${usersTable.id} desc nulls first`)` generates `select * from `users` order by `users`.`id` desc nulls first;`

sql in GROUP BY and HAVING clauses

The sql template can be used in HAVING and GROUP BY clauses to implement specific grouping and filtering logic not natively available in Drizzle, while maintaining type safety and parameterization.

sql in GROUP BY and HAVING example

Example: `import { sql } from 'drizzle-orm'; import { usersTable } from 'schema'; await db.select({ projectId: usersTable.projectId, count: sql<number>`count(${usersTable.id})`.mapWith(Number) }).from(usersTable).groupBy(sql`${usersTable.projectId}`).having(sql`count(${usersTable.id}) > 300`)` generates `select `project_id`, count(`users`.`id`) from users group by `users`.`project_id` having count(`users`.`id`) > 300;`

Basic transaction syntax in Drizzle

Drizzle ORM provides a db.transaction() API that accepts an async callback function receiving a tx object. Within the callback, use tx.update(), tx.select(), and other query methods instead of db methods. The transaction commits all statements as a single logical unit or rolls back entirely if an error occurs.

Transaction rollback with tx.rollback()

Inside a transaction callback, call tx.rollback() to manually roll back the entire transaction. This throws an exception that triggers the rollback. Rollback is useful for conditional logic, such as checking a balance before proceeding with updates.

Returning values from transactions

A transaction callback can return a value using the return statement. The value returned from the callback becomes the awaited result of db.transaction(). This allows queries within the transaction to compute and return a result.

Using relational queries inside transactions

Transactions support relational queries through the query builder API. Inside a transaction callback, use tx.query.tableName.findMany() with the 'with' option to eagerly load relations, just as you would with db.query.

MySQL transaction configuration options

The MySqlTransactionConfig interface provides three optional properties: isolationLevel (accepts 'read uncommitted', 'read committed', 'repeatable read', or 'serializable'), accessMode (accepts 'read only' or 'read write'), and withConsistentSnapshot (boolean). Pass these as the second argument to db.transaction().

MySQL transaction configuration example

Pass transaction configuration as the second argument to db.transaction() with an options object containing isolationLevel, accessMode, and withConsistentSnapshot properties. Example: db.transaction(async (tx) => { /* ... */ }, { isolationLevel: 'read committed', accessMode: 'read write', withConsistentSnapshot: true }).

Basic UPDATE query syntax

To update rows in a table, use db.update(table).set({ columnName: value }).where(condition). The object passed to set() should have keys matching column names in the database schema.

Undefined values ignored in UPDATE set object

When passing an object to the set() method in an UPDATE query, values of undefined are ignored. To set a column to null, explicitly pass null instead of undefined.

Using SQL expressions in UPDATE set values

You can pass SQL expressions as values in the set() object using the sql template tag. For example, await db.update(users).set({ updatedAt: sql`NOW()` }).where(eq(users.name, 'Dan')).

UPDATE query with LIMIT clause

Use the .limit(n) method to add a LIMIT clause to an UPDATE query. For example, await db.update(usersTable).set({ verified: true }).limit(2) generates SQL: update `users` set `verified` = true limit 2.

UPDATE query with ORDER BY clause

Use the .orderBy() method to add an ORDER BY clause to an UPDATE query, sorting results by specified fields. Import asc and desc from drizzle-orm to specify sort direction. Single field example: await db.update(usersTable).set({ verified: true }).orderBy(usersTable.name). Multiple fields example: await db.update(usersTable).set({ verified: true }).orderBy(asc(usersTable.name), desc(usersTable.name2)).

New Casing API replaces drizzle({ casing })

The legacy drizzle({ casing: 'camelCase' }) option is replaced with a table/view/schema-level API. Import snakeCase and camelCase from drizzle-orm/mysql-core and use them as factories: snakeCase.table(), camelCase.table(), etc. Available on: table, view, materializedView, and schema.

Casing API example with snakeCase

Example of casing API: import { snakeCase, camelCase } from 'drizzle-orm/mysql-core'; export const users = snakeCase.table('users', { id: int().primaryKey().autoincrement(), fullName: text(), createdAt: timestamp() }); The fullName field maps to full_name in the database and createdAt maps to created_at.

getTableColumns() deprecated in favor of getColumns()

The getTableColumns() function is deprecated. Use getColumns() instead: import { getColumns } from 'drizzle-orm'; const columns = getColumns(users);

JIT Mappers opt-in feature

Just-in-time compiled row mappers make mapping as fast as raw driver performance. Enable with the jit option: const db = drizzle({ ..., jit: true });

SQLcommenter support for custom query tags

Add custom tags to queries using .comment(). Tags are appended as SQL comments at the end of each query. Example: db.select().from(users).comment('my_first_tag'); outputs select "id", "name" from "users" /*my_first_tag*/

Give your agent this brain