query-api

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

Many-to-many join pattern with junction table

For many-to-many relationships, create a junction table with foreign keys to both tables, then perform multiple joins. Example: db.select().from(usersToChatGroups).leftJoin(users, eq(usersToChatGroups.userId, users.id)).leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id)).where(eq(chatGroups.id, 1)) to query a chat group and all its participants.

Drizzle provides both relational and SQL-like query APIs

Drizzle is the only ORM with both relational query API and SQL-like query API, providing the best of both worlds when accessing relational data.

Drizzle outputs exactly 1 SQL query per query builder call

Drizzle always outputs exactly 1 SQL query regardless of the complexity of the query builder call. This is safe to use with serverless databases without worrying about performance or roundtrip costs.

SQL-like query API example with joins

Example of Drizzle's SQL-like query API: ```typescript await db .select() .from(countries) .leftJoin(cities, eq(cities.countryId, countries.id)) .where(eq(countries.id, 10)) ``` This demonstrates accessing data with familiar SQL syntax including joins and where clauses.

Relational query API example for nested data

Example of Drizzle's relational query API for fetching nested data: ```typescript const result = await db.query.users.findMany({ with: { posts: true }, }); ``` This demonstrates the relational API for fetching nested related data without manual joins and mapping.

Filter and conditional operators import

All filter and conditional operators can be imported from drizzle-orm: import { eq, ne, gt, gte, ... } from "drizzle-orm";

lte operator - less than or equal comparison

The lte operator from drizzle-orm checks if a value is less than or equal to n. It can compare a column to a literal value or two columns to each other. Example: lte(table.column, 5) produces WHERE `table`.`column` <= 5. Example: lte(table.column1, table.column2) produces WHERE `table`.`column1` <= `table`.`column2`.

notExists operator - subquery non-existence check

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

exists operator - subquery existence check

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

MySQL query utils overview

Drizzle provides query utilities for MySQL, including the $count utility for counting rows in query results.

$count utility in MySQL

The $count utility is available in Drizzle's MySQL query utilities for performing count operations on query results.

Drizzle performance overhead

Drizzle is a thin TypeScript layer on top of SQL with almost 0 overhead. Using the prepared statements API can achieve effectively 0 overhead.

Prepared statement with placeholder example

Example of using sql.placeholder() with prepared statements: import { sql, eq } from "drizzle-orm"; const p1 = db .select() .from(customers) .where(eq(customers.id, sql.placeholder('id'))) .prepare() await p1.execute({ id: 10 }) // SELECT * FROM customers WHERE id = 10 await p1.execute({ id: 12 }) // SELECT * FROM customers WHERE id = 12 const p2 = db .select() .from(customers) .where(sql`${customers.name} like ${sql.placeholder('name')}`) .prepare(); await p2.execute({ name: '%an%' }) // SELECT * FROM customers WHERE name like '%an%'

Using sql.placeholder() for dynamic values in prepared statements

Use sql.placeholder(...) to embed dynamic runtime values in prepared statements. The placeholder name is passed as a string argument, and the actual value is provided when calling execute() with an object containing the placeholder names as keys.

How prepared statements work in Drizzle

When you run a query on the database, the configurations of the query builder get concatenated to the SQL string, that string and params are sent to the database driver, and the driver compiles SQL query to binary SQL executable format and sends it to the database. With prepared statements, SQL concatenation happens once on the Drizzle ORM side and then the database driver can reuse the precompiled binary SQL instead of parsing the query every time, providing extreme performance benefits on large SQL queries.

Create and execute a prepared statement

Call .prepare() on a query builder to create a prepared statement, then call .execute() to run it multiple times: const db = drizzle(...); const prepared = db.select().from(customers).prepare(); const res1 = await prepared.execute(); const res2 = await prepared.execute(); const res3 = await prepared.execute();

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

Drizzle outputs one SQL query for a single query builder call. When using prepared statements, the SQL concatenation happens once on the Drizzle ORM side.

Custom replica selection logic with withReplicas()

The withReplicas() function accepts an optional third parameter that is a callback function for custom replica selection logic. The callback receives an array of replica instances and must return a single replica instance to use for that query. This allows implementation of weighted selection, round-robin, or any other custom strategy for choosing which replica handles a read operation.

Example: basic withReplicas() setup with mysql2

This example shows how to set up read replicas with Drizzle and mysql2. Create separate mysql2 connections for the primary database and each read replica, then create Drizzle instances from each connection. Finally, pass the primary instance and an array of replica instances to withReplicas(): ```ts import { drizzle } from "drizzle-orm/mysql2"; import mysql from "mysql2/promise"; import { boolean, int, mysqlTable, text, withReplicas } from 'drizzle-orm/mysql-core'; const usersTable = mysqlTable('users', { id: int().primaryKey().autoincrement(), name: text().notNull(), verified: boolean().notNull().default(false), }); const primaryClient = await mysql.createConnection({ host: "host", user: "user", database: "primary_db", }) const primaryDb = drizzle({ client: primaryClient }); const read1Client = await mysql.createConnection({ host: "host", user: "user", database: "read_1", }) const read1 = drizzle({ client: read1Client }); const read2Client = await mysql.createConnection({ host: "host", user: "user", database: "read_2", }) const read2 = drizzle({ client: read2Client }); const db = withReplicas(primaryDb, [read1, read2]); ```

