0
# Relational Queries
1

2
Drizzle ORM provides a powerful relational query system that allows you to define relationships between tables and perform complex queries with automatic joins and nested results.
3

4
## Defining Relations
5

6
### One-to-One Relations
7

8
```typescript { .api }
9
function one<T extends AnyTable>(
10
  table: T,
11
  config?: {
12
    fields: AnyColumn[];
13
    references: AnyColumn[];
14
    relationName?: string;
15
  }
16
): One<T>;
17
```
18

19
### One-to-Many Relations
20

21
```typescript { .api }
22
function many<T extends AnyTable>(
23
  table: T,
24
  config?: {
25
    relationName?: string;
26
  }
27
): Many<T>;
28
```
29

30
### Relations Configuration
31

32
```typescript { .api }
33
function relations<T extends AnyTable>(
34
  table: T,
35
  callback: (helpers: RelationHelpers<T>) => Record<string, Relation>
36
): Relations<T>;
37

38
interface RelationHelpers<T extends AnyTable> {
39
  one: <TForeign extends AnyTable>(
40
    table: TForeign,
41
    config: OneConfig<T, TForeign>
42
  ) => One<TForeign>;
43
  many: <TForeign extends AnyTable>(
44
    table: TForeign,
45
    config?: ManyConfig
46
  ) => Many<TForeign>;
47
}
48
```
49

50
## Usage Examples
51

52
### Defining Table Relations
53

54
```typescript
55
import { relations, one, many } from "drizzle-orm";
56

57
// Tables
58
export const users = pgTable("users", {
59
  id: serial("id").primaryKey(),
60
  name: text("name").notNull(),
61
  email: text("email").unique(),
62
});
63

64
export const posts = pgTable("posts", {
65
  id: serial("id").primaryKey(),
66
  title: text("title").notNull(),
67
  content: text("content"),
68
  authorId: integer("author_id").references(() => users.id),
69
  categoryId: integer("category_id").references(() => categories.id),
70
});
71

72
export const categories = pgTable("categories", {
73
  id: serial("id").primaryKey(),
74
  name: text("name").notNull(),
75
});
76

77
export const comments = pgTable("comments", {
78
  id: serial("id").primaryKey(),
79
  content: text("content").notNull(),
80
  postId: integer("post_id").references(() => posts.id),
81
  authorId: integer("author_id").references(() => users.id),
82
});
83

84
// Relations
85
export const usersRelations = relations(users, ({ many }) => ({
86
  posts: many(posts),
87
  comments: many(comments),
88
}));
89

90
export const postsRelations = relations(posts, ({ one, many }) => ({
91
  author: one(users, {
92
    fields: [posts.authorId],
93
    references: [users.id],
94
  }),
95
  category: one(categories, {
96
    fields: [posts.categoryId],
97
    references: [categories.id],
98
  }),
99
  comments: many(comments),
100
}));
101

102
export const categoriesRelations = relations(categories, ({ many }) => ({
103
  posts: many(posts),
104
}));
105

106
export const commentsRelations = relations(comments, ({ one }) => ({
107
  post: one(posts, {
108
    fields: [comments.postId],
109
    references: [posts.id],
110
  }),
111
  author: one(users, {
112
    fields: [comments.authorId],
113
    references: [users.id],
114
  }),
115
}));
116
```
117

118
## Relational Query Builder
119

120
### Database with Relations
121

122
```typescript { .api }
123
interface DatabaseWithRelations<TSchema extends Record<string, unknown>> {
124
  query: TSchema extends Record<string, never> ? never : RelationalQueryBuilder<TSchema>;
125
}
126

127
interface RelationalQueryBuilder<TSchema> {
128
  [K in keyof TSchema]: TSchema[K] extends AnyTable
129
    ? TableRelationalQuery<TSchema[K], TSchema>
130
    : never;
131
}
132

133
interface TableRelationalQuery<T extends AnyTable, TSchema> {
134
  findFirst(config?: RelationalQueryConfig<T, TSchema>): Promise<InferSelectModel<T> | undefined>;
135
  findMany(config?: RelationalQueryConfig<T, TSchema>): Promise<InferSelectModel<T>[]>;
136
}
137
```
138

139
### Query Configuration
140

141
```typescript { .api }
142
interface RelationalQueryConfig<T extends AnyTable, TSchema> {
143
  columns?: ColumnsSelection<T>;
144
  with?: WithSelection<T, TSchema>;
145
  where?: SQL;
146
  orderBy?: OrderByConfig<T>;
147
  limit?: number;
148
  offset?: number;
149
}
150

151
type WithSelection<T extends AnyTable, TSchema> = {
152
  [K in keyof InferRelations<T, TSchema>]?: boolean | RelationalQueryConfig<any, TSchema>;
153
};
154

155
type ColumnsSelection<T extends AnyTable> = {
156
  [K in keyof InferSelectModel<T>]?: boolean;
157
};
158
```
159

