Prisma Guide

Complete guide to using Prisma ORM in the Dopamine Starter Kit

This guide covers how to work with Prisma in the Dopamine Starter Kit, from basic setup to advanced patterns and best practices.

What is Prisma?

Prisma is a next-generation ORM that provides:

Type-safe database client with auto-generated TypeScript types
Intuitive data modeling with declarative schema
Automated migrations with version control
Powerful query engine with optimized SQL generation
Visual database browser with Prisma Studio

Project Structure

The Prisma setup is organized in the packages/database shared package:

packages/database/
├── prisma/
│   ├── schema.prisma          # Database schema definition
│   └── migrations/            # Database migration files
├── src/
│   └── index.ts               # Prisma client export
└── package.json               # Database package config

Schema Definition

Core Schema Structure

The schema.prisma file defines your database structure:

// Database configuration
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
// Client generation settings
generator client {
  provider = "prisma-client-js"
}
 
// Data models
model User {
  id            String    @id @default(uuid()) @db.Uuid
  name          String?
 
  //.. other fields
 
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
 
  @@map("core_user")
}

Model Patterns

Naming Conventions

The schema follows consistent naming patterns:

Model names use PascalCase
Field names use camelCase
Database columns use snake_case
Table names use snake_case with core_ prefix. Avoid conflicts with postgres reserved words.

model WorkspaceMember {
  // PascalCase for model names
  id          String @id @default(uuid()) @db.Uuid
 
  // camelCase for field names
  workspaceId String @map("workspace_id") @db.Uuid
  userId      String @map("user_id") @db.Uuid
 
  // snake_case for database columns
  createdAt DateTime @default(now()) @map("created_at")
 
  // snake_case with core_ prefix for table names
  @@map("core_workspace_member")
}

Working with Prisma Client

Client Setup

The database package exports a singleton Prisma client:

// packages/database/src/index.ts
import { PrismaClient } from '@prisma/client';
 
export type * from '@prisma/client';
 
declare global {
  var prisma: PrismaClient | undefined;
}
 
export const prisma = global.prisma || new PrismaClient();
 
if (process.env.NODE_ENV !== 'production') {
  global.prisma = prisma;
}
 
export { PrismaClient };

Basic Queries

Create Operations

import { prisma } from '@repo/database';
 
// Create a new user
const user = await prisma.user.create({
  data: {
    name: 'John Doe',
    email: 'john@example.com',
  },
});
 
// Create with relations
const workspace = await prisma.workspace.create({
  data: {
    name: 'My Team',
    members: {
      create: {
        userId: user.id,
      },
    },
  },
});

Read Operations

// Find unique record
const user = await prisma.user.findUnique({
  where: { email: 'john@example.com' },
});
 
// Find many with filtering
const users = await prisma.user.findMany({
  where: {
    emailVerified: { not: null },
  },
  orderBy: { createdAt: 'desc' },
  take: 10,
});
 
// Include relations
const userWithWorkspaces = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    workspaces: {
      include: {
        workspace: true,
      },
    },
  },
});

Update Operations

// Update single record
const user = await prisma.user.update({
  where: { id: userId },
  data: { name: 'Jane Doe' },
});
 
// Update many records
await prisma.user.updateMany({
  where: { emailVerified: null },
  data: { emailVerified: new Date() },
});

Delete Operations

// Delete single record
await prisma.user.delete({
  where: { id: userId },
});
 
// Delete many records
await prisma.invitation.deleteMany({
  where: {
    status: 'DECLINED',
    createdAt: {
      lt: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000), // 30 days ago
    },
  },
});

Advanced Queries

Transactions

// Database transaction
await prisma.$transaction(async (tx) => {
  // Create workspace
  const workspace = await tx.workspace.create({
    data: { name: 'New Team' },
  });
 
  // Add user as member
  await tx.workspaceMember.create({
    data: {
      workspaceId: workspace.id,
      userId: userId,
    },
  });
 
  // Create subscription
  await tx.subscription.create({
    data: {
      workspaceId: workspace.id,
      plan: 'PRO',
      stripeCustomerId: customerId,
      stripeSubscriptionId: subscriptionId,
    },
  });
});

