query-builder

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

Drizzle subquery composition example

Subqueries can be separated into variables: const subquery = db.select().from(internalStaff).leftJoin(customUser, eq(internalStaff.userId, customUser.id)).as('internal_staff'); const mainQuery = await db.select().from(ticket).leftJoin(subquery, eq(subquery.internal_staff.userId, ticket.staffId));

Drizzle SQL-like query example with select and join

Example SQL-like query: await db.select().from(posts).leftJoin(comments, eq(posts.id, comments.post_id)).where(eq(posts.id, 10)) generates SQL: SELECT * FROM "posts" LEFT JOIN "comments" ON "posts"."id" = "comments"."post_id" WHERE "posts"."id" = 10

SQL-like syntax in Drizzle mirrors SQL structure

Drizzle's SQL-like syntax is designed to be familiar to anyone who knows SQL. The framework embraces SQL as its core, minimizing the learning curve by allowing developers to write SQL-familiar code that translates directly to SQL queries.

SQL-like queries support select, insert, update, delete and advanced features

SQL-like queries support select, insert, update, delete operations, aliases, WITH clauses, subqueries, and prepared statements.

Drizzle insert operation example

Insert example: await db.insert(users).values({ email: 'user@gmail.com' }) generates SQL: INSERT INTO "users" ("email") VALUES ('user@gmail.com')

Drizzle supports SQL-like and relational query syntax

Drizzle provides two ways to query the database: SQL-like syntax and Relational Syntax. Users can choose which approach suits their needs.

Drizzle update operation example

Update example: await db.update(users).set({ email: 'user@gmail.com' }).where(eq(users.id, 1)) generates SQL: UPDATE "users" SET "email" = 'user@gmail.com' WHERE "users"."id" = 1

Drizzle composable filters example

Example of composable filters: const filters: SQL[] = []; if (name) filters.push(ilike(products.name, name)); if (category) filters.push(eq(products.category, category)); if (maxPrice) filters.push(lte(products.price, maxPrice)); return db.select().from(products).where(and(...filters));

Drizzle delete operation example

Delete example: await db.delete(users).where(eq(users.id, 1)) generates SQL: DELETE FROM "users" WHERE "users"."id" = 1

Composable WHERE filters in Drizzle

Filters can be composed independently from the main query. Multiple filter conditions can be collected in an array of SQL type and combined with and() operator before passing to the where() clause.

Dynamic query building with generic functions

Generic functions for dynamic query building should extend the appropriate query builder type (CockroachSelect, CockroachInsert, CockroachUpdate, or CockroachDelete). This allows the function to accept query builders in dynamic mode and modify their result types by adding operations like joins. The generic parameter preserves the evolving type of the query as modifications are chained.

Standalone query builder with dynamic mode

Standalone query builders can be used with dynamic mode. Import QueryBuilder from 'drizzle-orm/cockroach-core', create an instance with new QueryBuilder(), then call .$dynamic() on queries: const qb = new QueryBuilder(); let query = qb.select().from(users).where(eq(users.id, 1)).$dynamic();

Dynamic query building example with pagination

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

Dynamic query building with joins example

This example shows chaining dynamic query modifications through generic functions: 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); The result type evolves as functions add operations like joins.

Standalone query builder types extend regular types

The ...QueryBuilder types (CockroachSelectQueryBuilder, etc.) are for use with standalone query builder instances created with new QueryBuilder(). DB query builders are subclasses of these types, so standalone ...QueryBuilder types can be used as generic parameter constraints that accept both standalone and DB query builders.

Dynamic mode query builder types for CockroachDB

For dynamic query building with CockroachDB, use these generic types: CockroachSelect or CockroachSelectQueryBuilder for SELECT queries, CockroachInsert for INSERT, CockroachUpdate for UPDATE, and CockroachDelete for DELETE. These types are specifically designed to support dynamic mode and can only be used after calling .$dynamic().

