query-api

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

UNION ALL combines query results keeping duplicates

The UNION ALL set operation combines results from two query blocks into a single result set, retaining duplicate rows. In Drizzle, use the unionAll() function from 'drizzle-orm/sqlite-core' (import-pattern) or chain .unionAll() on a query builder (builder-pattern). Example import-pattern: unionAll(queryA, queryB). Example builder-pattern: db.select().from(table1).unionAll(db.select().from(table2)).

INTERSECT finds common rows omitting duplicates

The INTERSECT set operation returns only rows that are common to both query blocks, omitting duplicates. In Drizzle, use the intersect() function from 'drizzle-orm/sqlite-core' (import-pattern) or chain .intersect() on a query builder (builder-pattern). Example import-pattern: intersect(queryA, queryB). Example builder-pattern: db.select().from(table1).intersect(db.select().from(table2)).

INTERSECT ALL not supported by SQLite

SQLite does not support the INTERSECT ALL set operation. This operation would return only rows common to both query blocks while retaining duplicates, but this functionality is not available in SQLite.

SQL standard defines six set operations

The SQL standard defines the following six set operations: UNION, INTERSECT, EXCEPT, UNION ALL, INTERSECT ALL, and EXCEPT ALL.

EXCEPT ALL not supported by SQLite

SQLite does not support the EXCEPT ALL set operation. This operation would return all rows from the first query block not present in the second query block while retaining duplicates, but this functionality is not available in SQLite.

Drizzle avoids multiple SQL queries per query builder call

Drizzle is designed to output a single, optimal SQL query for each query builder call, not multiple queries. This is achieved through careful query composition and internal optimization.

Basic select syntax and auto-inferred types

Use `db.select().from(table)` to retrieve all columns from a table. The result type is automatically inferred from the table schema, including column nullability. For example: `const result = await db.select().from(users);` returns an array of objects with types `{id: number; name: string; age: number | null}[]`.

Drizzle always explicitly lists columns instead of SELECT *

Drizzle never uses `SELECT *` in generated SQL. Instead, it explicitly lists all columns in the SELECT clause. This is required internally to guarantee field order in the query result and is considered a best practice.

Partial select with custom field names

To select only specific columns, pass a selection object to `.select()` with custom field names: `db.select({field1: users.id, field2: users.name}).from(users)`. You can also use arbitrary expressions as selection fields, not just table columns.

Using sql<T> for custom expressions in select

Include custom SQL expressions in selections using `sql<Type>` template literals. For example: `sql<string>`lower(${users.name})`` tells Drizzle the expected type is string. The type generic only affects TypeScript typing; Drizzle cannot perform runtime type casts. If the runtime value doesn't match the expected type, use `.mapWith()` for runtime transformations.

Conditional select with spread operator

Build dynamic selection objects using TypeScript spread operator: `db.select({id: users.id, ...(withName ? {name: users.name} : {})}).from(users)`. This allows columns to be conditionally included.

selectDistinct to retrieve unique rows

Use `.selectDistinct()` instead of `.select()` to retrieve only unique rows. Example: `await db.selectDistinct().from(users).orderBy(users.id, users.name)` generates `select distinct "id", "name", "age" from "users" order by...`.

getColumns helper for dynamic column selection

Use `getColumns(table)` from 'drizzle-orm' to get all columns of a table as an object. Can be spread into selection objects and combined with other fields: `{...getColumns(posts), titleLength: sql<number>`length(${posts.title})`}`. Can also exclude columns: `const {content, ...rest} = getColumns(posts)` then spread `{...rest}`.

Filter operators: eq, lt, gte, ne

Import filter operators from 'drizzle-orm' and use in `.where()`: `eq(column, value)`, `lt(column, value)`, `gte(column, value)`, `ne(column, value)`. All values are automatically parameterized. Example: `await db.select().from(users).where(eq(users.id, 42))` generates `select... where "users"."id" = ?` with params `[42]`.

Custom filter operators using sql function

Write custom filter operators using the `sql` function. Example: `function equals42(col: SQLiteColumn) { return sql`${col} = 42`; }` creates a reusable filter. The `sql` function automatically parameterizes values.

not operator to invert conditions

Use the `not` operator to invert filter conditions: `not(eq(users.id, 42))` generates `not ("users"."id" = 42)`. Alternatively use raw SQL: `sql`not ${users.id} = 42``.

and() and or() operators to combine filters

