query-api

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

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 })

Relational Queries v2: from and to replace fields and references

In v2, the fields and references parameters are renamed to from and to respectively. Both from and to accept either a single value or an array. Examples: r.one.users({ from: r.posts.authorId, to: r.users.id }) or r.one.users({ from: [r.posts.authorId], to: [r.users.id] })

Relational Queries v2: alias replaces relationName

The relationName parameter from v1 is replaced with alias in v2. This is used in the same way to provide a custom name for the relation. Syntax: r.one.users({ from: r.posts.authorId, to: r.users.id, alias: 'author_post' })

Relational Queries v2: through() for many-to-many relations

Many-to-many relations now use the through() function on the from and to fields to reference the junction table columns. Syntax: r.many.groups({ from: r.users.id.through(r.usersToGroups.userId), to: r.groups.id.through(r.usersToGroups.groupId) }). This eliminates the need to manually query and map the junction table.

Relational Queries v2: many-to-many example with through

Example of a complete many-to-many relation definition: export const relations = defineRelations(schema, (r) => ({ users: { groups: r.many.groups({ from: r.users.id.through(r.usersToGroups.userId), to: r.groups.id.through(r.usersToGroups.groupId) }) }, groups: { participants: r.many.users() } }))

Relational Queries v2: where is now an object

In v2, the where parameter for queries changed from a function format to an object format. V1 used: where: (users, { eq }) => eq(users.id, 1). V2 uses: where: { id: 1 }. Complex filters can use AND, OR, NOT, and RAW operators.

Relational Queries v2: where object with AND operator

Multiple conditions can be combined with AND implicitly or explicitly. Example: where: { age: 15, name: 'John' } or where: { AND: [{ age: 15 }, { name: 'John' }] }. Multiple conditions at the top level are implicitly ANDed together.

Relational Queries v2: where object with OR operator

OR conditions can be specified using the OR key with an array of condition objects. Example: where: { OR: [{ id: { gt: 10 } }, { name: { like: 'John%' } }] }. This generates a WHERE clause with OR conditions.

Relational Queries v2: where object with NOT operator

NOT conditions negate a filter expression. Example: where: { NOT: { id: { gt: 10 } }, name: { like: 'John%' } }. The NOT key negates the condition inside it, and is combined with other top-level conditions using AND.

Relational Queries v2: where object with RAW operator

Raw SQL can be included in where conditions using RAW. Example: where: { RAW: (table) => sql`${table.age} BETWEEN 25 AND 35` }. RAW accepts a callback that receives the table object and returns a SQL expression.

Relational Queries v2: filtering by relations in where

In v2, you can filter by related table fields directly in the where clause. Example: where: { id: { gt: 10 }, posts: { content: { like: 'M%' } } }. This allows filtering users who have ID > 10 and have at least one post with content starting with 'M'.

Relational Queries v2: orderBy is now an object

In v2, orderBy changed from a function format to an object format. V1 used: orderBy: (users, { asc }) => [asc(users.id)]. V2 uses: orderBy: { id: 'asc' }. The value is either 'asc' or 'desc'.

Relational Queries v2: offset on related objects

In v2, you can use offset and limit on related objects in the with clause. Example: with: { comments: { offset: 3, limit: 3 } }. This was not supported in v1.

Relational Queries v2: drizzle() no longer takes mode parameter

In v2, the mode parameter is no longer needed in the drizzle() initialization for any dialect. The same strategy works for all databases. V1 required: drizzle(url, { mode: 'planetscale', schema }). V2 uses: drizzle(url, { relations }).

Relational Queries v2: drizzle instance initialization with relations

To initialize a Drizzle instance in v2, import the relations object and pass it to drizzle(). Example: import { relations } from './relations'; const db = drizzle(process.env.DATABASE_URL, { relations });

Relational Queries v2: internal type changes for database classes

In v2, database classes (BetterSQLite3Database, SQLiteBunDatabase, NodeSQLiteDatabase, etc.) were updated with a new generic argument TRelations extends AnyRelations. Example: BetterSQLite3Database<TSchema, TRelations> extends BaseSQLiteDatabase<'sync', RunResult, TSchema, TRelations>

Relational Queries v2: internal type changes for session classes

In v2, session classes (BetterSQLiteSession, SQLiteBunSession, NodeSQLiteSession, etc.) were updated with a new generic argument TRelations extends AnyRelations. The new signature includes: TRelations extends AnyRelations as the second generic parameter after TFullSchema.

Relational Queries v2: internal type changes for transaction classes

In v2, transaction classes (BetterSQLiteTransaction, BunSQLiteTransaction, NodeSQLiteTransaction, etc.) were updated with a new generic argument TRelations extends AnyRelations. The parameter order is TFullSchema, TRelations, TSchema.

Relational Queries v2: DrizzleConfig interface updated

The DrizzleConfig interface was updated in v2. It now has TRelationConfigs extends AnyRelations as a generic parameter and includes relations?: TRelationConfigs | undefined field. It no longer has a schema field. The interface also includes optional cache and jit fields.

Relational Queries v2: removed entities from drizzle-orm