$primary key to force primary instance for read operations

You can use the $primary key on the Drizzle database instance to force read operations to use the primary database instead of a read replica. For example: await db.$primary.select().from(usersTable) will read from the primary instance instead of being routed to a replica.

Example: weighted read replica selection with withReplicas()

This example shows custom replica selection where the first replica has a 70% probability of being chosen and the second replica has a 30% probability: ```ts 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]! }); await db.select().from(usersTable) ```

withReplicas() function for managing read replicas

The withReplicas() function allows you to configure a Drizzle database instance to route SELECT queries to read replica instances and CREATE, DELETE, UPDATE operations to the primary instance. It is imported from 'drizzle-orm/mysql-core'. The function takes the primary database instance and an array of read replica database instances as arguments: withReplicas(primaryDb, [replica1, replica2]). Drizzle automatically routes queries to the appropriate instance based on operation type.

v1 relation types removed from drizzle-orm

The following v1 relation entities were removed and should not be imported: Relations, TableRelationsKeysOnly, ExtractTableRelationsFromSchema, ExtractRelationsFromTableExtraConfigSchema, getOperators, FindTableByDBName, RelationalSchemaConfig, RelationConfig, extractTablesRelationalConfig, relations, createOne, createMany, NormalizedRelation, normalizeRelation, createTableRelationsHelpers, TableRelationsHelpers.

offset on related objects in v2

Relational Queries v2 supports offset and limit on related objects in with clauses. Example: {with: {comments: {offset: 3, limit: 3}}}

Predefined filters in relation definitions v2

Relations v2 supports a where option in relation definitions to filter related data automatically. Example: r.many.users({from: ..., to: ..., where: {verified: true}}) will always filter to verified users when querying that relation.

where is now object-based in relational queries v2

Relational Queries v2 changed where clauses from callback functions to plain objects. Simple equality: {age: 15}. Complex conditions use AND, OR, NOT, and RAW. Example: {AND: [{age: 15}, {name: 'John'}]} or {OR: [{id: {gt: 10}}, {name: {like: 'John%'}}]}

defineRelationsPart for splitting relations across files

You can split relations into multiple parts using defineRelationsPart(). Each part defines relations for a subset of tables. Combine parts when creating the db instance: const db = drizzle(url, { relations: { ...relations, ...part } })

fields renamed to from, references renamed to to in v2

Relation definition parameters changed: fields becomes from, and references becomes to. Both from and to accept either a single value or an array of values.

optional: false makes relation key required at type level

The optional: false parameter in relation definitions (e.g., r.one.posts({from: ..., to: ..., optional: false})) makes the relation key required in the result type, used when you are certain the related entity will always exist.

many side can be defined without one side in v2

Relational Queries v2 allows defining only the many side of a relationship without requiring the one side to be defined on the other table, unlike v1 which required both sides.

defineRelations replaces separate relations objects

In Relational Queries v2, use defineRelations() to specify all relations for all tables in one dedicated place, rather than creating separate relations objects for each table. The function accepts the schema and a callback with parameter r that provides autocomplete for all tables and relation functions (one, many, through).

Relation definition v2 syntax with r parameter

Relations v2 uses r.one and r.many accessed through the r parameter callback. For many-to-one: r.one.tableName({from: r.table.field, to: r.otherTable.field}). For one-to-many: r.many.tableName(). Both from and to accept single values or arrays.

Filtering by related objects in v2

Relational Queries v2 supports filtering by related data in the where clause. Example: {id: {gt: 10}, posts: {content: {like: 'M%'}}} filters users with id>10 who have at least one post with content starting with M.

orderBy is now object in relational queries v2

orderBy in Relational Queries v2 uses a simple object syntax instead of callback functions. Syntax: {columnName: 'asc'} or {columnName: 'desc'}

Relational query with with clause for eager loading

Relational queries use the 'with' option in findMany or findFirst to eagerly load related data. Example: db.query.users.findMany({ with: { profileInfo: true } }) fetches users with their related profileInfo in a single query operation.

Example: Filter by column with equals

const users = await db.query.users.findMany({ where: { id: 1 } }); This filters users where id equals 1.

Example: Partial field selection

const posts = await db.query.posts.findMany({ columns: { id: true, content: true, }, with: { comments: true, } }); This retrieves only id and content from posts, but includes all fields from related comments.