Combine multiple filters logically using `and()` and `or()` from 'drizzle-orm'. Example: `where(and(eq(users.id, 42), eq(users.name, 'Dan')))` or `where(or(eq(users.id, 42), eq(users.name, 'Dan')))`. These generate properly parenthesized SQL.

Conditional filtering with undefined

Pass `undefined` to `.where()` to conditionally apply filters. Example: `where(term ? like(posts.title, term) : undefined)`. When undefined is passed, no WHERE clause is added to the query.

limit() and offset() for pagination

Use `.limit(n)` and `.offset(n)` to add LIMIT and OFFSET clauses. Example: `db.select().from(users).limit(10).offset(10)` generates `select... limit 10 offset 10`. offset() is called after limit().

orderBy with asc and desc modifiers

Use `.orderBy()` to sort results. Import `asc` and `desc` from 'drizzle-orm' for sort direction. Examples: `orderBy(users.name)` (ascending), `orderBy(desc(users.name))` (descending), `orderBy(asc(users.name), desc(users.name2))` (multiple fields with mixed directions).

Cursor-based pagination pattern

Implement cursor pagination by passing cursor value to filter: `where(cursor ? gt(users.id, cursor) : undefined).limit(pageSize).orderBy(asc(users.id))`. Pass the cursor (last row's id from previous page) to get rows after it.

WITH clause for Common Table Expressions (CTEs)

Use CTEs to simplify complex queries by splitting them into smaller subqueries. Create a CTE with `db.$with('name').as(subquery)`, then use it with `db.with(sq).select().from(sq)`. 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);`.

CTEs with insert, update, delete statements

CTEs can contain INSERT, UPDATE, or DELETE statements instead of SELECT. Example with INSERT: `db.$with('sq').as(db.insert(users).values({name: 'John'}).returning())`. Example with UPDATE: `db.$with('sq').as(db.update(users).set({age: 25}).where(eq(users.name, 'John')).returning())`. Example with DELETE: `db.$with('sq').as(db.delete(users).where(eq(users.name, 'John')).returning())`.

Aliasing SQL expressions in CTEs

When selecting custom SQL expressions in CTEs, add aliases using `.as('name')` so they can be referenced in other CTEs or the main query. Example: `sql<string>`upper(${users.name})`.as('name')`. Without an alias, the field type becomes `DrizzleTypeError` and cannot be referenced, causing runtime errors.

Subqueries with .as() for embedding queries

Embed queries into other queries using `.as('name')` on a select query. Example: `const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(sq);`. Subqueries can be used anywhere a table can be used, including in joins.

Subqueries in joins

Subqueries can be used in join clauses. Example: `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));`. The subquery is treated as a table source for the join.

Aggregation functions: count, countDistinct, avg, avgDistinct

Import aggregation helpers from 'drizzle-orm': `count()`, `countDistinct()`, `avg()`, `avgDistinct()`. Use in select: `db.select({value: count()}).from(users)` or `db.select({value: count(users.id)}).from(users)`. These return pre-typed number or string values without needing sql<Type> templates.

Aggregation functions: sum, sumDistinct, max, min

Import aggregation functions from 'drizzle-orm': `sum()`, `sumDistinct()`, `max()`, `min()`. Examples: `sum(users.id)`, `sumDistinct(users.id)`, `max(users.id)`, `min(users.id)`. All return properly typed values from wrapped sql functions.

groupBy() clause for aggregations

Use `.groupBy(column)` when using aggregation functions with other columns in the select. Example: `db.select({age: users.age, count: sql<number>`count(...)`}).from(users).groupBy(users.age)`. When selecting aggregating functions and other columns together, always include a GROUP BY clause.

having() clause to filter aggregated results

Use `.having()` to filter groups based on aggregation results. The callback receives the aggregated columns. Example: `having(({count}) => gt(count, 1))` filters to groups where the count is greater than 1.

cast() for aggregation type safety

Use `cast(count(...) as int)` in sql templates to ensure aggregation results are typed correctly. Example: `sql<number>`cast(count(${users.id}) as int)`` casts the count to int for proper typing. Alternatively use `.mapWith(Number)` for runtime casting.

sql.as() for field aliasing

The sql.as() method allows explicitly specifying an alias for a custom field. Example: `sql`lower(usersTable.name)`.as('lower_name')` generates `... (usersTable.name) as lower_name ...` in the SQL output. This is useful for complex queries where you need to provide a clear and meaningful name for the field.

sql.raw() for unparameterized SQL

sql.raw() includes raw SQL statements without additional processing or escaping. It does not create parameterized values or escape tables/columns. Example: `sql.raw(`select * from users where id = ${12}`)` generates `select * from users where id = 12;` vs `sql`select * from users where id = ${12}`` generates `select * from users where id = ?; --> [12]`. You can use sql.raw() inside the sql function to include unescaped raw strings: `sql`select * from ${usersTable} where id = ${sql.raw(12)}`` generates `select * from "users" where id = 12;`

sql.fromList() for combining SQL chunks

sql.fromList() combines multiple SQL chunks (arrays of SQL parts) into a single SQL statement. This is useful when you need to aggregate SQL chunks using custom business logic before concatenation. Example: Building sqlChunks array with select, where, and multiple id conditions using a loop, then calling `sql.fromList(sqlChunks)` generates `select * from users where id = ? or id = ? or id = ? or id = ? or id = ?; --> [0, 1, 2, 3, 4]`

sql template basic usage

The sql template in Drizzle allows type-safe and parameterized queries. Tables and columns are automatically mapped to escaped SQL names. Dynamic parameters like ${id} are converted to ? placeholders with values passed separately to prevent SQL injection. Example: `sql`select * from ${usersTable} where ${usersTable.id} = ${id}`` generates `select * from "users" where "users"."id" = ?; --> [69]`

sql.join() for SQL chunks with custom separators

sql.join() concatenates SQL chunks with a specified separator string. This is similar to fromList but provides additional flexibility for handling spaces between SQL chunks or specifying custom separators. Example: `sql.join(sqlChunks, sql.raw(' '))` joins chunks with spaces, generating `select * from users where id = ? or id = ? or id = ? or id = ? or id = ?; --> [0, 1, 2, 3, 4]`

sql.append() for dynamic SQL construction

sql.append() adds new SQL chunks to an existing SQL object generated by the sql template. This allows incremental SQL construction with custom logic. Example: Starting with `sql`select * from users``, then appending ` where ` and id conditions in a loop produces `select * from users where id = ? or id = ? or id = ? or id = ? or id = ?; --> [0, 1, 2, 3, 4]`

sql.empty() for blank SQL object

sql.empty() initializes a blank SQL object that can be built incrementally by appending SQL chunks. This allows constructing queries dynamically with custom logic while maintaining access to all sql template features like parameterization, composition, and escaping. Example: Starting with `sql.empty()`, then appending select, where, and id condition chunks produces `select * from users where id = ? or id = ? or id = ? or id = ? or id = ?; --> [0, 1, 2, 3, 4]`

sql in ORDER BY clause

The sql template can be used in ORDER BY clauses for specific ordering functionality not available in Drizzle. Example: `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 GROUP BY and HAVING clauses for specific functionality. Example: `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;`

