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 activeUsers = await prisma.user.findMany({
  where: { active: true },
  orderBy: { createdAt: 'desc' },
  take: 20,
});

// Create a record
const newUser = await prisma.user.create({
  data: { name, email },
});

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

Patterns

Repository Pattern

Encapsulating data access in repository files.

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

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

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

export async function findUsersByProgram(programId: string) {
  return prisma.user.findMany({
    where: { programs: { some: { id: programId } } },
  });
}

export async function createUser(data: { name: string; email: string }) {
  return prisma.user.create({ data });
}

// src/features/users/services.ts
// Services call repository, never Prisma directly
import * as userRepo from './repository';

export async function getUserProfile(userId: string) {
  const user = await userRepo.findUserById(userId);
  if (!user) throw new NotFoundError('User not found');
  return user;
}

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

Relations & Includes

Loading related data efficiently.

// Loading related data with includes
const userWithProjects = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    projects: true,           // All projects
    programs: {
      where: { active: true }, // Only active programs
      include: {
        organization: true,   // Nested include
      },
    },
  },
});

// Select specific fields
const userNames = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: 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, programId },
  });

  // Create default settings
  await tx.projectSettings.create({
    data: { projectId: project.id, ...defaults },
  });

  // Add creator as admin
  await tx.projectMember.create({
    data: {
      projectId: project.id,
      userId: creatorId,
      role: 'ADMIN',
    },
  });

  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' },
});

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}
`;

// 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 } });
}

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

export async function getUser(id: string) {
  return userRepo.findUserById(id);
}

N+1 queries from missing includes
Missing indexes on frequently queried fields

Feature Structure

Feature modules

Validation

Input validation

Scope Model

Scope isolation

External Documentation

Prisma Documentation|Neon Serverless

// 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 activeUsers = await prisma.user.findMany({ where: { active: true }, orderBy: { createdAt: 'desc' }, take: 20, }); // Create a record const newUser = await prisma.user.create({ data: { name, email }, }); // Update a record const updated = await prisma.user.update({ where: { id: userId }, data: { name: newName }, });

// src/features/users/repository.ts // ONLY repository files import Prisma client import { prisma } from '@/lib/db'; export async function findUserById(id: string) { return prisma.user.findUnique({ where: { id }, }); } export async function findUsersByProgram(programId: string) { return prisma.user.findMany({ where: { programs: { some: { id: programId } } }, }); } export async function createUser(data: { name: string; email: string }) { return prisma.user.create({ data }); } // src/features/users/services.ts // Services call repository, never Prisma directly import * as userRepo from './repository'; export async function getUserProfile(userId: string) { const user = await userRepo.findUserById(userId); if (!user) throw new NotFoundError('User not found'); return user; }

// Loading related data with includes const userWithProjects = await prisma.user.findUnique({ where: { id: userId }, include: { projects: true, // All projects programs: { where: { active: true }, // Only active programs include: { organization: true, // Nested include }, }, }, }); // Select specific fields const userNames = await prisma.user.findMany({ select: { id: true, name: true, email: true, }, });

// Atomic operations with transactions const result = await prisma.$transaction(async (tx) => { // Create the project const project = await tx.project.create({ data: { name, programId }, }); // Create default settings await tx.projectSettings.create({ data: { projectId: project.id, ...defaults }, }); // Add creator as admin await tx.projectMember.create({ data: { projectId: project.id, userId: creatorId, role: 'ADMIN', }, }); return project; }); // If any operation fails, all are rolled back

// 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' }, });

Prisma ORM

Overview

Key Features

Quick Start

Basic Queries

Patterns

Repository Pattern

Relations & Includes

Transactions

Pagination

Watch Out

Using $queryRaw or raw SQL (injection risk)

Importing Prisma outside repository files

Related

External Documentation

Prisma ORM

Overview

Key Features

Quick Start

Basic Queries

Patterns

Repository Pattern

Relations & Includes

Transactions

Pagination

Watch Out

Using $queryRaw or raw SQL (injection risk)

Importing Prisma outside repository files

Related

External Documentation