← Back to Articles
DatabasePrismaBackendORM

Database Design Best Practices with Prisma

8 min read

Database Design Best Practices with Prisma

Prisma is a powerful ORM that simplifies database operations while maintaining type safety and excellent developer experience. In this comprehensive guide, we'll explore best practices for designing efficient database schemas using Prisma.

Getting Started with Prisma

First, let's set up Prisma in your project:

npm install prisma @prisma/client
npx prisma init

This creates a prisma directory with your schema file and a .env file for your database connection.

Schema Design Principles

1. Choose Appropriate Data Types

Selecting the right data types is crucial for performance and storage efficiency:

model User {
  id          String   @id @default(cuid())  // Use cuid() for distributed systems
  email       String   @unique               // Unique constraint for email
  name        String?                        // Optional field
  age         Int?                           // Optional integer
  isActive    Boolean  @default(true)        // Boolean with default
  salary      Decimal? @db.Decimal(10, 2)    // Precise decimal for money
  createdAt   DateTime @default(now())       // Auto-generated timestamp
  updatedAt   DateTime @updatedAt            // Auto-updated timestamp
  
  @@map("users") // Custom table name
}

2. Model Relationships Correctly

Define clear and efficient relationships between your models:

model User {
  id       String    @id @default(cuid())
  email    String    @unique
  name     String
  posts    Post[]    // One-to-many
  profile  Profile?  // One-to-one
  
  // Many-to-many through join table
  categories UserCategory[]
  
  @@map("users")
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?  @db.Text
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  String
  tags      PostTag[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  @@map("posts")
}

model Profile {
  id      String  @id @default(cuid())
  bio     String? @db.Text
  website String?
  user    User    @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId  String  @unique
  
  @@map("profiles")
}

model Category {
  id    String         @id @default(cuid())
  name  String         @unique
  users UserCategory[]
  posts PostTag[]
  
  @@map("categories")
}

model UserCategory {
  user       User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId     String
  category   Category @relation(fields: [categoryId], references: [id], onDelete: Cascade)
  categoryId String
  createdAt  DateTime @default(now())
  
  @@id([userId, categoryId])
  @@map("user_categories")
}

model PostTag {
  post       Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId     String
  category   Category @relation(fields: [categoryId], references: [id], onDelete: Cascade)
  categoryId String
  
  @@id([postId, categoryId])
  @@map("post_tags")
}

Indexing Strategy

Proper indexing is crucial for query performance:

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?  @db.Text
  published Boolean  @default(false)
  authorId  String
  createdAt DateTime @default(now())
  
  // Single column indexes
  @@index([published])
  @@index([createdAt])
  @@index([authorId])
  
  // Composite indexes for complex queries
  @@index([published, createdAt])
  @@index([authorId, published])
  
  @@map("posts")
}

model User {
  id    String @id @default(cuid())
  email String @unique  // Automatically creates an index
  name  String
  
  // Full-text search index (PostgreSQL)
  @@index([name], type: Gin)
  
  @@map("users")
}

Query Optimization Techniques

1. Select Only Required Fields

Always specify exactly which fields you need:

// ❌ Bad: Fetches all fields
const users = await prisma.user.findMany()

// ✅ Good: Only fetch required fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true,
  },
})

2. Efficient Relation Loading

Use include and select strategically:

// ✅ Include related data efficiently
const userWithPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 10,
      select: {
        id: true,
        title: true,
        excerpt: true,
        createdAt: true,
      },
    },
    profile: {
      select: {
        bio: true,
        website: true,
      },
    },
  },
})

3. Pagination Best Practices

Implement efficient pagination:

// ✅ Cursor-based pagination (recommended for large datasets)
async function getPaginatedPosts(cursor?: string, take: number = 10) {
  return await prisma.post.findMany({
    take,
    skip: cursor ? 1 : 0,
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { createdAt: 'desc' },
    select: {
      id: true,
      title: true,
      excerpt: true,
      createdAt: true,
      author: {
        select: {
          name: true,
          email: true,
        },
      },
    },
  })
}

// ✅ Offset-based pagination (simpler, but less efficient for large datasets)
async function getPaginatedPostsOffset(page: number = 1, limit: number = 10) {
  const skip = (page - 1) * limit
  
  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      skip,
      take: limit,
      orderBy: { createdAt: 'desc' },
    }),
    prisma.post.count(),
  ])
  
  return {
    posts,
    total,
    page,
    totalPages: Math.ceil(total / limit),
  }
}

