Adding Database Models | Client Guides

Advanced Developer Content

This guide requires technical knowledge:

Basic understanding of relational databases
Familiarity with the existing database schema
Knowledge of your feature requirements

This guide shows you how to add new database models to your SaaS application using Prisma ORM. Follow the Todo model pattern for consistency.

Prisma Schema Location

All database models are defined in prisma/schema.prisma. This file contains your data model, database connection, and Prisma Client configuration.

Adding a Simple Model

Let's add a Project model where users can create and manage projects.

Define the Model in Schema

Open prisma/schema.prisma and add your new model:

prisma/schema.prisma

1model Project {
2  id          String   @id @default(cuid())
3  name        String
4  description String?
5  userId      String
6 
7  // Relationship to User model
8  user        User     @relation(fields: [userId], references: [id], onDelete: Cascade)
9 
10  createdAt   DateTime @default(now())
11  updatedAt   DateTime @updatedAt
12 
13  // Index for fast queries by user
14  @@index([userId])
15}

ℹ️

Model Naming

Use singular names for models (e.g., Project, not Projects). Prisma will pluralize table names automatically.

Update the User Model

Add the reverse relationship to the User model:

prisma/schema.prisma

1model User {
2  id            String    @id @default(cuid())
3  name          String?
4  email         String?   @unique
5  emailVerified DateTime?
6  image         String?
7 
8  accounts      Account[]
9  sessions      Session[]
10  projects      Project[]      // Add this line
11  subscription  Subscription?
12  todos         Todo[]
13 
14  createdAt     DateTime  @default(now())
15  updatedAt     DateTime  @updatedAt
16}

Create and Run Migration

Generate a migration to update your database:

bash

1npx prisma migrate dev --name add_project_model

This command will:

Create a new migration file in prisma/migrations/
Apply the migration to your local database
Regenerate the Prisma Client with the new model

⚠️

Production Migrations

Never run migrate dev in production! Use npx prisma migrate deploy instead.

Use the New Model

You can now use the Project model in your code:

src/app/api/projects/route.ts

1import { prisma } from "@/lib/db";
2import { auth } from "@/lib/auth";
3 
4export async function POST(request: Request) {
5  const session = await auth();
6  if (!session?.user) {
7    return Response.json({ error: "Unauthorized" }, { status: 401 });
8  }
9 
10  const body = await request.json();
11  const { name, description } = body;
12 
13  // Create a new project
14  const project = await prisma.project.create({
15    data: {
16      name,
17      description,
18      userId: session.user.id
19    }
20  });
21 
22  return Response.json({ project }, { status: 201 });
23}

Field Types Summary

Common field types: String, Int, Boolean, DateTime, Json. The Todo model uses String and Boolean - follow that pattern for simplicity.

For complete field type reference and advanced options, see the Prisma Schema Reference.

One-to-Many Relationships

The most common relationship pattern: One User has many Projects (demonstrated in the Project example above).

This follows the User → Todos pattern in the codebase - use onDelete: Cascade to auto-delete related data when the parent is removed.

For advanced patterns (enums, one-to-one, many-to-many relationships), see the Prisma Relations Documentation.

Adding Indexes for Performance

Add indexes to fields that are frequently queried:

prisma/schema.prisma

1model Project {
2  id          String        @id @default(cuid())
3  name        String
4  status      ProjectStatus
5  userId      String
6  isPublic    Boolean       @default(false)
7 
8  user        User          @relation(fields: [userId], references: [id], onDelete: Cascade)
9 
10  createdAt   DateTime      @default(now())
11  updatedAt   DateTime      @updatedAt
12 
13  // Single field indexes
14  @@index([userId])        // Fast lookup by user
15  @@index([status])        // Fast filtering by status
16  @@index([createdAt])     // Fast sorting by date
17 
18  // Composite index
19  @@index([userId, status]) // Fast lookup by user AND status together
20}

💡

When to Add Indexes

Add indexes on fields used in WHERE clauses, JOIN conditions, or ORDER BY statements. Don't over-index - each index increases write time.

Migration Workflow

Development Workflow

While developing locally with SQLite:

bash

1# 1. Modify prisma/schema.prisma
2# 2. Create migration and apply it
3npx prisma migrate dev --name descriptive_migration_name
4 
5# This will:
6# - Create migration file
7# - Apply to local database
8# - Regenerate Prisma Client

Production Deployment

When deploying to production with PostgreSQL:

bash

1# In your CI/CD pipeline or production environment
2npx prisma migrate deploy
3 
4# This applies all pending migrations without prompting

Reset Database (Dev Only)

If you need to start fresh during development:

bash

1npx prisma migrate reset
2 
3# WARNING: This will:
4# - Drop the database
5# - Create a new database
6# - Apply all migrations
7# - Run seed script (if configured)

🚨

Never in Production!

NEVER run migrate reset in production - it will delete all your data!

Troubleshooting

Migration Failed: Foreign Key Constraint

Check that referenced models exist
Ensure the references field points to a valid field (usually id)
Verify field types match (e.g., both String or both Int)

Error: "Unique constraint failed"

Check if you're trying to create duplicate @unique values
For composite unique constraints (@@unique([field1, field2])), ensure the combination is unique

Prisma Client Outdated

Run npx prisma generate to regenerate the client
Restart your dev server after schema changes

Migration Conflicts

Check migration status: npx prisma migrate status
If migrations are out of sync, you may need to reset (dev only): npx prisma migrate reset
For production, ensure all migrations are committed to Git

Related Guides

Database Schema

Understand the existing database structure and relationships

Adding API Routes

Create API endpoints to interact with your new models

Creating New Features

Complete workflow for building full-stack features

💡

Next Steps

Now that you know how to add database models, you might want to:

