query-api

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

Declaring views with inline query builder syntax

Views can be declared using inline query builder syntax with mysqlView. The view columns schema is automatically inferred from the query. Example: `export const userView = mysqlView("user_view").as((qb) => qb.select().from(user));` creates a view named user_view that selects from the user table.

Declaring views with standalone query builder

Views can be declared using a standalone QueryBuilder instance. Create a QueryBuilder with `const qb = new QueryBuilder();` and then use it in the view declaration: `mysqlView("user_view").as(qb.select().from(user))`. The view columns schema is automatically inferred.

Declaring views with raw SQL

When using raw SQL syntax not supported by the query builder, use the sql operator and explicitly specify view columns schema. Example: `mysqlView("new_yorkers", { id: int().primaryKey().autoincrement(), name: text().notNull(), cityId: int("city_id").notNull() }).as(sql\`select * from ${users} where ${eq(users.cityId, 1)}\`)`. The view columns must be manually declared when using raw sql.

View column schema inference with query builders

When views are created with either inlined or standalone query builders, view columns schema will be automatically inferred. When you use raw sql, you have to explicitly declare view columns schema.

Declaring existing views with .existing()

When you have read-only access to an existing view in the database, use the .existing() view configuration. This tells drizzle-kit to ignore the view and not generate a create view statement in the generated migration. Example: `mysqlView("trimmed_user", { id: int("id"), name: text("name"), email: text("email") }).existing();`

mysqlView function for declaring views

The mysqlView function is used to declare MySQL views. It accepts a view name as the first parameter and optionally a column schema object as the second parameter. It has an .as() method to specify the view definition and an .existing() method for existing views.

Generated SQL for views uses CREATE ALGORITHM = undefined SQL SECURITY definer

When Drizzle generates CREATE VIEW statements for MySQL, it uses the syntax: CREATE ALGORITHM = undefined SQL SECURITY definer VIEW `view_name` AS (select...).

Dynamic query building with .$dynamic()

By default, Drizzle query builders only allow invoking most methods once to conform to SQL structure. To enable dynamic query building where methods can be invoked multiple times, call .$dynamic() on a query builder. This is useful when building queries dynamically in shared functions.

Restriction on multiple where() calls in normal mode

In standard (non-dynamic) query builder mode, you cannot invoke .where() multiple times. For example, calling .where() twice in sequence causes a type error because there can only be one WHERE clause in a SELECT statement.

MySQL dynamic query builder types

For MySQL dialect, the following types support dynamic query building: MySqlSelect, MySqlSelectQueryBuilder, MySqlInsert, MySqlUpdate, and MySqlDelete. These are generic types designed to be used as parameters when implementing functions that dynamically enhance query builders.

Using generic types with dynamic query building

When implementing dynamic query building functions, use generic parameters constrained to query builder types like MySqlSelect. This allows the function to modify the query builder and return an enhanced result type. For example, a function can add joins while preserving the generic type structure.

QueryBuilder types for standalone instances

Standalone query builder instances use types ending in QueryBuilder, such as PgSelectQueryBuilder, MySqlSelectQueryBuilder, and SQLiteSelectQueryBuilder. DB query builders are subclasses of these types, so either can be used as generic parameters in dynamic query building functions.

Dynamic query building example with pagination

Example of a dynamic query building function that adds pagination: ```ts function withPagination<T extends MySqlSelect>( 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); // ✅ OK ```

Chaining multiple dynamic query builder operations

Multiple functions can be chained together when working with dynamic query builders. Each function receives the query builder from the previous operation and can apply additional modifications, with the generic type allowing proper typing throughout the chain.

Enable dynamic mode with $dynamic() for repeated method calls

To build queries dynamically and invoke the same method multiple times, call .$dynamic() on a query builder. This removes the restriction of invoking methods only once and enables shared functions to enhance queries.

Query builders can only invoke methods once by default

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

Dynamic query building with pagination example

This example shows a withPagination function that takes a dynamic query builder and adds LIMIT and OFFSET clauses: ```ts function withPagination<T extends CockroachSelect>( 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); // ✅ OK ```

Generic dynamic query builders for composing query enhancements

