[ [0x10] Features ] Database Prisma

Database Prisma

> Type-safe database access with Prisma ORM, including models, queries, migrations, and best practices for PostgreSQL.

Overview

[ [0x10] STATUS ]

Fabrk uses Prisma ORM with PostgreSQL for type-safe database operations. The system includes pre-built models for users, organizations, payments, and more. It provides type-safe queries with full TypeScript support, migration management for schema changes, seeding scripts for development data, and connection pooling for production.

Setup

[ [0x01] SET DATABASE URL ]

DESC: Add your PostgreSQL connection string to .env.local

1$DATABASE_URL="postgresql://user:password@localhost:5432/fabrk?schema=public"
2
3$# For production with connection pooling (e.g., Supabase):
4$DATABASE_URL="postgresql://user:password@host:6543/postgres?pgbouncer=true"
5$DIRECT_URL="postgresql://user:password@host:5432/postgres"

[ [0x02] INITIALIZE DATABASE ]

DESC: Push the schema to your database

1$# Push schema changes (development)
2$npm run db:push
3
4$# Generate Prisma Client
5$npx prisma generate
6
7$# Seed with test data
8$npm run db:seed
9
10$# Reset and reseed
11$npm run db:reset

[ [0x03] PRISMA STUDIO ]

DESC: Browse and edit data with the visual GUI

1$npm run db:studio
2$# Opens at http://localhost:5555

Usage

[ [0xF0] DATABASE CLIENT ]

Use the singleton client from src/lib/db/index.ts

1import { prisma } from "@/lib/db";
2
3// Find user by email
4const user = await prisma.user.findUnique({
5  where: { email: "user@example.com" },
6  include: { organizations: true },
7});
8
9// Create with relations
10const newUser = await prisma.user.create({
11  data: {
12    email: "new@example.com",
13    name: "New User",
14    organizations: {
15      create: {
16        organization: {
17          create: {
18            name: "My Team",
19            slug: "my-team",
20          },
21        },
22        role: "OWNER",
23      },
24    },
25  },
26});

[ [0xA9] API ROUTE QUERIES ]

Use Prisma in API routes

1// src/app/api/v1/users/route.ts
2import { NextResponse } from "next/server";
3import { prisma } from "@/lib/db";
4import { auth } from "@/lib/auth";
5
6export async function GET() {
7  const session = await auth();
8  if (!session?.user) {
9    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
10  }
11
12  const user = await prisma.user.findUnique({
13    where: { id: session.user.id },
14    select: {
15      id: true,
16      name: true,
17      email: true,
18      image: true,
19      createdAt: true,
20    },
21  });
22
23  return NextResponse.json(user);
24}

[ [0xAB] TRANSACTIONS ]

Use transactions for atomic operations

1// Transfer ownership atomically
2const result = await prisma.$transaction(async (tx) => {
3  // Remove current owner
4  await tx.organizationMember.update({
5    where: { id: currentOwnerId },
6    data: { role: "MEMBER" },
7  });
8
9  // Set new owner
10  const newOwner = await tx.organizationMember.update({
11    where: { id: newOwnerId },
12    data: { role: "OWNER" },
13  });
14
15  // Log the change
16  await tx.auditLog.create({
17    data: {
18      action: "OWNERSHIP TRANSFERRED",
19      organizationId: orgId,
20      userId: session.user.id,
21    },
22  });
23
24  return newOwner;
25});

[ [0x9A] PAGINATION ]

Implement cursor-based pagination

1const pageSize = 20;
2
3const users = await prisma.user.findMany({
4  take: pageSize + 1, // Fetch one extra to check for next page
5  cursor: cursor ? { id: cursor } : undefined,
6  skip: cursor ? 1 : 0, // Skip the cursor itself
7  orderBy: { createdAt: "desc" },
8});
9
10const hasNextPage = users.length > pageSize;
11const data = hasNextPage ? users.slice(0, -1) : users;
12const nextCursor = hasNextPage ? data[data.length - 1].id : null;
13
14return { data, nextCursor, hasNextPage };

