Nodejs Prisma

Prisma ORM

Purpose

Design database schemas, write performant queries, manage migrations, implement middleware, and optimize Prisma Client for production.

Agent Protocol

Trigger

User request includes: prisma, prisma schema, prisma migrate, prisma client, prisma relation, prisma middleware, prisma query, prisma performance, prisma seed, prisma studio.

Input Context

• Database (PostgreSQL, MySQL, SQLite, SQL Server, MongoDB)
• Prisma version (5.x, 6.x)
• Schema complexity (relations, enums, composite keys)
• Deployment (Node.js, serverless, edge)

Output Artifact

Schema definition, query examples, migration setup, middleware patterns, performance optimizations.

Response Format

Produce artifact directly. No preamble, no postamble, no explanations.

Completion Criteria

• Schema defined with proper relations, indexes, constraints
• Migrations generated and applied
• Queries use select, include, and where efficiently
• Middleware (interactive transactions, extensions) configured
• Connection pooling for serverless or production

Max Response Length

4096 tokens

Architecture Decision Trees

Prisma vs Drizzle ORM vs TypeORM vs Knex

Decision: Full type safety + auto-complete → Prisma. Maximum performance + SQL control → Drizzle. Active Record familiarity → TypeORM.

Schema Design: Prisma vs Raw SQL

Decision: Prisma-first project → use schema.prisma as source of truth. Existing DB → introspect with prisma db pull.

Workflow

Step 1: Schema Definition

// prisma/schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["extendedWhereUnique"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum UserRole {
  ADMIN
  USER
  MODERATOR
}

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  role      UserRole @default(USER)
  active    Boolean  @default(true)
  profile   Profile?
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email, active])
  @@map("users")
}

model Profile {
  id      String @id @default(uuid())
  bio     String?
  avatar  String?
  userId  String @unique
  user    User   @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

model Post {
  id        String   @id @default(uuid())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  String
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags      Tag[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId, published])
  @@map("posts")
}

model Tag {
  id    String @id @default(uuid())
  name  String @unique
  posts Post[]

  @@map("tags")
}

model PostTag {
  postId String
  tagId  String
  post   Post @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag    Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([postId, tagId])
  @@map("post_tags")
}

Step 2: Query Patterns

// src/repositories/user.repository.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// SELECT with specific fields
export async function findUserById(id: string) {
  return prisma.user.findUnique({
    where: { id },
    select: { id: true, name: true, email: true, role: true },
  });
}

// Include relations (N+1 safe — Prisma batches)
export async function findUserWithProfile(id: string) {
  return prisma.user.findUnique({
    where: { id },
    include: {
      profile: true,
      posts: {
        where: { published: true },
        select: { id: true, title: true, createdAt: true },
        orderBy: { createdAt: 'desc' },
        take: 10,
      },
    },
  });
}

// Paginated list
export async function findUsers(page: number, limit: number) {
  const [users, total] = await Promise.all([
    prisma.user.findMany({
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { createdAt: 'desc' },
      select: { id: true, name: true, email: true, role: true },
    }),
    prisma.user.count(),
  ]);
  return { data: users, total, page, totalPages: Math.ceil(total / limit) };
}

// Create with nested write
export async function createUserWithProfile(data: CreateUserDto) {
  return prisma.user.create({
    data: {
      email: data.email,
      name: data.name,
      profile: {
        create: { bio: data.bio },
      },
    },
    include: { profile: true },
  });
}

// Batch update
export async function deactivateInactiveUsers(days: number) {
  const cutoff = new Date(Date.now() - days * 86400000);
  return prisma.user.updateMany({
    where: { lastLoginAt: { lt: cutoff }, active: true },
    data: { active: false },
  });
}

// Delete cascade (defined in schema)
export async function deleteUser(id: string) {
  return prisma.user.delete({ where: { id } });
}

Step 3: Prisma Client Configuration

// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'info', 'warn', 'error']
    : ['error'],
  errorFormat: 'minimal',
});

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

Step 4: Migrations

# Create migration from schema changes
npx prisma migrate dev --name add_user_profile

# Apply to production
npx prisma migrate deploy

# Reset (dev only — drops data)
npx prisma migrate reset

# Generate client after schema change
npx prisma generate

# View migration status
npx prisma migrate status

Step 5: Middleware / Extensions (Prisma 5+)

// src/lib/prisma-extension.ts
import { PrismaClient } from '@prisma/client';

export const xprisma = new PrismaClient()
  .$extends({
    query: {
      user: {
        async create({ args, query }) {
          // Auto-generate slug or hash password before create
          return query(args);
        },
        async findUnique({ args, query }) {
          // Soft-delete filter
          args.where = { ...args.where, deletedAt: null };
          return query(args);
        },
      },
    },
    result: {
      user: {
        fullName: {
          needs: { firstName: true, lastName: true },
          compute(user) {
            return `${user.firstName} ${user.lastName}`;
          },
        },
      },
    },
    model: {
      user: {
        async findByEmail(email: string) {
          return prisma.user.findUnique({ where: { email } });
        },
      },
    },
  });

Step 6: Interactive Transactions

// Transfer funds with transaction
export async function transferFunds(fromId: string, toId: string, amount: number) {
  return prisma.$transaction(async (tx) => {
    const fromAccount = await tx.account.update({
      where: { id: fromId },
      data: { balance: { decrement: amount } },
    });

    if (fromAccount.balance < 0) {
      throw new Error('Insufficient funds');
    }

    await tx.account.update({
      where: { id: toId },
      data: { balance: { increment: amount } },
    });

    await tx.transaction.create({
      data: { fromId, toId, amount, type: 'TRANSFER' },
    });
  });
}

