Prisma ORM Skill

Expert assistance for Prisma ORM schema design, migrations, relations, query optimization, and database integration patterns.

Capabilities

• Design Prisma schemas with proper relations
• Generate and manage database migrations
• Optimize queries for performance
• Implement type-safe database access
• Configure multi-database support
• Set up seeding and testing strategies

Usage

Invoke this skill when you need to:

• Design database schemas with Prisma
• Set up migrations and database workflows
• Optimize database queries
• Implement complex relations
• Configure Prisma with Next.js or other frameworks

Inputs

Schema Configuration

{
  "database": "postgresql",
  "models": [
    {
      "name": "User",
      "fields": [
        { "name": "email", "type": "String", "unique": true },
        { "name": "name", "type": "String", "optional": true },
        { "name": "posts", "type": "Post", "relation": "one-to-many" }
      ]
    },
    {
      "name": "Post",
      "fields": [
        { "name": "title", "type": "String" },
        { "name": "content", "type": "String", "optional": true },
        { "name": "author", "type": "User", "relation": "many-to-one" }
      ]
    }
  ]
}

Output Structure

project/
├── prisma/
│   ├── schema.prisma           # Database schema
│   ├── migrations/             # Migration files
│   │   └── 20240101_init/
│   │       └── migration.sql
│   └── seed.ts                 # Seed script
├── lib/
│   └── db/
│       ├── prisma.ts           # Prisma client singleton
│       ├── queries/
│       │   ├── users.ts        # User queries
│       │   └── posts.ts        # Post queries
│       └── types.ts            # Extended types
└── package.json

Generated Code Patterns

Prisma Schema

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

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

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  password  String
  role      Role     @default(USER)
  posts     Post[]
  comments  Comment[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

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

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

  @@map("profiles")
}

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

  @@index([authorId])
  @@index([slug])
  @@map("posts")
}

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

  @@map("categories")
}

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

  @@map("tags")
}

model Comment {
  id        String   @id @default(cuid())
  content   String
  postId    String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  authorId  String
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  parentId  String?
  parent    Comment? @relation("CommentReplies", fields: [parentId], references: [id])
  replies   Comment[] @relation("CommentReplies")
  createdAt DateTime @default(now())

  @@index([postId])
  @@index([authorId])
  @@map("comments")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Prisma Client Singleton

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

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

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

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

export default prisma;

Query Functions

// lib/db/queries/users.ts
import { prisma } from '../prisma';
import { Prisma } from '@prisma/client';

export type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true; profile: true };
}>;

export async function getUserById(id: string): Promise<UserWithPosts | null> {
  return prisma.user.findUnique({
    where: { id },
    include: {
      posts: {
        where: { published: true },
        orderBy: { createdAt: 'desc' },
        take: 10,
      },
      profile: true,
    },
  });
}

export async function getUserByEmail(email: string) {
  return prisma.user.findUnique({
    where: { email },
    select: {
      id: true,
      email: true,
      name: true,
      role: true,
    },
  });
}

export async function createUser(data: Prisma.UserCreateInput) {
  return prisma.user.create({
    data,
    include: {
      profile: true,
    },
  });
}

export async function updateUser(id: string, data: Prisma.UserUpdateInput) {
  return prisma.user.update({
    where: { id },
    data,
  });
}

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

export async function getUsers(params: {
  skip?: number;
  take?: number;
  where?: Prisma.UserWhereInput;
  orderBy?: Prisma.UserOrderByWithRelationInput;
}) {
  const { skip = 0, take = 10, where, orderBy = { createdAt: 'desc' } } = params;

  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      skip,
      take,
      where,
      orderBy,
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
        createdAt: true,
        _count: {
          select: { posts: true },
        },
      },
    }),
    prisma.user.count({ where }),
  ]);

  return {
    users,
    total,
    pages: Math.ceil(total / take),
  };
}

Post Queries with Relations

// lib/db/queries/posts.ts
import { prisma } from '../prisma';
import { Prisma } from '@prisma/client';

export async function getPublishedPosts(params: {
  page?: number;
  limit?: number;
  categoryId?: string;
  authorId?: string;
  search?: string;
}) {
  const { page = 1, limit = 10, categoryId, authorId, search } = params;
  const skip = (page - 1) * limit;

  const where: Prisma.PostWhereInput = {
    published: true,
    ...(categoryId && {
      categories: { some: { id: categoryId } },
    }),
    ...(authorId && { authorId }),
    ...(search && {
      OR: [
        { title: { contains: search, mode: 'insensitive' } },
        { content: { contains: search, mode: 'insensitive' } },
      ],
    }),
  };

  const [posts, total] = await prisma.$transaction([
    prisma.post.findMany({
      where,
      skip,
      take: limit,
      orderBy: { createdAt: 'desc' },
      include: {
        author: {
          select: { id: true, name: true, email: true },
        },
        categories: true,
        tags: true,
        _count: {
          select: { comments: true },
        },
      },
    }),
    prisma.post.count({ where }),
  ]);

  return {
    posts,
    pagination: {
      page,
      limit,
      total,
      pages: Math.ceil(total / limit),
    },
  };
}

