06 - Relations

📋 Jump to Takeaways

Drizzle supports two ways to work with related data: SQL joins and the relational query API. Both are type-safe. You pick based on what you need.

SQL joins work with just foreign key columns. The relational query API (db.query with with) requires you to define relations() in your schema. This lesson covers both.

Foreign Keys

Define foreign keys in your schema with references().

import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';

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().references(() => users.id)
});

references() creates a foreign key constraint in the database. The arrow function avoids circular import issues.

Defining Relations

To use the relational query API (db.query), define relations separately from the table.

import { relations } from 'drizzle-orm';

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

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

one() needs fields and references to know which columns link the tables. many() does not because the other side already defines it.

Relational Queries

With relations defined, use db.query to fetch nested data in one call. For this to work, you must pass your schema to drizzle():

// src/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });
export { client };

Now db.query knows about your tables and relations:

// Get all users with their posts
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true
  }
});
// [{ id: 1, name: 'Alice', posts: [{ id: 1, title: 'Hello World', authorId: 1 }] }]

Filter and select specific fields:

const result = await db.query.users.findFirst({
  where: eq(users.id, 1),
  columns: {
    id: true,
    name: true
  },
  with: {
    posts: {
      columns: { title: true }
    }
  }
});
// { id: 1, name: 'Alice', posts: [{ title: 'Hello World' }] }

SQL Joins

For more control, use .leftJoin() or .innerJoin() directly.

import { eq } from 'drizzle-orm';

// Left join: all users, even those without posts
const rows = await db.select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));
// [{ users: { id: 1, name: 'Alice' }, posts: { id: 1, title: 'Hello World', authorId: 1 } },
//  { users: { id: 2, name: 'Bob' }, posts: null }]

// Inner join: only users who have posts
const active = await db.select({
  userName: users.name,
  postTitle: posts.title
}).from(users)
  .innerJoin(posts, eq(users.id, posts.authorId));
// [{ userName: 'Alice', postTitle: 'Hello World' }]

Joins return flat rows with each table's data nested under its name. Inner joins exclude rows with no match.

Many-to-Many

Many-to-many needs a join table. Example: users can have multiple tags, and tags can belong to multiple users.

export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  label: text('label').notNull()
});

export const usersToTags = pgTable('users_to_tags', {
  userId: integer('user_id').notNull().references(() => users.id),
  tagId: integer('tag_id').notNull().references(() => tags.id)
});

Define relations through the join table:

export const usersToTagsRelations = relations(usersToTags, ({ one }) => ({
  user: one(users, { fields: [usersToTags.userId], references: [users.id] }),
  tag: one(tags, { fields: [usersToTags.tagId], references: [tags.id] })
}));

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

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

Query through the join table:

const usersWithTags = await db.query.users.findMany({
  with: {
    usersToTags: {
      with: { tag: true }
    }
  }
});
// [{ id: 1, name: 'Alice', usersToTags: [{ userId: 1, tagId: 1, tag: { id: 1, label: 'developer' } }] }]

You can also query many-to-many with joins, without defining relations():

// Get all tags for a specific post
const postTags = await db.select({
  tagName: tags.name
}).from(postsTags)
  .innerJoin(tags, eq(postsTags.tagId, tags.id))
  .where(eq(postsTags.postId, 1));
// [{ tagName: "typescript" }, { tagName: "tutorial" }]

// Get all posts with a specific tag
const taggedPosts = await db.select({
  postTitle: posts.title
}).from(postsTags)
  .innerJoin(posts, eq(postsTags.postId, posts.id))
  .innerJoin(tags, eq(postsTags.tagId, tags.id))
  .where(eq(tags.name, "typescript"));
// [{ postTitle: "Getting Started with TS" }, { postTitle: "Advanced Types" }]

Column Name Collisions in Joins

Join results are namespaced by table, so same-named columns don't collide:

const rows = await db.select().from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));
// { users: { id: 1, title: "Admin" }, posts: { id: 5, title: "Hello World" } }

If you flatten with a custom select, TypeScript catches duplicate keys at compile time:

// ❌ TypeScript error — duplicate key
const rows = await db.select({
  title: users.title,
  title: posts.title
}).from(users).leftJoin(posts, eq(users.id, posts.authorId));

// ✅ Rename to avoid collision
const rows = await db.select({
  userTitle: users.title,
  postTitle: posts.title
}).from(users).leftJoin(posts, eq(users.id, posts.authorId));

Joins vs Relational Queries

SQL joins (.leftJoin(), .innerJoin()) don't need relations() defined. You specify the join condition directly in the query. They work with just the foreign key column.

db.query with with requires relations() to be defined in your schema. It gives you nested objects instead of flat rows.

Use db.query when you want nested objects and cleaner output. Use SQL joins when you need aggregations, custom select shapes, or complex conditions.

Key Takeaways

references() creates database-level foreign key constraints
relations() defines relationships for the db.query relational API; they do not create database constraints
one() requires fields and references; many() does not
db.query.table.findMany({ with: { ... } }) fetches nested related data
.leftJoin() and .innerJoin() give raw SQL-level control over joins
Many-to-many relationships require a join table with relations defined on all three tables