drizzle-orm/relations

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

One-to-many relation definition

One-to-many relations are defined using the `many()` operator in the parent table's relations. The child table defines a `one()` relation back to the parent with fields and references. Example: users have many posts; posts belong to one user via authorId field.

Relational queries overview with `with` syntax

Drizzle relational queries allow fetching related data in a simple and concise way using the `with` syntax. Example: `db._query.users.findMany({ with: { posts: true } })` fetches users with all their related posts in a single query, returning nested objects.

One-to-one relation definition with self-reference

A one-to-one relation can reference the same table (self-reference). Example: users table with `invitedBy` field referencing `users.id`. Define using `relations()` and `one()` operator with fields and references properties.

One-to-one relation with foreign key in related table

When a one-to-one foreign key is stored in the related table (not the current table), omit the fields and references from the current table's relation definition. This makes the relation nullable in TypeScript. The related table defines the foreign key with references().

Drizzle relational query syntax example

The relational query API uses a with property to specify related data to fetch. Example: const result = await db.query.users.findMany({ with: { posts: true } });

Drizzle relational query API single SQL query

Drizzle always outputs exactly 1 SQL query regardless of the complexity of relational queries. This makes it safe to use with serverless databases and eliminates concerns about performance or roundtrip costs.

Drizzle relations query syntax

Drizzle relations allow querying relational data using the findMany method with a 'with' object. Example: db.query.users.findMany({ with: { posts: true } }) returns users with their related posts nested in an array.

One-to-one relations definition

Use relations() with the one() function to define one-to-one relations. When the foreign key is on the current table, specify fields and references. When the foreign key is on the related table, omit fields and references, which makes the relation nullable.

Disambiguating multiple relations between same tables

When defining multiple relations between the same two tables, use the relationName option to disambiguate them. This option is specified in both the many()/one() call and must match between related sides. Example: many(posts, { relationName: 'author' }) paired with one(users, { relationName: 'author' }).

One-to-many relations definition

Use relations() with the many() function on the parent table and one() on the child table. The many() side has no configuration, while the one() side specifies fields and references to the parent table.

Many-to-many relations with junction tables

Many-to-many relations require an explicit junction/join table that stores associations between related tables. Both tables use many() to reference the junction table, and the junction table uses one() relations to reference both related tables with their respective fields and references.

SingleStore relational API not supported

The relational API is not supported by SingleStore in Drizzle ORM and will be implemented once the SingleStore team develops the necessary APIs for it.

Relational queries overview

Relational queries in Drizzle ORM are an extension to the query builder designed to provide an excellent developer experience for querying nested relational data from SQL databases, avoiding multiple joins and complex data mappings. They are opt-in and work alongside the existing schema definition and query builder. Relational queries require all tables and relations from the schema to be provided to the drizzle() initialization function.

findFirst() API for relational queries

The findFirst() method retrieves a single record from a table. It automatically adds a LIMIT 1 clause to the query. Example: await db.query.users.findFirst() returns the first user or undefined.

Where filters in relational queries

Relational queries support filtering using the where clause with operators: OR, AND, NOT, RAW, eq, ne, gt, gte, lt, lte, in, notIn, like, notLike, isNull, isNotNull. You can filter by columns and relations. Example: db.query.users.findMany({ where: { id: 1 } }) filters users by id.

Relation filters in findMany queries

You can filter not only by the table you're querying but also by any table included in the query using relations. Example: db.query.usersTable.findMany({ where: { id: { gt: 10 }, posts: { content: { like: 'M%' } } } }) gets all users with id > 10 who have at least one post starting with 'M'.

Single SQL statement output from relational queries

Drizzle outputs a single SQL statement for relational queries, even when using partial selects and nested relations. No additional data is transferred from the database beyond what is selected.

Initialize Drizzle with relations for relational queries

To use relational queries, pass all tables and relations from your schema to the drizzle() initialization function. The drizzle import path depends on the database driver being used. Example: const db = drizzle({ relations }) where relations contains all table and relation definitions.

Subqueries in relational queries

Subqueries can be used within relational queries using the extras parameter to perform advanced operations like counting related records. Example: db.query.users.findMany({ with: { posts: true }, extras: { totalPostsCount: (table) => db.$count(posts, eq(posts.authorId, table.id)) } }) returns users with posts and a count of total posts for each user.

Using sql template in extras with callback syntax

Extras fields can be defined using a callback function that receives the table and an object containing the sql function. Example: db.query.users.findMany({ extras: { loweredName: (users, { sql }) => sql`lower(${users.name})` } }).

Extras parameter for custom fields in relational queries

The extras parameter in relational queries lets you add custom fields to results by applying SQL functions. These fields are included in all returned objects. Aggregations are not supported in extras; use core queries instead. The .as() method for extras fields is ignored by Drizzle. Example: db.query.users.findMany({ extras: { loweredName: sql`lower(${users.name})` } }).

Order by in relational queries

