Loading...
Loading...
> Set up a production PostgreSQL database with Prisma ORM.
5 database providers supported: Supabase (recommended), Neon, Railway, PlanetScale, and AWS RDS. All use Prisma ORM with connection pooling.
DESC: Supabase recommended for free tier and DX
DESC: Sign up and create a new database project
DESC: Copy YOUR actual PostgreSQL connection URL from the provider
1$# Go to your database provider's dashboard and copy the connection string2$# Replace the example below with YOUR actual connection string34$# Supabase (pooler URL recommended for serverless apps like Vercel):5$DATABASE_URL="postgresql://postgres.abcdefghij:[YOUR_PASSWORD]@aws-0-us-west-1.pooler.supabase.com:6543/postgres?pgbouncer=true"67$# Neon (pooler URL with SSL):8$DATABASE_URL="postgresql://user:password@ep-cool-name-123456-pooler.us-east-2.aws.neon.tech/dbname?sslmode=require"910$# Railway (direct connection - Railway uses traditional servers):11$DATABASE_URL="postgresql://postgres:password@containers-us-west-123.railway.app:5432/railway"
DESC: Create all database tables in your PostgreSQL database
1$# Push schema to database (creates tables)2$npm run db:push34$# Expected output:5$# Prisma schema loaded from prisma/schema.prisma6$# Datasource "db": PostgreSQL database7$#8$# Your database is now in sync with your Prisma schema. Done in 2.34s9$#10$# What this means: Prisma created tables like User, Account, Session, Payment1112$# Alternative: Create a migration (for production)13$npm run db:migrate -- --name init1415$# Seed with test data (optional - adds sample users)16$npm run db:seed1718$# Open Prisma Studio to view your data in a GUI19$npm run db:studio20$# Opens http://localhost:5555 in your browser
Step-by-step Supabase configuration
1$# 1. Go to supabase.com and create an account2$# 2. Click "New Project"3$# 3. Set a strong database password4$# 4. Choose a region close to your users5$# 5. Wait for project to be ready (~2 minutes)67$# Get connection string from:8$# Project Settings → Database → Connection string910$# Direct connection (for migrations)11$DATABASE_URL="postgresql://postgres:[PASSWORD]@db.[PROJECT].supabase.co:5432/postgres"1213$# Pooler connection (for app - recommended)14$DATABASE_URL="postgresql://postgres.[PROJECT]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres?pgbouncer=true"
Serverless PostgreSQL with branching
1$# 1. Sign up at neon.tech2$# 2. Create a new project3$# 3. Copy the connection string45$# Neon connection string6$DATABASE_URL="postgresql://[user]:[password]@[endpoint].neon.tech/[dbname]?sslmode=require"78$# Enable connection pooling9$DATABASE_URL="postgresql://[user]:[password]@[endpoint]-pooler.neon.tech/[dbname]?sslmode=require"
Simple deployment platform
1$# 1. Sign up at railway.app2$# 2. Create new project → Add PostgreSQL3$# 3. Copy DATABASE_URL from Variables tab45$# Railway connection string6$DATABASE_URL="postgresql://postgres:[password]@[host].railway.app:5432/railway"
When to use pooler URL vs direct URL
1$# DECISION TREE: Which database URL should I use?23$# Question 1: Where is your app running?4$# ├─ Serverless (Vercel, AWS Lambda, Cloudflare Workers)5$# │ └─ Use POOLER URL (required to avoid connection limits)6$# └─ Traditional server (DigitalOcean, Heroku, Railway container)7$# └─ Use DIRECT URL (pooler optional but still helpful)89$# Question 2: What are you doing?10$# ├─ Running the app (production traffic)11$# │ └─ Use POOLER URL in DATABASE_URL12$# └─ Running database migrations (prisma migrate deploy)13$# └─ Use DIRECT URL (pooler doesn't support migrations)1415$# WHY POOLING MATTERS:16$# - Serverless creates a NEW database connection for EVERY request17$# - PostgreSQL has a connection limit (usually 100-200 connections)18$# - Without pooling, 100 simultaneous requests = 100 connections = LIMIT REACHED19$# - With pooling, 1000 simultaneous requests share ~10 connections = NO PROBLEM2021$# SUPABASE SETUP:22$# Pooler URL (for your app - use this in .env.local and Vercel):23$DATABASE_URL="postgresql://postgres.abcdefghij:[PASSWORD]@aws-0-us-west-1.pooler.supabase.com:6543/postgres?pgbouncer=true"2425$# Direct URL (for migrations only - add to .env.local):26$DATABASE_URL_DIRECT="postgresql://postgres:[PASSWORD]@db.abcdefghij.supabase.co:5432/postgres"2728$# NEON SETUP:29$# Pooler URL (for your app):30$DATABASE_URL="postgresql://user:password@ep-name-123456-pooler.us-east-2.aws.neon.tech/dbname?sslmode=require"3132$# Direct URL (for migrations):33$DATABASE_URL_DIRECT="postgresql://user:password@ep-name-123456.us-east-2.aws.neon.tech/dbname?sslmode=require"3435$# RAILWAY SETUP:36$# Railway provides traditional server, so direct connection is fine:37$DATABASE_URL="postgresql://postgres:password@containers-us-west-123.railway.app:5432/railway"3839$# PRISMA SCHEMA CONFIGURATION:40$# If using Supabase or Neon, add directUrl to prisma/schema.prisma:4142$// prisma/schema.prisma43$datasource db {44$ provider = "postgresql"45$ url = env("DATABASE_URL") // Pooler URL (for app)46$ directUrl = env("DATABASE_URL_DIRECT") // Direct URL (for migrations)47$}4849$# Then in .env.local, set BOTH variables
Key models in prisma/schema.prisma
1// Core authentication2model User {3 id String @id @default(cuid())4 email String @unique5 name String?6 image String?7 emailVerified DateTime?8 password String? // For credentials auth9 role Role @default(USER)10 sessionVersion Int @default(1)1112 // Stripe integration13 customerId String? @unique14 subscriptionTier String?15 trialEndsAt DateTime?1617 accounts Account[]18 sessions Session[]19 payments Payment[]20 organizations OrganizationMember[]21}2223// Payments24model Payment {25 id String @id @default(cuid())26 userId String27 stripeId String @unique28 amount Int // cents29 status String // succeeded, failed, pending30 createdAt DateTime @default(now())31 user User @relation(fields: [userId], references: [id], onDelete: Cascade)32}
Managing schema changes in production
1$# Development - Quick iterations2$npm run db:push # Pushes schema changes directly34$# Production - Use migrations5$npm run db:migrate -- --name add_audit_logs67$# This creates:8$# prisma/migrations/[timestamp]_add_audit_logs/migration.sql910$# Deploy migration to production11$npx prisma migrate deploy1213$# View migration history14$npx prisma migrate status1516$# Reset database (DANGER - deletes all data)17$npm run db:reset
Backup strategies per provider
1$# Supabase2$# - Automatic daily backups (Pro plan)3$# - Point-in-time recovery (Pro plan)4$# - Download backups from Dashboard56$# Neon7$# - Automatic backups with branching8$# - Create branches for testing migrations910$# Manual backup with pg_dump11$pg_dump $DATABASE_URL > backup.sql1213$# Restore14$psql $DATABASE_URL < backup.sql
Supabase - Free tier, great DX (recommended)Neon - Serverless PostgreSQL with branchingRailway - Simple and affordablePlanetScale - MySQL alternativeAWS RDS - Enterprise-grade@@index([userId, createdAt])