query-api

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

Infer insert type from table schema

Use `typeof tableName.$inferInsert` to get the TypeScript type for inserting rows into a specific table. For example, `type NewUser = typeof users.$inferInsert` creates a type that matches the expected shape for inserting into the users table.

Composite key upserts

For upserts on composite indexes or composite primary keys, pass an array of columns to the target property: `.onConflictDoUpdate({ target: [users.firstName, users.lastName], set: { firstName: 'John1' } })`.

targetWhere and setWhere in upserts

On conflict do update can include two different WHERE clauses. Use targetWhere for partial index conditions on the conflict target, and setWhere for conditions on the update clause itself. Both accept sql template tags for custom conditions.

onConflictDoUpdate updates row on conflict

The onConflictDoUpdate() method updates the conflicting row instead of canceling the insert. It requires a target column and a set object specifying which columns to update. Example: `.onConflictDoUpdate({ target: users.id, set: { name: 'John' } })`.

onConflictDoNothing prevents insert on conflict

The onConflictDoNothing() method cancels the insert operation if a conflict occurs. You can optionally specify the conflict target column: `.onConflictDoNothing({ target: users.id })`. If no target is specified, the behavior applies to any conflict.

INSERT multiple rows in single query

Pass an array of value objects to .values() to insert multiple rows in a single insert statement. Example: `db.insert(users).values([{ name: 'Andrew' }, { name: 'Dan' }])`.

Drizzle delivers name-mapped results without structural changes

Drizzle ORM returns name-mapped results from the driver without modifying the structure. Results can be aggregated and transformed client-side using JavaScript operations like `reduce()` to reshape relational data.

Drizzle join types supported

Drizzle ORM supports five join types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN.

Table aliases for self-joins

Use `alias(table, 'aliasName')` to create an alias of a table, enabling self-joins. Example: `const parent = alias(user, 'parent'); db.select().from(user).leftJoin(parent, eq(parent.id, user.parentId))` allows joining a table to itself.

Nested select object syntax avoids excessive nullable fields

Use nested select object syntax to group joined table fields together, making the entire nested object nullable rather than individual fields. Example: `select({ userId: users.id, pet: { id: pets.id, name: pets.name } }).from(users).fullJoin(pets, ...)` produces result type where the entire `pet` object is nullable instead of each field being nullable individually.

Partial select with joins

When using partial select with joins, pass an object to `.select()` specifying which fields to include. Drizzle automatically infers the return type based on the select structure. Fields from tables that can be null in the join will have nullable types. Example: `db.select({ userId: users.id, petId: pets.id }).from(users).leftJoin(pets, eq(users.id, pets.ownerId))` produces result type `{ userId: number; petId: number | null }[]`

Using sql operator with joins requires explicit type annotation

When using the `sql` operator in partial select with joins, explicitly specify the return type including nullability with `sql<type | null>` for proper type inference. This is especially important for fields that can be null due to the join type. Example: `sql<string | null>\`upper(${pets.name})\`` for a field in a LEFT JOIN.

Cross join syntax and result type

In Drizzle ORM, use `.crossJoin(table)` to perform a CROSS JOIN without a join condition. Example: `db.select().from(users).crossJoin(pets)` produces result type `{ user: {...}; pets: {...} }[]`

Full join syntax and result type

In Drizzle ORM, use `.fullJoin(table, condition)` to perform a FULL JOIN. The TypeScript result type includes both tables' fields as nullable. Example: `db.select().from(users).fullJoin(pets, eq(users.id, pets.ownerId))` produces result type `{ user: {...} | null; pets: {...} | null }[]`

Right join syntax and result type

In Drizzle ORM, use `.rightJoin(table, condition)` to perform a RIGHT JOIN. The TypeScript result type includes the main table's fields as nullable while the joined table's fields are required. Example: `db.select().from(users).rightJoin(pets, eq(users.id, pets.ownerId))` produces result type `{ user: {...} | null; pets: {...} }[]`

Inner join syntax and result type