Relational queries support ordering results using the orderBy parameter. You can order by column in asc or desc direction, or use custom SQL. When multiple orderBy statements are used on the same table, they are included in the query in the order they were added. Example: db.query.posts.findMany({ orderBy: { id: 'asc' }, with: { comments: { orderBy: { id: 'desc' } } } }).

Limit and offset in relational queries

Drizzle ORM provides limit and offset parameters for both the main query and nested relations. Limit restricts the number of records returned, offset skips records. Both can be used on the main query and within nested with relations. Example: db.query.posts.findMany({ limit: 5, offset: 2, with: { comments: { limit: 3, offset: 3 } } }) returns 5 posts starting from offset 2, with 3 comments starting from offset 3 for each post.

Filtering to ensure related records exist

You can filter by a relation to only return records that have at least one related record. Example: db.query.users.findMany({ with: { posts: true }, where: { posts: true } }) returns only users who have at least one post.

one-to-one relation indexing strategy

For optimal one-to-one relation performance, create an index on the foreign key column in the target table being referenced. For example, if users references profileInfo.userId, create an index on profileInfo.userId to speed up JOIN operations.

Drizzle relational queries vs SQL joins

Drizzle relations provide a simpler and more concise API for querying relational data compared to writing manual SQL joins. Instead of using leftJoin and manual result mapping, you can use the `with` option in relational queries to fetch related data declaratively.

one() relation definition fields

The one() method in defineRelations accepts: author (custom key name), r.one.users (target table), from (source column), to (target column), optional (boolean, defaults to false when omitted, makes relation required if false), alias (string, differentiates multiple identical relations between same tables), where (object, filters relations based on where statement for polymorphic relations).

many() relation definition fields

The many() method in defineRelations accepts: feed (custom key name), r.many.posts (target table), from (source column), to (target column), alias (string, differentiates multiple identical relations between same tables), where (object, filters relations based on where statement for polymorphic relations).

one-to-one self-referencing relation example

A user can invite another user using a self-referencing one-to-one relation. The invitedBy column stores the ID of the inviting user, and the relation uses r.one.users() with from: r.users.invitedBy and to: r.users.id.

one-to-one with foreign key in related table

When the foreign key is stored in the related table (not the source), the relation is nullable. For example, if profileInfo table has userId foreign key referencing users, querying user.profileInfo will have type { ... } | null since the foreign key exists in the profile_info table.

one-to-many relation example with users and posts

Define one-to-many by creating r.one.users() relation on the posts table (from: r.posts.authorId, to: r.users.id) and r.many.posts() relation on the users table. This allows fetching users with their posts or posts with their authors.

many-to-many relation through junction table

Many-to-many relations require explicit junction/join tables. Use r.many.groups() with from: r.users.id.through(r.usersToGroups.userId) and to: r.groups.id.through(r.usersToGroups.groupId). The through() method chains the junction table columns to establish the relationship.

many-to-many query example with response type

Query many-to-many with await db.query.users.findMany({ with: { groups: true } }). The response type is an array of objects with id, name, and groups array containing related group objects.

Predefined filters in relations (where clause)

The where option in relation definitions acts as a polymorphic relation implementation, allowing you to filter related data by custom conditions. For example, r.many.users({ where: { verified: true } }) only retrieves verified users. The where clause can only filter on the target (to) table.

Predefined filters can only target the 'to' table

When using the where clause in relation definitions, you can only filter on columns from the target table (the 'to' side of the relation). Filters on the source ('from') table are not supported.

defineRelationsPart for separating relations config

Use defineRelationsPart to split relation definitions across multiple files or sections. Export a main relation definition and one or more part definitions, then merge them: const db = drizzle(url, { relations: { ...relations, ...part } }).

defineRelationsPart ordering rule

When using defineRelationsPart, main relations must be spread first: { ...relations, ...part }. Reverse order ({ ...part, ...relations }) will cause the main relation's recursively inferred table names to be lost in autocomplete.

defineRelationsPart minimum relations requirement

If using only defineRelationsPart with no main defineRelations, create an empty main part: const mainPart = defineRelationsPart(schema). This allows Drizzle to infer all table names for proper autocomplete.

one-to-one index example

To optimize one-to-one queries with profileInfo: index('profile_info_user_id_idx').on(table.userId) or CREATE INDEX idx_profile_info_user_id ON profile_info (user_id);

one-to-many relation indexing strategy

For one-to-many relations, create an index on the foreign key column in the table that represents the 'many' side. For users and posts, index the authorId column in posts table to efficiently retrieve posts for a user or find author of a post.

one-to-many index example

To optimize one-to-many queries: index('posts_author_id_idx').on(table.authorId) or CREATE INDEX idx_posts_author_id ON posts (author_id);

many-to-many junction table indexing strategy

For many-to-many relations using junction tables, create three indexes: individual index on each foreign key column (userId, groupId) for single-side queries, and a composite index on both columns together for efficient many-to-many resolution.

many-to-many index example

