query api

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

Default query builder constraint - methods invoked once

By default, query builders in Drizzle conform to SQL and only allow most methods to be invoked once. For example, in a SELECT statement there is only one WHERE clause, so .where() can only be invoked once. Attempting to invoke the same method multiple times results in a type error.

Enable dynamic mode with $dynamic()

To build queries dynamically and remove the restriction of invoking methods only once, call .$dynamic() on a query builder. This is useful when you have shared functions that take a query builder and enhance it.

QueryBuilder types are for standalone query builder instances

The ...QueryBuilder types (such as PgSelectQueryBuilder) are for usage with standalone query builder instances created with 'new QueryBuilder()'. DB query builders are subclasses of them, so you can use the ...QueryBuilder types as well.

Generic query builder functions can modify result type

When using generic query builder functions in dynamic mode with types like PgSelect, you can modify the result type of the query builder inside the function, for example by adding a join operation.

Example of dynamic query building with pagination

function withPagination<T extends PgSelect>( qb: T, page: number = 1, pageSize: number = 10, ) { return qb.limit(pageSize).offset((page - 1) * pageSize); } const query = db.select().from(users).where(eq(users.id, 1)); const dynamicQuery = query.$dynamic(); withPagination(dynamicQuery, 1);

Example of chaining dynamic query building functions

function withFriends<T extends PgSelect>(qb: T) { return qb.leftJoin(friends, eq(friends.userId, users.id)); } let query = db.select().from(users).where(eq(users.id, 1)).$dynamic(); query = withFriends(query);

$dynamic() mode for query building

By default, Drizzle query builders enforce SQL conformance by allowing most methods to be invoked only once. For example, you can only call .where() once on a SELECT statement. To remove this restriction and enable dynamic query building, call .$dynamic() on a query builder. This allows multiple invocations of the same method and is useful for shared functions that enhance queries, such as adding pagination or joins.

Dynamic query building use cases

Dynamic query building is useful when you want to build a query dynamically, such as in a shared function that takes a query builder and enhances it. Without $dynamic() mode, attempting to invoke methods multiple times results in a type error. With $dynamic() enabled, you can modify and enhance query builders across multiple function calls.

Generic query builder types for dynamic mode

Drizzle provides specific types designed for use as generic parameters in dynamic query building: CockroachSelect or CockroachSelectQueryBuilder for SELECT queries, CockroachInsert for INSERT queries, CockroachUpdate for UPDATE queries, and CockroachDelete for DELETE queries. These types can only be used in dynamic mode.

QueryBuilder types for standalone query builder instances

For standalone query builder instances (not DB query builders), use the corresponding QueryBuilder types: CockroachSelectQueryBuilder for SELECT, CockroachInsert for INSERT, CockroachUpdate for UPDATE, and CockroachDelete for DELETE. DB query builders are subclasses of these QueryBuilder types and can be used with functions expecting the base QueryBuilder types.

Dynamic query builder example: withPagination

Example function that adds pagination to a query: function withPagination<T extends CockroachSelect>(qb: T, page: number = 1, pageSize: number = 10) { return qb.limit(pageSize).offset((page - 1) * pageSize); } Usage: const dynamicQuery = db.select().from(users).where(eq(users.id, 1)).$dynamic(); withPagination(dynamicQuery, 1);

Dynamic query builder example: withFriends

Example function that adds a join to a query: function withFriends<T extends CockroachSelect>(qb: T) { return qb.leftJoin(friends, eq(friends.userId, users.id)); } Usage: let query = db.select().from(users).where(eq(users.id, 1)).$dynamic(); query = withFriends(query);

Type error when missing $dynamic() mode

Calling a function that modifies a query builder without first enabling $dynamic() mode results in a type error. For example, withPagination(query, 1) fails with a type error if query was not created with .$dynamic(). The function must receive a query builder in dynamic mode to allow method invocations.

Prepared statement with prepare() method

To create a prepared statement in Drizzle, call the prepare() method on a query with a statement name string. The prepared statement can then be executed multiple times with execute() without recompiling. Example: db.select().from(customers).prepare("statement_name") creates a prepared statement that can be executed multiple times with prepared.execute().

sql.placeholder() for dynamic prepared statement values

Use sql.placeholder(paramName) to embed dynamic runtime values in prepared statements. The parameter name is used when calling execute(). Example: where(eq(customers.id, sql.placeholder('id'))).prepare("p1") allows execute({ id: 10 }) and execute({ id: 12 }) with different id values.

Prepared statement performance benefit

Prepared statements in Drizzle perform SQL concatenation once on the ORM side, allowing the database driver to reuse the precompiled binary SQL instead of parsing the query repeatedly. This provides extreme performance benefits especially for large SQL queries.

sql.placeholder() with SQL expressions

sql.placeholder() can be used within SQL template expressions. Example: sql`lower(${customers.name}) like ${sql.placeholder('name')}`.prepare("p2") creates a prepared statement where the placeholder is embedded in a SQL function call.

Give your agent this brain