CentercodePorygon
DittoPorygonAPI

Prisma ORM

Type-safe database access with Prisma

SafeFastReliable

Overview

All database access goes through Prisma ORM. Only repository.ts files import the Prisma client. This maintains a clear data access boundary.

What it is

Prisma provides type-safe database queries, relations, and migrations for PostgreSQL via Neon serverless.

Why we use it

Type safety, automatic query optimization, connection pooling, and protection against SQL injection.

When to use

Any data persistence. Create repository functions for each entity, call them from services.

Key Features

  • Fully typed queries with autocomplete
  • Easy relation handling with includes
  • Transaction support for atomic operations
  • Schema migrations with version control

Quick Start

Basic Queries

Common Prisma query patterns.

// Basic Prisma queries
import { prisma } from '@/lib/db';

// Find one record
const user = await prisma.user.findUnique({
  where: { id: userId },
});

// Find many with filter
const recentUsers = await prisma.user.findMany({
  where: { emailVerified: true },
  orderBy: { createdAt: 'desc' },
  take: 20,
});

// Create a record (Note: User creation is handled by Better Auth)
const project = await prisma.project.create({
  data: { name, slug, programId, settings: {} },
});

// Update a record
const updated = await prisma.project.update({
  where: { id: projectId },
  data: { name: newName },
});

Patterns

Repository Pattern

Encapsulating data access in repository files.

// src/features/projects/repository.ts
// ONLY repository files import Prisma client

import { prisma } from '@/lib/db';
import type { Project } from '@/generated/prisma';

export async function getProjectById(projectId: string): Promise<Project | null> {
  return prisma.project.findUnique({
    where: { id: projectId },
  });
}

export async function getProjectWithProgram(projectId: string) {
  return prisma.project.findUnique({
    where: { id: projectId },
    include: {
      program: {
        select: { id: true, slug: true, name: true },
      },
    },
  });
}

// src/features/projects/services.ts
// Services call repository, never Prisma directly
import * as projectsRepo from './repository';
import { NotFoundError } from '@/lib/errors';

export async function getProject(projectId: string) {
  const project = await projectsRepo.getProjectById(projectId);
  if (!project) throw new NotFoundError('Project not found');
  return project;
}

Critical: Only repository.ts files should import Prisma client.

Relations & Includes

Loading related data efficiently.

// Loading related data with includes
const projectWithDetails = await prisma.project.findUnique({
  where: { id: projectId },
  include: {
    program: true,              // Parent program
    memberships: {
      where: { status: 'ACTIVE' },
      include: {
        user: true,             // Nested include
      },
    },
  },
});

// Select specific fields (more performant)
const projectNames = await prisma.project.findMany({
  where: { programId },
  select: {
    id: true,
    name: true,
    slug: true,
  },
});

Transactions

Atomic operations across multiple tables.

// Atomic operations with transactions
const result = await prisma.$transaction(async (tx) => {
  // Create the project
  const project = await tx.project.create({
    data: { name, slug, programId, settings: {} },
  });

  // Create related context
  await tx.context.create({
    data: {
      entityType: 'PROJECT',
      entityId: project.id,
      name: `${name} Context`,
      content: contextContent,
      createdById: userId,
    },
  });

  // Add creator as owner
  await tx.membership.create({
    data: {
      projectId: project.id,
      userId,
      role: 'OWNER',
      status: 'ACTIVE',
    },
  });

  return project;
});

// If any operation fails, all are rolled back

Pagination

Cursor and offset pagination patterns.

// Offset pagination
const page = 1;
const pageSize = 20;

const users = await prisma.user.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: 'desc' },
});

// Cursor pagination (better for large datasets)
const users = await prisma.user.findMany({
  take: 20,
  cursor: lastUserId ? { id: lastUserId } : undefined,
  skip: lastUserId ? 1 : 0, // Skip the cursor
  orderBy: { createdAt: 'desc' },
});

Row-Level Security (RLS)

All queries are filtered by PostgreSQL Row-Level Security policies. This ensures tenant isolation at the database level.

API Routes

RLS context is set automatically by withApiLogging().

// API routes: RLS context set AUTOMATICALLY by withApiLogging()
// No wrapper needed - just use prisma normally

import { prisma } from '@/lib/db';

// Queries are automatically filtered to current user's access scope
const projects = await prisma.project.findMany(); // Only returns allowed projects

Inngest Jobs & Seeds

Background jobs and seeds need explicit bypass.

// Background jobs & seeds: MUST wrap in withRLSBypass()
import { withRLSBypass } from '@/lib/jobs';
import { prisma } from '@/lib/db';

await withRLSBypass('inngest:cleanup-expired', async () => {
  await prisma.verification.deleteMany({
    where: { expiresAt: { lt: new Date() } },
  });
});

Debugging tip: If queries return empty unexpectedly, RLS is working correctly. Check if the current user has an active membership for that scope.

Watch Out

Using $queryRaw or raw SQL (injection risk)

Don't

// Using raw SQL - injection risk!
const result = await prisma.$queryRaw`
  SELECT * FROM users WHERE id = ${userId}
`;

Do

// Use Prisma client methods
const result = await prisma.user.findUnique({
  where: { id: userId },
});
// Prisma handles parameterization safely

Importing Prisma outside repository files

Don't

// services.ts - importing Prisma directly
import { prisma } from '@/lib/db';

export async function getUser(id: string) {
  return prisma.user.findUnique({ where: { id } });
}

Do

// services.ts - using repository
import * as projectsRepo from './repository';

export async function getProject(id: string) {
  return projectsRepo.getProjectById(id);
}
  • N+1 queries from missing includes
  • Missing indexes on frequently queried fields

Related

Feature Structure

Feature modules

Validation

Input validation

Scope Model

Scope isolation

External Documentation

Prisma Documentation|Neon Serverless