Basic select query in v0.11.0
Select queries are executed by calling .select().execute() on a table instance. Results are fully typed based on the table schema. Example: const users: User[] = await usersTable.select().execute().
query builder & relational queries
68 notes in this subject, read out of this brain and free to use. This is page 1 of 2.
WHERE clause with eq filter
WHERE conditions use the eq() filter function. Example: await table.select().where(eq(table.id, 42)).execute(). The eq() function takes a column and a value to compare.
Combine multiple WHERE conditions with and and or
Multiple WHERE conditions are combined using and() or or() functions that take an array of conditions. Example: and([eq(table.id, 42), eq(table.name, 'Dan')]) or or([eq(table.id, 42), eq(table.id, 1)]).
Partial select query mapping
Partial selects are performed by passing an object to select() with mapped property names. Example: table.select({ mapped1: table.id, mapped2: table.name }).execute(). Results can be destructured with the mapped names.
Limit and offset in v0.11.0
Pagination is implemented with .limit(number).offset(number) methods chained to select queries. Example: table.select().limit(10).offset(10).execute().
Order by with ASC and DESC in v0.11.0
Sorting is done with .orderBy((table) => table.column, Order.ASC|DESC). Example: table.select().orderBy((table) => table.name, Order.ASC). Order is either Order.ASC or Order.DESC.
Insert single row in v0.11.0
Single row insert is executed with usersTable.insert({ column: value }).execute(). Example: usersTable.insert({ name: 'Andrew', createdAt: new Date() }).execute().
Insert multiple rows with insertMany
Multiple row insert is executed with usersTable.insertMany([{ row1 }, { row2 }]).execute(). Each element in the array is an object with column values.
Update query in v0.11.0
Updates are performed with table.update().where(condition).set({ column: newValue }).execute(). Example: usersTable.update().where(eq(usersTable.name, 'Dan')).set({ name: 'Mr. Dan' }).execute().
Delete query in v0.11.0
Deletes are performed with table.delete().where(condition).execute(). Example: usersTable.delete().where(eq(usersTable.name, 'Dan')).execute().
Left join in v0.11.0
Left joins are executed with .leftJoin(otherTable, (table1, table2) => joinCondition). The callback receives both tables as parameters and should return the join condition using eq(). Results map to { table1: row1, table2: row2 }.
Multiple joins in a single query
Multiple tables can be joined in sequence. Each additional .leftJoin() adds another table. The callback parameters accumulate: first join has (table1, table2), second join has (table1, table2, table3), and so on. WHERE clause parameters follow the same pattern.
Relational queries filtering by nested relations removed in v0.28.0
In Drizzle v0.28.0, filtering by fields from nested relations in the where callback is no longer supported. The table object in the where callback no longer has fields from the with and extras properties. This was removed to enable more efficient relational queries with improved row reads and performance. Workarounds include applying filters manually at the code level after rows are fetched, or using the core API.
Relational queries now use lateral joins for improved performance in v0.28.0
Drizzle v0.28.0 changed the query generation strategy for relational queries to use lateral joins (LEFT JOIN LATERAL) for efficient data retrieval from related tables. For MySQL in PlanetScale and SQLite, simple subquery selects are used instead. This strategy also includes selective data retrieval to fetch only necessary data, reduced aggregation functions, and removal of GROUP BY clauses where possible, all resulting in improved query performance and reduced read usage.
PostgreSQL arrayContained operator example
const contained = await db.select({ id: posts.id }).from(posts) .where(arrayContained(posts.tags, ['Typescript', 'ORM']));
Relational Query API .toSQL() method
The Relational Query API now supports .toSQL() method to convert queries to SQL. Example: const query = db.query.usersTable.findFirst().toSQL();
PostgreSQL array operators: arrayContains, arrayContained, arrayOverlaps
PostgreSQL now supports three new array operators: arrayContains checks if array contains elements, arrayContained checks if array is contained by another array, and arrayOverlaps checks if arrays overlap. All three can be used in where() clauses and support subqueries as the second argument.
PostgreSQL arrayOverlaps operator example
const overlaps = await db.select({ id: posts.id }).from(posts) .where(arrayOverlaps(posts.tags, ['Typescript', 'ORM']));
PostgreSQL array operator with subquery example
const withSubQuery = await db.select({ id: posts.id }).from(posts) .where(arrayContains( posts.tags, db.select({ tags: posts.tags }).from(posts).where(eq(posts.id, 1)), ));
SQL operators available in Relational Queries where filter
Relational Queries where filter function now has access to SQL operators through the second parameter. Instead of importing operators like inArray from drizzle-orm/pg-core, they can be accessed via the second parameter in the where function callback: await db.users.findFirst({ where: (table, { inArray }) => inArray(table.id, [...]) })
Query builder methods can only be invoked once by default
Starting from v0.29.0, by default most query builder methods in Drizzle can only be invoked once to conform to SQL. For example, .where() can only be invoked once in a SELECT statement. Attempting to call .where() twice will result in a type error.
Dynamic query building with .$dynamic()
To enable dynamic query building that removes the restriction of invoking methods only once, call .$dynamic() on a query builder. This is useful when building queries dynamically, such as in shared functions that enhance a query builder.
Dynamic query builder function example
Example of using $dynamic() for a pagination helper function: ```ts 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)); const dynamicQuery = query.$dynamic(); withPagination(dynamicQuery, 1); // ✅ OK ```
Set operators support in v0.29.0
Drizzle v0.29.0 adds support for set operators: UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, and EXCEPT ALL.
Set operators import approach example
Example of using set operators with import approach: ```ts import { union } from 'drizzle-orm/pg-core' const allUsersQuery = db.select().from(users); const allCustomersQuery = db.select().from(customers); const result = await union(allUsersQuery, allCustomersQuery) ```
Set operators builder approach example
Example of using set operators with builder approach: ```ts const result = await db.select().from(users).union(db.select().from(customers)); ```
Fix selectDistinctOn multiple columns
In Drizzle ORM v0.29.1, a bug was fixed where selectDistinctOn was not working with multiple columns. This was addressed in pull request #1466.
JSDoc documentation for all query builders
Drizzle ORM v0.29.1 adds detailed JSDoc documentation for all query builders in all dialects. This documentation is accessible within the IDE while developing and provides information, hints, and documentation links. Previously JSDoc was only available for filter expressions.
Aggregate function helpers in Drizzle
Drizzle ORM v0.29.1 introduces new helper functions for aggregate functions: count(), countDistinct(), avg(), avgDistinct(), sum(), sumDistinct(), max(), and min(). These helpers are alternatives to using the sql template directly. Aggregation functions should typically be used with the GROUP BY clause when selecting other columns alongside aggregates.
count() aggregate helper
The count() helper counts all rows or rows for a specific column. Usage: await db.select({ value: count() }).from(users) counts all rows, or await db.select({ value: count(users.id) }).from(users) counts non-null id values. This is equivalent to sql`count('*')`.mapWith(Number) or sql`count(${users.id})`.mapWith(Number).
countDistinct() aggregate helper
The countDistinct() helper counts distinct values of a column. Usage: await db.select({ value: countDistinct(users.id) }).from(users). This is equivalent to sql`count(distinct ${users.id})`.mapWith(Number).
avg() aggregate helper
The avg() helper calculates the average value of a column. Usage: await db.select({ value: avg(users.id) }).from(users). This is equivalent to sql`avg(${users.id})`.mapWith(String).
avgDistinct() aggregate helper
The avgDistinct() helper calculates the average of distinct values of a column. Usage: await db.select({ value: avgDistinct(users.id) }).from(users). This is equivalent to sql`avg(distinct ${users.id})`.mapWith(String).
sum() aggregate helper
The sum() helper calculates the sum of values in a column. Usage: await db.select({ value: sum(users.id) }).from(users). This is equivalent to sql`sum(${users.id})`.mapWith(String).
sumDistinct() aggregate helper
The sumDistinct() helper calculates the sum of distinct values in a column. Usage: await db.select({ value: sumDistinct(users.id) }).from(users). This is equivalent to sql`sum(distinct ${users.id})`.mapWith(String).
max() aggregate helper
The max() helper returns the maximum value of a column. Usage: await db.select({ value: max(users.id) }).from(users). This is equivalent to sql`max(${users.id})`.mapWith(users.id).
min() aggregate helper
The min() helper returns the minimum value of a column. Usage: await db.select({ value: min(users.id) }).from(users). This is equivalent to sql`min(${users.id})`.mapWith(users.id).
.if() function for conditional WHERE clauses
The .if() function can be added to WHERE expressions to conditionally apply a filter. It accepts a boolean condition and only applies the WHERE clause when the condition is true. For example, gt(posts.views, views).if(views > 100) will only filter posts where views is greater than 100 when the views parameter is greater than 100.
Postgres onConflictDoUpdate split where clauses
The .onConflictDoUpdate method now supports two separate where clauses: targetWhere for the conflict detection condition, and setWhere for the condition on which columns to update. This allows handling both where cases in the ON CONFLICT clause.
Postgres onConflictDoNothing where clause fix
The query generation for the where clause in Postgres .onConflictDoNothing method was fixed; the where clause was previously placed in the wrong location in the generated SQL.
onConflictDoUpdate example with setWhere in SQLite
Example: await db.insert(employees).values({ employeeId: 123, name: 'John Doe' }).onConflictDoUpdate({ target: employees.employeeId, set: { name: 'John Doe' }, setWhere: sql`name <> 'John Doe'` });
onConflictDoUpdate targetWhere parameter in SQLite
The onConflictDoUpdate() method in SQLite now supports a targetWhere field, which allows you to specify a WHERE clause that determines which rows are considered for the conflict. Use targetWhere when you want to conditionally target rows for the conflict resolution based on a condition that applies to the existing row.
onConflictDoUpdate setWhere parameter in SQLite
The onConflictDoUpdate() method in SQLite now supports a setWhere field, which allows you to specify a WHERE clause that determines when the SET updates are applied. Use setWhere when you want to conditionally apply the set updates only when a specific condition is met on the existing row.
onConflictDoUpdate example with targetWhere in SQLite
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` } });
RQB fix for tables with same names in different schemas
Drizzle ORM v0.31.3 fixed RQB (relational query builder) behavior when handling tables with the same names in different schemas.
limit 0 support in all dialects
Drizzle ORM v0.32.1 added support for 'limit 0' in all dialects, addressing issue #2011.
inArray and notInArray accept empty lists
In Drizzle ORM v0.32.1, the inArray and notInArray functions now accept empty lists, resolving issue #1295.
MySQL $returningId() function for autoincrement primary keys
MySQL does not have native RETURNING support after INSERT. The $returningId() function provides an automatic way to access insertId for primary keys with autoincrement or serial types. It returns an array of objects with the inserted IDs. Example: await db.insert(usersTable).values([{ name: 'John' }, { name: 'John1' }]).$returningId() returns { id: number }[].
MySQL $returningId() with custom primary keys using $defaultFn
Custom primary keys can be specified using the $defaultFn() function, which generates keys at runtime. The $returningId() function will also return these generated custom keys. Example: customId: varchar('id', { length: 256 }).primaryKey().$defaultFn(createId) will return { customId: string }[] when using $returningId().
MySQL $returningId() with no primary keys
If a table has no primary keys, the $returningId() function will return an empty object type: {}[].
useLiveQuery forwarding dependencies fix in v0.32.2
Version 0.32.2 added forwarding dependencies within useLiveQuery to fix issue #2651.
defineRelationsPart example
Example of using defineRelationsPart to separate relations config: ```ts import { defineRelations, defineRelationsPart } from 'drizzle-orm'; import * as schema from './schema'; export const relations = defineRelations(schema, (r) => ({ users: { invitee: r.one.users({ from: r.users.invitedBy, to: r.users.id, }), posts: r.many.posts(), } })); export const part = defineRelationsPart(schema, (r) => ({ posts: { author: r.one.users({ from: r.posts.authorId, to: r.users.id, }), } })); const db = drizzle(process.env.DB_URL, { relations: { ...relations, ...part } }) ```
defineRelationsPart for separating relations config
The `defineRelationsPart` helper allows separating relations configuration into multiple parts. Define relations with `defineRelations` and additional parts with `defineRelationsPart`, then merge them using spread syntax when passing to the db instance: `const db = drizzle(process.env.DB_URL, { relations: { ...relations, ...part } })`.
Column alias using .as() method
Columns can now have direct aliases using the `.as()` method. Example: `const query = db.select({ age: users.age.as('ageOfUser'), id: users.id.as('userId') }).from(users).orderBy(asc(users.id.as('userId')));`.
Standalone QueryBuilder with dynamic mode
Example: Using standalone QueryBuilder instances with dynamic query building: ```ts import { QueryBuilder } from 'drizzle-orm/mysql-core'; function withFriends<T extends MySqlSelectQueryBuilder>(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); ```
Dynamic query building overview
By default, Drizzle query builders conform to SQL strictly and restrict invoking methods only once. For example, calling .where() multiple times causes a type error. To enable dynamic query building and remove this restriction, call .$dynamic() on a query builder.
Why dynamic mode exists
Dynamic mode solves the problem of building queries dynamically when shared functions need to enhance a query builder. Without dynamic mode, a function cannot invoke methods like .limit(), .offset(), or .leftJoin() on a query builder passed as a parameter.
Dynamic mode with generic functions
When a function is generic with a constraint like T extends MySqlSelect, it can modify the result type of the query builder by adding operations such as joins. The query builder must be in dynamic mode to allow this.
Dynamic query builder types for MySQL
The types that can be used as generic parameters for dynamic query building in MySQL are: MySqlSelect or MySqlSelectQueryBuilder for SELECT queries, MySqlInsert for INSERT, MySqlUpdate for UPDATE, and MySqlDelete for DELETE.
QueryBuilder type hierarchy
The QueryBuilder types like MySqlSelectQueryBuilder are for standalone query builder instances. DB query builders are subclasses of these types, so they can be used interchangeably in generic constraints.