In Drizzle ORM, use `.innerJoin(table, condition)` to perform an INNER JOIN. The TypeScript result type includes both tables' fields as required (non-nullable). Example: `db.select().from(users).innerJoin(pets, eq(users.id, pets.ownerId))` produces result type `{ user: {...}; pets: {...} }[]`

Many-to-one join example

For a many-to-one relationship where multiple users belong to one city, query using: `db.select().from(cities).leftJoin(users, eq(cities.id, users.cityId)).all()`. This retrieves all cities with their associated users.

Many-to-many join example with junction table

For a many-to-many relationship using a junction table, perform multiple joins on the junction table. Example: `db.select().from(usersToChatGroups).leftJoin(users, eq(usersToChatGroups.userId, users.id)).leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id)).where(eq(chatGroups.id, 1)).all()` retrieves all users in a specific chat group.

or operator - any condition true

The or operator from drizzle-orm combines multiple conditions where one or more must return true. Usage: or(gt(table.column, 5), lt(table.column, 7)) generates WHERE (table.column > 5 OR table.column < 7).

not operator - negation of conditions

The not operator from drizzle-orm negates a condition so all conditions must return false. Usage: not(eq(table.column, 5)) generates WHERE NOT (table.column = 5).

Import filter and conditional operators from drizzle-orm

All SQLite filter and conditional operators can be imported from the drizzle-orm package. Usage: import { eq, ne, gt, gte, lt, lte, exists, notExists, isNull, isNotNull, inArray, notInArray, between, notBetween, like, notLike, not, and, or } from "drizzle-orm".

notLike operator - pattern non-matching case sensitive

The notLike operator from drizzle-orm checks if a column value does not match a specified pattern in a case-sensitive manner. Usage: notLike(table.column, "%llo wor%") generates WHERE table.column NOT LIKE '%llo wor%'.

and operator - all conditions true

The and operator from drizzle-orm combines multiple conditions where all must return true. Usage: and(gt(table.column, 5), lt(table.column, 7)) generates WHERE (table.column > 5 AND table.column < 7).

like operator - pattern matching case sensitive

The like operator from drizzle-orm performs case-sensitive pattern matching on a column value. Usage: like(table.column, "%llo wor%") generates WHERE table.column LIKE '%llo wor%'.

between operator - value range check

The between operator from drizzle-orm checks if a column value is between two specified values inclusive. Usage: between(table.column, 2, 7) generates WHERE table.column BETWEEN 2 AND 7.

notInArray operator - value not in array

The notInArray operator from drizzle-orm checks if a column value does not exist in a provided array or subquery result. Usage with array: notInArray(table.column, [1, 2, 3, 4]) generates WHERE table.column NOT in (1, 2, 3, 4). Usage with subquery: const query = db.select({ data: table2.column }).from(table2); notInArray(table.column, query) generates WHERE table.column NOT IN (SELECT table2.column FROM table2).

inArray operator - value in array

The inArray operator from drizzle-orm checks if a column value exists in a provided array or subquery result. Usage with array: inArray(table.column, [1, 2, 3, 4]) generates WHERE table.column in (1, 2, 3, 4). Usage with subquery: const query = db.select({ data: table2.column }).from(table2); inArray(table.column, query) generates WHERE table.column IN (SELECT table2.column FROM table2).

isNotNull operator - non-null value check

The isNotNull operator from drizzle-orm checks if a column value is not null. Usage: isNotNull(table.column) generates WHERE table.column IS NOT NULL.

notExists operator - check subquery non-existence

The notExists operator from drizzle-orm checks if a subquery returns no rows. Usage: const query = db.select().from(table2); db.select().from(table).where(notExists(query)) generates WHERE NOT EXISTS (SELECT * from table2).

exists operator - check subquery existence

The exists operator from drizzle-orm checks if a subquery returns any rows. Usage: const query = db.select().from(table2); db.select().from(table).where(exists(query)) generates WHERE EXISTS (SELECT * from table2).

