Database Relationships

Database relationships define how different tables (models) connect to each other. They're essential for organizing data efficiently and maintaining data integrity. In this section, we'll explore the relationships in our task management schema and learn how to work with them using Prisma.

Why Relationships Matter

  • Data Integrity: Ensure data consistency across tables
  • Efficiency: Avoid data duplication and reduce storage
  • Flexibility: Query related data easily and efficiently
  • Maintainability: Update data in one place, reflect everywhere

Our Schema Relationships

Let's examine the relationships in our task management application:

User

One user

Tasks

Many tasks

Category

One category

Tasks

Many tasks

Types of Relationships

1. One-to-Many: User → Tasks

Each user can have many tasks, but each task belongs to exactly one user. This is the most common type of relationship in databases.

prisma/schema.prisma
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  tasks     Task[]
  
  @@map("users")
}

model Task {
  id          String    @id @default(cuid())
  title       String
  description String?
  completed   Boolean   @default(false)
  priority    Priority  @default(MEDIUM)
  dueDate     DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  
  userId      String
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  categoryId  String?
  category    Category? @relation(fields: [categoryId], references: [id], onDelete: SetNull)
  
  @@map("tasks")
}

Key Components Explained

  • tasks Task[] - Array field on User model (virtual field)
  • userId String - Foreign key field storing the user's ID
  • user User @relation(...) - Relation field connecting to User
  • fields: [userId] - Local field used for the relationship
  • references: [id] - Field on the related model to connect to
  • onDelete: Cascade - Delete all tasks when user is deleted

2. Optional One-to-Many: Category → Tasks

Tasks can optionally belong to a category. A category can have many tasks, but tasks don't require a category.

prisma/schema.prisma
model Category {
  id          String   @id @default(cuid())
  name        String   @unique
  description String?
  color       String   @default("#3B82F6")
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  tasks       Task[]
  
  @@map("categories")
}

categoryId  String?     // Optional foreign key (nullable)
category    Category?   // Optional relation (nullable)
            @relation(fields: [categoryId], references: [id], onDelete: SetNull)

Optional Relationships

The ? symbol makes both the foreign key (categoryId?) and the relation field (category?) optional. When a category is deleted,onDelete: SetNull sets the categoryId to null instead of deleting the tasks.

Delete Behaviors

Delete behaviors determine what happens to related records when a parent record is deleted:

Cascade

When a user is deleted, all their tasks are automatically deleted too.

user User @relation(\n  fields: [userId], \n  references: [id], \n  onDelete: Cascade\n)

SetNull

When a category is deleted, tasks become uncategorized (categoryId becomes null).

category Category? @relation(\n  fields: [categoryId], \n  references: [id], \n  onDelete: SetNull\n)

Working with Relationships in Queries

Including Related Data

Use the include option to fetch related data in your queries:

// Fetch tasks with user and category information\nconst tasks = await prisma.task.findMany({\n  include: {\n    user: {\n      select: {\n        id: true,\n        name: true,\n        email: true,\n      },\n    },\n    category: {\n      select: {\n        id: true,\n        name: true,\n        color: true,\n      },\n    },\n  },\n  orderBy: {\n    createdAt: 'desc',\n  },\n})

Selecting Specific Fields

Use select instead of include for more control over returned fields:

// Only get specific fields from tasks and related data\nconst tasks = await prisma.task.findMany({\n  select: {\n    id: true,\n    title: true,\n    completed: true,\n    user: {\n      select: {\n        name: true,\n      },\n    },\n    category: {\n      select: {\n        name: true,\n        color: true,\n      },\n    },\n  },\n})

Counting Related Records

Use _count to get the number of related records:

// Get users with their task count\nconst users = await prisma.user.findMany({\n  select: {\n    id: true,\n    name: true,\n    email: true,\n    _count: {\n      select: {\n        tasks: true,\n      },\n    },\n  },\n})\n\nconst categories = await prisma.category.findMany({\n  include: {\n    _count: {\n      select: {\n        tasks: true,\n      },\n    },\n  },\n})

Filtering by Related Data

You can filter records based on their relationships:

// Get all tasks for a specific user
const userTasks = await prisma.task.findMany({
  where: {
    userId: "specific-user-id",
  },
  include: {
    category: true,
  },
})

const categoryTasks = await prisma.task.findMany({
  where: {
    categoryId: "specific-category-id",
  },
  include: {
    user: {
      select: {
        name: true,
        email: true,
      },
    },
  },
})

const usersWithIncompleteTasks = await prisma.user.findMany({
  where: {
    tasks: {
      some: {
        completed: false,
      },
    },
  },
  include: {
    _count: {
      select: {
        tasks: true,
      },
    },
  },
})

Creating Records with Relationships

Method 1: Using Foreign Keys

// Create a task by providing the userId
const task = await prisma.task.create({
  data: {
    title: "New task",
    description: "Task description",
    userId: "existing-user-id",
    categoryId: "existing-category-id", // Optional
  },
  include: {
    user: true,
    category: true,
  },
})

Method 2: Using Nested Creates

// Create a user and their first task in one operation
const userWithTask = await prisma.user.create({
  data: {
    email: "newuser@example.com",
    name: "New User",
    tasks: {
      create: [
        {
          title: "First task",
          description: "Getting started",
          priority: "HIGH",
        },
      ],
    },
  },
  include: {
    tasks: true,
  },
})

Method 3: Connecting Existing Records

// Create a task and connect it to existing user and category
const task = await prisma.task.create({
  data: {
    title: "Connected task",
    user: {
      connect: {
        id: "existing-user-id",
      },
    },
    category: {
      connect: {
        id: "existing-category-id",
      },
    },
  },
  include: {
    user: true,
    category: true,
  },
})

Advanced Relationship Patterns

Nested Filtering

// Get categories that have high-priority tasks
const categoriesWithHighPriorityTasks = await prisma.category.findMany({
  where: {
    tasks: {
      some: {
        priority: "HIGH",
      },
    },
  },
  include: {
    tasks: {
      where: {
        priority: "HIGH",
      },
    },
    _count: {
      select: {
        tasks: true,
      },
    },
  },
})

Ordering by Related Fields

// Get tasks ordered by user name
const tasks = await prisma.task.findMany({
  include: {
    user: {
      select: {
        name: true,
      },
    },
  },
  orderBy: {
    user: {
      name: 'asc',
    },
  },
})

Relationships Mastered!

You now understand how to design, implement, and query database relationships with Prisma. These concepts are fundamental to building scalable, well-structured applications.

Best Practices

  • Always consider the delete behavior for your relationships
  • Use select to limit data transfer and improve performance
  • Index foreign key fields for better query performance
  • Be mindful of N+1 query problems - use include wisely
  • Consider using _count instead of loading all related records when you only need counts
← Previous: CRUD OperationsNext: Interactive Demo →