Dynamic query builder functions can be made generic over CockroachSelect and similar types to modify the result type of the query builder inside the function, for example by adding a join: ```ts function withFriends<T extends CockroachSelect>(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 query builder generic types for all query types

The following generic types can be used for dynamic query building with their corresponding query builders: | Query | Type | |-------|------| | Select | `CockroachSelect` / `CockroachSelectQueryBuilder` | | Insert | `CockroachInsert` | | Update | `CockroachUpdate` | | Delete | `CockroachDelete` | The `...QueryBuilder` types are for usage with standalone query builder instances, and DB query builders are subclasses of them so can be used as well.

Using standalone query builder with dynamic mode

Standalone query builder instances can be used with dynamic query building by importing from drizzle-orm/cockroach-core: ```ts import { QueryBuilder } from 'drizzle-orm/cockroach-core'; function withFriends<T extends CockroachSelectQueryBuilder>(qb: T) { return qb.leftJoin(friends, eq(friends.userId, users.id)); } const qb = new QueryBuilder(); let query = qb.select().from(users).where(eq(users.id, 1)).$dynamic(); query = withFriends(query); ```

CockroachDB $count utility function

Drizzle provides a $count query utility function for CockroachDB. This function can be used to count rows matching specified conditions in a query.

Transaction basics in Drizzle

A transaction is a grouping of one or more SQL statements that interact with a database. The transaction in its entirety can commit to a database as a single logical unit or rollback (become undone) as a single logical unit. In Drizzle ORM, you run transactions by calling db.transaction() with an async callback that receives a transaction object (tx) as a parameter.

Basic transaction example in Drizzle

Example of running multiple update statements in a transaction: 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')); });

Nested transaction example

Example of using nested transactions with savepoints: 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")); }); });

Rolling back transactions in Drizzle

You can call tx.rollback() inside a transaction to rollback the entire transaction. This is useful for conditional logic where you want to abort the transaction based on business rules. When rollback is called, an exception is thrown that undoes all changes made in the transaction.

Rollback example with business logic

Example of rolling back a transaction based on a condition: 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) { // This throws an exception that rollbacks the transaction. 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')); });

Returning values from transactions

Transactions in Drizzle can return values. The return value of the async callback passed to db.transaction() becomes the return value of the transaction call.

Savepoints and nested transactions in Drizzle

Drizzle ORM supports savepoints with nested transactions API. You can nest transactions by calling tx.transaction() inside a transaction callback, creating a savepoint.

Return value from transaction example

Example of returning a value from a transaction: 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; });

Using transactions with relational queries

Transactions work with relational queries in Drizzle. You can use tx._query within a transaction callback to run relational queries with joins and relations.

CockroachDB transaction configuration

Drizzle provides dialect-specific transaction configuration APIs. For CockroachDB, the configuration interface is CockroachTransactionConfig with the following options: - isolationLevel (optional): string, one of "read uncommitted", "read committed", "repeatable read", or "serializable" - accessMode (optional): string, one of "read only" or "read write" - deferrable (optional): boolean These options are passed as a second argument object to db.transaction().

CockroachDB transaction configuration example

Example of using CockroachDB-specific transaction configuration: 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")); }, { isolationLevel: "read committed", accessMode: "read write", deferrable: true, } );

LEFT JOIN syntax with type safety

Use leftJoin() method on a select query. Provide the table to join and a callback that receives the source table and joined table, returning an eq() condition. Example: const result = await citiesTable.select().leftJoin(usersTable, (cities, users) => eq(cities.userId, users.id)).where((cities, users) => eq(cities.id, 1)).execute();

PostgreSQL table definition with enums and indexes

In Drizzle v0.11.0, PostgreSQL tables extend PgTable and can include enum types. Enums are created with createEnum({ alias: 'enumName', values: ['value1', 'value2'] }). Indexes are declared as class properties using uniqueIndex(). Example: export class CitiesTable extends PgTable<CitiesTable> { id = this.serial("id").primaryKey(); name = this.varchar("name", { size: 256 }); popularity = this.type(popularityEnum, "popularity"); public tableName(): string { return 'cities'; } }

Basic database connection and select query

To connect to a PostgreSQL database and run a typed select query: import { drizzle } from 'drizzle-orm'; const db = await drizzle.connect("postgres://user:password@host:port/db"); const usersTable = new UsersTable(db); const users = await usersTable.select().execute();

WHERE clause with filters in select queries

Use eq() function for single condition filtering, and() for combining multiple conditions with AND logic, or() for combining with OR logic. Example: await table.select().where(eq(table.id, 42)).execute(); await table.select().where(and([eq(table.id, 42), eq(table.name, "Dan")])).execute(); await table.select().where(or([eq(table.id, 42), eq(table.id, 1)])).execute();

Partial select, limit, offset, and orderBy in queries

Partial select allows selecting specific columns mapped to new names. Limit/offset pagination is supported. Order by accepts a callback returning the column and an Order enum (Order.ASC or Order.DESC). Example: const result = await table.select({ mapped1: table.id, mapped2: table.name }).execute(); await table.select().limit(10).offset(10).execute(); await table.select().orderBy((table) => table.name, Order.ASC).execute();

INSERT operations in Drizzle

Insert single record: await usersTable.insert({ name: "Andrew", createdAt: new Date() }).execute(); Insert multiple records: await usersTable.insertMany([{ name: "Andrew", createdAt: new Date() }, { name: "Dan", createdAt: new Date() }]).execute();

UPDATE and DELETE operations in Drizzle

Update with where clause: await usersTable.update().where(eq(usersTable.name, 'Dan')).set({ name: 'Mr. Dan' }).execute(); Delete with where clause: await usersTable.delete().where(eq(usersTable.name, 'Dan')).execute();

Relational Query API where() operators now passed as helper parameter

In Drizzle v0.28.6, SQL operators used in Relational Query API where() filters can now be accessed as a second parameter to the where callback function. Before: await db.users.findFirst({ where: (table, _) => inArray(table.id, [ ... ]) }) - required importing inArray separately. After: await db.users.findFirst({ where: (table, { inArray }) => inArray(table.id, [ ... ]) }) - operators available as destructured helpers.

PostgreSQL arrayOverlaps operator example

const overlaps = await db.select({ id: posts.id }).from(posts) .where(arrayOverlaps(posts.tags, ['Typescript', 'ORM']));

LibSQL batch API support in Drizzle

Drizzle v0.28.6 added support for LibSQL batch API. The db.batch() method accepts an array of queries and returns results for each query in the same order. All possible builders that can be used inside db.batch include: db.all(), db.get(), db.values(), db.run(), db.query.<table>.findMany(), db.query.<table>.findFirst(), db.select()..., db.update()..., db.delete()..., db.insert()...

LibSQL batch API example with multiple operations

Example showing LibSQL batch API usage: const batchResponse = await db.batch([ db.insert(usersTable).values({ id: 1, name: 'John' }).returning({ id: usersTable.id, }), db.update(usersTable).set({ name: 'Dan' }).where(eq(usersTable.id, 1)), db.query.usersTable.findMany({}), db.select().from(usersTable).where(eq(usersTable.id, 1)), db.select({ id: usersTable.id, invitedBy: usersTable.invitedBy }).from( usersTable, ), ]); The batch returns a tuple with results from each query in order.

toSQL() method added to Relational Query API

Drizzle v0.28.6 added the .toSQL() method to Relational Query API calls. This allows retrieving the SQL representation of a relational query: const query = db.query.usersTable.findFirst().toSQL();

PostgreSQL array operators: arrayContains, arrayContained, arrayOverlaps

Drizzle v0.28.6 added three new PostgreSQL operators for Array columns: arrayContains() - checks if array contains values, arrayContained() - checks if array is contained within values, arrayOverlaps() - checks if arrays overlap. All three operators take a column and an array or subquery as arguments.

PostgreSQL arrayContained operator example

const contained = await db.select({ id: posts.id }).from(posts) .where(arrayContained(posts.tags, ['Typescript', 'ORM']));

D1 Batch API usage example

Example of D1 Batch API: const batchResponse = await db.batch([ db.insert(usersTable).values({ id: 1, name: 'John' }).returning({ id: usersTable.id }), db.update(usersTable).set({ name: 'Dan' }).where(eq(usersTable.id, 1)), db.query.usersTable.findMany({}), db.select().from(usersTable).where(eq(usersTable.id, 1)), db.select({ id: usersTable.id, invitedBy: usersTable.invitedBy }).from(usersTable), ]);

Query builder method invocation restriction in v0.29.0

Starting from Drizzle ORM v0.29.0, query builder methods can only be invoked once by default to conform to SQL semantics. For example, .where() can only be called once on a SELECT statement. Calling .where() twice results in a type error.

Dynamic query building with $dynamic() method

To build queries dynamically in v0.29.0 and later, call .$dynamic() on a query builder to remove the restriction of invoking methods only once. This allows shared functions to take a query builder and enhance it by adding additional clauses like LIMIT and OFFSET.

Read Replicas support with withReplicas function

Drizzle ORM v0.29.0 introduces withReplicas() function for all dialects. By default, read operations use a random read replica and write operations use the main instance. Syntax: const db = withReplicas(primaryDb, [read1, read2]). You can specify custom logic for selecting replicas: withReplicas(primaryDb, [read1, read2], (replicas) => { ... }). Access the primary database with db.$primary.select().

Set operators support in v0.29.0

Drizzle ORM v0.29.0 adds support for set operators: UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL. They can be used via import approach (import { union } from 'drizzle-orm/pg-core') or builder approach (.union()). Example: await union(allUsersQuery, allCustomersQuery) or await db.select().from(users).union(db.select().from(customers)).

D1 Batch API support in v0.29.0

Drizzle ORM v0.29.0 adds support for Cloudflare D1 Batch API. Use: db.batch([...]) to execute multiple queries in a batch. Supported builders: db.all(), db.get(), db.values(), db.run(), db.query.<table>.findMany(), db.query.<table>.findFirst(), db.select()..., db.update()..., db.delete()..., db.insert()... Each builder in the batch array returns typed results corresponding to its operation.

Dynamic query building example with withPagination

Example of dynamic query building: function withPagination<T extends PgSelect>(qb: T, page: number, pageSize: number = 10) { return qb.limit(pageSize).offset(page * pageSize); } const query = db.select().from(users).where(eq(users.id, 1)).$dynamic(); withPagination(query, 1); // OK. Without $dynamic(), the query builder type error occurs.

Read replicas with weighted selection example

Example of custom weighted read replica selection: const db = withReplicas(primaryDb, [read1, read2], (replicas) => { const weight = [0.7, 0.3]; let cumulativeProbability = 0; const rand = Math.random(); for (const [i, replica] of replicas.entries()) { cumulativeProbability += weight[i]!; if (rand < cumulativeProbability) return replica; } return replicas[0]! });

Set operators import and builder approach

Set operators can be used in two ways: Import approach: import { union } from 'drizzle-orm/pg-core'; const result = await union(allUsersQuery, allCustomersQuery). Builder approach: const result = await db.select().from(users).union(db.select().from(customers)).

count() aggregate function

The count() aggregate function can be used with no arguments to count all rows, or with a column argument to count specific column values. Example: await db.select({ value: count() }).from(users); or await db.select({ value: count(users.id) }).from(users);. Equivalent SQL using sql template: sql`count('*')`.mapWith(Number) or sql`count(${users.id})`.mapWith(Number)

countDistinct() aggregate function

The countDistinct() aggregate function counts distinct values in a column. Example: await db.select({ value: countDistinct(users.id) }).from(users);. Equivalent SQL: sql`count(distinct ${users.id})`.mapWith(Number)

avg() aggregate function

The avg() aggregate function calculates the average of values in a column. Example: await db.select({ value: avg(users.id) }).from(users);. Equivalent SQL: sql`avg(${users.id})`.mapWith(String)

avgDistinct() aggregate function

The avgDistinct() aggregate function calculates the average of distinct values in a column. Example: await db.select({ value: avgDistinct(users.id) }).from(users);. Equivalent SQL: sql`avg(distinct ${users.id})`.mapWith(String)

sum() aggregate function

The sum() aggregate function calculates the sum of values in a column. Example: await db.select({ value: sum(users.id) }).from(users);. Equivalent SQL: sql`sum(${users.id})`.mapWith(String)

Give your agent this brain