Documentation

Database & Prisma

Learn how to work with the database, customize the schema, and add new models.

Overview

ShipSecure uses Prisma ORM with PostgreSQL for type-safe database operations. The database schema is defined in prisma/schema.prisma and includes:

  • User - User accounts with plan information
  • Account - OAuth provider accounts (GitHub, Google)
  • Session - Active user sessions
  • License - Gumroad license keys

Database Client

The Prisma client is configured in src/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;
}

Why this pattern?

  • Prevents creating multiple Prisma clients during hot-reload in development
  • Uses a single client instance in production
  • Import as import { db } from "@/lib/db" throughout your app

Using the Database

Basic Queries

import { db } from "@/lib/db";

// Find a user by ID
const user = await db.user.findUnique({
  where: { id: "user-id" },
});

// Find a user by email
const userByEmail = await db.user.findUnique({
  where: { email: "user@example.com" },
});

// Get all users
const allUsers = await db.user.findMany();

// Get users with pagination
const paginatedUsers = await db.user.findMany({
  skip: 0,
  take: 10,
  orderBy: { createdAt: "desc" },
});

Creating Records

// Create a new user
const newUser = await db.user.create({
  data: {
    email: "new@example.com",
    name: "New User",
    plan: "FREE",
  },
});

// Create with relations
const userWithLicense = await db.user.create({
  data: {
    email: "pro@example.com",
    name: "Pro User",
    plan: "PRO",
    licenses: {
      create: {
        key: "LICENSE-KEY-123",
        type: "pro",
        provider: "gumroad",
      },
    },
  },
  include: { licenses: true },
});

Updating Records

// Update a user
const updatedUser = await db.user.update({
  where: { id: "user-id" },
  data: { plan: "PRO" },
});

// Update or create (upsert)
const upsertedUser = await db.user.upsert({
  where: { email: "user@example.com" },
  update: { name: "Updated Name" },
  create: {
    email: "user@example.com",
    name: "New User",
  },
});

Deleting Records

// Delete a user (cascades to related records)
await db.user.delete({
  where: { id: "user-id" },
});

// Delete many
await db.session.deleteMany({
  where: {
    expires: { lt: new Date() }, // Delete expired sessions
  },
});

Transactions

// Atomic transaction - all succeed or all fail
await db.$transaction([
  db.user.update({
    where: { id: userId },
    data: { plan: "PRO" },
  }),
  db.license.create({
    data: {
      key: licenseKey,
      userId: userId,
      type: "pro",
      provider: "gumroad",
    },
  }),
]);

// Interactive transaction
const result = await db.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: userId } });

  if (!user) throw new Error("User not found");

  return tx.user.update({
    where: { id: userId },
    data: { plan: "PRO" },
  });
});

Schema Structure

The default schema includes these models:

User Model

model User {
  id            String    @id @default(cuid())
  email         String    @unique
  name          String?
  image         String?
  emailVerified DateTime?

  // Subscription fields
  plan          Plan      @default(FREE)
  customerId    String?   // Future: Stripe Customer ID
  subscriptionId String?  // Future: Stripe Subscription ID

  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt

  // Relations
  accounts Account[]
  sessions Session[]
  licenses License[]
}

enum Plan {
  FREE
  PRO
}

License Model

model License {
  id          String    @id @default(cuid())
  key         String    @unique
  type        String    // License type (e.g., "standard", "lifetime")
  status      String    @default("active")
  userId      String
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  provider    String    @default("gumroad")
  plan        Plan      @default(PRO)

  expiresAt   DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
}

Auth.js Models

// OAuth accounts (GitHub, Google)
model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

// Active sessions
model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

// Email verification tokens
model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

Adding New Models

Step 1: Define the Model

Open prisma/schema.prisma and add your new model:

// Example: Adding a Project model
model Project {
  id          String   @id @default(cuid())
  name        String
  description String?
  isPublic    Boolean  @default(false)

  // Relations
  userId      String
  user        User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([userId])
}

Step 2: Add Relation to User

Update the User model to include the relation:

model User {
  // ... existing fields

  projects Project[]  // Add this line
}

Step 3: Run Migration

# Development - quick sync (no migration files)
npx prisma db push

