Module 5: Database & PostgreSQL

PostgreSQL is the gold standard for SaaS applications. Here's everything you need to know.

Why PostgreSQL?

Feature	PostgreSQL	MySQL	MongoDB
ACID Compliance	Full	Full	Limited
JSON Support	Excellent (JSONB)	Basic	Native
Full-Text Search	Built-in	Basic	Atlas Search
Vector Search	pgvector	None	Atlas Vector
Extensions	100+	Limited	N/A
Scaling	Read replicas, partitioning	Same	Sharding

Supabase: Managed PostgreSQL

Supabase gives you PostgreSQL with extra features:

Supabase = PostgreSQL
         + Authentication
         + Realtime subscriptions
         + Storage (S3-compatible)
         + Edge Functions
         + Auto-generated APIs

Setup Supabase

1. Create project at supabase.com
2. Get connection string from Settings → Database

# Install client
npm install @supabase/supabase-js

// lib/supabase.ts
import { createClient } from '@supabase/supabase-js'

export const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

Prisma ORM

Prisma is the most popular TypeScript ORM.

Setup

npm install prisma @prisma/client
npx prisma init

Schema Definition

// 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
  name      String?
  avatar    String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  posts     Post[]
  subscription Subscription?
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String

  @@index([authorId])
}

model Subscription {
  id                 String   @id @default(cuid())
  stripeCustomerId   String   @unique
  stripeSubscriptionId String? @unique
  stripePriceId      String?
  stripeCurrentPeriodEnd DateTime?

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

Migrations

# Create migration
npx prisma migrate dev --name init

# Apply in production
npx prisma migrate deploy

# Reset database (dev only)
npx prisma migrate reset

Database Client

// lib/db.ts
import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const db = globalForPrisma.prisma ?? new PrismaClient()

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = db
}

CRUD Operations

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

// Read one
const user = await db.user.findUnique({
  where: { id: 'user_123' },
})

// Read many with filter
const users = await db.user.findMany({
  where: {
    email: { contains: '@example.com' },
  },
  orderBy: { createdAt: 'desc' },
  take: 10,
})

// Update
const updated = await db.user.update({
  where: { id: 'user_123' },
  data: { name: 'Jane Doe' },
})

// Delete
await db.user.delete({
  where: { id: 'user_123' },
})

// Include relations
const userWithPosts = await db.user.findUnique({
  where: { id: 'user_123' },
  include: {
    posts: true,
    subscription: true,
  },
})

Drizzle ORM (Alternative)

Drizzle is lighter-weight and SQL-like:

// schema.ts
import { pgTable, text, timestamp, boolean } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow(),
})

export const posts = pgTable('posts', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  published: boolean('published').default(false),
  authorId: text('author_id').references(() => users.id),
})

// Usage
import { db } from './db'
import { users, posts } from './schema'
import { eq } from 'drizzle-orm'

// Select
const allUsers = await db.select().from(users)

// Select with where
const user = await db.select()
  .from(users)
  .where(eq(users.email, 'john@example.com'))

// Join
const userPosts = await db.select()
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .where(eq(users.id, 'user_123'))

// Insert
await db.insert(users).values({
  id: 'user_123',
  email: 'john@example.com',
  name: 'John',
})

// Update
await db.update(users)
  .set({ name: 'Jane' })
  .where(eq(users.id, 'user_123'))

// Delete
await db.delete(users).where(eq(users.id, 'user_123'))

Database Design Patterns

Soft Deletes

model Post {
  id        String    @id @default(cuid())
  title     String
  deletedAt DateTime? // null = not deleted

  @@index([deletedAt])
}

// "Delete" = set deletedAt
await db.post.update({
  where: { id },
  data: { deletedAt: new Date() },
})

// Query excludes deleted
const posts = await db.post.findMany({
  where: { deletedAt: null },
})

Multi-Tenancy

model Organization {
  id    String @id @default(cuid())
  name  String
  users User[]
  posts Post[]
}

model User {
  id             String       @id
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id])

  @@index([organizationId])
}

model Post {
  id             String       @id
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id])

  @@index([organizationId])
}

// Always filter by organization
const posts = await db.post.findMany({
  where: {
    organizationId: currentUser.organizationId,
  },
})

Row Level Security (RLS)

PostgreSQL RLS enforces access at the database level:

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their own posts
CREATE POLICY "Users can view own posts"
ON posts FOR SELECT
USING (author_id = auth.uid());

-- Policy: Users can only insert their own posts
CREATE POLICY "Users can insert own posts"
ON posts FOR INSERT
WITH CHECK (author_id = auth.uid());

Indexing for Performance

When to Add Indexes

model Post {
  id        String   @id
  title     String
  authorId  String
  status    String
  createdAt DateTime

  // Index columns you filter/sort by
  @@index([authorId])           // Filter by author
  @@index([status])             // Filter by status
  @@index([createdAt])          // Sort by date
  @@index([authorId, status])   // Compound: filter both
}

Checking Query Performance

-- Explain query plan
EXPLAIN ANALYZE
SELECT * FROM posts
WHERE author_id = 'user_123'
ORDER BY created_at DESC
LIMIT 10;

-- Look for:
-- - "Seq Scan" = bad (full table scan)
-- - "Index Scan" = good (using index)

Connection Pooling

Serverless functions need connection pooling:

# Without pooling: Each function opens new connection
Function 1 → Connection 1 → Database
Function 2 → Connection 2 → Database
Function 3 → Connection 3 → Database
... (connections exhausted!)

# With pooling: Functions share connections
Function 1 ┐
Function 2 ├→ Pool → 5 Connections → Database
Function 3 ┘

Supabase: Built-in pooler at port 6543

# Direct connection (migrations)
DATABASE_URL="postgresql://...@db.xxx.supabase.co:5432/postgres"

# Pooled connection (app)
DATABASE_URL="postgresql://...@db.xxx.supabase.co:6543/postgres?pgbouncer=true"

Summary

1. PostgreSQL - Best database for SaaS (features, reliability, ecosystem)
2. Supabase - Managed Postgres with auth, realtime, storage
3. Prisma - Type-safe ORM with great DX
4. Drizzle - Lighter alternative, SQL-like syntax
5. Indexes - Add to columns you filter/sort by
6. RLS - Enforce access at database level
7. Connection pooling - Essential for serverless

Next: Module 6: Vector Search & AI →