Multiple where() calls type error in static mode

In standard (non-dynamic) query builder mode, invoking .where() multiple times on the same query results in a type error. The second .where() call will not compile. This restriction ensures conventional queries are built correctly by preventing accidental multiple condition definitions.

CockroachDB $count utility function

Drizzle provides a $count utility function for CockroachDB queries. This utility is used in CockroachDB query operations.

Relational query API example with nested relations

Example of fetching nested relational data using the Queries API: ```ts const result = await db._query.users.findMany({ with: { posts: true }, }); ```

SQL-like query API in Drizzle

Drizzle embraces SQL and is built to be SQL-like at its core, providing zero to no learning curve if you know SQL. It gives you access to the full power of SQL. The SQL-like query API covers 100% of query needs.

SQL query example with joins

Example of SQL-like query with left join: ```typescript await db .select() .from(countries) .leftJoin(cities, eq(cities.countryId, countries.id)) .where(eq(countries.id, 10)) ```

Prepared statement example basic usage

Example of using prepared statements: ```typescript const db = drizzle(...); const prepared = db.select().from(customers).prepare("statement_name"); const res1 = await prepared.execute(); const res2 = await prepared.execute(); const res3 = await prepared.execute(); ``` This example shows defining a prepared statement on a select query from customers, then executing it three times to reuse the precompiled query.

arrayContained operator - array reverse containment

The arrayContained operator tests that the list passed as the second argument contains all elements of a column or expression. Usage: db.select({ id: posts.id }).from(posts).where(arrayContained(posts.tags, ['Typescript', 'ORM'])) generates select "id" from "posts" where "posts"."tags" <@ {Typescript,ORM}.

arrayContains operator - array element containment

The arrayContains operator tests that a column or expression contains all elements of a provided list. Usage: db.select({ id: posts.id }).from(posts).where(arrayContains(posts.tags, ['Typescript', 'ORM'])) generates select "id" from "posts" where "posts"."tags" @> {Typescript,ORM}. The operator also supports subqueries: arrayContains(posts.tags, db.select({ tags: posts.tags }).from(posts).where(eq(posts.id, 1))) generates where "posts"."tags" @> (select "tags" from "posts" where "posts"."id" = 1).

lte operator - less than or equal comparison

The lte operator filters for values less than or equal to a specified value. Usage: db.select().from(table).where(lte(table.column, 5)) generates SELECT * FROM "table" WHERE "table"."column" <= 5. The lte operator also supports comparing two columns: lte(table.column1, table.column2) generates WHERE "table"."column1" <= "table"."column2".

gte operator - greater than or equal comparison

The gte operator filters for values greater than or equal to a specified value. Usage: db.select().from(table).where(gte(table.column, 5)) generates SELECT * FROM "table" WHERE "table"."column" >= 5. The gte operator also supports comparing two columns: gte(table.column1, table.column2) generates WHERE "table"."column1" >= "table"."column2".

notBetween operator - range exclusion

The notBetween operator filters for values outside a specified range. Usage: db.select().from(table).where(notBetween(table.column, 2, 7)) generates SELECT * FROM "table" WHERE "table"."column" NOT BETWEEN 2 AND 7.

ilike operator - case-insensitive pattern matching

The ilike operator performs case-insensitive pattern matching. Usage: db.select().from(table).where(ilike(table.column, "%llo wor%")) generates SELECT * FROM "table" WHERE "table"."column" ILIKE '%llo wor%'.

arrayOverlaps operator - array element overlap

The arrayOverlaps operator tests that a column or expression contains any elements of the list passed as the second argument. Usage: db.select({ id: posts.id }).from(posts).where(arrayOverlaps(posts.tags, ['Typescript', 'ORM'])) generates select "id" from "posts" where "posts"."tags" && {Typescript,ORM}.

notInArray operator - value not in list

