Database

PostgreSQL with Prisma ORM for type-safe database operations and migrations.

Stack

• PostgreSQL database
• Prisma ORM with TypeScript
• Automated migrations
• Connection pooling

Features

• User & subscription management
• Auth tables (NextAuth.js)
• Event tracking
• Waitlist management

Schema

Core Models

prisma/schema.prisma

 1model User {
 2  id            String    @id @default(cuid())
 3  name          String?
 4  email         String    @unique
 5  emailVerified DateTime?
 6  image         String?
 7  accounts      Account[]
 8  subscription  Subscription?
 9  createdAt     DateTime  @default(now())
10  updatedAt     DateTime  @updatedAt
11}
12
13model Subscription {
14  id                     String   @id @default(cuid())
15  userId                 String   @unique
16  user                   User     @relation(...)
17  stripeCustomerId       String?
18  stripeSubscriptionId   String?  @unique
19  stripePriceId          String?
20  stripeCurrentPeriodEnd DateTime?
21  status                 String?
22  cancelAtPeriodEnd      Boolean  @default(false)
23  type                   SubscriptionType
24  createdAt              DateTime @default(now())
25  updatedAt              DateTime @updatedAt
26}

Connection

src/server/db.ts

 1import { PrismaClient } from "@prisma/client";
 2
 3const globalForPrisma = globalThis as unknown as {
 4  prisma: PrismaClient | undefined;
 5};
 6
 7export const db = globalForPrisma.prisma ?? new PrismaClient();
 8
 9if (process.env.NODE_ENV !== "production") {
10  globalForPrisma.prisma = db;
11}

Singleton pattern prevents too many connections during development.

Common Queries

 1import { db } from "~/server/db";
 2
 3// Get user with subscription
 4const user = await db.user.findUnique({
 5  where: { id: userId },
 6  include: { subscription: true },
 7});
 8
 9// Create with transaction
10await db.$transaction(async (tx) => {
11  const subscription = await tx.subscription.create({
12    data: { userId, status: "active", ... },
13  });
14  
15  await tx.subscriptionEvent.create({
16    data: { subscriptionId: subscription.id, eventType: "created" },
17  });
18  
19  return subscription;
20});

Commands

Development

pnpm db:generate — Generate client
pnpm db:push — Push schema changes
pnpm db:studio — Open Prisma Studio
pnpm db:reset — Reset database

Production

pnpm db:migrate dev — Create migration
pnpm db:migrate deploy — Apply migrations
Always backup before migrations
Test in staging first

Use db:push for rapid prototyping. Create migrations with db:migrate dev before committing.

Next: GitHub Actions

Automated database migrations

Continue →