160
## Relational Query Examples
161

162
### Basic Queries with Relations
163

164
```typescript
165
// Database instance with relations
166
const db = drizzle(client, {
167
  schema: { users, posts, categories, comments, usersRelations, postsRelations, categoriesRelations, commentsRelations }
168
});
169

170
// Find user with their posts
171
const userWithPosts = await db.query.users.findFirst({
172
  with: {
173
    posts: true,
174
  },
175
});
176

177
// Find post with author and comments
178
const postWithDetails = await db.query.posts.findFirst({
179
  with: {
180
    author: true,
181
    comments: {
182
      with: {
183
        author: true,
184
      },
185
    },
186
  },
187
});
188
```
189

190
### Advanced Relational Queries
191

192
```typescript
193
// Selective columns and nested relations
194
const result = await db.query.users.findMany({
195
  columns: {
196
    id: true,
197
    name: true,
198
    // email excluded
199
  },
200
  with: {
201
    posts: {
202
      columns: {
203
        id: true,
204
        title: true,
205
        // content excluded
206
      },
207
      with: {
208
        category: true,
209
      },
210
    },
211
  },
212
  where: eq(users.id, 1),
213
});
214

215
// Conditional relation loading
216
const posts = await db.query.posts.findMany({
217
  with: {
218
    author: true,
219
    category: true,
220
    comments: {
221
      where: eq(comments.content, "Great post!"),
222
      with: {
223
        author: {
224
          columns: {
225
            id: true,
226
            name: true,
227
          },
228
        },
229
      },
230
    },
231
  },
232
  orderBy: [desc(posts.id)],
233
  limit: 10,
234
});
235
```
236

237
### Filtering and Ordering Relations
238

239
```typescript
240
// Filter posts by category and include comments
241
const techPosts = await db.query.categories.findFirst({
242
  where: eq(categories.name, "Technology"),
243
  with: {
244
    posts: {
245
      with: {
246
        author: true,
247
        comments: {
248
          orderBy: [desc(comments.id)],
249
          limit: 5,
250
        },
251
      },
252
      orderBy: [desc(posts.id)],
253
    },
254
  },
255
});
256

257
// Complex filtering
258
const activeUsersWithRecentPosts = await db.query.users.findMany({
259
  with: {
260
    posts: {
261
      where: gte(posts.createdAt, new Date('2023-01-01')),
262
      with: {
263
        category: true,
264
      },
265
    },
266
  },
267
  where: isNotNull(users.email),
268
});
269
```
270

271
## Self-Referencing Relations
272

273
### Hierarchical Data
274

275
```typescript
276
export const categories = pgTable("categories", {
277
  id: serial("id").primaryKey(),
278
  name: text("name").notNull(),
279
  parentId: integer("parent_id").references(() => categories.id),
280
});
281

282
export const categoriesRelations = relations(categories, ({ one, many }) => ({
283
  parent: one(categories, {
284
    fields: [categories.parentId],
285
    references: [categories.id],
286
    relationName: "parent",
287
  }),
288
  children: many(categories, {
289
    relationName: "parent",
290
  }),
291
}));
292

293
// Query hierarchical data
294
const categoryWithChildren = await db.query.categories.findFirst({
295
  where: isNull(categories.parentId), // Root category
296
  with: {
297
    children: {
298
      with: {
299
        children: true, // Grandchildren
300
      },
301
    },
302
  },
303
});
304
```
305

306
## Many-to-Many Relations
307

308
### Junction Table Pattern
309

310
```typescript
311
export const users = pgTable("users", {
312
  id: serial("id").primaryKey(),
313
  name: text("name").notNull(),
314
});
315

316
export const roles = pgTable("roles", {
317
  id: serial("id").primaryKey(),
318
  name: text("name").notNull(),
319
});
320

321
export const usersToRoles = pgTable("users_to_roles", {
322
  userId: integer("user_id").references(() => users.id),
323
  roleId: integer("role_id").references(() => roles.id),
324
}, (table) => ({
325
  pk: primaryKey({ columns: [table.userId, table.roleId] }),
326
}));
327

328
export const usersRelations = relations(users, ({ many }) => ({
329
  usersToRoles: many(usersToRoles),
330
}));
331

332
export const rolesRelations = relations(roles, ({ many }) => ({
333
  usersToRoles: many(usersToRoles),
334
}));
335

336
export const usersToRolesRelations = relations(usersToRoles, ({ one }) => ({
337
  user: one(users, {
338
    fields: [usersToRoles.userId],
339
    references: [users.id],
340
  }),
341
  role: one(roles, {
342
    fields: [usersToRoles.roleId],
343
    references: [roles.id],
344
  }),
345
}));
346

347
// Query many-to-many relationships
348
const userWithRoles = await db.query.users.findFirst({
349
  with: {
350
    usersToRoles: {
351
      with: {
352
        role: true,
353
      },
354
    },
355
  },
356
});
357
```
358

