prisma-orm

Prisma ORM - Type-Safe Database Toolkit

Safety Notice

This listing is imported from skills.sh public index metadata. Review upstream SKILL.md and repository scripts before running.

Copy this and send it to your AI assistant to learn

Install skill "prisma-orm" with this command: npx skills add bobmatnyc/claude-mpm-skills/bobmatnyc-claude-mpm-skills-prisma-orm

Prisma ORM - Type-Safe Database Toolkit

Modern database toolkit for TypeScript with schema-first development, auto-generated type-safe client, and powerful migration system.

Quick Reference

Installation

npm install prisma @prisma/client npx prisma init

Basic Workflow

1. Define schema

Edit prisma/schema.prisma

2. Create migration

npx prisma migrate dev --name init

3. Generate client

npx prisma generate

4. Open Studio

npx prisma studio

Core Schema Pattern

// prisma/schema.prisma generator client { provider = "prisma-client-js" }

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

model User { id String @id @default(cuid()) email String @unique name String? posts Post[] createdAt DateTime @default(now()) updatedAt DateTime @updatedAt }

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

@@index([authorId]) }

Type-Safe CRUD

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Create const user = await prisma.user.create({ data: { email: 'alice@example.com', name: 'Alice', posts: { create: { title: 'First Post', content: 'Hello World' } } }, include: { posts: true } });

// Read with filters const users = await prisma.user.findMany({ where: { email: { contains: '@example.com' } }, include: { posts: { where: { published: true } } }, orderBy: { createdAt: 'desc' }, take: 10 });

// Update await prisma.user.update({ where: { id: userId }, data: { name: 'Bob' } });

// Delete await prisma.user.delete({ where: { id: userId } });

Schema Design Patterns

Field Types and Attributes

model Product { id Int @id @default(autoincrement()) sku String @unique name String description String? // Optional field price Decimal @db.Decimal(10, 2) inStock Boolean @default(true) quantity Int @default(0) tags String[] // Array field (PostgreSQL) metadata Json? // JSON field createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

@@index([sku]) @@index([name, inStock]) }

Relations

One-to-Many:

model User { id String @id @default(cuid()) posts Post[] }

model Post { id String @id @default(cuid()) author User @relation(fields: [authorId], references: [id]) authorId String

@@index([authorId]) }

Many-to-Many:

model Post { id String @id @default(cuid()) categories Category[] @relation("PostCategories") }

model Category { id String @id @default(cuid()) name String @unique posts Post[] @relation("PostCategories") }

One-to-One:

model User { id String @id @default(cuid()) profile Profile? }

model Profile { id String @id @default(cuid()) bio String user User @relation(fields: [userId], references: [id]) userId String @unique }

Self-Relations:

model User { id String @id @default(cuid()) following User[] @relation("UserFollows") followers User[] @relation("UserFollows") }

Client Operations

Nested Writes

// Create with nested relations const user = await prisma.user.create({ data: { email: 'bob@example.com', profile: { create: { bio: 'Software Engineer' } }, posts: { create: [ { title: 'Post 1', content: 'Content 1' }, { title: 'Post 2', content: 'Content 2' } ] } } });

// Update with nested operations await prisma.user.update({ where: { id: userId }, data: { posts: { create: { title: 'New Post' }, update: { where: { id: postId }, data: { published: true } }, delete: { id: oldPostId } } } });

Transactions

Sequential (Interactive):

