Skip to Content
DocsDatabasePostgreSQL + Prisma

PostgreSQL + Prisma

PostgreSQL with Prisma ORM provides type-safe database operations with excellent developer experience. This module is already integrated in the template.

Overview

Prisma ORM offers type-safe database access, automatic migrations, and excellent TypeScript integration with PostgreSQL.

Key Features:

  • Type-safe database queries
  • Automatic migration generation
  • Database schema management
  • Built-in connection pooling
  • Introspection and code generation

Getting Started

1. Database Setup Options

Option A: Use Local PostgreSQL

  1. Install PostgreSQL locally
  2. Create a database
  3. Update DATABASE_URL in .env.local

Option B: Use Hosted PostgreSQL (Recommended)

  • Neon: Free PostgreSQL hosting at neon.tech 
  • Railway: PostgreSQL with easy deployment
  • Supabase: PostgreSQL with additional features
  • PlanetScale: MySQL alternative (compatible with Prisma)

2. Add Database URL

Add this to your .env.local file:

DATABASE_URL="postgresql://username:password@hostname:5432/database?schema=public"

3. How It Works in the Template

The template includes pre-configured Prisma setup:

  • Schema File: prisma/schema.prisma defines your data models
  • Client Setup: lib/prisma.ts configures Prisma client
  • Generated Types: TypeScript types auto-generated from schema
  • API Integration: Database operations in API routes
  • User Synchronization: Automatic user creation from authentication

4. Template Structure

prisma/
├── schema.prisma       # Database schema definition
lib/
├── prisma.ts          # Prisma client configuration
├── generated/prisma/  # Auto-generated Prisma client
app/api/
├── user/              # User management endpoints
├── stripe/webhook/    # Payment webhook handlers

Database Schema

The template includes these pre-configured models:

User Model

// User table (synced with Clerk)
model User {
  id        String   @id @default(cuid())
  clerkId   String   @unique
  email     String   @unique
  firstName String?
  lastName  String?
  imageUrl  String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
 
  subscriptions Subscription[]
  payments      Payment[]
}

Subscription Model

// Subscription management
model Subscription {
  id                   String             @id @default(cuid())
  userId               String
  stripeCustomerId     String?
  stripeSubscriptionId String?            @unique
  stripePriceId        String?
  status               SubscriptionStatus @default(INACTIVE)
  currentPeriodStart   DateTime?
  currentPeriodEnd     DateTime?
  cancelAtPeriodEnd    Boolean            @default(false)
  
  user User @relation(fields: [userId], references: [id])
}

Payment Model

// Payment tracking
model Payment {
  id                    String        @id @default(cuid())
  userId                String
  stripePaymentIntentId String?       @unique
  amount                Int           // Amount in cents
  currency              String        @default("usd")
  status                PaymentStatus @default(PENDING)
  description           String?
  
  user User @relation(fields: [userId], references: [id])
}

Database Operations

The template provides type-safe database operations:

User Operations

// Get user by Clerk ID (implemented in template)
const user = await prisma.user.findUnique({
  where: { clerkId: userId },
  include: { subscriptions: true, payments: true }
})
 
// Create or update user (auto-sync from Clerk)
const user = await prisma.user.upsert({
  where: { clerkId: userId },
  update: { email, firstName, lastName, imageUrl },
  create: { clerkId: userId, email, firstName, lastName, imageUrl }
})

Subscription Operations

// Get user's active subscription
const subscription = await prisma.subscription.findFirst({
  where: { 
    userId: user.id,
    status: 'ACTIVE'
  }
})
 
// Create subscription from webhook
const subscription = await prisma.subscription.create({
  data: {
    userId: user.id,
    stripeCustomerId: customerId,
    stripeSubscriptionId: subscriptionId,
    stripePriceId: priceId,
    status: 'ACTIVE',
    currentPeriodStart: new Date(periodStart * 1000),
    currentPeriodEnd: new Date(periodEnd * 1000)
  }
})

Payment Operations