Raw Queries

// Raw SQL for complex queries
const result = await prisma.$queryRaw`
  SELECT w.name, COUNT(wm.id) as member_count
  FROM core_workspace w
  LEFT JOIN core_workspace_member wm ON w.id = wm.workspace_id
  GROUP BY w.id, w.name
  ORDER BY member_count DESC
`;

Aggregations

// Count records
const userCount = await prisma.user.count({
  where: { emailVerified: { not: null } },
});
 
// Aggregate data
const stats = await prisma.subscription.aggregate({
  where: { status: 'ACTIVE' },
  _count: { id: true },
  _sum: { quantity: true },
});

Database Migrations

Development Workflow

Creating Migrations

# Navigate to database package
cd packages/database
 
# Create and apply migration
npx prisma migrate dev --name add_user_preferences
 
# This will:
# 1. Generate SQL migration file
# 2. Apply migration to database
# 3. Regenerate Prisma client

Migration Files

Migrations are stored in prisma/migrations/:

migrations/
├── 20250220152237_init/
│   └── migration.sql
├── 20250307134850_workspace/
│   └── migration.sql
└── migration_lock.toml

Reset Database

# Reset database (development only)
npx prisma migrate reset
 
# This will:
# 1. Drop database
# 2. Recreate database
# 3. Apply all migrations
# 4. Run seed script (if configured)

Production Deployment

Deploy Migrations

# Deploy pending migrations to production
npx prisma migrate deploy
 
# This only applies migrations, doesn't generate client

Generate Client

# Regenerate Prisma client after schema changes
npx prisma generate

Turbo Scripts

The database package includes convenient npm scripts:

{
  "scripts": {
    "build": "prisma generate && tsc",
    "dev": "tsc --watch",
    "db:generate": "prisma generate",
    "db:push": "prisma db push",
    "db:migrate": "prisma migrate dev",
    "db:migrate:deploy": "prisma migrate deploy",
    "db:test-only:reset": "prisma migrate reset --force"
  }
}

Prisma Studio

Visual Database Browser

Launch Prisma Studio to browse and edit data:

cd packages/database
npx prisma studio

This opens a web interface at http://localhost:5555 where you can:

Browse all tables and records
Edit data with a user-friendly interface
Execute queries visually
Understand data relationships

Production Access

For production databases, use Prisma Studio with appropriate connection strings:

DATABASE_URL="postgresql://..." npx prisma studio

Best Practices

Type Safety

Always use generated types for full type safety:

import { type User, type Workspace, prisma } from '@repo/database';
 
// Type-safe function parameters
async function getUserWorkspaces(userId: User['id']): Promise<Workspace[]> {
  const user = await prisma.user.findUnique({
    where: { id: userId },
    include: {
      workspaces: {
        include: {
          workspace: true,
        },
      },
    },
  });
 
  return user?.workspaces.map((wm) => wm.workspace) || [];
}

Error Handling

Handle Prisma errors appropriately:

import { Prisma } from '@prisma/client';
 
try {
  const user = await prisma.user.create({
    data: { email: 'user@example.com' },
  });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    // Handle specific Prisma errors
    if (error.code === 'P2002') {
      throw new Error('Email already exists');
    }
  }
  throw error;
}

Query Optimization

Optimize queries for performance:

// ✅ Good: Select only needed fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
});
 
// ❌ Avoid: Fetching all fields when not needed
const users = await prisma.user.findMany();
 
// ✅ Good: Use pagination
const users = await prisma.user.findMany({
  skip: page * pageSize,
  take: pageSize,
});

Connection Management

The singleton pattern handles connections efficiently:

// ✅ Good: Use exported instance
import { prisma } from '@repo/database';
 
// ❌ Avoid: Creating new instances
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient(); // Don't do this

Troubleshooting

Debugging

Enable query logging for debugging:

const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

Conclusion

This comprehensive guide covers all aspects of working with Prisma in the Dopamine Starter Kit, from basic setup to advanced patterns and production best practices.