The following entities were removed from drizzle-orm in v2: Relations, TableRelationsKeysOnly, ExtractTableRelationsFromSchema, ExtractRelationsFromTableExtraConfigSchema, getOperators, FindTableByDBName, RelationalSchemaConfig, RelationConfig, extractTablesRelationalConfig, relations, createOne, createMany, NormalizedRelation, normalizeRelation, createTableRelationsHelpers, TableRelationsHelpers. Update imports accordingly when migrating.

Relational queries provide simpler syntax than manual joins

Relational queries with Drizzle relations provide a simpler and more concise way to query related data compared to manually writing SELECT with JOINs. The relations framework handles the JOIN logic automatically when using db.query with the with parameter.

One-to-many relations with r.many()

One-to-many relations are defined using r.many(). The foreign key is stored in the "many" side table. Example: one user can have many posts, so the foreign key authorId is stored in the posts table, and the user.posts relation returns an array.

r.many() relation configuration fields

The r.many() method configures one-to-many relations with the following fields: from (specifies the source table and column), to (specifies the target table and column), alias (custom name for the relation key), and where (condition for polymorphic relations that filters based on target table columns).

r.one() relation configuration fields

The r.one() method configures one-to-one relations with the following fields: from (specifies the source table and column), to (specifies the target table and column), optional (boolean, default false - set to true to make the relation nullable), alias (custom name for the relation key), and where (condition for polymorphic relations that filters based on target table columns).

defineRelations function for SQLite relations

The defineRelations function is used to define relations between SQLite tables in Drizzle ORM. It takes a schema object and a callback function that defines the relations. Relations are a higher-level abstraction used at the application level and do not create foreign keys implicitly in the database schema.

One-to-one relations with r.one()

One-to-one relations are defined using r.one(). The foreign key can be stored in either table. When the foreign key is in the related table (not the main table), the relation is nullable in TypeScript. Example: a user has one profile, where userId is in the profile_info table, making user.profileInfo nullable.

Many-to-many relations with junction tables

Many-to-many relations are defined using junction (join) tables that explicitly store associations between related tables. The through() method is used to bypass junction table selection and directly select related entities. Example: users and groups connected through usersToGroups junction table with userId and groupId columns.

Many-to-many relation with through() syntax

In many-to-many relations, use the through() method on both the from and to fields to specify the junction table columns. Example: from: r.users.id.through(r.usersToGroups.userId), to: r.groups.id.through(r.usersToGroups.groupId)

Predefined filters in relations with where clauses

The where clause in relation definitions allows filtering on the target (to) table only. This creates polymorphic relations where related data is filtered based on specific conditions. Example: a group can have verifiedUsers relation that only retrieves users where verified: true.

defineRelationsPart for splitting relations configuration

Use defineRelationsPart to separate relation definitions into multiple parts. Main relations must be spread first, followed by parts: { ...relations, ...part }. At least one part should define all tables (empty defineRelationsPart() if needed) so Drizzle can infer all table names for autocomplete.

Querying relations with findMany and with parameter

Drizzle relational queries use the with parameter to include related data. Example: db.query.posts.findMany({ with: { author: true } }) fetches posts with their related authors. The with parameter accepts boolean values or nested relation queries.

One-to-one relation indexing strategy

For optimal performance in one-to-one relationships, create an index on the foreign key column in the target table (the table being referenced). This speeds up JOIN operations by allowing the database to quickly locate related rows. Example: index on userId in profile_info table when joining with users.

One-to-many relation indexing strategy

For one-to-many relationships, create an index on the foreign key column in the "many" side table (the table with the foreign key). Example: index on authorId in posts table to optimize queries fetching users with their posts.

Many-to-many relation indexing strategy

For many-to-many relationships, create three indexes on the junction table: one on each foreign key column individually, and one composite index on both foreign keys together. Example: index on userId, index on groupId, and composite index on (userId, groupId) in usersToGroups table.

Relations vs foreign keys in Drizzle

Relations and foreign keys are separate concepts. Foreign keys are database-level constraints checked on insert/update/delete operations. Relations are application-level abstractions that do not create foreign keys implicitly. They can be used independently - you can define relations without foreign keys or vice versa, allowing relations to work with databases that do not support foreign keys.

Using alias to disambiguate multiple relations between tables

When defining multiple relations between the same two tables, use the alias option to disambiguate them. Example: a posts table can have both author and reviewer relations to users, each with different alias values ("author" and "reviewer") to distinguish them.

One-to-one self-referencing relation example

A user can invite another user with a self-referencing one-to-one relation. Define invitedBy column as integer, then create a relation r.one.users({ from: r.users.invitedBy, to: r.users.id }) to get the inviter from invitee.

Prepared statement placeholder example in where clause

Example: db.query.users.findMany({ where: { id: { eq: sql.placeholder('id') } } }).prepare() creates a prepared statement that can be executed with .execute({ id: 1 })

Relational queries overview and purpose

