Prismav7INTERMEDIATE

Prisma ORM

Prisma ORM cheat sheet covering schema definition, relations, CRUD queries, migrations, and database management with code examples.

15 min read

prismaormdatabasepostgresqlmysqlsqlitemigrationsschema

Loading your progress

Prisma Schema & Models

Schema Definition & Models

Define your database schema using Prisma Schema Language with models, fields, and attributes

typescript

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

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

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

  @@map("users")
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  content  String?
  authorId Int
  author   User   @relation(fields: [authorId], references: [id])

  @@map("posts")
}

// BASH_BLOCK:
# Generate Prisma Client after schema changes
npx prisma generate

# Format schema file
npx prisma format

# Validate schema
npx prisma validate

# Open Prisma Studio to view data
npx prisma studio

💡 Prisma schema is the single source of truth for your database

⚡ Run npx prisma generate after schema changes

📌 Use npx prisma format to auto-format schema

✅ VSCode extension provides syntax highlighting

Prisma Relations & Associations

Define relationships between models including one-to-one, one-to-many, and many-to-many

typescript

// One-to-One
model User {
  id      Int     @id @default(autoincrement())
  profile Profile?
}

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

// One-to-Many
model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int  @id @default(autoincrement())
  authorId Int
  author   User @relation(fields: [authorId], references: [id])
}

💡 Relations are defined on both sides of the relationship

⚠️ Use @relation to disambiguate multiple relations

📌 Cascade deletes with onDelete: Cascade

✅ Self-relations require explicit relation names

Prisma Client Queries

CRUD Operations

Create, read, update, and delete records using Prisma Client methods

typescript

import { PrismaClient } from './generated/prisma/client'
import { PrismaPg } from '@prisma/adapter-pg'

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })
const prisma = new PrismaClient({ adapter })

// CREATE
const user = await prisma.user.create({
  data: {
    email: 'john@example.com',
    name: 'John Doe',
    posts: {
      create: { title: 'My First Post' }
    }
  }
})

// READ
const users = await prisma.user.findMany({
  where: { email: { contains: 'example' } },
  include: { posts: true }
})

// UPDATE
const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Jane Doe' }
})

// DELETE
await prisma.user.delete({ where: { id: 1 } })

💡 All operations return Promises

⚡ Use select to fetch only needed fields

📌 Include relations with include or select

✅ findUnique requires unique fields

Advanced Querying & Filtering

Complex queries with filtering, sorting, pagination, and aggregations

typescript

// Complex filtering
const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: 'prisma' } },
      { content: { contains: 'database' } }
    ],
    AND: [
      { status: 'PUBLISHED' },
      { author: { role: 'ADMIN' } }
    ],
    tags: {
      some: { name: { in: ['tech', 'tutorial'] } }
    }
  }
})

// Aggregations and grouping
const stats = await prisma.post.aggregate({
  _count: { id: true },
  _avg: { viewCount: true },
  where: { status: 'PUBLISHED' }
})

💡 Combine multiple filters with AND, OR, NOT

⚡ Use cursor-based pagination for large datasets

📌 Aggregate functions: count, sum, avg, min, max

✅ Use distinct to remove duplicates

Prisma Migrations & Database Management

Database Migrations

Version control your database schema changes with Prisma Migrate

bash

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

# Apply migrations in production
npx prisma migrate deploy

# Create migration without applying (preview)
npx prisma migrate dev --create-only

# Reset database (removes all data!)
npx prisma migrate reset

# Check migration status
npx prisma migrate status

# Resolve failed migrations
npx prisma migrate resolve --applied "20231201123456_migration_name"

// BASH_BLOCK:
# Development workflow
npx prisma migrate dev    # Create and apply migration
npx prisma generate        # Regenerate Prisma Client
npx prisma studio          # Open GUI to view/edit data

# Production deployment
npx prisma migrate deploy  # Apply pending migrations
npx prisma generate        # Generate Prisma Client

# Migration management
npx prisma migrate diff \
  --from-schema-datamodel prisma/schema.prisma \
  --to-schema-datasource prisma/schema.prisma \
  --script > migration.sql

💡 Always review migration SQL before applying

⚠️ Don't edit migration files after applying

📌 Use --create-only to preview migrations

✅ Reset dev DB with npx prisma migrate reset

Database Configuration & Management

Configure database connections, manage schema, and handle multiple environments

typescript

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  // Shadow database for migrations
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

// .env file
DATABASE_URL="postgresql://user:password@localhost:5432/mydb"
SHADOW_DATABASE_URL="postgresql://user:password@localhost:5432/shadow"

// BASH_BLOCK:
# Initialize Prisma in project
npx prisma init

# Generate Prisma Client
npx prisma generate

# Open Prisma Studio GUI
npx prisma studio