The notInArray operator filters for values not present in a provided array. Usage: db.select().from(table).where(notInArray(table.column, [1, 2, 3, 4])) generates SELECT * FROM "table" WHERE "table"."column" NOT IN (1, 2, 3, 4). The notInArray operator also supports subqueries: const query = db.select({ data: table2.column }).from(table2); db.select().from(table).where(notInArray(table.column, query)) generates WHERE "table"."column" NOT IN (SELECT "table2"."column" FROM "table2").

eq operator - equal comparison

The eq operator filters for values equal to a specified value. Usage: db.select().from(table).where(eq(table.column, 5)) generates SELECT * FROM "table" WHERE "table"."column" = 5. The eq operator also supports comparing two columns: eq(table.column1, table.column2) generates WHERE "table"."column1" = "table"."column2".

inArray operator - value in list

The inArray operator filters for values present in a provided array. Usage: db.select().from(table).where(inArray(table.column, [1, 2, 3, 4])) generates SELECT * FROM "table" WHERE "table"."column" IN (1, 2, 3, 4). The inArray operator also supports subqueries: const query = db.select({ data: table2.column }).from(table2); db.select().from(table).where(inArray(table.column, query)) generates WHERE "table"."column" IN (SELECT "table2"."column" FROM "table2").

ne operator - not equal comparison

The ne operator filters for values not equal to a specified value. Usage: db.select().from(table).where(ne(table.column, 5)) generates SELECT * FROM "table" WHERE "table"."column" <> 5. The ne operator also supports comparing two columns: ne(table.column1, table.column2) generates WHERE "table"."column1" <> "table"."column2".

insert values basic syntax

Insert data using db.insert(table).values({column: value}). Example: await db.insert(users).values({ name: 'Andrew' });

onConflictDoNothing cancel insert on conflict

Use .onConflictDoNothing() to cancel an insert if there is a conflict. You can optionally specify the conflict target: await db.insert(users).values({ id: 1, name: 'John' }).onConflictDoNothing(); or await db.insert(users).values({ id: 1, name: 'John' }).onConflictDoNothing({ target: users.id });

onConflictDoUpdate update on conflict

Use .onConflictDoUpdate() to update a row if there is a conflict. Example: await db.insert(users).values({ id: 1, name: 'Dan' }).onConflictDoUpdate({ target: users.id, set: { name: 'John' } });

infer insert type from table

Use typeof table.$inferInsert to get the insert type for a table. Example: type NewUser = typeof users.$inferInsert;

insert returning clause

Return inserted rows using .returning(). Example: await db.insert(users).values({ name: "Dan" }).returning(); You can also return only specific columns: await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });

insert with CTE using with clause

Use the with clause with insert to define common table expressions. Example: const userCount = db.$with('user_count').as(db.select({ value: sql`count(*)`.as('value') }).from(users)); const result = await db.with(userCount).insert(users).values([{ username: 'user1', admin: sql`((select * from ${userCount}) = 0)` }]).returning({ admin: users.admin });

onConflictDoUpdate with targetWhere clause

Use targetWhere in onConflictDoUpdate to add a WHERE clause for partial indexes on the conflict target. Example: await db.insert(employees).values({ employeeId: 123, name: 'John Doe' }).onConflictDoUpdate({ target: employees.employeeId, targetWhere: sql`name <> 'John Doe'`, set: { name: sql`excluded.name` } });

onConflictDoUpdate with setWhere clause

Use setWhere in onConflictDoUpdate to add a WHERE clause for the update part. Example: await db.insert(employees).values({ employeeId: 123, name: 'John Doe' }).onConflictDoUpdate({ target: employees.employeeId, set: { name: 'John Doe' }, setWhere: sql`name <> 'John Doe'` });

insert values are parameterized automatically

All values provided to .values() are parameterized automatically. For example, await db.insert(users).values({ name: 'Andrew' }); translates to insert into "users" ("id", "name") values (default, $1) -- params: ['Andrew'].

