query api - advanced sql

29 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Dynamic query building use case

Dynamic query building is useful when creating shared functions that take a query builder and enhance it by adding clauses conditionally. Without dynamic mode, calling methods multiple times on the same query builder results in a type error.

Dynamic query building preserves type modification

When building queries dynamically using generic types like PgSelect, MySqlSelect, or SQLiteSelect, you can modify the result type of the query builder inside a function, for example by adding a join. The generic types are specifically designed to support dynamic query building and can only be used in dynamic mode.

SQLite dynamic query builder types

For SQLite, the types that can be used as generic parameters in dynamic query building are SQLiteSelect and SQLiteSelectQueryBuilder for SELECT queries, SQLiteInsert for INSERT queries, SQLiteUpdate for UPDATE queries, and SQLiteDelete for DELETE queries.

QueryBuilder types for standalone query builder instances

The ...QueryBuilder types (e.g., PgSelectQueryBuilder, MySqlSelectQueryBuilder, SQLiteSelectQueryBuilder) are for usage with standalone query builder instances created with new QueryBuilder(). DB query builders are subclasses of them, so both can be used in generic constraints.

Default query builder behavior restricts repeated method calls

By default, Drizzle query builders conform to SQL and only allow invoking methods once. For example, a SELECT statement can have only one WHERE clause, so calling .where() multiple times results in a type error.

Dynamic query building example with pagination

A withPagination function that adds LIMIT and OFFSET clauses can be implemented by accepting a generic query builder type and calling .limit(pageSize).offset((page - 1) * pageSize). The query builder must be in dynamic mode by calling .$dynamic() before passing it to the function.

Dynamic query building enables multiple method invocations

By default, query builders in Drizzle restrict most methods to be invoked only once to conform to SQL structure. For example, .where() can only be invoked once in a SELECT statement. Dynamic mode, enabled by calling .$dynamic() on a query builder, removes these restrictions and allows methods to be invoked multiple times, enabling dynamic query construction when building queries across shared functions.

Use .$dynamic() to enable multiple method invocations on query builders

Call .$dynamic() on a query builder to enable dynamic mode. This allows you to invoke methods like .where(), .limit(), and .offset() multiple times. Example: const dynamicQuery = db.select().from(users).where(eq(users.id, 1)).$dynamic(); dynamicQuery.limit(10).offset(5);

Dynamic query building with generic functions example

When building queries dynamically with generic functions, use specific Drizzle types as generic parameters. Example: function withPagination<T extends CockroachSelect>(qb: T, page: number = 1, pageSize: number = 10) { return qb.limit(pageSize).offset((page - 1) * pageSize); } The function can then be called with .$dynamic() query builders to add pagination clauses.

Generic types for dynamic query building in Cockroach

The following types are used for dynamic query building generic parameters: Select uses CockroachSelect or CockroachSelectQueryBuilder; Insert uses CockroachInsert; Update uses CockroachUpdate; Delete uses CockroachDelete. The ...QueryBuilder variants are for standalone query builder instances, while DB query builders are subclasses of them and can also be used.

Joining tables in dynamic query functions

Generic dynamic query functions can modify the result type of the query builder by adding operations like joins. Example: function withFriends<T extends CockroachSelect>(qb: T) { return qb.leftJoin(friends, eq(friends.userId, users.id)); } This allows the query builder's type to evolve as transformations are applied.

Dynamic query building incompatibility with static mode

A query builder not in dynamic mode cannot be passed to functions expecting dynamic query builders. Attempting to pass a regular query builder to a function typed for dynamic query builders will result in a type error. The .$dynamic() method must be called before passing to such functions.

QueryBuilder standalone import for dynamic queries

The QueryBuilder class can be imported from 'drizzle-orm/cockroach-core' for use with standalone query builder instances. Example: import { QueryBuilder } from 'drizzle-orm/cockroach-core'; const qb = new QueryBuilder(); let query = qb.select().from(users).where(eq(users.id, 1)).$dynamic();

Set operations supported by SQLite through Drizzle

Drizzle supports six set operations for combining results from multiple query blocks: UNION (omits duplicates), UNION ALL (retains duplicates), INTERSECT (common rows only, omits duplicates), INTERSECT ALL (common rows only, retains duplicates), EXCEPT (rows in first query but not second, omits duplicates), and EXCEPT ALL (rows in first query but not second, retains duplicates).

UNION operation example with import pattern

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);

UNION operation example with builder pattern

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);

UNION ALL operation in Drizzle

UNION ALL combines all results from two query blocks into a single result while retaining duplicates. It can be used via import-pattern with the unionAll() function or builder-pattern with the .unionAll() method chained on a select query.

UNION ALL operation example with import pattern

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);

UNION ALL operation example with builder pattern

import { onlineSales, inStoreSales } from './schema' const result = await db .select({ transaction: onlineSales.transactionId }) .from(onlineSales) .unionAll( db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales) );

INTERSECT operation example with import pattern

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);

INTERSECT operation example with builder pattern

import { depA, depB } from './schema' const result = await db .select({ courseName: depA.courseName }) .from(depA) .intersect(db.select({ courseName: depB.courseName }).from(depB));

INTERSECT ALL operation in Drizzle

INTERSECT ALL combines only those rows which the results of two query blocks have in common, retaining duplicates. It can be used via import-pattern with the intersectAll() function or builder-pattern with the .intersectAll() method chained on a select query.

INTERSECT ALL operation example with import pattern

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);

INTERSECT ALL operation example with builder pattern

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) );

EXCEPT operation example with import pattern

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);

EXCEPT operation example with builder pattern

import { depA, depB } from './schema' const result = await db .select({ courseName: depA.projectsName }) .from(depA) .except(db.select({ courseName: depB.projectsName }).from(depB));

EXCEPT ALL operation in Drizzle

EXCEPT ALL returns all results from the first query block which are not also present in the second query block, retaining duplicates. It can be used via import-pattern with the exceptAll() function or builder-pattern with the .exceptAll() method chained on a select query.

EXCEPT ALL operation example with import pattern

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);

EXCEPT ALL operation example with builder pattern

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) );

Give your agent this brain