Database

Working with PostgreSQL and Prisma ORM in this boilerplate

This boilerplate uses Prisma as the ORM (Object-Relational Mapping) layer for PostgreSQL, providing type-safe database access with excellent developer experience.

Overview

The database setup includes:

PostgreSQL - Production-ready relational database
Prisma ORM - Type-safe database client
Pre-configured schemas - Users, sessions, payments, subscriptions, and more
Migrations - Version control for your database schema
UUID primary keys - Database-generated UUIDs for new tables

Database schema

The Prisma schema is located in prisma/schema.prisma. Here's an overview of the main models:

User and authentication tables

model User {
  id            String         @id
  name          String
  email         String         @unique
  emailVerified Boolean
  image         String?
  createdAt     DateTime
  updatedAt     DateTime
  // Relations
  sessions      Session[]
  accounts      Account[]
  subscriptions Subscription[]
  payments      Payment[]
}

model Session {
  id        String   @id
  token     String   @unique
  expiresAt DateTime
  userId    String
  user      User     @relation(fields: [userId], references: [id])
}

model Account {
  id           String    @id
  providerId   String
  userId       String
  accessToken  String?
  // ... other OAuth fields
  user         User      @relation(fields: [userId], references: [id])
}

Payment and subscription tables

model Subscription {
  id                      String    @id @default(dbgenerated("gen_random_uuid()"))
  customerEmail           String
  userId                  String?
  plan                    String    // pro, elite, etc.
  status                  String    // active, canceled, etc.
  currentPeriodStart      DateTime
  currentPeriodEnd        DateTime
  processorSubscriptionId String    @unique
  // ... other fields
  payments                Payment[]
}

model Payment {
  id                 String        @id @default(dbgenerated("gen_random_uuid()"))
  customerEmail      String
  userId             String?
  amount             Int           // Amount in cents
  currency           String
  status             String        // succeeded, failed, etc.
  subscriptionId     String?
  subscription       Subscription? @relation(fields: [subscriptionId], references: [id])
}

User feedback tables

model BugReport {
  id        String   @id @default(dbgenerated("gen_random_uuid()"))
  type      String
  details   String
  userId    String
  url       String
  createdAt DateTime @default(now())
}

model FeatureRequest {
  id        String   @id @default(dbgenerated("gen_random_uuid()"))
  type      String
  details   String
  userId    String
  url       String
  createdAt DateTime @default(now())
}

For a detailed explanation of the payment architecture, see the payments integration guide.

Working with Prisma

Prisma client

The Prisma client is initialized in lib/prisma.ts:

lib/prisma.ts

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

export default prisma

Querying data

Find a single record:

const user = await prisma.user.findUnique({
  where: { email: 'user@example.com' },
})

Find many records:

const activeSubscriptions = await prisma.subscription.findMany({
  where: {
    status: 'active',
    currentPeriodEnd: { gt: new Date() },
  },
})

Create a record:

const newUser = await prisma.user.create({
  data: {
    name: 'John Doe',
    email: 'john@example.com',
    emailVerified: false,
    createdAt: new Date(),
    updatedAt: new Date(),
  },
})

Update a record:

const updated = await prisma.user.update({
  where: { id: userId },
  data: { name: 'Jane Doe' },
})

Delete a record:

await prisma.user.delete({
  where: { id: userId },
})

Relations and joins

Include related data:

const userWithSessions = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    sessions: true,
    subscriptions: true,
  },
})

Select specific fields:

const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
})

Aggregations

// Count records
const userCount = await prisma.user.count()

// Sum values
const totalRevenue = await prisma.payment.aggregate({
  where: { status: 'succeeded' },
  _sum: { amount: true },
})

// Average, min, max
const stats = await prisma.payment.aggregate({
  _avg: { amount: true },
  _min: { amount: true },
  _max: { amount: true },
})

Migrations

Prisma migrations allow you to version control your database schema changes.

Create a new migration

After modifying your schema, create a migration:

pnpm prisma migrate dev --name add_new_field

This:

Generates SQL migration files
Applies the migration to your database
Regenerates the Prisma client

Apply migrations in production

pnpm prisma migrate deploy

Always test migrations in a staging environment before applying to production.

Reset the database

To reset your database (⚠️ deletes all data):

pnpm prisma migrate reset

Schema conventions

This boilerplate follows these naming conventions:

Tables

Database table names: kebab-case (e.g., user-data, bug-report)
Prisma model names: PascalCase (e.g., UserData, BugReport)

model BugReport {
  // ...
  @@map("bug_report")
}

Fields

Field names: camelCase (e.g., firstName, createdAt)
Primary keys: Use id for consistency
Timestamps: Always include createdAt and updatedAt

IDs

Auth-related tables: Use better-auth generated IDs
New tables: Use database-generated UUIDs

model MyNewTable {
  id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
}

Best practices

await prisma.$transaction([
  prisma.payment.create({
    /* ... */
  }),
  prisma.subscription.update({
    /* ... */
  }),
])

Handle errors properly

try {
  const user = await prisma.user.create({ data: userData })
} catch (error) {
  if (error.code === 'P2002') {
    // Unique constraint violation
    throw new Error('Email already exists')
  }
  throw error
}

Use proper indexes

Add indexes for frequently queried fields:

model Payment {
  // ...
  @@index([userId])
  @@index([customerEmail])
  @@index([status])
}

Optimize queries

// Bad: N+1 query problem
const users = await prisma.user.findMany()
for (const user of users) {
  const subscriptions = await prisma.subscription.findMany({
    where: { userId: user.id },
  })
}

// Good: Use include
const users = await prisma.user.findMany({
  include: { subscriptions: true },
})

Adding new tables

To add a new table to your schema:

Edit the schema in prisma/schema.prisma:

model Product {
  id          String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name        String
  description String?
  price       Decimal  @db.Decimal(10, 2)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@map("product")
}

Create a migration:

pnpm prisma migrate dev --name add_product_table

Use your new model:

const product = await prisma.product.create({
  data: {
    name: 'My Product',
    price: 29.99,
  },
})

Prisma Studio

Prisma Studio is a visual database browser. Launch it with:

pnpm prisma studio

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

View all your tables
Browse and edit data
Run queries
Test relations

Prisma Studio is great for debugging and testing during development.

Environment variables

Database configuration uses environment variables:

.env

# PostgreSQL connection string
DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

# Optional: Connection pooling (for serverless)
DATABASE_URL_UNPOOLED="postgresql://..."
PRISMA_ACCELERATE_URL="prisma://..."

For serverless deployments, consider using Prisma Accelerate for connection pooling.

Common tasks

Seed the database

Create a seed script in prisma/seed.ts:

prisma/seed.ts

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  await prisma.user.create({
    data: {
      name: 'Test User',
      email: 'test@example.com',
      emailVerified: true,
      createdAt: new Date(),
      updatedAt: new Date(),
    },
  })
}

main()
  .catch(e => console.error(e))
  .finally(() => prisma.$disconnect())

Run it with:

pnpm prisma db seed

Generate Prisma client

After pulling changes that modify the schema:

pnpm prisma generate

Inspect database

View your database schema:

pnpm prisma db pull

Reference

For detailed Prisma features and best practices, always refer to the official Prisma documentation.

Authentication

Learn how authentication works with better-auth in this boilerplate

Styling

Learn about Tailwind CSS v4 and theming in this boilerplate