359
## Performance Considerations
360

361
### Optimizing Relational Queries
362

363
```typescript
364
// Use selective column loading
365
const optimized = await db.query.posts.findMany({
366
  columns: {
367
    id: true,
368
    title: true,
369
    // Skip large content field
370
  },
371
  with: {
372
    author: {
373
      columns: {
374
        id: true,
375
        name: true,
376
        // Skip email and other sensitive fields
377
      },
378
    },
379
  },
380
});
381

382
// Limit nested results
383
const limited = await db.query.users.findMany({
384
  with: {
385
    posts: {
386
      limit: 5,
387
      orderBy: [desc(posts.id)],
388
      with: {
389
        comments: {
390
          limit: 3,
391
          orderBy: [desc(comments.id)],
392
        },
393
      },
394
    },
395
  },
396
});
397
```
398

399
### Query vs. Manual Joins
400

401
```typescript
402
// Relational query (convenient, automatic joins)
403
const relationalResult = await db.query.posts.findMany({
404
  with: { author: true, category: true },
405
});
406

407
// Manual join (more control, potentially better performance)
408
const manualResult = await db
409
  .select()
410
  .from(posts)
411
  .leftJoin(users, eq(posts.authorId, users.id))
412
  .leftJoin(categories, eq(posts.categoryId, categories.id));
413
```
414

415
## Types
416

417
```typescript { .api }
418
type InferSelectModel<T extends AnyTable> = T extends Table<infer TConfig>
419
  ? TConfig['columns'] extends Record<string, Column<infer TColumnConfig>>
420
    ? {
421
        [K in keyof TConfig['columns']]: TConfig['columns'][K] extends Column<infer C>
422
          ? C['data']
423
          : never;
424
      }
425
    : never
426
  : never;
427

428
type InferInsertModel<T extends AnyTable> = T extends Table<infer TConfig>
429
  ? TConfig['columns'] extends Record<string, Column<infer TColumnConfig>>
430
    ? {
431
        [K in keyof TConfig['columns'] as TConfig['columns'][K] extends Column<infer C>
432
          ? C['notNull'] extends true
433
            ? C['hasDefault'] extends true
434
              ? K
435
              : K
436
            : K
437
          : never
438
        ]?: TConfig['columns'][K] extends Column<infer C> ? C['data'] : never;
439
      } & {
440
        [K in keyof TConfig['columns'] as TConfig['columns'][K] extends Column<infer C>
441
          ? C['notNull'] extends true
442
            ? C['hasDefault'] extends true
443
              ? never
444
              : K
445
            : never
446
          : never
447
        ]: TConfig['columns'][K] extends Column<infer C> ? C['data'] : never;
448
      }
449
    : never
450
  : never;
451

452
interface One<T extends AnyTable> {
453
  readonly referencedTable: T;
454
  readonly config: OneConfig<any, T>;
455
}
456

457
interface Many<T extends AnyTable> {
458
  readonly referencedTable: T;
459
  readonly config?: ManyConfig;
460
}
461

462
interface OneConfig<T extends AnyTable, TForeign extends AnyTable> {
463
  fields: ExtractTablesWithColumns<T['$inferSelect'], AnyColumn>[];
464
  references: ExtractTablesWithColumns<TForeign['$inferSelect'], AnyColumn>[];
465
  relationName?: string;
466
}
467

468
interface ManyConfig {
469
  relationName?: string;
470
}
471

472
type Relations<T extends AnyTable> = Record<string, One<any> | Many<any>>;
473

474
interface RelationHelpers<T extends AnyTable> {
475
  one: <TForeign extends AnyTable>(
476
    table: TForeign,
477
    config: OneConfig<T, TForeign>
478
  ) => One<TForeign>;
479
  many: <TForeign extends AnyTable>(
480
    table: TForeign,
481
    config?: ManyConfig
482
  ) => Many<TForeign>;
483
}
484
```