Handling Relationships

Database relationships connect your tables together, enabling you to model real-world data accurately. This chapter explains how to define and manage relationships in D1 using Drizzle ORM.

Understanding Database Relationships

Before diving into code, let’s understand the types of relationships you’ll encounter:

flowchart LR
  subgraph OneToMany["One-to-Many"]
      U1[User] -->|has many| P1[Posts]
      U1 -->|has many| P2[Comments]
  end

  subgraph ManyToMany["Many-to-Many"]
      A1[Author] <-->|writes| B1[Books]
      B1 <-->|written by| A1
  end

  subgraph OneToOne["One-to-One"]
      US1[User] <-->|has one| PR1[Profile]
  end

Expand

Relationship	Example	Implementation
One-to-Many	User → Posts	Foreign key on Posts table
Many-to-Many	Authors ↔ Books	Junction table with two foreign keys
One-to-One	User ↔ Profile	Foreign key with unique constraint

For most applications, one-to-many relationships are the most common, so we’ll focus on those.

One-to-Many Relationships

A one-to-many relationship exists when one record in a table can be associated with multiple records in another table. The classic example is users and posts: one user can have many posts.

The Basic Pattern

erDiagram
  users ||--o{ posts : "has many"
  users {
      integer id PK
      text name
      text email
  }
  posts {
      integer id PK
      text title
      text content
      integer author_id FK
  }

Expand

In this relationship:

• Each user can have zero or more posts
• Each post belongs to exactly one user
• The author_id column in posts points to a user’s id

Implementing with Drizzle

Here’s how to define this relationship in Drizzle:

src/db/schema.ts

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

// Parent table (the "one" side)
export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
});

// Child table (the "many" side)
export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  content: text('content').notNull(),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id),
});

Key points:

• The .references() method creates a foreign key constraint
• Use an arrow function () => users.id to reference the parent table
• The foreign key column (author_id) stores the parent record’s ID

Adding More Relationships

A user might also have comments, favorites, or other related data:

src/db/schema.ts

export const comments = sqliteTable('comments', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  content: text('content').notNull(),
  postId: integer('post_id')
    .notNull()
    .references(() => posts.id),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id),
});

This creates a table that references both posts and users, allowing you to track who wrote each comment and which post it belongs to.

Foreign Key Constraints in D1

Foreign keys are more than just columns storing IDs—they’re database constraints that enforce data integrity.

What Foreign Keys Enforce

flowchart TD
  subgraph Protections["Foreign Key Protections"]
      A["Referential Integrity"] --> B["Cannot insert post<br/>with non-existent author_id"]
      A --> C["Cannot delete user<br/>if posts reference them<br/>(by default)"]
      A --> D["Parent record must exist<br/>before child can reference it"]
  end

Expand

Foreign keys guarantee:

1. Insert validation: You cannot insert a post with an author_id that doesn’t exist in the users table
2. Delete protection: By default, you cannot delete a user who has posts
3. Update protection: Changing a user’s ID is blocked if posts reference it

Enabling Foreign Keys in D1

SQLite (and D1) has foreign key enforcement disabled by default for backwards compatibility. Drizzle and D1 handle this automatically, but it’s important to understand.

When you create a foreign key constraint, D1 enforces it at the database level:

src/db/schema.ts

authorId: integer('author_id')
  .notNull()
  .references(() => users.id), // This creates a real database constraint

If you try to insert a post with an invalid author_id:

// ❌ This will throw an error - user ID 999 doesn't exist
await db.insert(posts).values({
  title: 'My Post',
  content: 'Content here',
  authorId: 999, // Error: FOREIGN KEY constraint failed
});

Optional Relationships

Sometimes a relationship is optional. For example, a post might optionally have a category:

src/db/schema.ts

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  content: text('content').notNull(),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id),
  categoryId: integer('category_id')
    .references(() => categories.id), // Optional - no notNull()
});

Without .notNull(), the categoryId can be NULL, meaning the post has no category.

Cascade Behaviors

Cascade behaviors define what happens to child records when a parent record is updated or deleted. This is where relationships become powerful.

Understanding Cascade Options

D1/SQLite supports these cascade behaviors:

Behavior	On Delete	On Update
no action	Block deletion if children exist	Block update if children reference it
cascade	Delete all child records	Update all child references
set null	Set foreign key to NULL	Set foreign key to NULL
restrict	Same as no action (immediately checked)	Same as no action
set default	Set foreign key to default value	Set foreign key to default value

Visual Guide to Cascade Behaviors

