💻 Prisma Schema Definition prisma

🟢 simple

Database schema definition with models, relationships, and field types

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

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

model User {
  id        Int      @id @default(autoincrement())
    email     String   @unique
    username  String?  @unique
    password  String
    firstName String?
    lastName  String?
    isActive  Boolean @default(true)
    role      Role     @default(USER)
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt

    posts     Post[]
    comments  Comment[]
    profile   Profile?

    @@map("users")
}

model Profile {
    id        Int      @id @default(autoincrement())
    userId    Int      @unique
    bio       String?
    avatar    String?
    website   String?
    birthDate DateTime?

    user      User      @relation(fields: [userId], references: [id])

    @@map("profiles")
}

model Post {
    id          Int      @id @default(autoincrement())
    title       String
    slug        String   @unique
    content     String
    publishedAt DateTime?
    createdAt   DateTime @default(now())
    updatedAt   DateTime @updatedAt

    authorId    Int
    author      User      @relation(fields: [authorId], references: [id])
    comments    Comment[]

    @@map("posts")
}

model Comment {
    id        Int      @id @default(autoincrement())
    content   String
    createdAt DateTime @default(now)
    updatedAt DateTime @updatedAt

    postId   Int
    authorId Int

    post     Post     @relation(fields: [postId], references: [id])
    author   User     @relation(fields: [authorId], references: [id])

    @@map("comments")
}

enum Role {
  USER
  ADMIN
  MODERATOR
  AUTHOR
}

💻 CRUD Operations typescript

🟢 simple

Complete CRUD operations with Prisma Client including create, read, update, and delete

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// CREATE Operations
async function createUser(userData: {
  email: string
  username?: string
  password: string
  firstName?: string
  lastName?: string
}) {
  try {
    const user = await prisma.user.create({
      data: userData,
      include: {
        profile: true
      }
    })
    return user
  } catch (error) {
    console.error('Error creating user:', error)
    throw error
  }
}

// Create user with profile
async function createUserWithProfile(userData: {
  email: string
  password: string
  profile: {
    bio?: string
    avatar?: string
    website?: string
  }
}) {
  const user = await prisma.user.create({
    data: {
      email: userData.email,
      password: userData.password,
      profile: {
        create: userData.profile
      }
    },
    include: {
      profile: true
    }
  })
  return user
}

// Create multiple users
async function createMultipleUsers(usersData: Array<{
  email: string
  password: string
}>) {
  const users = await prisma.user.createMany({
    data: usersData
  })
  return users
}

// READ Operations
async function getUserById(id: number) {
  const user = await prisma.user.findUnique({
    where: { id },
    include: {
      profile: true,
      posts: {
        include: {
          comments: true
        }
      }
    }
  })
  return user
}

async function getUserByEmail(email: string) {
  const user = await prisma.user.findUnique({
    where: { email },
    select: {
      id: true,
      email: true,
      username: true,
      firstName: true,
      lastName: true,
      role: true
    }
  })
  return user
}

