Database Setup with Prisma ORM and PostgreSQL

A reliable, scalable database is the backbone of every successful SaaS application. Prisma ORM makes working with databases feel like magic type-safe queries, effortless migrations, and excellent DX.

This guide shows you how to set up Prisma with PostgreSQL in a Next.js 16+ app the exact production-ready configuration used in SaaSJet. By the end, you'll have a fully connected database with models for users, subscriptions, and more.

Why Prisma + PostgreSQL is Ideal for SaaS

• Type Safety: Full TypeScript autocomplete and compile-time checks
• Migrations: Safe, version-controlled schema changes
• Performance: Optimized queries with Prisma Client
• Scalability: PostgreSQL handles complex relations and high traffic
• Developer Friendly: Prisma Studio for visual database browsing

Step 1: Set Up PostgreSQL

Option A: Local Development (Recommended)

Use Docker for instant PostgreSQL:

Create docker-compose.yml in your project root:

version: '3.8'
services:
  postgres:
    image: postgres:16
    container_name: saasjet-postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: saasjet
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    restart: unless-stopped

volumes:
  postgres_data:

Run it:

docker-compose up -d

Option B: Cloud (Production)

Use a managed provider:

• Prisma (free tier available)
• Neon.tech (serverless Postgres)
• Supabase

Get your connection string (e.g., postgresql://user:password@host:5432/dbname)

Step 2: Install Prisma

npm install prisma @prisma/client
npx prisma init

This creates:

• prisma/schema.prisma – Your database schema
• .env – With DATABASE_URL

Update .env:

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/dbname?..."

(Replace with your cloud URL in production)

Step 3: Define Your Prisma Schema

Replace the default schema with a SaaS-ready model:

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id                 String    @id @default(cuid())
  email              String    @unique
  emailVerified      Boolean   @default(false)
  name               String?
  image              String?
  role               String    @default("user") // user, admin
  createdAt          DateTime  @default(now())
  updatedAt          DateTime  @updatedAt

  // Relations
  accounts           Account[]
  subscriptions      Subscription[]
  billing            Billing[]

  @@map("users")
}

model Account {
  id                 String   @id @default(cuid())
  userId             String
  provider           String   // google, github, etc.
  providerAccountId  String
  accessToken        String?
  refreshToken       String?

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

  @@unique([provider, providerAccountId])
  @@map("accounts")
}

model Subscription {
  id                 String   @id @default(cuid())
  userId             String
  stripeCustomerId   String?
  stripeSubscriptionId String?
  plan               String   // pro, lifetime, etc.
  status             String   // active, canceled, past_due
  currentPeriodStart DateTime?
  currentPeriodEnd   DateTime?
  createdAt          DateTime @default(now())

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

  @@map("subscriptions")
}

model Billing {
  id          String   @id @default(cuid())
  userId      String
  amount      Int
  currency    String   @default("usd")
  description String
  status      String   // paid, pending, failed
  createdAt   DateTime @default(now())

  user        User     @relation(fields: [userId], references: [id])

  @@map("billing")
}

Step 4: Run Your First Migration

npx prisma migrate dev --name init

This:

• Creates the tables in PostgreSQL
• Generates the Prisma Client (node_modules/@prisma/client)

Step 5: Use Prisma Client in Your App

Generate the client:

npx prisma generate

Create a singleton client (src/lib/prisma.ts):

import { PrismaClient } from '@prisma/client';

const globalForPrisma = global as unknown as { prisma: PrismaClient };

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

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

export default prisma;

Now use it anywhere:

// Example: Create a user
import prisma from '@/lib/prisma';

await prisma.user.create({
  data: {
    email: 'user@example.com',
    name: 'John Doe',
    role: 'admin',
  },
});

Step 6: Explore Your Data with Prisma Studio

npx prisma studio

Open http://localhost:5555 visual database editor!

Production Tips

• Use connection pooling (Neon, Supabase, or Prisma Accelerate)
• Set pgbouncer mode in connection string for serverless
• Enable SSL in production
• Never commit .env with real credentials
• Use separate databases for staging/production

Final Thoughts

Prisma + PostgreSQL gives you a powerful, type-safe foundation that scales from prototype to production. No more raw SQL strings or mismatched types.

SaaSJet ships with this entire setup pre-configured: Prisma client, models for users/subscriptions/billing, seeded data, and migration scripts so you can start building features immediately.