SQLiteSyncDialect.sqlToQuery() to convert sql to string and params

To obtain the query string and parameters from an sql template, you must specify the database dialect. Use SQLiteSyncDialect from 'drizzle-orm/sqlite-core' and call sqlToQuery() with the sql object. Example: `new SQLiteSyncDialect().sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`)` generates `select * from "users" where "users"."id" = ?; --> [ 12 ]`. Different databases have varying syntax for parameterization and escaping.

sql in partial SELECT queries

The sql template can be used in partial select queries to retrieve specific fields or columns. Example: `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

The sql template can be used directly in WHERE clauses to leverage SQL expressions not natively supported by Drizzle. Example basic filtering: `await db.select().from(usersTable).where(sql`${usersTable.id} = ${id}`)` generates `select * from "users" where "users"."id" = ?; --> [ 77 ]`. Example advanced fulltext search: `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<T> type parameter

sql<T> allows defining a custom type in Drizzle for use in places where fields require a specific type other than unknown. This is purely a helper for Drizzle and does not perform any runtime mapping. The type is determined by the developer since SQL queries are versatile and customizable. Example: `sql<string>`lower(${usersTable.id})`" ensures the response is typed as { lowerName: string }[] instead of { lowerName: unknown }[]

sql.mapWith() for runtime value mapping

sql.mapWith() provides runtime mapping for values passed from the database driver to Drizzle. It accepts different values to map responses. You can replicate a specific column mapping strategy by passing a column as the interface (e.g., `sql`...`.mapWith(usersTable.name)`) or pass a custom DriverValueDecoder implementation with mapFromDriverValue function, or pass Number directly. Example: `sql`...`.mapWith(Number)` applies Number mapping at runtime.

