Relations API

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:

Table

required

The table to define relations for

function

required

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:

Table

required

The related table

object

Optional configuration for the relation

Column[]

Foreign key columns in the current table

Column[]

Referenced columns in the related table

string

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],
  }),
}));

Implicit relations (inferred from the reverse side):

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:

Table

required

The related table

object

Optional configuration

string

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

Using extras

Add computed fields to the result:

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:

object

Select specific columns from the table

columns: {
  id: true,
  name: true,
  email: false, // Explicitly exclude
}

object

Include related data

with: {
  posts: true,
  profile: {
    columns: { bio: true },
  },
}

SQL | function

Filter conditions for the query

where: (users, { eq }) => eq(users.active, true)

Column | SQL | function | array

Order the results

orderBy: (users, { desc }) => desc(users.createdAt)

number | Placeholder

Limit the number of results

limit: 10

number | Placeholder

Offset for pagination (only available on root queries)

offset: 20

object | function

Additional computed fields

extras: (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, 'user@example.com'),
  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

Query Builder - Alternative query syntax
Schema Definition - Define table schemas
SQL - Use raw SQL in queries

PostgreSQL

MySQL

SQLite

Core APIs

Drizzle Kit

Import

Defining Relations

relations()

Relation Helpers

one()

many()

Relation Types

One-to-One

One-to-Many

Many-to-Many

Querying Relations

Basic Relational Query

Nested Relations

Selecting Specific Columns

Using extras

Query Configuration

DBQueryConfig Type

Relational Query Methods

findMany()

findFirst()

Disambiguating Relations

Operators in Relational Queries

Comparison Operators

Logical Operators

Pattern Matching

Array Operators

Order By Operators

Best Practices

​Import

​Defining Relations

​relations()

​Relation Helpers

​one()

​many()

​Relation Types

​One-to-One

​One-to-Many

​Many-to-Many

​Querying Relations

​Basic Relational Query

​Nested Relations

​Filtering Related Data

​Selecting Specific Columns

​Using extras

​Query Configuration

​DBQueryConfig Type

​Relational Query Methods

​findMany()

​findFirst()

​Disambiguating Relations

​Operators in Relational Queries

​Comparison Operators

​Logical Operators

​Pattern Matching

​Array Operators

​Order By Operators

​Best Practices

​Related APIs

Import

Defining Relations

relations()

Relation Helpers

one()

many()

Relation Types

One-to-One

One-to-Many

Many-to-Many

Querying Relations

Basic Relational Query

Nested Relations

Filtering Related Data

Selecting Specific Columns

Using extras

Query Configuration

DBQueryConfig Type

Relational Query Methods

findMany()

findFirst()

Disambiguating Relations

Operators in Relational Queries

Comparison Operators

Logical Operators

Pattern Matching

Array Operators

Order By Operators

Best Practices

Related APIs