query api

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

Conditional filters in WHERE clause

To pass a conditional filter in a query, use the .where() method with a logical operator. Pass undefined to omit a filter condition. Example: .where(term ? ilike(posts.title, term) : undefined) will only apply the filter if term is defined.

Combining multiple conditional filters with AND

Use the and() operator to combine multiple conditional filters in a single .where() clause. Pass undefined for conditions that should not apply. Example: and(term ? ilike(posts.title, term) : undefined, categories.length > 0 ? inArray(posts.category, categories) : undefined, views > 100 ? gt(posts.views, views) : undefined).

Combining filters with OR operator

Use the or() operator to combine multiple conditional filters when you need an OR condition instead of AND.

Building filter arrays dynamically

To combine conditional filters from different parts of the project, create a variable of type SQL[], push filters to it, then use it in .where() with and(...filters) or or(...filters). Example: const filters: SQL[] = []; filters.push(ilike(posts.title, 'AI')); then .where(and(...filters)).

Custom filter operators with sql template

Create custom filter operators by using the sql template function. Example: const lenlt = (column: AnyColumn, value: number) => { return sql`length(${column}) < ${value}`; }. The operator can then be used in .where() like any built-in filter.

Filter operators are SQL expressions

Drizzle filter operators are SQL expressions under the hood. For example, the lt operator is implemented as: const lt = (left, right) => { return sql`${left} < ${bindIfParam(right, left)}`; } where bindIfParam is an internal function.

Conditional filter example with ilike

Example: await db.select().from(posts).where(term ? ilike(posts.title, term) : undefined). When term is undefined, generates: select * from posts. When term is 'AI', generates: select * from posts where title ilike 'AI'.

Conditional filter example with multiple conditions

Example showing combined conditional filters: await db.select().from(posts).where(and(term ? ilike(posts.title, term) : undefined, categories.length > 0 ? inArray(posts.category, categories) : undefined, views > 100 ? gt(posts.views, views) : undefined)). With no arguments generates: select * from posts. With term='AI', categories=['Tech', 'Art', 'Science'], views=200 generates: select * from posts where (title ilike 'AI' and category in ('Tech', 'Science', 'Art') and views > 200).

Custom filter operator example lenlt

Example of custom filter operator: const lenlt = (column: AnyColumn, value: number) => { return sql`length(${column}) < ${value}`; }. Can be used in queries like: .where(and(maxLen ? lenlt(posts.title, maxLen) : undefined, views > 100 ? gt(posts.views, views) : undefined)). Generates SQL: select * from posts where length(title) < 8 or select * from posts where (length(title) < 8 and views > 200).

INSERT single row basic syntax

Insert a single row using `db.insert(table).values({ field: value })`. The query is automatically parameterized. Example: `await db.insert(users).values({ name: 'Andrew' });` translates to SQL `insert into "users" ("id", "name") values (default, $1) -- params: ['Andrew']`

INSERT with returning clause

After inserting a row, retrieve it using `.returning()`. You can return all columns or specify a subset using `.returning({ field: table.field })`. Example: `await db.insert(users).values({ name: "Dan" }).returning();` or partial return: `await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });`

INSERT multiple rows

Insert multiple rows by passing an array of objects to `.values()`. Example: `await db.insert(users).values([{ name: 'Andrew' }, { name: 'Dan' }]);`

onConflictDoNothing for INSERT

Use `.onConflictDoNothing()` to cancel an insert if a conflict occurs. You can optionally specify the conflict target column with `.onConflictDoNothing({ target: table.column })`. Example: `await db.insert(users).values({ id: 1, name: 'John' }).onConflictDoNothing();` or with explicit target: `await db.insert(users).values({ id: 1, name: 'John' }).onConflictDoNothing({ target: users.id });`

onConflictDoUpdate for upserts

Use `.onConflictDoUpdate()` to update a row if a conflict occurs. Requires `target` column and `set` object with updated values. Example: `await db.insert(users).values({ id: 1, name: 'Dan' }).onConflictDoUpdate({ target: users.id, set: { name: 'John' } });`