Example: Transaction returning a value

```ts const db = drizzle(...) const newBalance: number = await db.transaction(async (tx) => { await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan')); await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew')); const [account] = await tx.select({ balance: accounts.balance }).from(accounts).where(eq(users.name, 'Dan')); return account.balance; }); ``` This example shows how to return the new balance value after completing the transaction.

Basic transaction syntax in Drizzle

Drizzle ORM transactions are created using db.transaction(async (tx) => { ... }). The transaction callback receives a tx object that provides the same query methods as the database object. All SQL statements within the transaction are executed atomically—they either all commit or all rollback as a single unit.

Nested transactions and savepoints

Drizzle ORM supports savepoints through nested transactions. You can call tx.transaction() inside an outer transaction callback to create a nested transaction. Each nested level creates a savepoint, allowing partial rollback of inner transactions while preserving outer transaction progress.

Rollback in transactions

You can manually trigger a rollback by calling tx.rollback() within a transaction callback. This throws an exception that causes the entire transaction to rollback. This is useful for conditional rollbacks based on business logic, such as checking an account balance before proceeding with a transfer.

Return values from transactions

Transactions can return values to the caller. The value returned from the async callback in db.transaction() becomes the resolved value of the transaction promise. This allows you to return query results or computed values after all operations complete successfully.

Transactions with relational queries

Drizzle transactions support relational queries through the tx.query API. You can use all relational query features including nested relations with the 'with' clause inside a transaction callback.

SQLite transaction behavior options

SQLite transactions accept a configuration object as the second parameter to db.transaction(). The SQLiteTransactionConfig interface has one optional property: behavior, which accepts 'deferred', 'immediate', or 'exclusive'. The default is not specified in the documentation.

Transaction behavior modes in SQLite

Drizzle provides dialect-specific transaction configuration for SQLite. The 'behavior' option accepts three values: 'deferred' begins a deferred transaction, 'immediate' begins an immediate transaction with an immediate lock, and 'exclusive' begins an exclusive transaction. These control when SQLite acquires locks.

Example: Basic money transfer transaction

```ts const db = drizzle(...) await db.transaction(async (tx) => { await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan')); await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew')); }); ``` This example transfers $100 from Dan to Andrew atomically within a single transaction.

Example: Nested transaction with savepoint

```ts const db = drizzle(...) await db.transaction(async (tx) => { await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan')); await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew')); await tx.transaction(async (tx2) => { await tx2.update(users).set({ name: "Mr. Dan" }).where(eq(users.name, "Dan")); }); }); ``` This example shows a nested transaction that creates a savepoint for updating the user's name after the transfer operations.

Example: Conditional rollback with business logic

```ts const db = drizzle(...) await db.transaction(async (tx) => { const [account] = await tx.select({ balance: accounts.balance }).from(accounts).where(eq(users.name, 'Dan')); if (account.balance < 100) { tx.rollback() } await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, 'Dan')); await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, 'Andrew')); }); ``` This example checks if the account has sufficient balance before proceeding with the transfer, and rolls back the entire transaction if the balance is too low.

Example: Transaction with deferred behavior

```ts await db.transaction( async (tx) => { await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` }).where(eq(users.name, "Dan")); await tx.update(accounts).set({ balance: sql`${accounts.balance} + 100.00` }).where(eq(users.name, "Andrew")); }, { behavior: "deferred", } ); ``` This example shows how to configure a transaction with deferred behavior, which delays lock acquisition until the first SQL statement is executed.

Relational Queries upgrade path v1 to v2

If upgrading from Drizzle v0 to v1 and using Relational Queries, you must additionally upgrade to v2, which requires migrating both the relations definition and the queries themselves.

purpose of CTEs in SQL queries

Common table expressions (CTEs) help simplify complex queries by splitting them into smaller subqueries. They are defined using the WITH clause and can be referenced within the main query body.

update .set() values are parameterized automatically

All values provided to `.set()` are parameterized automatically. For example, `db.update(users).set({ name: "Mr. Dan" }).where(eq(users.name, "Dan"))` is translated to `update "users" set "name" = ? where "users"."name" = ?` with params `['Mr. Dan', 'Dan']`.

Give your agent this brain