Create API Routes to expose your data
Review Creating New Features for the complete development workflow
Learn about User Management for user-scoped data

Advanced Developer Content

This guide requires technical knowledge:

Basic understanding of relational databases
Familiarity with the existing database schema
Knowledge of your feature requirements

This guide shows you how to add new database models to your SaaS application using Prisma ORM. Follow the Todo model pattern for consistency.

Prisma Schema Location

All database models are defined in prisma/schema.prisma. This file contains your data model, database connection, and Prisma Client configuration.

Adding a Simple Model

Let's add a Project model where users can create and manage projects.

Define the Model in Schema

Open prisma/schema.prisma and add your new model:

prisma/schema.prisma

1model Project {
2  id          String   @id @default(cuid())
3  name        String
4  description String?
5  userId      String
6 
7  // Relationship to User model
8  user        User     @relation(fields: [userId], references: [id], onDelete: Cascade)
9 
10  createdAt   DateTime @default(now())
11  updatedAt   DateTime @updatedAt
12 
13  // Index for fast queries by user
14  @@index([userId])
15}

ℹ️

Model Naming

Use singular names for models (e.g., Project, not Projects). Prisma will pluralize table names automatically.

Update the User Model

Add the reverse relationship to the User model:

prisma/schema.prisma

1model User {
2  id            String    @id @default(cuid())
3  name          String?
4  email         String?   @unique
5  emailVerified DateTime?
6  image         String?
7 
8  accounts      Account[]
9  sessions      Session[]
10  projects      Project[]      // Add this line
11  subscription  Subscription?
12  todos         Todo[]
13 
14  createdAt     DateTime  @default(now())
15  updatedAt     DateTime  @updatedAt
16}

Create and Run Migration

Generate a migration to update your database:

bash

1npx prisma migrate dev --name add_project_model

This command will:

Create a new migration file in prisma/migrations/
Apply the migration to your local database
Regenerate the Prisma Client with the new model

⚠️

Production Migrations

Never run migrate dev in production! Use npx prisma migrate deploy instead.

Use the New Model

You can now use the Project model in your code:

src/app/api/projects/route.ts

1import { prisma } from "@/lib/db";
2import { auth } from "@/lib/auth";
3 
4export async function POST(request: Request) {
5  const session = await auth();
6  if (!session?.user) {
7    return Response.json({ error: "Unauthorized" }, { status: 401 });
8  }
9 
10  const body = await request.json();
11  const { name, description } = body;
12 
13  // Create a new project
14  const project = await prisma.project.create({
15    data: {
16      name,
17      description,
18      userId: session.user.id
19    }
20  });
21 
22  return Response.json({ project }, { status: 201 });
23}

Field Types Summary

Common field types: String, Int, Boolean, DateTime, Json. The Todo model uses String and Boolean - follow that pattern for simplicity.

For complete field type reference and advanced options, see the Prisma Schema Reference.

One-to-Many Relationships

The most common relationship pattern: One User has many Projects (demonstrated in the Project example above).

This follows the User → Todos pattern in the codebase - use onDelete: Cascade to auto-delete related data when the parent is removed.

For advanced patterns (enums, one-to-one, many-to-many relationships), see the Prisma Relations Documentation.

Adding Indexes for Performance

Add indexes to fields that are frequently queried:

prisma/schema.prisma

1model Project {
2  id          String        @id @default(cuid())
3  name        String
4  status      ProjectStatus
5  userId      String
6  isPublic    Boolean       @default(false)
7 
8  user        User          @relation(fields: [userId], references: [id], onDelete: Cascade)
9 
10  createdAt   DateTime      @default(now())
11  updatedAt   DateTime      @updatedAt
12 
13  // Single field indexes
14  @@index([userId])        // Fast lookup by user
15  @@index([status])        // Fast filtering by status
16  @@index([createdAt])     // Fast sorting by date
17 
18  // Composite index
19  @@index([userId, status]) // Fast lookup by user AND status together
20}

💡

When to Add Indexes

Add indexes on fields used in WHERE clauses, JOIN conditions, or ORDER BY statements. Don't over-index - each index increases write time.

Migration Workflow

Development Workflow

While developing locally with SQLite:

bash

1# 1. Modify prisma/schema.prisma
2# 2. Create migration and apply it
3npx prisma migrate dev --name descriptive_migration_name
4 
5# This will:
6# - Create migration file
7# - Apply to local database
8# - Regenerate Prisma Client

Production Deployment

When deploying to production with PostgreSQL:

bash

1# In your CI/CD pipeline or production environment
2npx prisma migrate deploy
3 
4# This applies all pending migrations without prompting

Reset Database (Dev Only)

If you need to start fresh during development:

bash

1npx prisma migrate reset
2 
3# WARNING: This will:
4# - Drop the database
5# - Create a new database
6# - Apply all migrations
7# - Run seed script (if configured)

🚨

Never in Production!

NEVER run migrate reset in production - it will delete all your data!

Troubleshooting

Migration Failed: Foreign Key Constraint

Check that referenced models exist
Ensure the references field points to a valid field (usually id)
Verify field types match (e.g., both String or both Int)

Error: "Unique constraint failed"

Check if you're trying to create duplicate @unique values
For composite unique constraints (@@unique([field1, field2])), ensure the combination is unique

Prisma Client Outdated

Run npx prisma generate to regenerate the client
Restart your dev server after schema changes

Migration Conflicts

Check migration status: npx prisma migrate status
If migrations are out of sync, you may need to reset (dev only): npx prisma migrate reset
For production, ensure all migrations are committed to Git

Next Steps

Now that you know how to add database models, you might want to:

Create API Routes to expose your data
Review Creating New Features for the complete development workflow
Learn about User Management for user-scoped data