Step 7: Seed Script

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  const user = await prisma.user.create({
    data: {
      email: 'admin@test.com',
      name: 'Admin',
      role: 'ADMIN',
      profile: { create: { bio: 'System admin' } },
      posts: {
        create: [
          { title: 'First Post', content: 'Hello world', published: true },
          { title: 'Draft', content: 'Not yet published' },
        ],
      },
    },
  });
  console.log('Seeded user:', user.id);
}

main()
  .catch(e => { console.error(e); process.exit(1); })
  .finally(() => prisma.$disconnect());

Production Considerations

Connection Pooling (Serverless)

// Connection pool for serverless (Vercel, Lambda)
import { PrismaClient } from '@prisma/client';
import { Pool } from '@neondatabase/serverless';
import { PrismaNeon } from '@prisma/adapter-neon';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const adapter = new PrismaNeon(pool);
const prisma = new PrismaClient({ adapter });

Query Performance

• Use select over include when only specific fields needed
• Batch relation loading: Prisma already batches via DATABASE_URL connection
• Use raw queries with $queryRaw for complex aggregations
• Add @@index on frequently filtered/sorted columns
• Use @relation with onDelete: Cascade for referential integrity at DB level
• Limit relation depth — each include adds a JOIN

Error Handling

import { PrismaClientKnownRequestError } from '@prisma/client/runtime/library';

try {
  await prisma.user.create({ data: { email: 'dupe@test.com' } });
} catch (error) {
  if (error instanceof PrismaClientKnownRequestError) {
    if (error.code === 'P2002') { // Unique constraint violation
      throw new ConflictError('Email already exists');
    }
    if (error.code === 'P2025') { // Record not found
      throw new NotFoundError('User not found');
    }
  }
  throw error;
}

Anti-Patterns

Security Considerations

• Raw queries ($queryRawUnsafe) risk SQL injection — use $queryRaw with parameterized templates
• Prisma validates input types, but always validate business rules in application layer
• Connection string in .env — never committed to repo
• Audit logging via Prisma middleware for sensitive models
• Field-level @map for column obfuscation not needed — use DB-level encryption
• Use select to avoid exposing sensitive fields (password hash, etc.)

Testing Strategies

import { PrismaClient } from '@prisma/client';
import { vi, describe, it, expect, beforeAll, afterAll } from 'vitest';

const prisma = new PrismaClient();

describe('User Repository', () => {
  beforeAll(async () => {
    await prisma.$executeRawUnsafe('TRUNCATE TABLE users CASCADE');
  });

  afterAll(async () => {
    await prisma.$disconnect();
  });

  it('should create and find user', async () => {
    const user = await createUser({ email: 'test@test.com', name: 'Test' });
    const found = await findUserById(user.id);
    expect(found?.email).toBe('test@test.com');
  });
});

Use separate test database with test user. Use prisma migrate deploy in CI. Use @prisma/nextjs-monorepo-workaround-plugin for monorepos.

Rules

• Schema is the source of truth — prisma migrate dev after every schema change.
• prisma generate after every pull/sync — always regenerate client.
• select over include for production queries — minimize data transfer.
• Soft deletes via deletedAt + middleware filter — never hard delete user data.
• $transaction for atomic multi-table operations.
• $extends for cross-cutting concerns (soft delete, audit, computed fields).
• No prisma.$disconnect() in serverless handlers — let adapter handle pooling.
• Index all foreign keys and frequently queried columns.

References

• references/prisma-advanced.md — Prisma Advanced Patterns
• references/prisma-deployment.md — Deployment and Performance
• references/prisma-middleware.md — Middleware and Extensions
• references/prisma-relations.md — Relation Patterns
• references/query-optimization.md — Query Optimization
• references/schema-migrations.md — Schema and Migration Patterns

Handoff

Hand off to backend/nodejs/drizzle/SKILL.md for Drizzle ORM patterns or backend/nodejs/patterns/SKILL.md for advanced Node patterns.

Implementation Patterns

Factory Pattern for Module Creation

function createModule<T>(config: ModuleConfig): T { const dependencies = initializeDependencies(config); const module = new Module(dependencies); module.hooks.onInit(); return module as T; }

Builder Pattern for Complex Configuration

class ConfigBuilder { private config: AppConfig = new AppConfig(); withDatabase(url: string): ConfigBuilder { ... } withCache(ttl: number): ConfigBuilder { ... } withLogging(level: string): ConfigBuilder { ... } build(): AppConfig { return this.config; } }

Production Considerations

Deployment Checklist

• Production build with optimizations enabled
• Environment variables configured per environment
• Health check endpoint responds correctly
• Error tracking and monitoring integrated
• Logging level configured (not debug in production)
• Resource limits configured
• Database migrations applied
• Static assets built and served from CDN or cache
• Feature flags toggled appropriately
• Rollback plan documented and tested

Monitoring and Alerting

Rules

• Prefer composition over inheritance
• Favor immutable data structures
• Use dependency injection for testability
• Keep functions pure when possible — no side effects
• Fail fast with clear error messages
• Don't repeat yourself (DRY) — extract shared logic
• Keep it simple (KISS) — avoid unnecessary complexity
• You aren't gonna need it (YAGNI) — build what's required
• Separate concerns — single responsibility per module
• Code to interfaces, not implementations
• Write self-documenting code — clear names over comments
• Prefer standard library over third-party dependencies
• Handle errors explicitly — no silent failures
• Validate inputs at boundaries
• Log at appropriate levels (debug, info, warn, error)