async function getAllUsers(options: {
  page?: number
  limit?: number
  search?: string
  role?: string
}) {
  const { page = 1, limit = 10, search, role } = options
  const skip = (page - 1) * limit

  const where = {
    AND: [
      search ? {
        OR: [
          { username: { contains: search, mode: 'insensitive' as const } },
          { email: { contains: search, mode: 'insensitive' as const } },
          { firstName: { contains: search, mode: 'insensitive' as const } },
          { lastName: { contains: search, mode: 'insensitive' as const } }
        ]
      } : {},
      role ? { role: role as any } : {},
      { isActive: true }
    ]
  }

  const [users, total] = await Promise.all([
    prisma.user.findMany({
      where,
      skip,
      take: limit,
      include: {
        profile: true,
        _count: {
          select: {
            posts: true,
            comments: true
          }
        }
      },
      orderBy: {
        createdAt: 'desc'
      }
    }),
    prisma.user.count({ where })
  ])

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

// UPDATE Operations
async function updateUser(id: number, updateData: {
  email?: string
  username?: string
  firstName?: string
  lastName?: string
  role?: any
  isActive?: boolean
}) {
  const user = await prisma.user.update({
    where: { id },
    data: updateData,
    include: {
      profile: true
    }
  })
  return user
}

async function updateOrCreateProfile(userId: number, profileData: {
  bio?: string
  avatar?: string
  website?: string
  birthDate?: Date
}) {
  const profile = await prisma.profile.upsert({
    where: { userId },
    update: profileData,
    create: {
      userId,
      ...profileData
    }
  })
  return profile
}

// DELETE Operations
async function deleteUser(id: number) {
  // Soft delete
  const user = await prisma.user.update({
    where: { id },
    data: { isActive: false }
  })
  return user
}

async function hardDeleteUser(id: number) {
  const user = await prisma.user.delete({
    where: { id }
  })
  return user
}

// Transaction Operations
async function transferPostOwnership(postId: number, newAuthorId: number) {
  const result = await prisma.$transaction(async (tx) => {
    const post = await tx.post.findUnique({
      where: { id: postId },
      include: { author: true }
    })

    if (!post) {
      throw new Error('Post not found')
    }

    const updatedPost = await tx.post.update({
      where: { id: postId },
      data: { authorId: newAuthorId }
    })

    return {
      previousAuthor: post.author,
      updatedPost
    }
  })

  return result
}

// Advanced Queries
async function getUserWithPostStats(userId: number) {
  const userWithStats = await prisma.user.findUnique({
    where: { id: userId },
    include: {
      _count: {
        select: {
          posts: true,
          comments: true
        }
      },
      posts: {
        select: {
          id: true,
          title: true,
          publishedAt: true,
          _count: {
            select: {
              comments: true
            }
          }
        },
        orderBy: {
          publishedAt: 'desc'
        },
        take: 5
      }
    }
  })
  return userWithStats
}

export {
  createUser,
  createUserWithProfile,
  createMultipleUsers,
  getUserById,
  getUserByEmail,
  getAllUsers,
  updateUser,
  updateOrCreateProfile,
  deleteUser,
  hardDeleteUser,
  transferPostOwnership,
  getUserWithPostStats
}

💻 Prisma Migrations bash

🟡 intermediate

Database migration scripts and version control for schema changes

# Prisma Migrations Guide

# Create a new migration
npx prisma migrate dev --name add_user_bio

# View migration history
npx prisma migrate status

# Deploy migrations to production
npx prisma migrate deploy

# Reset database (development only)
npx prisma migrate reset

# Generate migration SQL without applying
npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script

# Example migration SQL:
-- AlterTable "users" ADD COLUMN "bio" TEXT;

-- CreateTable "profiles" (
--   "id" SERIAL NOT NULL,
--   "userId" INTEGER NOT NULL,
--   "bio" TEXT,
--   "avatar" TEXT,
--   "website" TEXT,
--   "birthDate" TIMESTAMP(3),
--   CONSTRAINT "profiles_pkey" PRIMARY KEY ("id")
-- );

-- CreateIndex
-- CreateIndex "posts_authorId_idx" ON "posts" ("authorId");

-- AlterTable
-- AlterTable "users" ALTER COLUMN "email" SET NOT NULL;

-- DropTable
-- DropTable "temp_table";

-- Raw SQL (use sparingly)
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS "custom_index" ON "users" ("email");

💻 Prisma Advanced Features typescript

🔴 complex

Advanced Prisma features including transactions, middleware, complex queries, and performance optimization

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// 1. Advanced Query Operations
async function advancedQueries() {
  // Complex filtering with OR conditions
  const users = await prisma.user.findMany({
    where: {
      OR: [
        { email: { contains: '@gmail.com' } },
        { username: { startsWith: 'admin' } }
      ],
      isActive: true
    },
    include: {
      posts: {
        include: {
          author: {
            select: {
              id: true,
              firstName: true,
              lastName: true
            }
          }
        }
      }
    }
  })

  // Date range filtering with timezone
  const recentPosts = await prisma.post.findMany({
    where: {
      createdAt: {
        gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000)
      },
      publishedAt: {
        not: null
      }
    },
    orderBy: {
      createdAt: 'desc'
    },
    include: {
      author: true,
      _count: {
        select: {
          comments: true
        }
      }
    }
  })

  // Aggregation with grouping
  const postStats = await prisma.post.groupBy({
    by: ['authorId'],
    _count: {
      select: {
        id: true,
        title: true
      }
    },
    _sum: {
      id: true
    },
    _avg: {
      id: true
    }
  })

  return { users, recentPosts, postStats }
}

// 2. Transaction Management
async function transactionExample() {
  const result = await prisma.$transaction(async (tx) => {
    // Create user
    const user = await tx.user.create({
      data: {
        email: '[email protected]',
        username: 'newuser',
        password: 'hashedpassword'
      }
    })

    // Create profile
    const profile = await tx.profile.create({
      data: {
        userId: user.id,
        bio: 'User bio'
      }
    })

    // Create first post
    const post = await tx.post.create({
      data: {
        title: 'First Post',
        content: 'This is the content of the first post',
        authorId: user.id,
        slug: 'first-post',
        publishedAt: new Date()
      }
    })

    return { user, profile, post }
  })

  return result
}