onConflictDoUpdate with targetWhere clause

Use `targetWhere` in `.onConflictDoUpdate()` to add a WHERE clause to the conflict target for partial indexes. 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 to the update part of the conflict handler. 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 with composite key

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

INSERT with WITH clause (CTE)

Use `.with()` clause to simplify complex insert queries using common table expressions (CTEs). Define a CTE with `db.$with('name').as(subquery)`, then use it in the insert: `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 });`

INSERT INTO ... SELECT with query builder

Insert rows from a SELECT query using `.insert(table).select()`. Pass a query builder directly: `await db.insert(employees).select(db.select({ id: users.id, name: users.name }).from(users).where(eq(users.role, 'employee'))).returning({ id: employees.id, name: employees.name });`

INSERT INTO ... SELECT with callback

Pass a callback function to `.select()` that returns a query builder: `await db.insert(employees).select(() => db.select({ id: users.id, name: users.name }).from(users).where(eq(users.role, 'employee')));` or with query builder parameter: `await db.insert(employees).select((qb) => qb.select({ id: users.id, name: users.name }).from(users).where(eq(users.role, 'employee')));`

INSERT INTO ... SELECT with SQL template tag

Use SQL template tags for custom select queries in insert: `await db.insert(employees).select(sql`select "users"."id" as "id", "users"."name" as "name" from "users" where "users"."role" = 'employee'`);` or with callback: `await db.insert(employees).select(() => sql`select "users"."id" as "id", "users"."name" as "name" from "users" where "users"."role" = 'employee'`);`

Values are automatically parameterized in INSERT

All values provided to `.values()` in insert operations are parameterized automatically to prevent SQL injection. For example, `db.insert(users).values({ name: 'Andrew' })` becomes `insert into "users" ("id", "name") values (default, $1) -- params: ['Andrew']`

Select parent rows with related child using innerJoin

To select parent rows with at least one related child row and retrieve both parent and child data, use the .innerJoin() method. The inner join automatically filters to only parent rows that have matching child rows. Example: await db.select({ user: users, post: posts }).from(users).innerJoin(posts, eq(users.id, posts.userId)). This returns an array of objects with user and post properties, with parent rows appearing multiple times if they have multiple children.

Select only parent rows with at least one child using exists

To select only parent rows (without child data) that have at least one related child row, use a subquery with the exists() function. Create a subquery that selects from the child table with a WHERE clause correlating to the parent table, then use .where(exists(sq)) on the parent table query. Example: const sq = db.select({ id: sql`1` }).from(posts).where(eq(posts.userId, users.id)); await db.select().from(users).where(exists(sq)). This returns only parent rows that have at least one matching child.

exists() function usage

The exists() function is used with subqueries to filter results based on whether matching rows exist in a related table. It returns true if the subquery returns at least one row, false otherwise. Used in combination with a correlated subquery to create efficient filtering conditions.

Update many rows with different values per row using CASE statement

To update multiple rows with different values for each row in a single request, use the sql operator with a CASE statement combined with .update().set() methods. Build an array of SQL chunks starting with sql`(case`, then for each input object add sql`when ${users.id} = ${input.id} then ${input.city}`, collect all IDs in a separate array, close with sql`end)`, and join all chunks with sql.join(). Finally, execute db.update(users).set({ city: finalSql }).where(inArray(users.id, ids)). The resulting SQL statement will be: update users set "city" = (case when id = 1 then 'New York' when id = 2 then 'Los Angeles' when id = 3 then 'Chicago' end) where id in (1, 2, 3).

Empty array check before batch update

Before performing a batch update with different values for each row, check that the inputs array is not empty. If it is empty, return early to avoid unnecessary database operations.

MySQL unique constraints - single column

In MySQL, unique constraints can be defined at the column level for single-column constraints using the .unique() method with an optional custom constraint name. For example: text('state').unique('custom') creates a unique constraint with a custom name, while text('name').unique() creates a unique constraint with a default name.

