Drizzle Relations Pattern

Drizzle Relations Pattern

Define Drizzle relations with relations(), one(), many(), references(), and inferred types

When to Use

• Defining relationships between Drizzle tables for the relational query API
• Enabling with clauses to load related data
• Modeling one-to-one, one-to-many, and many-to-many associations
• Inferring TypeScript types for query results with nested relations

Instructions

1. Define relations separately from the table. Relations are metadata for the relational query API — they do not create foreign keys:

import { relations } from 'drizzle-orm';
import { users, posts } from './schema';

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

2. One-to-one — use one() on both sides:

export const usersRelations = relations(users, ({ one }) => ({
  profile: one(profiles, {
    fields: [users.id],
    references: [profiles.userId],
  }),
}));

export const profilesRelations = relations(profiles, ({ one }) => ({
  user: one(users, {
    fields: [profiles.userId],
    references: [users.id],
  }),
}));

3. One-to-many — many() on the parent, one() with fields/references on the child:

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

4. Many-to-many — use an explicit join table with relations on all three tables:

export const postsRelations = relations(posts, ({ many }) => ({
  postTags: many(postTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  postTags: many(postTags),
}));

export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, { fields: [postTags.postId], references: [posts.id] }),
  tag: one(tags, { fields: [postTags.tagId], references: [tags.id] }),
}));

5. Query with relations using the relational query API:

const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      limit: 10,
      orderBy: (posts, { desc }) => [desc(posts.createdAt)],
      with: { tags: true },
    },
  },
});

6. Self-relations — name the relation to disambiguate:

export const employeesRelations = relations(employees, ({ one, many }) => ({
  manager: one(employees, {
    fields: [employees.managerId],
    references: [employees.id],
    relationName: 'managerReports',
  }),
  reports: many(employees, { relationName: 'managerReports' }),
}));

7. Pass all relations to drizzle() when initializing the client:

import * as schema from './schema';

const db = drizzle(pool, { schema });
// db.query.users.findMany({ with: { posts: true } }) now works

Details

Drizzle has two query APIs: the SQL-like query builder (db.select().from(users)) and the relational query API (db.query.users.findMany()). Relations only affect the relational query API. The SQL-like builder uses explicit join() calls instead.

Relations are not foreign keys. Defining relations() does not create database constraints. Foreign keys are defined on the schema columns with .references(). Relations are a query-time concept for the ORM to know how to join tables.

Type inference with relations:

type UserWithPosts = Awaited<
  ReturnType<
    typeof db.query.users.findFirst<{
      with: { posts: true };
    }>
  >
>;

Or extract with InferSelectModel and manually add the relation types.

Multiple relations to the same table: Use relationName to disambiguate:

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
    relationName: 'postAuthor',
  }),
  editor: one(users, {
    fields: [posts.editorId],
    references: [users.id],
    relationName: 'postEditor',
  }),
}));

Trade-offs:

• Relations are defined separately from the table schema, which means two files to update when adding a relationship
• The relational query API generates subqueries rather than JOINs — for complex multi-table queries, use the SQL-like builder with explicit joins
• Drizzle does not have implicit many-to-many — you always need an explicit join table

Source

https://orm.drizzle.team/docs/relations

Process

1. Read the instructions and examples in this document.
2. Apply the patterns to your implementation, adapting to your specific context.
3. Verify your implementation against the details and edge cases listed above.

Harness Integration

• Type: knowledge — this skill is a reference document, not a procedural workflow.
• No tools or state — consumed as context by other skills and agents.

Success Criteria

• The patterns described in this document are applied correctly in the implementation.
• Edge cases and anti-patterns listed in this document are avoided.