lte operator - less than or equal comparison

The lte operator from drizzle-orm checks if a column value is less than or equal to a specified value or another column. Usage: lte(table.column, 5) generates WHERE table.column <= 5. Can also compare two columns: lte(table.column1, table.column2) generates WHERE table.column1 <= table.column2.

ne operator - value not equal comparison

The ne operator from drizzle-orm checks if a column value does not equal a specified value or another column. Usage: ne(table.column, 5) generates WHERE table.column <> 5. Can also compare two columns: ne(table.column1, table.column2) generates WHERE table.column1 <> table.column2.

gte operator - greater than or equal comparison

The gte operator from drizzle-orm checks if a column value is greater than or equal to a specified value or another column. Usage: gte(table.column, 5) generates WHERE table.column >= 5. Can also compare two columns: gte(table.column1, table.column2) generates WHERE table.column1 >= table.column2.

notBetween operator - value outside range check

The notBetween operator from drizzle-orm checks if a column value is outside a specified range. Usage: notBetween(table.column, 2, 7) generates WHERE table.column NOT BETWEEN 2 AND 7.

eq operator - value equal comparison

The eq operator from drizzle-orm checks if a column value equals a specified value or matches another column. Usage: eq(table.column, 5) generates WHERE table.column = 5. Can also compare two columns: eq(table.column1, table.column2) generates WHERE table.column1 = table.column2.

SQL-like query example with joins

Example of using Drizzle's SQL-like query API: `await db.select().from(countries).leftJoin(cities, eq(cities.countryId, countries.id)).where(eq(countries.id, 10))`

Drizzle is a headless ORM, not a data framework

Drizzle is a library and collection of complementary opt-in tools. Unlike data frameworks, Drizzle lets developers build projects the way they want without interfering with project structure. It allows defining and managing database schemas in TypeScript, accessing data in SQL-like or relational ways, and using opt-in tools for improved developer experience.

Drizzle relational query example with nested relations

Example of using the Queries API to fetch relational nested data: `const result = await db.query.users.findMany({ with: { posts: true } });` This fetches users with all their related posts in a single SQL query without manual joins.

Drizzle always outputs exactly 1 SQL query per builder call

Drizzle always outputs exactly 1 SQL query regardless of the complexity of the query builder call. This means it is safe to use with serverless databases and there is no need to worry about performance or roundtrip costs.

Drizzle has two query APIs: relational and SQL-like

Drizzle ORM is the only ORM with both relational and SQL-like query APIs, providing the best of both worlds when accessing relational data. The SQL-like API allows users to write queries similar to standard SQL. The relational API is called the Queries API and fetches relational nested data from the database in a convenient and performant way without requiring joins and manual data mapping.

Drizzle embraces SQL-like syntax

Drizzle is built to be SQL-like at its core so developers who know SQL can use Drizzle with zero to no learning curve. It provides SQL schema declaration, SQL-like queries, automatic migrations, and a relational query API.

$count query utility for SQLite

Drizzle provides a $count query utility for SQLite that is documented in the query utils section. This utility can be used in SQLite queries.

sql.placeholder example with expression

import { sql } from "drizzle-orm"; const p2 = db .select() .from(customers) .where(sql`lower(${customers.name}) like ${sql.placeholder('name')}`) .prepare(); p2.all({ name: '%an%' }) // SELECT * FROM customers WHERE lower(name) like '%an%'

Prepared statement example

const db = drizzle(...); const prepared = db.select().from(customers).prepare(); const res1 = prepared.all(); const res2 = prepared.all(); const res3 = prepared.all();

Prepared statements performance benefit over repeated parsing

With prepared statements, the database driver compiles the SQL query to binary SQL executable format once and then reuses that precompiled binary instead of parsing the query each time it is executed. Different database drivers support prepared statements in different ways, and Drizzle ORM can sometimes achieve faster performance than the native better-sqlite3 driver.

Prepared statements in Drizzle