# Or with migration files (recommended for team projects)
npx prisma migrate dev --name add_project_model

Step 4: Generate Client

The Prisma client is regenerated automatically, but you can run manually:

npx prisma generate

Step 5: Use in Your Code

import { db } from "@/lib/db";

// Create a project
const project = await db.project.create({
  data: {
    name: "My Project",
    description: "A great project",
    userId: session.user.id,
  },
});

// Get user's projects
const userProjects = await db.project.findMany({
  where: { userId: session.user.id },
  orderBy: { createdAt: "desc" },
});

Common Patterns

Soft Delete

Instead of permanently deleting records:

model Post {
  id        String    @id @default(cuid())
  title     String
  content   String

  deletedAt DateTime?  // Soft delete marker

  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
}

// Soft delete
await db.post.update({
  where: { id: postId },
  data: { deletedAt: new Date() },
});

// Query excluding soft-deleted
const activePosts = await db.post.findMany({
  where: { deletedAt: null },
});

Slug Fields

For URL-friendly identifiers:

model Article {
  id        String   @id @default(cuid())
  title     String
  slug      String   @unique  // URL-friendly identifier
  content   String

  createdAt DateTime @default(now())
}

// Find by slug
const article = await db.article.findUnique({
  where: { slug: "my-article-title" },
});

Many-to-Many Relations

model Post {
  id       String   @id @default(cuid())
  title    String
  tags     Tag[]    // Many-to-many
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[] // Many-to-many
}

// Create post with tags
const post = await db.post.create({
  data: {
    title: "My Post",
    tags: {
      connectOrCreate: [
        { where: { name: "javascript" }, create: { name: "javascript" } },
        { where: { name: "prisma" }, create: { name: "prisma" } },
      ],
    },
  },
  include: { tags: true },
});

Prisma Studio

Prisma includes a visual database editor:

npx prisma studio

This opens a web interface at http://localhost:5555 where you can:

  • View all tables and records
  • Edit data directly
  • Add new records
  • Debug relationships

Database Commands Reference

# Push schema changes (development)
npx prisma db push

# Create migration (production-ready)
npx prisma migrate dev --name your_migration_name

# Apply migrations (production)
npx prisma migrate deploy

# Reset database (warning: deletes all data)
npx prisma migrate reset

# Generate Prisma Client
npx prisma generate

# Open Prisma Studio
npx prisma studio

# Format schema file
npx prisma format

# Validate schema
npx prisma validate

# Pull schema from existing database
npx prisma db pull

Production Best Practices

1. Use Migrations

# Always use migrations for production deployments
npx prisma migrate deploy

Why?

  • Creates version-controlled migration files
  • Tracks schema history in Git
  • Safe to run multiple times (idempotent)

2. Connection Pooling

For serverless deployments (Vercel, Netlify), use connection pooling:

Supabase:

# Use pooler connection string (port 6543)
DATABASE_URL=postgresql://postgres.xxx:password@aws-0-us-east-1.pooler.supabase.com:6543/postgres?pgbouncer=true

Neon:

# Neon provides serverless driver
DATABASE_URL=postgres://user:password@ep-xxx.region.aws.neon.tech/dbname?sslmode=require

3. SSL in Production

Ensure SSL is enabled:

DATABASE_URL=postgresql://user:password@host:5432/db?sslmode=require

4. Backup Strategy

  • Enable automated backups on your database provider
  • Consider periodic exports for critical data
  • Test restore procedures

Troubleshooting

"Cannot find module '@prisma/client'"

# Regenerate Prisma Client
npx prisma generate

"The table does not exist"

# Sync schema with database
npx prisma db push

# Or run migrations
npx prisma migrate dev

"Invalid connection string"

Check these:

  1. URL format: postgresql://user:password@host:port/database
  2. Special characters in password are URL-encoded
  3. Database is accessible (not paused on free tier)

"Too many connections"

Solutions:

  1. Use connection pooling (see Production Best Practices)
  2. Reduce connection pool size in Prisma
  3. Use a serverless-optimized database (Neon, Supabase)

Next Steps

That's it! You now know how to work with the database in ShipSecure. Start adding your own models to build your SaaS features!