Relational queries in Drizzle ORM provide a great developer experience for querying nested relational data from an SQL database, avoiding multiple joins and complex data mappings. They are an extension to the existing schema definition and query builder that can be opted into based on needs, designed to provide both best-in-class developer experience and performance.

Relational queries require callback parameters not direct table references

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.

Drizzle outputs a single SQL statement for relational queries with partial selects

Drizzle performs partial selects on the query level, with no additional data transferred from the database. A single SQL statement is outputted by Drizzle for relational queries.

Relational query API methods: findMany and findFirst

Drizzle provides two main relational query API methods: findMany() which returns an array of results, and findFirst() which returns a single result and adds LIMIT 1 to the query.

Include relations with 'with' operator

The 'with' operator lets you combine data from multiple related tables and properly aggregate results. You can chain nested with statements as much as necessary. For nested with queries, Drizzle will infer types using Core Type API.

Partial fields select with columns parameter

The 'columns' parameter lets you include or omit columns you want to get from the database. You can use columns: { id: true, content: true } to include specific fields, or columns: { content: false } to exclude fields. When both true and false select options are present, all false options are ignored. If you include any field as true, all other fields not explicitly set to true are excluded.

Nested partial fields select in relations

Just like with partial select, you can include or exclude columns of nested relations by using the columns parameter within the nested with block.

Multiple placeholders in prepared statements

Prepared statements can use multiple placeholders with different names. Example: sql.placeholder('uLimit'), sql.placeholder('uOffset'), sql.placeholder('id'), sql.placeholder('pid'), sql.placeholder('pLimit'). All placeholders are passed to .execute() as an object with matching keys.

Filter operators in relational queries where clause

The where clause supports the following filter operators: OR, AND, NOT, RAW (with callback syntax), eq, ne, gt, gte, lt, lte, in, notIn, like, notLike, isNull, isNotNull. RAW operator accepts a callback that takes the table parameter and returns sql with custom SQL logic. Relation filtering is also supported.

Relations filters in relational queries

With Drizzle Relations, you can filter not only by the table you're querying but also by any table you include in the query. You can filter by relation existence (e.g., where: { posts: true } gets users with at least one post) and by nested relation properties.

Limit and offset in relational queries

Drizzle ORM provides limit and offset API for queries and for nested entities. Both can be applied to the main query and to nested relations in the with block.

Order by in relational queries

Drizzle provides API for ordering in the relational query builder. You can use the same ordering core API or use order by operator from the callback with no imports. The format is orderBy: { id: 'asc' } or orderBy: (table) => sql`${table.id} asc`. When you use multiple orderBy statements in the same table, they will be included in the query in the same order in which you added them.

Include custom fields with extras in relational queries

Relational query API lets you add custom additional fields using the extras parameter. This is useful when you need to retrieve data and apply additional functions to it. Extras accepts an object where each key becomes a field in the result, and the value is a callback function that receives the table parameter and a helpers object containing sql function. Aggregations are not supported in extras; use core queries for that instead.

Extras field aliases are ignored by Drizzle

If you specify .as() for any extras field, Drizzle will ignore it.

Subqueries in relational query extras

You can use subqueries within Relational Queries extras to leverage custom SQL syntax. Use db.$count(table, whereCondition) to count related records with a condition.

Prepared statements in relational queries

Prepared statements are designed to massively improve query performance in Drizzle. They can be used in relational queries by calling .prepare() on the query and then executing with .execute(parameters). Placeholders can be used in where conditions, limit, and offset using sql.placeholder('name').

Prepared statement placeholder example in limit

Example: db.query.users.findMany({ with: { posts: { limit: sql.placeholder('limit') } } }).prepare() creates a prepared statement with placeholder in nested limit, executed with .execute({ limit: 1 })

Prepared statement placeholder example in offset

Example: db.query.users.findMany({ offset: sql.placeholder('offset'), with: { posts: true } }).prepare() creates a prepared statement with placeholder in offset, executed with .execute({ offset: 1 })

UNION example with import-pattern

import { union } from 'drizzle-orm/sqlite-core' import { users, customers } from './schema' const allNamesForUserQuery = db.select({ name: users.name }).from(users); const result = await union( allNamesForUserQuery, db.select({ name: customers.name }).from(customers) ).limit(10);

Set operations can be chained with other query methods

Set operations like union(), unionAll(), intersect(), and except() can be combined with other query builder methods. For example, .limit() can be applied after a union operation: db.select().from(table1).union(db.select().from(table2)).limit(10).

EXCEPT example with import-pattern

import { except } from 'drizzle-orm/sqlite-core' import { depA, depB } from './schema' const departmentACourses = db.select({ courseName: depA.projectsName }).from(depA); const departmentBCourses = db.select({ courseName: depB.projectsName }).from(depB); const result = await except(departmentACourses, departmentBCourses);

INTERSECT example with import-pattern

import { intersect } from 'drizzle-orm/sqlite-core' import { depA, depB } from './schema' const departmentACourses = db.select({ courseName: depA.courseName }).from(depA); const departmentBCourses = db.select({ courseName: depB.courseName }).from(depB); const result = await intersect(departmentACourses, departmentBCourses);

Give your agent this brain