General

Database

Learn how to manage your database, schema, and migrations with Prisma ORM.

The Pro Next.js Prisma starter kit uses Prisma ORM with PostgreSQL. Prisma provides a powerful and intuitive way to manage your database schema and interact with your data.

Client Setup

The Prisma client is initialized in lib/db/prisma.ts and exported from lib/db/index.ts. It uses the pg driver for optimized connections.

lib/db/prisma.ts

import 'server-only';

import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from '@prisma/client';
import { Pool } from 'pg';

function createPrismaClient() {
  const connectionString = process.env.DATABASE_URL;
  if (!connectionString) {
    throw new Error('DATABASE_URL is required to initialize PrismaClient');
  }

  const pool = new Pool({ connectionString });
  return new PrismaClient({
    adapter: new PrismaPg(pool)
  });
}

export const prisma = createPrismaClient();

The client is then exported from lib/db/index.ts:

lib/db/index.ts

export * from './prisma';

Schema Definition

Your database schema is defined in prisma/schema.prisma. This file contains your models, enums, and relations.

Example Model Definition

prisma/schema.prisma

model Lead {
    id             String       @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    organizationId String       @map("organization_id") @db.Uuid
    firstName      String       @map("first_name") @db.Text
    lastName       String       @map("last_name") @db.Text
    email          String       @unique @db.Text
    createdAt      DateTime     @default(now()) @map("created_at") @db.Timestamptz(6)
    updatedAt      DateTime     @default(now()) @updatedAt @map("updated_at") @db.Timestamptz(6)

    organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

    @@index([organizationId], map: "lead_organization_id_idx")
    @@map("lead")
}

Migrations

Commands

Command	Description
`npm run db:generate`	Generate Prisma Client from schema
`npm run db:migrate`	Apply pending migrations
`npm run db:studio`	Open Prisma Studio GUI
`npm run db:push`	Push schema directly (dev only, no migration)

Migration Workflow

Edit schema in prisma/schema.prisma.
Create migration: npm run db:migrate:dev -- --name your_migration_name.
Review migration in prisma/migrations/.
Deploy migration: npm run db:migrate (production) or npm run db:migrate:dev (development).

Multi-Tenancy

Critical: Always filter by organizationId for tenant data to ensure data isolation.

trpc/routers/lead-router.ts

const leads = await prisma.lead.findMany({
  where: { organizationId: ctx.organization.id }
});

Transactions

Use $transaction for related operations that must be atomic.

lib/actions/widget.ts

const result = await prisma.$transaction(async (tx) => {
  await tx.subscriptionItem.deleteMany({
    where: { subscriptionId: subId }
  });

  await tx.subscriptionItem.createMany({
    data: newItems
  });

  return tx.subscriptionItem.findMany({
    where: { subscriptionId: subId }
  });
});