// Record payment from webhook
const payment = await prisma.payment.create({
  data: {
    userId: user.id,
    stripePaymentIntentId: paymentIntentId,
    amount: amountPaid,
    currency: 'usd',
    status: 'SUCCEEDED',
    description: 'Pro subscription payment'
  }
})

API Routes Integration

The template includes these database-connected APIs:

User API (/api/user)

  • GET - Fetch current user data
  • POST - Create/update user (called automatically from Clerk sync)
  • Handles user synchronization from authentication

Subscription API (/api/user/subscription)

  • GET - Get user’s subscription status
  • Returns subscription details and billing period

Payments API (/api/user/payments)

  • GET - Get user’s payment history
  • Returns all payments with status and dates

Database Commands

The template includes these helpful scripts:

# Generate Prisma client (run after schema changes)
npm run db:generate
 
# Push schema changes to database
npm run db:push
 
# Create and run migrations
npm run db:migrate
 
# Open database GUI
npm run db:studio
 
# Reset database (development only)
npm run db:reset

Webhook Integration

The template handles Stripe webhooks to update database:

// Stripe webhook handler (implemented in template)
// app/api/stripe/webhook/route.ts
 
// On successful checkout
case 'checkout.session.completed':
  // Create subscription record
  // Update user's subscription status
 
// On subscription change
case 'customer.subscription.updated':
  // Update subscription status and billing period
 
// On payment success
case 'invoice.payment_succeeded':
  // Record successful payment

Type Safety

Prisma generates TypeScript types for all your models:

// Use generated types (available in template)
import { User, Subscription, Payment } from '@prisma/client'
 
// Type for user with relations
type UserWithSubscriptions = User & {
  subscriptions: Subscription[]
  payments: Payment[]
}
 
// Query with full type safety
const usersWithData: UserWithSubscriptions[] = await prisma.user.findMany({
  include: {
    subscriptions: true,
    payments: true
  }
})

Environment Configuration

Development

DATABASE_URL="postgresql://user:pass@localhost:5432/mydb"

Production

DATABASE_URL="postgresql://user:pass@host:5432/mydb?connection_limit=5&pool_timeout=60"

Schema Updates

When you need to modify the database schema:

  1. Edit prisma/schema.prisma
  2. Generate client: npm run db:generate
  3. Push changes: npm run db:push (development)
  4. Create migration: npm run db:migrate (production)

Example: Adding a New Field

// Add a new field to User model
model User {
  id        String   @id @default(cuid())
  clerkId   String   @unique
  email     String   @unique
  firstName String?
  lastName  String?
  imageUrl  String?
  timezone  String?  // New field
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Advanced Features

Transactions

// Multiple operations in a transaction
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.update({
    where: { id: userId },
    data: { updatedAt: new Date() }
  })
  
  const subscription = await tx.subscription.create({
    data: { userId, status: 'ACTIVE' }
  })
  
  return { user, subscription }
})

Raw Queries

// For complex queries
const result = await prisma.$queryRaw`
  SELECT u.*, COUNT(p.id) as payment_count
  FROM users u
  LEFT JOIN payments p ON u.id = p.userId
  WHERE u.createdAt > NOW() - INTERVAL '30 days'
  GROUP BY u.id
`

Production Considerations

Connection Pooling

The template includes optimized connection settings:

// lib/prisma.ts includes singleton pattern
// Prevents connection pool exhaustion in serverless

Performance

  • Use select to fetch only needed fields
  • Use include for related data
  • Index frequently queried fields
  • Monitor query performance with Prisma’s built-in logging

Troubleshooting

Migration conflicts:

  • Reset development database: npm run db:reset
  • Create new migration: npm run db:migrate

Type errors after schema changes:

  • Regenerate client: npm run db:generate
  • Restart TypeScript server in your editor

Connection issues:

  • Verify DATABASE_URL format is correct
  • Check database server is running and accessible
  • Ensure database exists and user has proper permissions

Webhook integration issues:

  • Check Stripe webhook endpoint is correctly configured
  • Verify webhook secret matches environment variable
  • Look at webhook logs in Stripe dashboard for errors
Last updated on
Supabase AuthenticationSupabase Database