Prepared statements allow you to do SQL concatenation once on the Drizzle ORM side, and then the database driver can reuse the precompiled binary SQL instead of parsing the query every time. This provides extreme performance benefits, especially on large SQL queries. Call the .prepare() method on a query builder to create a prepared statement.

sql.placeholder example with simple parameter

import { sql } from "drizzle-orm"; const p1 = db .select() .from(customers) .where(eq(customers.id, sql.placeholder('id'))) .prepare() p1.get({ id: 10 }) // SELECT * FROM customers WHERE id = 10 p1.get({ id: 12 }) // SELECT * FROM customers WHERE id = 12

Using sql.placeholder for dynamic values in prepared statements

The sql.placeholder(...) API allows you to embed dynamic runtime values in prepared statements. When calling the prepared statement with methods like .get() or .all(), pass an object with the placeholder names as keys and the runtime values as values.

How many SQL queries does Drizzle output for a single query builder call

Drizzle is a thin TypeScript layer on top of SQL with almost 0 overhead. When you run a query on the database, a single query builder call results in one SQL query being executed: the query builder configurations are concatenated into a single SQL string, and that string is sent to the database driver as one query.

Drizzle query execution steps

When you run a query on the database, three main steps occur: (1) all the configurations of the query builder get concatenated to an SQL string, (2) that string and params are sent to the database driver, (3) the driver compiles the SQL query to binary SQL executable format and sends it to the database.

withReplicas() function for read/write separation

The withReplicas() function in Drizzle allows you to manage SELECT queries against read replica instances while directing create, delete, and update operations to a primary instance. It automatically routes queries to the appropriate database instance.

withReplicas() weighted selection example implementation

You can implement weighted random selection by calculating cumulative probabilities and comparing against Math.random(). For example, with weights [0.7, 0.3], the first replica has 70% chance and the second has 30% chance of being selected.

withReplicas() custom selection logic with weighting

withReplicas() accepts an optional third parameter: a callback function that receives the replicas array and returns the replica to use. This allows custom selection logic such as weighted random selection. The callback receives (replicas) and should return one replica instance.

$primary key to force primary instance for reads

Use db.$primary.select().from(table) to force read operations to use the primary database instance instead of a read replica.

withReplicas() basic usage with multiple read replicas

Create a database instance with read replicas by calling withReplicas(primaryDb, [replica1, replica2, ...]). All SELECT queries will automatically use one of the replica instances, while INSERT, UPDATE, and DELETE operations use the primary instance.

Relational Queries v2: defineRelationsPart for separating relations into parts

Relations can be split into multiple parts using defineRelationsPart() and then combined when creating the drizzle instance. Example: export const relations = defineRelations(schema, (r) => ({ users: {...} })); export const part = defineRelationsPart(schema, (r) => ({ posts: {...} })); const db = drizzle(url, { relations: { ...relations, ...part } })

Relational Queries v2: defineRelations syntax example

Relations are now defined using defineRelations(schema, (r) => ({ users: { invitee: r.one.users({ from: r.users.invitedBy, to: r.users.id }), posts: r.many.posts() }, posts: { author: r.one.users({ from: r.posts.authorId, to: r.users.id }) } }))

Relational Queries v2: defineRelations replaces separate relations objects

In Relational Queries v2, all relations for all tables are defined in a single location using defineRelations() instead of creating separate relation objects for each table in v1. The function takes a schema object and a callback that receives an r parameter providing autocomplete for all tables and functions like one(), many(), and through().

Relational Queries v2: many relation without one relation

In v2, you can define a many() relation without defining a corresponding one() relation on the other side. In v1, this was not possible and required defining the one side explicitly. Example: r.many.posts({ from: r.users.id, to: r.posts.authorId })

Relational Queries v2: optional parameter for one relations

The optional: false parameter at the relation level makes the related entity key required in the returned object type. This should be used when you are certain that the specific entity will always exist. Syntax: r.one.posts({ from: r.users.id, to: r.posts.authorId, optional: false })

Give your agent this brain