Database Setup

1$# 1. Go to supabase.com and create an account
2$# 2. Click "New Project"
3$# 3. Set a strong database password
4$# 4. Choose a region close to your users
5$# 5. Wait for project to be ready (~2 minutes)
6
7$# Get connection string from:
8$# Project Settings → Database → Connection string
9
10$# Direct connection (for migrations)
11$DATABASE_URL="postgresql://postgres:[PASSWORD]@db.[PROJECT].supabase.co:5432/postgres"
12
13$# Pooler connection (for app - recommended)
14$DATABASE_URL="postgresql://postgres.[PROJECT]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres?pgbouncer=true"

1$# 1. Sign up at neon.tech
2$# 2. Create a new project
3$# 3. Copy the connection string
4
5$# Neon connection string
6$DATABASE_URL="postgresql://[user]:[password]@[endpoint].neon.tech/[dbname]?sslmode=require"
7
8$# Enable connection pooling
9$DATABASE_URL="postgresql://[user]:[password]@[endpoint]-pooler.neon.tech/[dbname]?sslmode=require"

1$# 1. Sign up at railway.app
2$# 2. Create new project → Add PostgreSQL
3$# 3. Copy DATABASE_URL from Variables tab
4
5$# Railway connection string
6$DATABASE_URL="postgresql://postgres:[password]@[host].railway.app:5432/railway"

1$# DECISION TREE: Which database URL should I use?
2
3$# 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)
8
9$# Question 2: What are you doing?
10$# ├─ Running the app (production traffic)
11$# │  └─ Use POOLER URL in DATABASE_URL
12$# └─ Running database migrations (prisma migrate deploy)
13$#    └─ Use DIRECT URL (pooler doesn't support migrations)
14
15$# WHY POOLING MATTERS:
16$# - Serverless creates a NEW database connection for EVERY request
17$# - PostgreSQL has a connection limit (usually 100-200 connections)
18$# - Without pooling, 100 simultaneous requests = 100 connections = LIMIT REACHED
19$# - With pooling, 1000 simultaneous requests share ~10 connections = NO PROBLEM
20
21$# 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"
24
25$# Direct URL (for migrations only - add to .env.local):
26$DATABASE_URL_DIRECT="postgresql://postgres:[PASSWORD]@db.abcdefghij.supabase.co:5432/postgres"
27
28$# 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"
31
32$# Direct URL (for migrations):
33$DATABASE_URL_DIRECT="postgresql://user:password@ep-name-123456.us-east-2.aws.neon.tech/dbname?sslmode=require"
34
35$# 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"
38
39$# PRISMA SCHEMA CONFIGURATION:
40$# If using Supabase or Neon, add directUrl to prisma/schema.prisma:
41
42$// prisma/schema.prisma
43$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$}
48
49$# Then in .env.local, set BOTH variables

1// Core authentication
2model User {
3  id            String    @id @default(cuid())
4  email         String    @unique
5  name          String?
6  image         String?
7  emailVerified DateTime?
8  password      String?   // For credentials auth
9  role          Role      @default(USER)
10  sessionVersion Int      @default(1)
11
12  // Stripe integration
13  customerId       String?   @unique
14  subscriptionTier String?
15  trialEndsAt      DateTime?
16
17  accounts        Account[]
18  sessions        Session[]
19  payments        Payment[]
20  organizations   OrganizationMember[]
21}
22
23// Payments
24model Payment {
25  id        String   @id @default(cuid())
26  userId    String
27  stripeId  String   @unique
28  amount    Int      // cents
29  status    String   // succeeded, failed, pending
30  createdAt DateTime @default(now())
31  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
32}

1$# Development - Quick iterations
2$npm run db:push  # Pushes schema changes directly
3
4$# Production - Use migrations
5$npm run db:migrate -- --name add_audit_logs
6
7$# This creates:
8$# prisma/migrations/[timestamp]_add_audit_logs/migration.sql
9
10$# Deploy migration to production
11$npx prisma migrate deploy
12
13$# View migration history
14$npx prisma migrate status
15
16$# Reset database (DANGER - deletes all data)
17$npm run db:reset

1$# Supabase
2$# - Automatic daily backups (Pro plan)
3$# - Point-in-time recovery (Pro plan)
4$# - Download backups from Dashboard
5
6$# Neon
7$# - Automatic backups with branching
8$# - Create branches for testing migrations
9
10$# Manual backup with pg_dump
11$pg_dump $DATABASE_URL > backup.sql
12
13$# Restore
14$psql $DATABASE_URL < backup.sql