For users_to_groups junction table: index('users_to_groups_user_id_idx').on(table.userId), index('users_to_groups_group_id_idx').on(table.groupId), index('users_to_groups_composite_idx').on(table.userId, table.groupId), or use CREATE INDEX statements with same names and columns.

Difference between relations and foreign keys

Foreign keys are database-level constraints checked on every insert/update/delete operation and throw errors on violation. Relations are application-level abstractions that do not affect database schema or create foreign keys implicitly. They can be used independently or together, and relations work with databases that don't support foreign keys.

Disambiguating multiple relations between same tables with alias

Use the alias option to differentiate multiple relations between the same two tables. For example, posts can have both author and reviewer relations to users. Specify alias on both the many() side (users table) and one() side (posts table) to disambiguate them.

Alias for multiple relations example

For posts with author and reviewer relations to users: r.many.posts({ alias: 'author' }) and r.many.posts({ alias: 'reviewer' }) on users, plus r.one.users({ from: r.posts.authorId, to: r.users.id, alias: 'author' }) and r.one.users({ from: r.posts.reviewerId, to: r.users.id, alias: 'reviewer' }) on posts.

v1 relation imports removed in v2

The following v1 entities have been removed from drizzle-orm and drizzle-orm/relations: Relations, TableRelationsKeysOnly, ExtractTableRelationsFromSchema, ExtractRelationsFromTableExtraConfigSchema, getOperators, FindTableByDBName, RelationalSchemaConfig, RelationConfig, extractTablesRelationalConfig, relations (function), createOne, createMany, NormalizedRelation, normalizeRelation, createTableRelationsHelpers, TableRelationsHelpers.

v2 relations defined in one place with defineRelations

In Relational Queries v2, all relations for all tables are defined in a single dedicated place using defineRelations(). This replaces the v1 approach of defining separate relations objects for each table. The function accepts a schema object and a callback that receives an 'r' parameter providing autocomplete for all tables and relation functions like one(), many(), and through().

v2 predefined filters in many-to-many relations

Relations can include a where clause to define predefined filters. For example: verifiedUsers: r.many.users({ from: r.groups.id.through(r.usersToGroups.groupId), to: r.users.id.through(r.usersToGroups.userId), where: { verified: true } }). This allows filtering related objects without additional query steps.

v2 many-to-many relations with through

import * as schema from './schema'; import { defineRelations } from 'drizzle-orm'; 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(), }, })); This eliminates the need to explicitly query through junction tables and manually map results.

v2 relationName renamed to alias

The relationName parameter in v1 relations has been renamed to alias in v2. This is used in the same way to provide an alternative name for the relation.

v2 relations renamed fields to from and references to to

In v2, the field names for relation configuration changed: 'fields' became 'from' and 'references' became 'to'. Both from and to now accept either a single value or an array. Example: r.one.users({ from: r.posts.authorId, to: r.users.id }) or r.one.users({ from: [r.posts.authorId], to: [r.users.id] })

v2 optional parameter in one relations

The optional: false parameter at the type level makes the related entity key in the object required. Setting optional: false indicates that this specific entity will always exist and should not be nullable in the result type.

v2 relations structure with r parameter

import * as schema from './schema'; import { defineRelations } from 'drizzle-orm'; export const relations = 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, }), }, }));

v2 relations can be split with defineRelationsPart

Relations can be split into multiple parts using defineRelationsPart() for better organization. Each part is defined the same way as the main defineRelations() call. Multiple parts can then be combined with spread operators when providing to the db instance: const db = drizzle(process.env.DB_URL, { relations: { ...relations, ...part } })

v2 many relations do not require matching one relations

In v2, you can define a 'many' relationship without defining the corresponding 'one' relationship on the other side. For example, you can define posts: r.many.posts({ from: r.users.id, to: r.posts.authorId }) in the users relation without needing to define an author: one(...) in the posts relation.

extras for custom fields in relational queries

The extras parameter lets you add custom additional fields to results. You can use SQL expressions with the sql tagged template. You must explicitly specify .as('<name_for_column>') for each custom field. Aggregations are not supported in extras; use core queries for that. Example: extras: { loweredName: sql`lower(${users.name})`.as('lowered_name') }.

Example: partial field selection with columns

This example selects only specific columns from posts and includes comments: const posts = await db._query.posts.findMany({ columns: { id: true, content: true, }, with: { comments: true, } });

Example: relational query with where and nested filters

This example finds a post with id=1 and filters its comments by date: await db._query.posts.findMany({ where: (posts, { eq }) => (eq(posts.id, 1)), with: { comments: { where: (comments, { lt }) => lt(comments.createdAt, new Date()), }, }, });

Example: extras with SQL expressions

This example retrieves users with a custom fullName field concatenating the name field twice: const res = await db._query.users.findMany({ extras: { fullName: sql<string>`concat(${users.name}, " ", ${users.name})`.as('full_name'), }, with: { usersToGroups: { with: { group: true, }, }, }, });

Give your agent this brain