Single SQL statement output in relational queries

A single SQL statement is outputted by Drizzle for relational queries, even with partial field selection, nested relations, and custom fields. This is important for understanding query performance characteristics.

Prepared statements in relational queries

Relational queries support prepared statements for performance optimization. Use sql.placeholder() to define placeholders in where conditions, limit, and offset parameters. Call .prepare() on the query and then execute() with an object containing the parameter values.

Subqueries in relational queries with extras

You can use subqueries within the extras parameter of relational queries. Use db.$count() to count records matching a condition. Example: totalPostsCount: (table) => db.$count(posts, eq(posts.authorId, table.id)). This allows calculating aggregate values for each record without using traditional aggregation functions.

SQL aliases in extras are ignored

If you specify .as('<alias>') on any extras field, Drizzle will ignore it. The key name used in the extras object becomes the field name in the result.

Aggregations not supported in extras

Aggregations are not currently supported in the extras parameter of relational queries. Use core queries instead for aggregation operations.

Custom fields with extras parameter

The extras parameter in relational queries lets you add custom additional fields by applying functions to data. This is useful when you need to retrieve data and apply transformations like lowercasing, concatenation, or length calculation. The key name you provide becomes a field in the returned object.

Limit and offset in relational queries

Drizzle provides limit and offset APIs for both the main query and nested relations. The offset parameter can now be used in nested tables ('with' clauses) as well as the main query. Both can be used together to paginate results.

Relations filtering in relational queries

You can filter by any table included in the query using the relations filtering syntax. For example, filter users who have posts with specific content, or filter users only if they have at least one post by setting the relation filter to 'true'.

Select filters with operators in relational queries

Relational queries support the same filters and operators as the SQL-like query builder. Operators can be imported from 'drizzle-orm' or used from the callback syntax. Available operators include: OR, AND, NOT, RAW, eq, ne, gt, gte, lt, lte, in, notIn, like, notLike, isNull, isNotNull.

Partial select with nested relations in columns

You can include or exclude columns of nested relations using the columns parameter in nested 'with' queries. This allows fine-grained control over which fields are returned from related tables.

Mixing true and false in columns selection

When both 'true' and 'false' select options are present in the columns parameter, all 'false' options are ignored. If you include one field as 'true', all other fields are automatically excluded.

Partial field selection with columns parameter

The columns parameter in relational queries lets you include or omit specific columns. You can use 'true' to include a column or 'false' to exclude it. Drizzle performs partial selects on the query level with no additional data transferred from the database, and outputs a single SQL statement.

Order by in relational queries

Relational queries support ordering using either the object syntax (with 'asc' or 'desc' strings) or callback syntax. When multiple orderBy statements are used in the same table, they are included in the query in the same order they were added. Custom SQL can be used with orderBy by passing a callback with sql template.

Including relations with 'with' operator

The 'with' operator in relational queries lets you combine data from multiple related tables and properly aggregate results. You can pass 'true' to include all fields from the related table, or pass an object to configure nested queries. Multiple levels of nested 'with' statements can be chained as needed.

findFirst() relational query method

The findFirst() method returns a single record from the specified table. It automatically adds LIMIT 1 to the query. The return type is a single object (not an array) with properly typed fields.

findMany() relational query method

The findMany() method returns an array of records from the specified table with properly typed results based on the query configuration. It can be used without any parameters to retrieve all records.

Relational queries setup with drizzle() initialization

To use relational queries, you must provide all tables and relations from your schema file/files upon drizzle() initialization. Pass both tables and relations objects to the drizzle() constructor, then use the db.query API to access relational query methods.

Callback parameter requirement in relational queries

Inside relational queries, references to a table's columns must go through the callback parameter, not through the imported table object. This applies to every clause that accepts a callback: orderBy, where, RAW, extras, and subqueries inside extras. The callback exposes the aliased table for the current query scope, which is required for correct SQL generation in nested or self-referential queries.

Relational queries extension

Relational queries are an extension to Drizzle's original query builder. They provide a great developer experience for querying nested relational data from an SQL database, avoiding multiple joins and complex data mappings. You can opt-in to use relational queries based on your needs.

Example: Basic relational query with with operator

const result = await db.query.users.findMany({ with: { posts: true, }, }); This retrieves all users with their related posts in a single SQL statement.

Example: Nested partial field selection

const posts = await db.query.posts.findMany({ columns: { id: true, content: true, }, with: { comments: { columns: { authorId: false } } } }); This retrieves specific fields from posts and excludes authorId from nested comments.

Example: Nested relational query

const users = await db.query.users.findMany({ with: { posts: { with: { comments: true, }, }, }, }); This retrieves users with their posts and each post's comments.

Example: Filter with AND condition

const response = await db.query.users.findMany({ where: { age: 15, name: 'John' }, }); Multiple conditions at the same level are combined with AND.

Give your agent this brain