export async function getPostBySlug(slug: string) {
  return prisma.post.findUnique({
    where: { slug },
    include: {
      author: {
        select: { id: true, name: true, email: true },
      },
      categories: true,
      tags: true,
      comments: {
        where: { parentId: null },
        include: {
          author: { select: { id: true, name: true } },
          replies: {
            include: {
              author: { select: { id: true, name: true } },
            },
          },
        },
        orderBy: { createdAt: 'desc' },
      },
    },
  });
}

export async function createPost(data: {
  title: string;
  content?: string;
  authorId: string;
  categoryIds?: string[];
  tagNames?: string[];
}) {
  const { title, content, authorId, categoryIds = [], tagNames = [] } = data;

  const slug = title
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/(^-|-$)/g, '');

  return prisma.post.create({
    data: {
      title,
      slug,
      content,
      authorId,
      categories: {
        connect: categoryIds.map((id) => ({ id })),
      },
      tags: {
        connectOrCreate: tagNames.map((name) => ({
          where: { name },
          create: { name },
        })),
      },
    },
    include: {
      author: true,
      categories: true,
      tags: true,
    },
  });
}

Seed Script

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

const prisma = new PrismaClient();

async function main() {
  console.log('Seeding database...');

  // Create categories
  const categories = await Promise.all([
    prisma.category.upsert({
      where: { name: 'Technology' },
      update: {},
      create: { name: 'Technology' },
    }),
    prisma.category.upsert({
      where: { name: 'Design' },
      update: {},
      create: { name: 'Design' },
    }),
  ]);

  // Create admin user
  const adminPassword = await hash('admin123', 12);
  const admin = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email: 'admin@example.com',
      name: 'Admin User',
      password: adminPassword,
      role: 'ADMIN',
      profile: {
        create: {
          bio: 'System administrator',
        },
      },
    },
  });

  // Create sample posts
  await prisma.post.createMany({
    data: [
      {
        title: 'Getting Started with Prisma',
        slug: 'getting-started-with-prisma',
        content: 'Learn how to use Prisma ORM...',
        published: true,
        authorId: admin.id,
      },
      {
        title: 'Database Best Practices',
        slug: 'database-best-practices',
        content: 'Tips for designing efficient databases...',
        published: true,
        authorId: admin.id,
      },
    ],
    skipDuplicates: true,
  });

  console.log('Seeding completed!');
}

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

Migration Workflow

# Create initial migration
npx prisma migrate dev --name init

# Apply migrations to production
npx prisma migrate deploy

# Reset database (development only)
npx prisma migrate reset

# Generate Prisma Client
npx prisma generate

# Seed database
npx prisma db seed

Package.json Scripts

{
  "scripts": {
    "db:generate": "prisma generate",
    "db:push": "prisma db push",
    "db:migrate": "prisma migrate dev",
    "db:migrate:deploy": "prisma migrate deploy",
    "db:seed": "prisma db seed",
    "db:studio": "prisma studio",
    "db:reset": "prisma migrate reset"
  },
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

Query Optimization Patterns

Select Only Needed Fields

// Bad - fetches all fields
const user = await prisma.user.findUnique({ where: { id } });

// Good - fetches only needed fields
const user = await prisma.user.findUnique({
  where: { id },
  select: {
    id: true,
    name: true,
    email: true,
  },
});

Batch Operations

// Use transactions for multiple operations
const [user, posts] = await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.post.createMany({ data: postsData }),
]);

// Use interactive transactions for complex logic
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData });
  await tx.post.create({
    data: { ...postData, authorId: user.id },
  });
});

Pagination with Cursor

async function getPaginatedPosts(cursor?: string) {
  return prisma.post.findMany({
    take: 10,
    ...(cursor && {
      skip: 1,
      cursor: { id: cursor },
    }),
    orderBy: { createdAt: 'desc' },
  });
}

Dependencies

{
  "dependencies": {
    "@prisma/client": "^6.0.0"
  },
  "devDependencies": {
    "prisma": "^6.0.0"
  }
}

Workflow

1. Define schema - Create models and relations
2. Generate client - Run prisma generate
3. Create migrations - Run prisma migrate dev
4. Implement queries - Type-safe database access
5. Seed database - Create initial data
6. Optimize queries - Select, batch, index

Best Practices Applied

• Type-safe queries with Prisma Client
• Proper relation modeling
• Efficient pagination patterns
• Transaction support
• Cascade deletes where appropriate
• Indexed frequently queried fields

References

• Prisma Documentation: https://www.prisma.io/docs
• Prisma MCP Server: https://www.prisma.io/mcp
• Prisma Examples: https://github.com/prisma/prisma-examples

Target Processes

• database-schema-design
• migration-management
• query-optimization
• data-seeding
• database-testing