Database & Prisma

PostgreSQL database with Prisma ORM, type-safe queries, and automatic migrations.

Overview

The application uses PostgreSQL as the database and Prisma as the ORM. Prisma provides type-safe database access, automatic migrations, and a visual database browser (Prisma Studio).

Database Setup

PostgreSQL Installation

Choose one of the following options:

Local: Install PostgreSQL on your machine
Docker: Run PostgreSQL in a container
Cloud: Use Vercel Postgres, Supabase, Railway, or Neon

Docker Setup

docker run --name postgres \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=ai_saas \
  -p 5432:5432 \
  -d postgres:16

Connection String

# .env
DATABASE_URL="postgresql://user:password@localhost:5432/dbname?schema=public"

Prisma Schema

Models Overview

User - User accounts and authentication
Account - OAuth provider accounts
Session - User sessions
Role - RBAC roles (USER, ADMIN, SUPER_ADMIN)
UserRole - User-role assignments
Permission - Granular permissions
Plan - Subscription plans
Subscription - User subscriptions
Payment - Payment records
Blog - Blog posts
Category - Blog categories
BlogCategory - Many-to-many join table
Chat - AI chat conversations
CreditUsage - Credit consumption tracking

Key Relationships

// User has many roles (many-to-many)
User ↔ UserRole ↔ Role

// User has one subscription (one-to-one)
User ← Subscription → Plan

// User has many payments (one-to-many)
User ← Payment

// Blog has many categories (many-to-many)
Blog ↔ BlogCategory ↔ Category

Migrations

Create Migration

# Create a new migration
npx prisma migrate dev --name add_new_field

# Apply migrations to production
npx prisma migrate deploy

Reset Database

# Reset database (destructive!)
npx prisma migrate reset

# This will:
# 1. Drop the database
# 2. Create a new database
# 3. Apply all migrations
# 4. Run seed script

Prisma Client

Basic Queries

import { prisma } from "@/lib/prisma";

// Find many
const users = await prisma.user.findMany();

// Find one
const user = await prisma.user.findUnique({
  where: { id: userId },
});

// Create
const user = await prisma.user.create({
  data: {
    name: "John Doe",
    email: "john@example.com",
  },
});

// Update
const user = await prisma.user.update({
  where: { id: userId },
  data: { name: "Jane Doe" },
});

// Delete
await prisma.user.delete({
  where: { id: userId },
});

Relations

// Include related data
const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    subscription: {
      include: {
        plan: true,
      },
    },
    userRoles: {
      include: {
        role: true,
      },
    },
  },
});

// Select specific fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
});

Filtering

// Where conditions
const users = await prisma.user.findMany({
  where: {
    email: {
      contains: "@example.com",
    },
    credits: {
      gte: 100,
    },
  },
});

// OR conditions
const users = await prisma.user.findMany({
  where: {
    OR: [
      { name: { contains: "John" } },
      { email: { contains: "john" } },
    ],
  },
});

Pagination

const page = 1;
const perPage = 10;

const users = await prisma.user.findMany({
  take: perPage,
  skip: (page - 1) * perPage,
  orderBy: { createdAt: 'desc' },
});

const total = await prisma.user.count();

Prisma Studio

Visual database browser for viewing and editing data:

npx prisma studio

Access at http://localhost:5555

Seeding

Seed Script

Located at prisma/seed.ts, creates:

3 default roles (USER, ADMIN, SUPER_ADMIN)
Permissions for each role
3 subscription plans (Free, Pro, Business)
Sample blog categories

Run Seed

npx prisma db seed

Transactions

// All or nothing - rollback on error
await prisma.$transaction(async (tx) => {
  const user = await tx.user.update({
    where: { id: userId },
    data: { credits: { decrement: 10 } },
  });

  await tx.creditUsage.create({
    data: {
      userId,
      amount: 10,
      description: "AI Chat",
    },
  });
});

Performance Optimization

Indexes

model User {
  id    String @id @default(cuid())
  email String @unique // Automatic index
  name  String

  @@index([email]) // Custom index
  @@index([createdAt])
}

Connection Pooling

// DATABASE_URL with pooling
DATABASE_URL="postgresql://user:pass@host/db?connection_limit=10&pool_timeout=20"

Best Practices

Use transactions for related operations
Select only needed fields
Add indexes for frequently queried fields
Use connection pooling in production
Handle unique constraint errors
Use soft deletes for important data

Documentation