Drizzle’s relations API enables you to define relationships between tables and query them using an intuitive, type-safe syntax. Relations are defined separately from table schemas and power the relational query API.
Import
import { relations } from 'drizzle-orm';
Defining Relations
relations()
Define relationships between tables:
The table to define relations for
Function that receives helpers and returns relation definitions
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
export const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
});
export const posts = pgTable('posts', {
id: serial('id').primaryKey(),
title: text('title').notNull(),
authorId: integer('author_id').notNull(),
});
export const usersRelations = relations(users, ({ one, many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one, many }) => ({
author: one(users, {
fields: [posts.authorId],
references: [users.id],
}),
}));
Relation Helpers
The relations function provides two helper functions:
one()
Define a one-to-one or many-to-one relationship:
Optional configuration for the relation
Foreign key columns in the current table
Referenced columns in the related table
Optional name to disambiguate multiple relations between the same tables
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, {
fields: [posts.authorId],
references: [users.id],
}),
}));
export const profilesRelations = relations(profiles, ({ one }) => ({
// Drizzle infers fields/references from the users.profile relation
user: one(users),
}));
many()
Define a one-to-many relationship:
Optional name to disambiguate multiple relations
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
comments: many(comments),
}));
Relation Types
One-to-One
export const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name'),
});
export const profiles = pgTable('profiles', {
id: serial('id').primaryKey(),
userId: integer('user_id').notNull().unique(),
bio: text('bio'),
});
export const usersRelations = relations(users, ({ one }) => ({
profile: one(profiles, {
fields: [profiles.userId],
references: [users.id],
}),
}));
export const profilesRelations = relations(profiles, ({ one }) => ({
user: one(users, {
fields: [profiles.userId],
references: [users.id],
}),
}));
One-to-Many
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, {
fields: [posts.authorId],
references: [users.id],
}),
}));
Many-to-Many
Implemented using a junction table:
export const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name'),
});
export const groups = pgTable('groups', {
id: serial('id').primaryKey(),
name: text('name'),
});
export const usersToGroups = pgTable('users_to_groups', {
userId: integer('user_id').notNull(),
groupId: integer('group_id').notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
usersToGroups: many(usersToGroups),
}));
export const groupsRelations = relations(groups, ({ many }) => ({
usersToGroups: many(usersToGroups),
}));
export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
user: one(users, {
fields: [usersToGroups.userId],
references: [users.id],
}),
group: one(groups, {
fields: [usersToGroups.groupId],
references: [groups.id],
}),
}));
Querying Relations
Basic Relational Query
const result = await db.query.users.findMany({
with: {
posts: true,
},
});
// Returns users with their posts
Nested Relations
const result = await db.query.users.findMany({
with: {
posts: {
with: {
comments: true,
},
},
},
});
import { eq, gt } from 'drizzle-orm';
const result = await db.query.users.findMany({
with: {
posts: {
where: (posts, { eq }) => eq(posts.published, true),
orderBy: (posts, { desc }) => desc(posts.createdAt),
limit: 10,
},
},
});
Selecting Specific Columns
const result = await db.query.users.findMany({
columns: {
id: true,
name: true,
},
with: {
posts: {
columns: {
id: true,
title: true,
},
},
},
});
import { sql } from 'drizzle-orm';
const result = await db.query.users.findMany({
extras: {
lowerCaseName: sql<string>`lower(${users.name})`.as('lower_case_name'),
},
});
Query Configuration
DBQueryConfig Type
The configuration object for relational queries:
Select specific columns from the tablecolumns: {
id: true,
name: true,
email: false, // Explicitly exclude
}
Include related datawith: {
posts: true,
profile: {
columns: { bio: true },
},
}
Filter conditions for the querywhere: (users, { eq }) => eq(users.active, true)
orderBy
Column | SQL | function | array
Order the resultsorderBy: (users, { desc }) => desc(users.createdAt)
Limit the number of results Offset for pagination (only available on root queries) Additional computed fieldsextras: (fields, { sql }) => ({
fullName: sql<string>`concat(${fields.firstName}, ' ', ${fields.lastName})`.as('full_name'),
})
Relational Query Methods
findMany()
Find multiple records with relations:
const users = await db.query.users.findMany({
where: (users, { gt }) => gt(users.age, 18),
with: { posts: true },
});
findFirst()
Find the first matching record:
const user = await db.query.users.findFirst({
where: (users, { eq }) => eq(users.email, '[email protected]'),
with: { profile: true },
});
Disambiguating Relations
When you have multiple relations between the same tables, use relationName:
export const posts = pgTable('posts', {
id: serial('id').primaryKey(),
authorId: integer('author_id'),
reviewerId: integer('reviewer_id'),
});
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, {
fields: [posts.authorId],
references: [users.id],
relationName: 'author',
}),
reviewer: one(users, {
fields: [posts.reviewerId],
references: [users.id],
relationName: 'reviewer',
}),
}));
export const usersRelations = relations(users, ({ many }) => ({
authoredPosts: many(posts, { relationName: 'author' }),
reviewedPosts: many(posts, { relationName: 'reviewer' }),
}));
Operators in Relational Queries
The following operators are available in where and orderBy functions:
Comparison Operators
import { eq, ne, gt, gte, lt, lte } from 'drizzle-orm';
db.query.users.findMany({
where: (users, { eq, gt }) => eq(users.active, true),
});
Logical Operators
import { and, or, not } from 'drizzle-orm';
db.query.users.findMany({
where: (users, { and, eq, gt }) => and(
eq(users.active, true),
gt(users.age, 18)
),
});
Pattern Matching
import { like, ilike } from 'drizzle-orm';
db.query.users.findMany({
where: (users, { like }) => like(users.name, '%John%'),
});
Array Operators
import { inArray, notInArray } from 'drizzle-orm';
db.query.users.findMany({
where: (users, { inArray }) => inArray(users.id, [1, 2, 3]),
});
Order By Operators
import { asc, desc } from 'drizzle-orm';
db.query.users.findMany({
orderBy: (users, { desc }) => desc(users.createdAt),
});
Best Practices
- Define both sides: Always define relations on both tables for full type safety
- Use relationName: Disambiguate multiple relations between the same tables
- Select wisely: Only select columns you need to improve performance
- Limit nested queries: Deep nesting can impact performance
- Use where clauses: Filter related data to reduce payload size