Security

[ [0x03] DANGER ]

PROTECT YOUR DATABASE CREDENTIALS

CRITICAL: The DATABASE_URL contains your database password and grants full access to your data. Leaking it allows attackers to read, modify, or delete all data.

Never commit DATABASE_URL to git. Always use .env.local (in .gitignore) for local development.
Server-side only: DATABASE_URL must NEVER be exposed to the browser. Only use in server components, API routes, or build-time scripts.
Secure storage: Use Vercel Environment Variables (encrypted), AWS RDS IAM auth, or secret managers in production.
Connection pooling: Use connection pooling (Prisma Data Proxy, PgBouncer) to prevent connection limit issues and improve security.
Rotate if exposed: Change database password immediately if DATABASE_URL is compromised. Update in hosting provider and restart all services.

Tip: Use separate databases for development, staging, and production. Never connect to production from local dev environment.

Core Models

[ [0xF5] CORE MODELS ]

Fabrk includes these pre-built models in prisma/schema.prisma:

1// Authentication
2model User {
3  id            String    @id @default(cuid())
4  name          String?
5  email         String    @unique
6  emailVerified DateTime?
7  password      String?
8  image         String?
9  role          Role      @default(USER)
10  sessionVersion Int      @default(0)
11  createdAt     DateTime  @default(now())
12  updatedAt     DateTime  @updatedAt
13
14  accounts      Account[]
15  sessions      Session[]
16  payments      Payment[]
17  organizations OrganizationMember[]
18}
19
20// Payments
21model Payment {
22  id              String   @id @default(cuid())
23  userId          String
24  stripeSessionId String   @unique
25  stripePriceId   String
26  amount          Int
27  currency        String
28  status          String
29  createdAt       DateTime @default(now())
30
31  user            User     @relation(fields: [userId], references: [id])
32}
33
34// Multi-tenancy
35model Organization {
36  id        String   @id @default(cuid())
37  name      String
38  slug      String   @unique
39  createdAt DateTime @default(now())
40  updatedAt DateTime @updatedAt
41
42  members   OrganizationMember[]
43  invites   OrganizationInvite[]
44}
45
46model OrganizationMember {
47  id             String       @id @default(cuid())
48  userId         String
49  organizationId String
50  role           OrgRole      @default(MEMBER)
51  joinedAt       DateTime     @default(now())
52
53  user           User         @relation(fields: [userId], references: [id])
54  organization   Organization @relation(fields: [organizationId], references: [id])
55
56  @@unique([userId, organizationId])
57}

Migrations

[ [0x9B] MIGRATIONS ]

For production, use migrations instead of db:push:

1$# Create a new migration
2$npm run db:migrate -- --name add_user_preferences
3
4$# Apply migrations in production
5$npx prisma migrate deploy
6
7$# Check migration status
8$npx prisma migrate status

Common Use Cases

[ [0x55] USER UPDATES ]

Update user data with validation. Use select to return only needed fields and avoid exposing sensitive data like passwords.

[ [0x12] SOFT DELETES ]

Add deletedAt DateTime? field to models and filter with where: { deletedAt: null } for recoverable deletes.

[ [0xEA] FULL TEXT SEARCH ]

Use Prisma's full-text search with PostgreSQL: where: { name: { search: "query" } } for searching user names or content.

[ [0x2F] AGGREGATIONS ]

Calculate totals with prisma.payment.aggregate({ _sum: { amount: true } }) for dashboards and reports.

Best Practices

[ [0xC4] BEST PRACTICES ]

├─ Always use the singleton client from @/lib/db
├─ Use select to fetch only needed fields
├─ Add indexes for frequently queried fields
├─ Use transactions for multi-step operations
├─ Never expose sensitive fields like password in API responses
├─ Use migrations in production, db:push only in development
└─ Set up connection pooling for production (PgBouncer)