// 3. Performance Optimization
async function optimizedQueries() {
  // Use select for better performance
  const users = await prisma.user.findMany({
    select: {
      id: true,
      email: true,
      username: true,
      isActive: true
    },
    where: {
      isActive: true
    },
    take: 100,
    skip: 0
  })

  // Batch operations
  const batchCreateUsers = await prisma.user.createMany({
    data: Array.from({ length: 10 }, (_, i) => ({
      email: `user${i}@example.com`,
      username: `user${i}`,
      password: 'hashedpassword',
      isActive: true
    }))
  })

  // Raw SQL for complex operations
  const customQuery = await prisma.$queryRaw`
    SELECT u.*, COUNT(p.id) as post_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.authorId
    WHERE u.isActive = ${true}
    GROUP BY u.id
    HAVING COUNT(p.id) > 5
  `

  return { users, batchCreateUsers, customQuery }
}

// 4. Error Handling and Validation
async function errorHandling() {
  try {
    const user = await prisma.user.findUnique({
      where: { id: 1 }
    })

    if (!user) {
      throw new Error('User not found')
    }

    // Validation
    if (user.email && !user.email.includes('@')) {
      throw new Error('Invalid email format')
    }

    return user
  } catch (error) {
    console.error('Database error:', error)
    throw error
  }
}

// 5. Real-time Updates with Subscriptions
async function realTimeUpdates() {
  // Note: This requires PostgreSQL with logical replication
  // This is a conceptual example
  const stream = await prisma.$subscribe.user({
    where: {
      isActive: true
    },
    include: {
      posts: true,
      profile: true
    }
  })

  for await (const event of stream) {
    console.log('User event:', event)
    // Handle real-time updates
  }
}

// 6. Middleware Implementation
function setupMiddleware() {
  prisma.$use(async (params, next) => {
    // Log all queries
    console.log(`${params.model}.${params.action}`, params.args)

    // Add timestamps for create operations
    if (params.action === 'create') {
      params.args.data = {
        ...params.args.data,
        createdAt: new Date(),
        updatedAt: new Date()
      }
    }

    // Update timestamp for update operations
    if (params.action === 'update') {
      params.args.data = {
        ...params.args.data,
        updatedAt: new Date()
      }
    }

    return next(params)
  })

  // Soft delete middleware
  prisma.$use(async (params, next) => {
    if (params.action === 'delete') {
      // Convert delete to soft delete
      params.action = 'update'
      params.args.data = { isActive: false }
    }
    return next(params)
  })
}

// 7. Advanced Relationship Handling
async function complexRelationshipQueries() {
  // Nested creates and updates
  const userWithPosts = await prisma.user.create({
    data: {
      email: '[email protected]',
      password: 'password',
      username: 'author',
      posts: {
        create: [
          {
            title: 'First Post',
            content: 'Content 1',
            slug: 'first-post',
            publishedAt: new Date(),
            comments: {
              create: [
                {
                  content: 'Great post!',
                  authorId: 2 // Assuming user with ID 2 exists
                }
              ]
            }
          },
          {
            title: 'Second Post',
            content: 'Content 2',
            slug: 'second-post'
            // Not published yet
          }
        ]
      },
      profile: {
        create: {
          bio: 'Professional writer',
          website: 'https://example.com'
        }
      }
    },
    include: {
      posts: {
        include: {
          comments: {
            include: {
              author: {
                select: {
                  username: true,
                  firstName: true
                }
              }
            }
          }
        }
      },
      profile: true
    }
  })

  return userWithPosts
}

// 8. Pagination and Cursor-based Navigation
async function advancedPagination(options: {
  cursor?: string
  take?: number
  skip?: number
  direction?: 'forward' | 'backward'
}) {
  const { cursor, take = 10, skip, direction = 'forward' } = options

  const users = await prisma.user.findMany({
    take,
    skip,
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: {
      id: direction === 'forward' ? 'asc' : 'desc'
    },
    select: {
      id: true,
      email: true,
      username: true,
      createdAt: true
    }
  })

  return {
    users,
    nextCursor: users.length > 0 ? users[users.length - 1].id : null,
    hasMore: users.length === take
  }
}

export {
  advancedQueries,
  transactionExample,
  optimizedQueries,
  errorHandling,
  realTimeUpdates,
  setupMiddleware,
  complexRelationshipQueries,
  advancedPagination
}

🎯 Recommended Samples

Prisma Samples

💻 Prisma Schema Definition prisma

💻 CRUD Operations typescript

💻 Prisma Migrations bash

💻 Prisma Advanced Features typescript