onConflictDoUpdate with composite keys

Use onConflictDoUpdate with composite indexes or composite primary keys by passing an array of columns to target. Example: await db.insert(users).values({ firstName: 'John', lastName: 'Doe' }).onConflictDoUpdate({ target: [users.firstName, users.lastName], set: { firstName: 'John1' } });

Cross Join Lateral example

const subquery = db.select().from(pets).where(gte(users.age, 16)).as('userPets') const result = await db.select().from(users).crossJoinLateral(subquery). The subquery object in the result is not nullable.

Partial select with joins

Use .select({ field1: table1.column, field2: table2.column }).from(table1).join(...) to select specific fields. The return type is automatically inferred based on the select structure. Fields from the joined table that could be null (due to left join, right join, or full join) will have a nullable type.

Cross Join example

Query: db.select().from(users).crossJoin(pets). Neither the users nor pets object is nullable in the result.

Many-to-one relationship query

For a many-to-one relationship where users belong to cities, query: db.select().from(cities).leftJoin(users, eq(cities.id, users.cityId)). Each result row contains one city and zero or one user.

SQL expressions in partial select with joins

When using sql operator for partial selection with joins, explicitly provide the type including null if needed: sql<type | null>`expression`. Without explicit typing, the result type will be unknown. This is important for proper type inference with joins that produce nullable fields.

Join types supported

Drizzle ORM has APIs for INNER JOIN [LATERAL], FULL JOIN, LEFT JOIN [LATERAL], RIGHT JOIN, CROSS JOIN [LATERAL].

Right Join example

Query: db.select().from(users).rightJoin(pets, eq(users.id, pets.ownerId)). The users object in the result is nullable because the join is right and there can be pets without owners.

Inner Join example

Query: db.select().from(users).innerJoin(pets, eq(users.id, pets.ownerId)). Neither the users nor pets object is nullable in the result.

Self-join example

import { alias } from 'drizzle-orm/cockroach-core'; const parent = alias(user, 'parent'); const result = await db.select().from(user).leftJoin(parent, eq(parent.id, user.parentId));

Full Join example

Query: db.select().from(users).fullJoin(pets, eq(users.id, pets.ownerId)). Both the users and pets objects in the result are nullable.

Table aliases for self-joins

Import alias from drizzle-orm/cockroach-core. Use const parentAlias = alias(table, 'parentAlias') to create an alias of a table. This allows you to join a table to itself.

Aggregating results from joins

Drizzle ORM delivers name-mapped results from the driver without changing the structure. You can use Array.reduce() or similar array methods to map many-one relational data into a structured format, such as grouping pets by user.

Many-to-many relationship query

For a many-to-many relationship between users and chatGroups through usersToChatGroups junction table, query: db.select().from(usersToChatGroups).leftJoin(users, eq(usersToChatGroups.userId, users.id)).leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id)).where(eq(chatGroups.id, 1)). This retrieves all users in a specific chat group.

Relational query API entry point

After initializing drizzle with a schema, relational queries are accessed via the db._query API. For example: await db._query.users.findMany() or await db._query.posts.findFirst().

Placeholder in relational query offset parameter

Example of using a placeholder in the offset parameter of a relational query: const prepared = db._query.users.findMany({ offset: placeholder('offset'), with: { posts: true } }).prepare('query_name'); const usersWithPosts = await prepared.execute({ offset: 1 });

Partial field selection behavior with mixed true and false

When both true and false select options are present in the columns parameter, all false options are ignored. If you include any field with true, all fields not explicitly set to true are excluded. For example, columns: { name: true, id: false } results in only the name field being selected because id: false is redundant.

Select filters in relational queries

Relational queries support filters and conditions using operators like eq, lt, and others, the same as the SQL-like query builder. You can import operators from 'drizzle-orm' or use callback syntax: where: (users, { eq }) => eq(users.id, 1). Filters can be applied to nested relations as well.

Give your agent this brain