await prisma.$transaction(async (tx) => { const user = await tx.user.create({ data: { email: 'alice@example.com' } });

await tx.post.create({ data: { title: 'Post', authorId: user.id } });

// Rollback if error thrown if (someCondition) { throw new Error('Rollback transaction'); } });

Batch (Parallel):

const [deletedPosts, updatedUser] = await prisma.$transaction([ prisma.post.deleteMany({ where: { published: false } }), prisma.user.update({ where: { id: userId }, data: { name: 'Updated' } }) ]);

Advanced Queries

Aggregations:

const result = await prisma.post.aggregate({ _count: { id: true }, _avg: { views: true }, _sum: { likes: true }, _max: { createdAt: true }, where: { published: true } });

const grouped = await prisma.post.groupBy({ by: ['authorId'], _count: { id: true }, _avg: { views: true }, having: { views: { _avg: { gt: 100 } } } });

Raw SQL:

// Raw query const users = await prisma.$queryRaw<User[]> SELECT * FROM "User" WHERE email LIKE ${%${search}%} ;

// Execute await prisma.$executeRaw UPDATE "Post" SET views = views + 1 WHERE id = ${postId};

Migrations

Development Workflow

Create and apply migration

npx prisma migrate dev --name add_user_role

Reset database (WARNING: deletes all data)

npx prisma migrate reset

View migration status

npx prisma migrate status

Production Deployment

Apply pending migrations

npx prisma migrate deploy

Generate client (in CI/CD)

npx prisma generate

Schema Prototyping

Push schema without migrations (dev only)

npx prisma db push

Pull schema from existing database

npx prisma db pull

Integration Patterns

Next.js App Router

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

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

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

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

Server Component:

// app/users/page.tsx import { prisma } from '@/lib/prisma';

export default async function UsersPage() { const users = await prisma.user.findMany({ include: { posts: { take: 5 } } });

return ( <ul> {users.map(u => ( <li key={u.id}>{u.name} - {u.posts.length} posts</li> ))} </ul> ); }

Server Action:

// app/actions.ts 'use server';

import { prisma } from '@/lib/prisma'; import { revalidatePath } from 'next/cache';

export async function createPost(formData: FormData) { const title = formData.get('title') as string; const authorId = formData.get('authorId') as string;

await prisma.post.create({ data: { title, authorId } });

revalidatePath('/posts'); }

Node.js Middleware

import { PrismaClient } from '@prisma/client'; import express from 'express';

const app = express(); const prisma = new PrismaClient();

app.get('/users/:id', async (req, res) => { const user = await prisma.user.findUnique({ where: { id: req.params.id }, include: { posts: true } });

if (!user) return res.status(404).json({ error: 'Not found' }); res.json(user); });

app.listen(3000);

Performance Optimization

Query Optimization

// ❌ N+1 queries const users = await prisma.user.findMany(); for (const user of users) { const posts = await prisma.post.findMany({ where: { authorId: user.id } }); }

// ✅ Single query with include const users = await prisma.user.findMany({ include: { posts: true } });

// ✅ Select specific fields const users = await prisma.user.findMany({ select: { id: true, email: true, posts: { select: { title: true } } } });

Pagination

// Cursor-based (recommended for large datasets) const posts = await prisma.post.findMany({ take: 10, cursor: lastPostId ? { id: lastPostId } : undefined, skip: lastPostId ? 1 : 0, orderBy: { createdAt: 'desc' } });

// Offset-based (simple but slower) const posts = await prisma.post.findMany({ skip: (page - 1) * pageSize, take: pageSize });

Connection Pooling

// schema.prisma datasource db { provider = "postgresql" url = env("DATABASE_URL")

// Connection pool settings directUrl = env("DIRECT_URL")

// Serverless connection limit relationMode = "prisma" // For PlanetScale, Neon }

.env

DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=20"

Prisma Studio

Launch visual database browser

npx prisma studio

Features:

  • Visual data browser and editor

  • Create, read, update, delete records

  • Filter and search data

  • View relations visually

  • Runs on localhost:5555

Prisma vs Drizzle

Feature Prisma Drizzle

Schema Definition Custom DSL TypeScript code

Type Safety Generated types Inferred types

Migrations Built-in (migrate) drizzle-kit

Query Builder Fluent API SQL-like builders

Relations Automatic Manual joins

Studio Built-in GUI No GUI

Bundle Size ~300kB ~50kB

Raw SQL Supported First-class

Edge Runtime Limited Full support

Learning Curve Moderate Steeper

Best For Full-stack apps, rapid development, teams Edge functions, SQL experts, bundle-sensitive

Choose Prisma when:

  • Team prefers schema-first development

  • Need visual database tools (Studio)

  • Want automatic relation handling

  • Building full-stack monoliths

  • Rapid prototyping and migrations

Choose Drizzle when:

  • Need minimal bundle size (edge functions)

  • Prefer SQL-like syntax

  • Edge runtime deployment (Cloudflare Workers)

  • Want full control over SQL generation

  • Team has strong SQL expertise

Best Practices

  • Singleton Pattern - Reuse PrismaClient instance (especially in dev)

  • Connection Management - Configure pool size for serverless

  • Select Specific Fields - Use select to reduce payload size

  • Use Transactions - For multi-step operations requiring atomicity

  • Index Strategically - Add @@index on frequently queried fields

  • Migration Discipline - Never edit migrations after deployment

  • Schema Versioning - Use descriptive migration names

  • Soft Deletes - Add deletedAt field instead of hard deletes

  • Validate Before Saving - Use Zod schemas before Prisma operations

  • Monitor Queries - Use prisma.$on('query') for logging

Common Pitfalls

❌ Creating multiple PrismaClient instances:

// WRONG - creates connection leak function getUser() { const prisma = new PrismaClient(); // New instance every call return prisma.user.findMany(); }

// CORRECT - singleton pattern const prisma = new PrismaClient(); function getUser() { return prisma.user.findMany(); }

❌ N+1 queries:

// WRONG - multiple queries const users = await prisma.user.findMany(); for (const user of users) { user.posts = await prisma.post.findMany({ where: { authorId: user.id } }); }

// CORRECT - single query with include const users = await prisma.user.findMany({ include: { posts: true } });

❌ Missing transaction for multi-step operations:

// WRONG - not atomic, can leave inconsistent state await prisma.user.delete({ where: { id: userId } }); await prisma.post.deleteMany({ where: { authorId: userId } }); // May fail

// CORRECT - atomic transaction await prisma.$transaction([ prisma.post.deleteMany({ where: { authorId: userId } }), prisma.user.delete({ where: { id: userId } }) ]);

Red Flags

Stop and reconsider if:

  • Creating new PrismaClient in request handlers

  • Not using transactions for multi-step operations

  • Missing indexes on foreign keys or frequently queried fields

  • Using findMany without pagination on large tables

  • Fetching entire objects when only specific fields needed

  • Not handling connection errors in production

  • Using migrate dev in production (use migrate deploy )

Integration with Other Skills

  • typescript-core: Zod validation, type safety patterns

  • nextjs-core: Server Actions, Server Components integration

  • nextjs-v16: App Router data fetching, caching

  • database-migration: Safe schema evolution patterns

Resources

Related Skills

When using Prisma, these skills enhance your workflow:

  • drizzle: Drizzle ORM as lightweight alternative to Prisma

  • typescript: TypeScript best practices for Prisma generated types

  • nextjs: Prisma with Next.js: connection pooling, edge runtime considerations

  • test-driven-development: Testing Prisma models, migrations, and queries

[Full documentation available in these skills if deployed in your bundle]

Source Transparency

This detail page is rendered from real SKILL.md content. Trust labels are metadata-based hints, not a safety guarantee.

Open in GitHubOpen in ClawHub

Related Skills

Related by shared tags or category signals.

General

drizzle-orm

No summary provided by upstream source.

Repository SourceNeeds Review
-1.8K
bobmatnyc
General

pydantic

No summary provided by upstream source.

Repository SourceNeeds Review
-1.6K
bobmatnyc
General

playwright-e2e-testing

No summary provided by upstream source.

Repository SourceNeeds Review
-1.3K
bobmatnyc