MySQL unique constraints - multiple columns

In MySQL, unique constraints on multiple columns are defined in the third parameter of mysqlTable() using the unique() method. The syntax is: unique().on(t.column1, t.column2) for unnamed constraints, or unique('custom_name').on(t.column1, t.column2) for named constraints.

Insert rows with all default values

Drizzle supports inserting rows where all columns use their default values. Pass an empty object {} to insert a single row with all defaults, or an array of empty objects [{}, {}] to insert multiple rows with all defaults.

Insert with all defaults code example

// Insert 1 row with all defaults await db.insert(usersTable).values({}); // Insert 2 rows with all defaults await db.insert(usersTable).values([{}, {}]);

Filtering by nested relations removed in v0.28.0

Support for filtering by nested relations in relational queries was removed in v0.28.0. The table object in the where callback no longer contains fields from with and extras. This change was made to enable more efficient relational queries with improved row reads and performance. Workarounds include applying filters manually at the code level after fetching rows, or using the core API.

Relational queries use lateral joins with subqueries

Drizzle relational queries use LEFT JOIN LATERAL clauses with subqueries to retrieve data from related tables. For PlanetScale and SQLite, simple subquery selects are used instead of lateral joins to improve query plans and performance. This query generation strategy selectively retrieves only necessary data, reduces aggregation functions, and removes GROUP BY clauses for more efficient execution.

useLiveQuery hook return values

The useLiveQuery hook returns an object with three fields: data (the query result), error (any error that occurred), and updatedAt (timestamp of last update). This follows the error handling practices of React Query and Electric SQL.

useLiveQuery example with Expo SQLite

import { useLiveQuery, drizzle } from 'drizzle-orm/expo-sqlite'; import { openDatabaseSync } from 'expo-sqlite'; import { users } from './schema'; import { Text } from 'react-native'; const expo = openDatabaseSync('db.db', { enableChangeListener: true }); const db = drizzle(expo); const App = () => { const { data } = useLiveQuery(db.select().from(users)); return <Text>{JSON.stringify(data)}</Text>; }; export default App; This example shows how to use useLiveQuery to automatically re-render when data changes. The enableChangeListener option must be set to true when opening the database.

enableChangeListener required for Expo SQLite Live Queries

When opening an Expo SQLite database, the enableChangeListener option must be set to true to enable change listeners for Live Queries to function.

useLiveQuery API design rationale

The useLiveQuery hook follows conventional React Hook API patterns (useLiveQuery(databaseQuery)) rather than method chaining patterns (db.select().from(users).useLive()). This design was chosen to maintain consistency with React Hook conventions.

MySQL $returningId() function for INSERT

MySQL does not have native support for RETURNING after INSERT. Drizzle provides a $returningId() function that automatically returns inserted IDs for primary keys with autoincrement or serial types, accessing insertId and affectedRows fields. The function returns an array of objects with the primary key field. When a primary key uses $default to generate custom values at runtime, $returningId() also returns those generated keys. If there are no primary keys, the type is {}[].

$returningId() with autoincrement primary key example

import { boolean, int, text, mysqlTable } from 'drizzle-orm/mysql-core'; const usersTable = mysqlTable('users', { id: int('id').primaryKey(), name: text('name').notNull(), verified: boolean('verified').notNull().default(false), }); const result = await db.insert(usersTable).values([{ name: 'John' }, { name: 'John1' }]).$returningId(); // ^? { id: number }[]

$returningId() with custom primary key generation example

import { varchar, text, mysqlTable } from 'drizzle-orm/mysql-core'; import { createId } from '@paralleldrive/cuid2'; const usersTableDefFn = mysqlTable('users_default_fn', { customId: varchar('id', { length: 256 }).primaryKey().$defaultFn(createId), name: text('name').notNull(), }); const result = await db.insert(usersTableDefFn).values([{ name: 'John' }, { name: 'John1' }]).$returningId(); // ^? { customId: string }[]

Give your agent this brain