Transaction Management

Use transactions for operations that must succeed or fail together:

// ✅ Sequential transactions
async function createUserWithProfile(userData: UserData, profileData: ProfileData) {
  return await prisma.$transaction(async (tx) => {
    const user = await tx.user.create({
      data: userData,
    })
    
    const profile = await tx.profile.create({
      data: {
        ...profileData,
        userId: user.id,
      },
    })
    
    return { user, profile }
  })
}

// ✅ Batch transactions (faster for independent operations)
async function batchCreateUsers(usersData: UserData[]) {
  const createOperations = usersData.map(userData =>
    prisma.user.create({ data: userData })
  )
  
  return await prisma.$transaction(createOperations)
}

Error Handling and Validation

1. Database Constraint Handling

import { Prisma } from '@prisma/client'

async function createUser(userData: UserCreateInput) {
  try {
    return await prisma.user.create({
      data: userData,
    })
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2002') {
        // Unique constraint violation
        throw new Error('Email already exists')
      }
      if (error.code === 'P2003') {
        // Foreign key constraint violation
        throw new Error('Referenced record does not exist')
      }
    }
    throw error
  }
}

2. Input Validation with Zod

import { z } from 'zod'

const CreateUserSchema = z.object({
  email: z.string().email(),
  name: z.string().min(1).max(100),
  age: z.number().int().min(0).max(120).optional(),
})

async function createUserSafely(input: unknown) {
  const validatedData = CreateUserSchema.parse(input)
  
  return await prisma.user.create({
    data: validatedData,
  })
}

Performance Monitoring and Optimization

1. Enable Query Logging

const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'stdout', level: 'error' },
    { emit: 'stdout', level: 'info' },
    { emit: 'stdout', level: 'warn' },
  ],
})

prisma.$on('query', (e) => {
  console.log('Query: ' + e.query)
  console.log('Duration: ' + e.duration + 'ms')
})

2. Connection Pool Configuration

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
  // Configure connection pool
  // Note: These are typically set in DATABASE_URL
})

Migration Best Practices

1. Safe Migration Patterns

# Generate migration without applying
npx prisma migrate dev --create-only

# Review the generated SQL before applying
# Edit the migration file if needed

# Apply the migration
npx prisma migrate dev

2. Production Migration Strategy

# Generate migration
npx prisma migrate dev

# Deploy to production
npx prisma migrate deploy

# Generate Prisma Client
npx prisma generate

Security Considerations

1. Row Level Security (RLS)

model Post {
  id       String @id @default(cuid())
  title    String
  content  String
  authorId String
  author   User   @relation(fields: [authorId], references: [id])
  
  // Use database views or application-level filtering
  @@map("posts")
}

2. Secure Query Patterns

// ✅ Filter by user ownership
async function getUserPosts(userId: string, currentUserId: string) {
  // Ensure users can only access their own posts
  if (userId !== currentUserId) {
    throw new Error('Unauthorized')
  }
  
  return await prisma.post.findMany({
    where: {
      authorId: userId,
    },
  })
}

Testing Database Operations

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

beforeEach(async () => {
  // Clean database before each test
  await prisma.post.deleteMany()
  await prisma.user.deleteMany()
})

afterAll(async () => {
  await prisma.$disconnect()
})

test('should create user with profile', async () => {
  const user = await prisma.user.create({
    data: {
      email: 'test@example.com',
      name: 'Test User',
      profile: {
        create: {
          bio: 'Test bio',
        },
      },
    },
    include: {
      profile: true,
    },
  })
  
  expect(user.profile?.bio).toBe('Test bio')
})

Conclusion

Following these best practices will help you build efficient, scalable database schemas with Prisma:

  1. Design thoughtfully: Choose appropriate data types and relationships
  2. Index strategically: Add indexes for frequently queried fields
  3. Query efficiently: Use select/include wisely and implement proper pagination
  4. Handle errors gracefully: Implement proper error handling and validation
  5. Monitor performance: Keep track of query performance and optimize as needed
  6. Migrate safely: Use proper migration strategies for production deployments

Remember that database design is an iterative process. Start with a solid foundation and refine your schema as your application grows and evolves.

Happy coding with Prisma! 🚀