# Format schema file
npx prisma format

# Validate schema
npx prisma validate

💡 Use DATABASE_URL environment variable

⚠️ Never commit .env files with credentials

📌 Shadow database required for migrations

✅ Use connection pooling in production

Transactions & Raw Queries

Database Transactions

Ensure data consistency with interactive and sequential transactions

typescript

// Sequential transaction (auto-rollback on error)
const result = await prisma.$transaction([
  prisma.user.create({ data: { email: 'user@example.com' } }),
  prisma.post.create({ data: { title: 'Post' } }),
])

// Interactive transaction with custom logic
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: 'user@example.com' }
  })
  
  if (user.email.includes('spam')) {
    throw new Error('Spam detected')
  }
  
  await tx.post.create({
    data: { title: 'Post', authorId: user.id }
  })
})

💡 Transactions auto-rollback on any error

⚠️ Keep transactions short to avoid locks

📌 Use interactive transactions for complex logic

✅ Set appropriate timeout and isolation levels

Raw SQL Queries

Execute raw SQL queries when Prisma Client methods are not sufficient

typescript

// Raw query with type safety
type User = { id: number; email: string; name: string | null }
const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM "User" 
  WHERE email LIKE ${searchTerm + '%'}
`

// Execute raw SQL (returns affected rows)
const result = await prisma.$executeRaw`
  UPDATE "User" SET "updatedAt" = NOW() 
  WHERE "lastLogin" < NOW() - INTERVAL '30 days'
`

// Unsafe raw query (use carefully!)
const query = Prisma.sql`SELECT * FROM "User" WHERE id = ${userId}`
const users = await prisma.$queryRawUnsafe(query.text, ...query.values)

💡 Use template literals for safe parameterization

⚠️ $queryRawUnsafe is vulnerable to SQL injection

📌 BigInt values need conversion for JSON

✅ Always validate and sanitize user inputs

Seeding & Development

Database Seeding

Populate your database with initial or test data for development

typescript

// prisma/seed.ts
import { PrismaClient } from '../generated/prisma/client'
import { PrismaPg } from '@prisma/adapter-pg'
const prisma = new PrismaClient({ adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }) })

async function main() {
  // Clean existing data
  await prisma.post.deleteMany()
  await prisma.user.deleteMany()
  
  // Create users with posts
  const alice = await prisma.user.create({
    data: {
      email: 'alice@example.com',
      name: 'Alice',
      posts: {
        create: [
          { title: 'Hello World', published: true },
          { title: 'Prisma is great!' }
        ]
      }
    }
  })
  
  // Bulk create
  await prisma.user.createMany({
    data: [
      { email: 'bob@example.com', name: 'Bob' },
      { email: 'charlie@example.com', name: 'Charlie' }
    ]
  })
}

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

// BASH_BLOCK:
# Run database seeding
npx prisma db seed

# Reset the database (Prisma 7 no longer auto-seeds after reset)
npx prisma migrate reset

# Seed manually after reset
npx prisma db seed

💡 Configure the seed command in prisma.config.ts under migrations.seed

⚡ Use createMany for bulk inserts

📌 Clean data before seeding to ensure consistency

✅ Use faker for realistic test data

Error Handling & Optimization

Error Handling

Handle Prisma-specific errors and implement proper error recovery

typescript

import { Prisma } from '@prisma/client'

// Handle specific Prisma errors
try {
  await prisma.user.create({
    data: { email: 'user@example.com' }
  })
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    if (error.code === 'P2002') {
      console.log('Unique constraint violation:', error.meta?.target)
    } else if (error.code === 'P2025') {
      console.log('Record not found')
    }
  } else if (error instanceof Prisma.PrismaClientValidationError) {
    console.log('Validation error:', error.message)
  } else {
    throw error
  }
}

💡 Check error.code for specific error types

⚠️ P2002 includes constraint name in meta.target

📌 Implement retry logic for connection errors

✅ Log errors but don't expose internals to users

Query Optimization

Optimize database queries for better performance and efficiency

typescript

// Use select instead of include
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    posts: {
      select: {
        id: true,
        title: true
      }
    }
  }
})

// Pagination with cursor
const posts = await prisma.post.findMany({
  take: 20,
  skip: 1, // Skip the cursor
  cursor: { id: lastPostId },
  orderBy: { id: 'desc' }
})

// Connection pooling configuration
// schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  // connection_limit = 10
  // pool_timeout = 10
}

💡 Use select over include for better performance

⚡ Add database indexes for frequently queried fields

📌 Use cursor pagination for large datasets

✅ Monitor query performance with Prisma metrics

Prisma ORM

Table of Contents

Prisma Schema & Models

Prisma Client Queries

Prisma Migrations & Database Management

Transactions & Raw Queries

Seeding & Development

Error Handling & Optimization