flowchart TD
  subgraph DeleteUser["Delete User (id: 1)"]
      U["User #1"]
      P1["Post #1<br/>author_id: 1"]
      P2["Post #2<br/>author_id: 1"]
  end

  subgraph NoAction["no action / restrict"]
      U1["❌ Delete blocked"]
      U1 --> N1["Posts still reference user"]
  end

  subgraph Cascade["cascade"]
      U2["✅ User deleted"]
      U2 --> C1["Post #1 deleted"]
      U2 --> C2["Post #2 deleted"]
  end

  subgraph SetNull["set null"]
      U3["✅ User deleted"]
      U3 --> S1["Post #1<br/>author_id: NULL"]
      U3 --> S2["Post #2<br/>author_id: NULL"]
  end

  DeleteUser --> NoAction
  DeleteUser --> Cascade
  DeleteUser --> SetNull

Expand

Implementing Cascade Delete

When a user is deleted, automatically delete all their posts:

src/db/schema.ts

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  content: text('content').notNull(),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
});

Now when you delete a user:

// This will also delete all posts by user #1
await db.delete(users).where(eq(users.id, 1));

Implementing Set Null

When a category is deleted, keep the posts but remove their category assignment:

src/db/schema.ts

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  content: text('content').notNull(),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  categoryId: integer('category_id')
    .references(() => categories.id, { onDelete: 'set null' }),
});

Note: set null only works if the column allows NULL values (no .notNull() modifier).

Combining Delete and Update Behaviors

You can specify different behaviors for delete and update operations:

src/db/schema.ts

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id, {
      onDelete: 'cascade',  // Delete posts when user is deleted
      onUpdate: 'cascade',  // Update author_id if user.id changes
    }),
});

Choosing the Right Cascade Behavior

Here’s a decision guide for common scenarios:

Scenario	Recommended Behavior
User → Posts	cascade - Delete posts with user
Post → Comments	cascade - Delete comments with post
Post → Category	set null - Keep posts, remove category
Order → OrderItems	cascade - Delete items with order
Product → OrderItems	restrict - Don’t delete product if ordered

Querying Related Data

Once relationships are defined, you can query related data using joins.

Inner Join

Get posts with their authors (only posts that have authors):

src/routes/api/posts.ts

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

const postsWithAuthors = await db
  .select({
    postId: posts.id,
    postTitle: posts.title,
    authorName: users.name,
    authorEmail: users.email,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id));

Result type is automatically inferred:

// TypeScript knows the exact shape
type Result = {
  postId: number;
  postTitle: string;
  authorName: string;
  authorEmail: string;
}[];

Left Join

Get all users with their posts (including users with no posts):

src/routes/api/users.ts

const usersWithPosts = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title, // Will be null for users with no posts
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

Filtering by Related Data

Find posts by a specific author:

src/routes/api/posts.ts

const alicePosts = await db
  .select()
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(users.email, 'alice@example.com'));

Multiple Joins

Query across multiple tables:

src/routes/api/comments.ts

const commentsWithDetails = await db
  .select({
    commentContent: comments.content,
    postTitle: posts.title,
    commentAuthor: users.name,
  })
  .from(comments)
  .innerJoin(posts, eq(comments.postId, posts.id))
  .innerJoin(users, eq(comments.authorId, users.id));

Best Practices for Relationships

1. Always Define Foreign Keys

Even though SQLite allows orphaned records, always use foreign keys:

// ✅ Good: Explicit relationship
authorId: integer('author_id')
  .notNull()
  .references(() => users.id)

// ❌ Avoid: No constraint, allows invalid data
authorId: integer('author_id').notNull()

2. Choose Cascade Behaviors Intentionally

Don’t rely on defaults. Explicitly specify cascade behaviors:

// ✅ Good: Clear intent
.references(() => users.id, { onDelete: 'cascade' })

// ⚠️ Unclear: What happens on delete?
.references(() => users.id)

3. Index Foreign Key Columns

Foreign keys are frequently used in WHERE clauses and JOINs. Add indexes for better performance:

src/db/schema.ts

import { sqliteTable, text, integer, index } from 'drizzle-orm/sqlite-core';

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
}, (table) => [
  index('posts_author_id_idx').on(table.authorId),
]);

4. Consider Delete Order in Migrations

When dropping tables, drop children before parents:

migrations/0010_cleanup.sql

-- ✅ Correct order: children first
DROP TABLE `comments`;
DROP TABLE `posts`;
DROP TABLE `users`;

-- ❌ Wrong: Will fail if posts reference users
DROP TABLE `users`;
DROP TABLE `posts`;

Summary

Database relationships are fundamental to well-structured applications:

• One-to-many relationships use foreign keys on the “many” side
• Foreign key constraints ensure data integrity by preventing orphaned records
• Cascade behaviors (cascade, set null, restrict) define what happens when parent records change
• Joins allow querying related data efficiently

With these patterns, you can build complex data models that are type-safe, performant, and maintain data integrity.