# PostgreSQL
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"
# MySQL
DATABASE_URL="mysql://user:password@localhost:3306/mydb"
# SQLite
DATABASE_URL="file:./dev.db"
# SQL Server
DATABASE_URL="sqlserver://localhost:1433;database=mydb;user=sa;password=Password123"
# MongoDB
DATABASE_URL="mongodb://user:password@localhost:27017/mydb"
The Prisma Schema File
The schema.prisma file defines your database structure:
// Data source configuration
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
// Generator configuration
generator client {
provider = "prisma-client-js"
}
// Data model
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
authorId Int
author User @relation(fields: [authorId], references: [id])
}
Schema components:
Component
Purpose
datasource
Database connection configuration
generator
Specifies what to generate (Prisma Client, etc.)
model
Defines database tables/collections
enum
Defines enumeration types
Creating Your First Model
Define a simple User model:
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
Field type modifiers:
Modifier
Description
@id
Defines primary key
@unique
Ensures field values are unique
@default()
Sets default value
@updatedAt
Automatically updates on record modification
?
Makes field optional (nullable)
[]
Defines array/list type
Common field types:
model Example {
id Int @id
text String
number Int
decimal Float
boolean Boolean
date DateTime
json Json
bytes Bytes
}
Generating Prisma Client
After defining your schema, generate the Prisma Client:
npx prisma generate
This generates a type-safe client in node_modules/@prisma/client.
The client is automatically regenerated when you run migrations.
Import and use the client in your code:
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
async function main() {
const user = await prisma.user.create({
data: {
email: 'alice@example.com',
name: 'Alice'
}
})
console.log(user)
}
main()
.catch(console.error)
.finally(() => prisma.$disconnect())
const result = await prisma.$queryRaw`SELECT 1`
console.log('Connection test:', result)
Common Connection Issues
Connection refused:
Error: Can't reach database server at `localhost:5432`
Solutions:
- Check if database is running
- Verify host and port
- Check firewall rules
Authentication failed:
Error: Authentication failed against database server
Solutions:
- Verify username and password
- Check user permissions
- Ensure URL encoding for special characters
Database does not exist:
Error: Database `mydb` does not exist
Solutions:
- Create database manually
- Check database name in connection string
- Run migrations to create schema
SSL required:
Error: SSL connection is required
Solution:
DATABASE_URL="postgresql://user:pass@host:5432/db?sslmode=require"
Connection pool exhausted:
Error: Timed out fetching a connection from the pool
Solutions:
- Increase connection_limit
- Ensure connections are properly closed
- Check for connection leaks
Multiple Database Connections
Multiple Prisma instances for different databases:
// schema1.prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
schemas = ["public", "auth"]
}
model User {
id Int @id
email String
@@schema("auth")
}
model Post {
id Int @id
title String
@@schema("public")
}
Connection Pooling vs Direct Connection
What Is Connection Pooling?
A connection pool maintains a set of reusable database connections instead of creating new connections for each request.
Connection lifecycle:
Without pooling:
Request → Create Connection → Execute Query → Close Connection → Response
(Expensive: ~20-50ms overhead per request)
With pooling:
Request → Get Connection from Pool → Execute Query → Return to Pool → Response
(Fast: ~1-2ms overhead per request)
Built-in pooling in Prisma:
Prisma Client includes connection pooling by default
pool size defaults to (num_cpus * 2) + 1
connections are kept alive and reused
Direct Connection vs Pooled Connection
Aspect
Direct Connection
Pooled Connection
Connection Creation
Per request
Reused from pool
Latency
Higher (20-50ms overhead)
Lower (1-2ms overhead)
Database Load
High (many connections)
Low (controlled connections)
Scalability
Limited by max connections
Better (pool manages connections)
Features
All database features
May have limitations
Migrations
Supported
Often not supported
Prepared Statements
Fully supported
Limited in transaction mode
Configuring Both in Prisma
Use url for pooled, directUrl for direct:
datasource db {
provider = "postgresql"
url = env("DATABASE_URL") // Pooled connection
directUrl = env("DIRECT_DATABASE_URL") // Direct connection
}
Environment variables:
# Pooled connection (via PgBouncer)
DATABASE_URL="postgresql://user:pass@pgbouncer:6432/mydb?pgbouncer=true"
# Direct connection (to PostgreSQL)
DIRECT_DATABASE_URL="postgresql://user:pass@postgres:5432/mydb"
Behavior:
url - used for queries (prisma.user.findMany())
directUrl - used for migrations (prisma migrate)
PgBouncer Configuration
PgBouncer is a popular PostgreSQL connection pooler.
The ?pgbouncer=true parameter tells Prisma to use transaction mode optimizations.
Connection Pool Exhaustion
Common problem when pool size is too small:
Error: Timed out fetching a new connection from the connection pool.
More info: http://pris.ly/d/connection-pool
Causes:
too many concurrent requests
connections not being released
long-running queries
connection leaks
Solutions:
# Increase pool size
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=50"
# Increase pool timeout
DATABASE_URL="postgresql://user:pass@host:5432/db?pool_timeout=30"
Always disconnect in scripts:
async function script() {
try {
// Your database operations
} finally {
await prisma.$disconnect() // Release connections
}
}
Check for connection leaks:
// Bad - connection leak
async function badExample() {
const prisma = new PrismaClient()
const users = await prisma.user.findMany()
return users // Never disconnects!
}
// Good - proper cleanup
const prisma = new PrismaClient() // Singleton
async function goodExample() {
const users = await prisma.user.findMany()
return users // Prisma instance managed globally
}
A shadow database is a temporary database used by Prisma Migrate during development to detect schema drift and generate migrations.
Purpose:
validates migration files are correct
detects manual schema changes
ensures migrations can be applied cleanly
generates accurate migration SQL
Lifecycle:
1. Prisma creates shadow database
2. Applies all existing migrations
3. Compares result with your schema.prisma
4. Generates new migration if differences found
5. Drops shadow database
Only used during prisma migrate dev, not in production.
Automatic vs Manual Configuration
Prisma automatically creates shadow databases when possible:
-- CreateTable
CREATE TABLE "posts" (
"id" SERIAL PRIMARY KEY,
"title" TEXT NOT NULL
);
-- AlterTable
ALTER TABLE "users" ADD COLUMN "email" TEXT;
Step 5: Apply new migration to main database
Applying migration `20240103_add_posts`
Step 6: Drop shadow database (or keep for next run)
Cleaning up shadow database...
Schema Drift Detection
Shadow database enables detection of manual schema changes:
Scenario: Developer manually adds column to database:
-- Manual change (NOT via Prisma)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
Running prisma migrate dev detects drift:
⚠️ We found changes that cannot be executed:
• Added column `phone` on table `users`
These changes were made outside of Prisma Migrate.
Do you want to:
1. Mark as applied (skip)
2. Reset database
3. Cancel
Without shadow database, Prisma couldn't detect these changes reliably.
Error Messages and Solutions
Error: Cannot create shadow database
Error: A migration failed when applied to the shadow database
Database error: permission denied to create database
# Main database on production server
DATABASE_URL="postgresql://user:pass@prod-server.com:5432/mydb"
# Shadow database on local machine
SHADOW_DATABASE_URL="postgresql://user:pass@localhost:5432/mydb_shadow"
Useful when:
production database has strict permissions
want to avoid any impact on production
testing migrations locally before production
Requirements:
both databases must be same type (PostgreSQL, MySQL, etc.)
both must be accessible from development machine
Disabling Shadow Database
Some scenarios don't require shadow database:
MongoDB: Schema-less, shadow database not applicable
datasource db {
provider = "mongodb"
url = env("DATABASE_URL")
}
// Use: prisma db push
// Migrations not supported for MongoDB
Use --skip-seed and --skip-generate flags if needed:
npx prisma migrate dev --skip-seed --skip-generate
Testing Migrations with Shadow Database
Verify migration before applying to production:
# 1. Create migration using shadow database
npx prisma migrate dev --name add_users
# 2. Review generated SQL
cat prisma/migrations/*/migration.sql
# 3. Test on separate staging database
DATABASE_URL=$STAGING_URL npx prisma migrate deploy
# 4. If successful, deploy to production
DATABASE_URL=$PRODUCTION_URL npx prisma migrate deploy
Dry run with shadow database:
# Create migration without applying to main database
npx prisma migrate dev --create-only
# Review and edit migration.sql if needed
# Apply when ready
npx prisma migrate dev
Read Replicas
What Are Read Replicas?
A read replica is a copy of your primary database that handles read operations only.
Characteristics:
primary handles all writes (INSERT, UPDATE, DELETE)
replicas handle reads (SELECT)
data replicates from primary to replicas (asynchronous)
replicas are eventually consistent
Prisma 5.0+ includes native read replica support.
Typical read/write ratios that benefit from replicas:
const replicaWeights = [
{ url: process.env.DATABASE_REPLICA_1!, weight: 50 }, // 50%
{ url: process.env.DATABASE_REPLICA_2!, weight: 30 }, // 30%
{ url: process.env.DATABASE_REPLICA_3!, weight: 20 } // 20%
]
const prisma = new PrismaClient().$extends(
readReplicas({
url: () => {
const total = replicaWeights.reduce((sum, r) => sum + r.weight, 0)
let random = Math.random() * total
for (const replica of replicaWeights) {
random -= replica.weight
if (random <= 0) return replica.url
}
return replicaWeights[0].url
}
})
)
Replication Lag and Consistency
Read replicas use asynchronous replication - data isn't immediately consistent.
Typical replication lag:
Scenario
Typical Lag
Same datacenter
10-100ms
Same region
100-500ms
Cross-region
500ms-5s
Heavy load
Can increase significantly
Problem scenario:
// Write to primary
await prisma.user.create({
data: { id: 1, email: 'new@example.com' }
})
// Immediately read from replica - may not exist yet!
const user = await prisma.user.findUnique({
where: { id: 1 }
}).$replica()
console.log(user) // Might be null due to replication lag
Solution: Read from primary after writes:
// Write to primary
await prisma.user.create({
data: { id: 1, email: 'new@example.com' }
})
// Read from primary (not replica) to ensure consistency
const user = await prisma.user.findUnique({
where: { id: 1 }
})
console.log(user) // Always exists
Read-After-Write Consistency Pattern
Ensure users see their own writes immediately:
async function createAndReturnUser(email: string) {
// Write to primary
const newUser = await prisma.user.create({
data: { email }
})
// Return from primary, not replica
return newUser
}
async function updateAndReturnUser(id: number, data: any) {
// Update on primary
const updated = await prisma.user.update({
where: { id },
data
})
// Return from primary
return updated
}
Session-based routing:
// Track recent writes per session
const recentWrites = new Map()
async function getUserWithConsistency(userId: number, sessionId: string) {
const lastWrite = recentWrites.get(sessionId) || 0
const timeSinceWrite = Date.now() - lastWrite
// If wrote within last 5 seconds, use primary
if (timeSinceWrite < 5000) {
return prisma.user.findUnique({ where: { id: userId } })
}
// Otherwise use replica
return prisma.user.findUnique({ where: { id: userId } }).$replica()
}
async function updateUser(userId: number, data: any, sessionId: string) {
recentWrites.set(sessionId, Date.now())
return prisma.user.update({ where: { id: userId }, data })
}
Transactions and Read Replicas
Transactions always use primary database:
// All operations in transaction use primary
await prisma.$transaction(async (tx) => {
const user = await tx.user.findUnique({ where: { id: 1 } }) // Primary
await tx.post.create({ data: { userId: 1, title: 'Post' } }) // Primary
// Cannot use $replica() inside transactions
// This would throw an error:
// await tx.user.findMany().$replica()
})
prisma.$on('error', (e) => {
if (e.message.includes('replica')) {
// Alert: replica unavailable
notifyOps('Replica database unavailable', e)
// Prisma automatically falls back to primary
}
})
Testing with Read Replicas
Test environment without replicas:
// lib/prisma.ts
const prisma = new PrismaClient()
// Only add read replicas in production
const client = process.env.NODE_ENV === 'production'
? prisma.$extends(
readReplicas({
url: [
process.env.DATABASE_REPLICA_1!,
process.env.DATABASE_REPLICA_2!
]
})
)
: prisma
export { client as prisma }
Simulate replication lag in tests:
async function testReplicationLag() {
// Write to primary
await prisma.user.create({
data: { id: 999, email: 'test@example.com' }
})
// Simulate replication lag
await new Promise(resolve => setTimeout(resolve, 100))
// Try reading from replica
const user = await prisma.user.findUnique({
where: { id: 999 }
}).$replica()
// May be null due to lag
if (!user) {
console.log('Replication lag detected')
}
}
Mock replica for unit tests:
// test/setup.ts
jest.mock('@prisma/extension-read-replicas', () => ({
readReplicas: () => ({
// All replica reads use primary in tests
$replica: () => prisma
})
}))
Common Pitfalls
Reading immediately after write:
// ❌ Bad - may not see new data
await prisma.user.create({ data: { email: 'new@example.com' } })
const user = await prisma.user.findFirst({
where: { email: 'new@example.com' }
}).$replica() // May return null
// ✓ Good - read from primary
await prisma.user.create({ data: { email: 'new@example.com' } })
const user = await prisma.user.findFirst({
where: { email: 'new@example.com' }
}) // Uses primary
Using replicas for financial data:
// ❌ Bad - account balance must be accurate
const balance = await prisma.account.findUnique({
where: { userId }
}).$replica() // Could show stale balance
// ✓ Good - always use primary for critical data
const balance = await prisma.account.findUnique({
where: { userId }
})
Not handling replica failures:
// ❌ Bad - no error handling
const users = await prisma.user.findMany().$replica()
// ✓ Good - Prisma handles failover automatically
// But monitor and alert on failures
const users = await prisma.user.findMany().$replica()
// Automatically falls back to primary if replica fails
Over-using replicas:
// ❌ Bad - using replica for everything
const session = await prisma.session.findUnique({
where: { token }
}).$replica() // Session data should be fresh
const notification = await prisma.notification.findFirst({
where: { userId, read: false }
}).$replica() // Real-time data needs consistency
// ✓ Good - use primary for real-time/critical data
const session = await prisma.session.findUnique({ where: { token } })
const notification = await prisma.notification.findFirst({
where: { userId, read: false }
})
Prisma Schema Overview
What Is the Prisma Schema?
The Prisma schema is a declarative configuration file that defines:
database connection
Prisma Client generator
data models (tables/collections)
relationships between models
field types and constraints
Single source of truth for database structure.
File location: prisma/schema.prisma
Uses Prisma Schema Language (PSL), not SQL or a programming language.
Schema File Structure
A complete schema has three main blocks:
// 1. Data source - database connection
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
// 2. Generator - what to generate
generator client {
provider = "prisma-client-js"
}
// 3. Data models - your tables/collections
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
authorId Int
author User @relation(fields: [authorId], references: [id])
}
Order of blocks doesn't matter, but conventionally it is datasource → generator → models.
Data Source Configuration
Defines database connection:
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
Models represent database tables (SQL) or collections (MongoDB):
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
age Int
isActive Boolean @default(true)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
Model naming conventions:
singular PascalCase (e.g., User, not users)
Prisma automatically pluralizes for database table names
use @@map to override table name
Custom table name:
model User {
id Int @id
email String
@@map("custom_users_table")
}
Field Types
Scalar types:
Prisma Type
PostgreSQL
MySQL
SQLite
String
text, varchar
varchar, text
text
Int
integer
int
integer
BigInt
bigint
bigint
integer
Float
double precision
double
real
Decimal
decimal
decimal
N/A
Boolean
boolean
tinyint(1)
integer
DateTime
timestamp
datetime
numeric
Json
jsonb
json
text
Bytes
bytea
longblob
blob
Example with different types:
model Product {
id Int @id @default(autoincrement())
name String
price Decimal @db.Decimal(10, 2)
quantity Int
inStock Boolean @default(true)
metadata Json?
image Bytes?
releaseDate DateTime
views BigInt @default(0)
rating Float?
}
Field modifiers:
Modifier
Description
Example
?
Optional (nullable)
name String?
[]
Array/List
tags String[]
none
Required
email String
Arrays example:
model Article {
id Int @id @default(autoincrement())
title String
tags String[]
viewCount Int[]
}
Field Attributes
Attributes modify field behavior using @:
model User {
id Int @id @default(autoincrement())
email String @unique
username String @db.VarChar(50)
role Role @default(USER)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([email])
@@unique([username, email])
}
Common field attributes:
Attribute
Description
@id
Primary key
@default()
Default value
@unique
Unique constraint
@relation()
Define relationship
@map()
Custom column name
@db.
Native database type
@updatedAt
Auto-update timestamp
@ignore
Exclude from Prisma Client
Default value functions:
model Example {
id String @id @default(uuid())
autoId Int @default(autoincrement())
timestamp DateTime @default(now())
randomNum Int @default(dbgenerated("floor(random() * 100)"))
constant String @default("PENDING")
boolean Boolean @default(true)
}
Block Attributes
Block attributes apply to entire model using @@:
model User {
id Int @id @default(autoincrement())
email String
firstName String
lastName String
age Int
city String
@@unique([email, firstName])
@@index([city])
@@index([lastName, firstName])
@@map("users_table")
}
Common block attributes:
Attribute
Description
@@id([])
Composite primary key
@@unique([])
Composite unique constraint
@@index([])
Database index
@@map()
Custom table name
@@schema()
PostgreSQL schema
@@ignore
Exclude model from Prisma Client
Composite primary key:
model UserRole {
userId Int
roleId Int
user User @relation(fields: [userId], references: [id])
role Role @relation(fields: [roleId], references: [id])
@@id([userId, roleId])
}
Multiple indexes:
model Post {
id Int @id @default(autoincrement())
title String
content String
published Boolean
authorId Int
@@index([authorId])
@@index([published, authorId])
@@index([title(ops: raw("gin_trgm_ops"))], type: Gin)
}
Relations
One-to-Many:
model User {
id Int @id @default(autoincrement())
email String @unique
posts Post[] // Relation field (not in database)
}
model Post {
id Int @id @default(autoincrement())
title String
authorId Int // Foreign key
author User @relation(fields: [authorId], references: [id])
}
One-to-One:
model User {
id Int @id @default(autoincrement())
email String @unique
profile Profile?
}
model Profile {
id Int @id @default(autoincrement())
bio String
userId Int @unique
user User @relation(fields: [userId], references: [id])
}
Many-to-Many (implicit):
model Post {
id Int @id @default(autoincrement())
title String
categories Category[]
}
model Category {
id Int @id @default(autoincrement())
name String
posts Post[]
}
// Prisma creates join table automatically: _CategoryToPost
Many-to-Many (explicit):
model Post {
id Int @id @default(autoincrement())
title String
postCategories PostCategory[]
}
model Category {
id Int @id @default(autoincrement())
name String
postCategories PostCategory[]
}
model PostCategory {
postId Int
categoryId Int
assignedAt DateTime @default(now())
post Post @relation(fields: [postId], references: [id])
category Category @relation(fields: [categoryId], references: [id])
@@id([postId, categoryId])
}
Self-relations:
model User {
id Int @id @default(autoincrement())
name String
invitedBy Int?
inviter User? @relation("UserInvites", fields: [invitedBy], references: [id])
invitees User[] @relation("UserInvites")
}
Relation Actions
Define behavior when referenced record is deleted/updated:
model Post {
id Int @id @default(autoincrement())
title String
authorId Int
author User @relation(fields: [authorId], references: [id], onDelete: Cascade, onUpdate: Cascade)
}
model User {
id Int @id @default(autoincrement())
posts Post[]
}
Available actions:
Action
Description
Cascade
Delete/update related records
Restrict
Prevent if related records exist
NoAction
Database default behavior
SetNull
Set foreign key to NULL
SetDefault
Set foreign key to default value
Examples:
// Delete user → delete all their posts
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
// Delete user → set posts.authorId to NULL
author User @relation(fields: [authorId], references: [id], onDelete: SetNull)
// Prevent user deletion if posts exist
author User @relation(fields: [authorId], references: [id], onDelete: Restrict)
Enums
Define enumeration types:
enum Role {
USER
ADMIN
MODERATOR
}
model User {
id Int @id @default(autoincrement())
email String @unique
role Role @default(USER)
}
With custom database values:
enum Status {
PENDING @map("pending")
APPROVED @map("approved")
REJECTED @map("rejected")
}
model Application {
id Int @id
status Status @default(PENDING)
}
Array of enums:
enum Permission {
READ
WRITE
DELETE
}
model User {
id Int @id
permissions Permission[]
}
Note: Not supported in SQLite (use String instead).
Native Database Types
Specify exact database column type using @db.:
PostgreSQL:
model Product {
id Int @id
name String @db.VarChar(255)
description String @db.Text
price Decimal @db.Decimal(10, 2)
metadata Json @db.JsonB
tags String[] @db.VarChar(100)
count BigInt @db.BigInt
}
MySQL:
model Product {
id Int @id
name String @db.VarChar(255)
description String @db.Text
price Decimal @db.Decimal(10, 2)
metadata Json
tiny Int @db.TinyInt
medium String @db.MediumText
}
Common native types:
Type
PostgreSQL
MySQL
Variable string
@db.VarChar(n)
@db.VarChar(n)
Long text
@db.Text
@db.Text
Decimal
@db.Decimal(p,s)
@db.Decimal(p,s)
Timestamp
@db.Timestamp(p)
@db.DateTime(p)
JSON
@db.JsonB
@db.Json
UUID
@db.Uuid
N/A
Comments and Documentation
Single-line comments:
model User {
// Primary identifier
id Int @id @default(autoincrement())
// Unique email for authentication
email String @unique
name String? // Optional display name
}
Multi-line comments:
/*
* User model represents application users
* Supports authentication and profile management
*/
model User {
id Int @id
email String @unique
}
Documentation comments (appear in generated types):
/// User account information
model User {
id Int @id @default(autoincrement())
/// User's email address (used for login)
email String @unique
/// Optional display name
name String?
}
/// User role enumeration
enum Role {
/// Standard user permissions
USER
/// Administrator with full access
ADMIN
}
Documentation comments use /// and show in IDE tooltips and generated code.
Unsupported Features
Mark models/fields to ignore:
model User {
id Int @id
email String
/// @ignore: deprecated field
oldField String @ignore
}
/// @ignore: legacy table not used by application
model LegacyData {
id Int @id
@@ignore
}
Ignored models/fields:
excluded from Prisma Client
not validated by Prisma
preserved in database
useful for gradual migration
Multi-Schema Support
PostgreSQL supports multiple schemas:
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
schemas = ["public", "auth", "analytics"]
}
model User {
id Int @id
email String
@@schema("auth")
}
model Post {
id Int @id
title String
@@schema("public")
}
model Event {
id Int @id
data Json
@@schema("analytics")
}
Models can reference across schemas:
model User {
id Int @id
posts Post[]
@@schema("auth")
}
model Post {
id Int @id
authorId Int
author User @relation(fields: [authorId], references: [id])
@@schema("public")
}
Views
Define database views as models:
/// Database view for user statistics
view UserStats {
userId Int @unique
postCount Int
totalViews BigInt
@@map("user_stats_view")
}
Views are read-only:
// ✓ Can read from views
const stats = await prisma.userStats.findMany()
// ✗ Cannot write to views
await prisma.userStats.create({ data: {} }) // Error
Create view manually in database:
CREATE VIEW user_stats_view AS
SELECT
u.id as userId,
COUNT(p.id) as postCount,
SUM(p.views) as totalViews
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id;
Schema Organization
Organize large schemas with comments:
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
// ============================================
// Authentication Models
// ============================================
model User {
id Int @id @default(autoincrement())
email String @unique
password String
sessions Session[]
}
model Session {
id String @id @default(uuid())
userId Int
expiresAt DateTime
user User @relation(fields: [userId], references: [id])
}
// ============================================
// Content Models
// ============================================
model Post {
id Int @id @default(autoincrement())
title String
content String
authorId Int
}
model Comment {
id Int @id @default(autoincrement())
text String
postId Int
userId Int
}
// ============================================
// E-commerce Models
// ============================================
model Product {
id Int @id @default(autoincrement())
name String
price Decimal
}
model Order {
id Int @id @default(autoincrement())
total Decimal
userId Int
}
Note: Prisma doesn't officially support multiple schema files yet, but tools like prisma-merge can help.
Schema Validation
Validate schema without database connection:
npx prisma validate
Format schema file:
npx prisma format
Common validation errors:
Error
Cause
Missing @relation
Ambiguous relation, needs explicit definition
Invalid field type
Unsupported type for database provider
Missing required field
Non-optional field without default value
Duplicate model name
Two models with same name
Invalid attribute
Attribute not supported for field type
Complete Schema Example
Real-world blog application schema:
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
enum Role {
USER
AUTHOR
ADMIN
}
enum PostStatus {
DRAFT
PUBLISHED
ARCHIVED
}
model User {
id Int @id @default(autoincrement())
email String @unique
username String @unique @db.VarChar(30)
passwordHash 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 Int @id @default(autoincrement())
bio String? @db.Text
avatar String?
website String?
userId Int @unique
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@map("profiles")
}
model Post {
id Int @id @default(autoincrement())
title String @db.VarChar(200)
slug String @unique
content String @db.Text
excerpt String? @db.VarChar(500)
coverImage String?
status PostStatus @default(DRAFT)
published Boolean @default(false)
publishedAt DateTime?
views Int @default(0)
authorId Int
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
comments Comment[]
tags Tag[]
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([slug])
@@index([authorId])
@@index([published, publishedAt])
@@map("posts")
}
model Comment {
id Int @id @default(autoincrement())
content String @db.Text
postId Int
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
userId Int
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
parentId Int?
parent Comment? @relation("CommentReplies", fields: [parentId], references: [id])
replies Comment[] @relation("CommentReplies")
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([postId])
@@index([userId])
@@map("comments")
}
model Tag {
id Int @id @default(autoincrement())
name String @unique @db.VarChar(50)
slug String @unique
posts Post[]
@@map("tags")
}
Understanding Relationships in Prisma
What Are Foreign Keys and Relationships?
A foreign key is a field that references the primary key of another table.
In traditional SQL:
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE posts (
id INT PRIMARY KEY,
title VARCHAR(200),
author_id INT, -- This is the foreign key
FOREIGN KEY (author_id) REFERENCES users(id)
);
Prisma on the other hand, represents this relationship in the schema file, not with raw SQL.
The Two Sides of Every Relationship
Every relationship in Prisma has two sides:
Side 1: The field that stores the foreign key (the ID)
This is a regular field that exists in the database
Example: authorId Int
This actually stores a number in the database
Side 2: The relation field (the connection)
This does NOT exist in the database as a column
Example: author User
This is virtual - Prisma uses it to navigate relationships
Think of it like this:
model Post {
id Int @id @default(autoincrement())
title String
authorId Int // Real column in database: stores user ID
author User @relation(fields: [authorId], references: [id])
// Virtual: lets you access the User object
}
The database table posts has columns: id, title, authorId
It does NOT have a column called author - that's just for Prisma to use.
One-to-Many: The Most Common Relationship
Scenario: One user can have many posts, but each post belongs to one user.
Complete example:
model User {
id Int @id @default(autoincrement())
name String
email String @unique
posts Post[] // Array because one user has MANY posts
}
model Post {
id Int @id @default(autoincrement())
title String
content String
authorId Int // Foreign key: stores which user owns this post
author User @relation(fields: [authorId], references: [id])
}
Breaking down the @relation attribute:
Part
Meaning
fields: [authorId]
This model's field that stores the foreign key
references: [id]
The field in the User model being referenced
"The authorId field references the id field in User"
Database structure created:
-- users table
id | name | email
-----|-----------|------------------
1 | Alice | alice@example.com
2 | Bob | bob@example.com
-- posts table
id | title | content | authorId
-----|----------------|----------------|----------
1 | First Post | Hello world | 1
2 | Second Post | More content | 1
3 | Bob's Post | Hi there | 2
Using in code:
// Get posts WITH their author information
const posts = await prisma.post.findMany({
include: {
author: true // Include the related User
}
})
// Result:
// [
// {
// id: 1,
// title: "First Post",
// authorId: 1,
// author: { id: 1, name: "Alice", email: "alice@example.com" }
// },
// ...
// ]
// Get user WITH all their posts
const user = await prisma.user.findUnique({
where: { id: 1 },
include: {
posts: true // Include all related Posts
}
})
// Result:
// {
// id: 1,
// name: "Alice",
// posts: [
// { id: 1, title: "First Post", ... },
// { id: 2, title: "Second Post", ... }
// ]
// }
Which Side Gets the Foreign Key?
Rule: The "many" side stores the foreign key.
Visualizing the relationship:
User (ONE) Post (MANY)
----------- -----------
id id
name title
email authorId ← Foreign key here!
posts [] ← Virtual author ← Virtual
Why? Because many posts can point to one user.
If we put it on User, we could only store ONE post ID (not many).
Another example:
// One category can have many products
model Category {
id Int @id @default(autoincrement())
name String
products Product[] // Virtual: "this category has many products"
}
model Product {
id Int @id @default(autoincrement())
name String
categoryId Int // Real: stores the category ID
category Category @relation(fields: [categoryId], references: [id])
// Virtual: "this product belongs to a category"
}
Remember: Foreign key goes on the many side, array goes on the one side.
One-to-One Relationships
Scenario: One user has exactly one profile.
Complete example:
model User {
id Int @id @default(autoincrement())
email String @unique
profile Profile? // Optional (?) because user might not have profile yet
}
model Profile {
id Int @id @default(autoincrement())
bio String
avatar String?
userId Int @unique // Must be @unique for one-to-one!
user User @relation(fields: [userId], references: [id])
}
Key difference from one-to-many:
Aspect
One-to-Many
One-to-One
Foreign key
authorId Int
userId Int @unique
Other side
posts Post[] (array)
profile Profile? (single, optional)
The @unique constraint ensures one user can have only one profile.
Database structure:
-- users table
id | email
-----|------------------
1 | alice@example.com
2 | bob@example.com
-- profiles table
id | bio | avatar | userId (UNIQUE)
-----|------------------|--------------|----------------
1 | I love coding | pic1.jpg | 1
2 | Hello world | pic2.jpg | 2
Using in code:
// Create user with profile
const user = await prisma.user.create({
data: {
email: 'alice@example.com',
profile: {
create: {
bio: 'I love coding',
avatar: 'pic1.jpg'
}
}
}
})
// Get user with profile
const userWithProfile = await prisma.user.findUnique({
where: { id: 1 },
include: { profile: true }
})
// Result:
// {
// id: 1,
// email: "alice@example.com",
// profile: {
// id: 1,
// bio: "I love coding",
// avatar: "pic1.jpg",
// userId: 1
// }
// }
Why Use ? (Optional) on Relations?
The ? makes a relation optional:
model User {
id Int @id
profile Profile? // User might not have a profile
}
model Profile {
userId Int @unique
user User @relation(fields: [userId], references: [id])
}
With?: User can exist without a profile
// Valid: user without profile
await prisma.user.create({
data: { email: 'test@example.com' }
})
Without?: Every user MUST have a profile immediately
model User {
id Int @id
profile Profile // Required! Must provide profile when creating user
}
// Must create profile with user
await prisma.user.create({
data: {
email: 'test@example.com',
profile: {
create: { bio: 'Required!' }
}
}
})
Common pattern: Make optional on the side without the foreign key.
model User {
profile Profile? // Optional here
}
model Profile {
userId Int // Required here (no ?)
user User @relation(fields: [userId], references: [id])
}
Many-to-Many: Implicit (Simple)
Scenario: Posts can have many tags, tags can be on many posts.
model Post {
id Int @id @default(autoincrement())
title String
tags Tag[] // Array of tags
}
model Tag {
id Int @id @default(autoincrement())
name String
posts Post[] // Array of posts
}
Notice: NO foreign keys defined explicitly!
Prisma automatically creates a join table named _PostToTag:
-- posts table
id | title
-----|------------------
1 | First Post
2 | Second Post
-- tags table
id | name
-----|-------------
1 | JavaScript
2 | Tutorial
-- _PostToTag (created automatically by Prisma)
A (postId) | B (tagId)
-----------|----------
1 | 1
1 | 2
2 | 1
model Post {
id Int @id @default(autoincrement())
title String
postTags PostTag[] // Connection to join table
}
model Tag {
id Int @id @default(autoincrement())
name String
postTags PostTag[] // Connection to join table
}
// Explicit join table with extra fields
model PostTag {
postId Int
tagId Int
assignedAt DateTime @default(now()) // Extra field!
post Post @relation(fields: [postId], references: [id])
tag Tag @relation(fields: [tagId], references: [id])
@@id([postId, tagId]) // Composite primary key
}
Database structure:
-- posts table
id | title
-----|------------------
1 | First Post
-- tags table
id | name
-----|-------------
1 | JavaScript
-- post_tags table (explicit join table)
postId | tagId | assignedAt
-------|-------|------------------------
1 | 1 | 2024-01-15 10:30:00
Using in code:
// Create relationship with timestamp
await prisma.postTag.create({
data: {
postId: 1,
tagId: 1,
assignedAt: new Date()
}
})
// Get post with tags AND assignment timestamps
const post = await prisma.post.findUnique({
where: { id: 1 },
include: {
postTags: {
include: {
tag: true // Include the actual tag data
}
}
}
})
// Result:
// {
// id: 1,
// title: "First Post",
// postTags: [
// {
// postId: 1,
// tagId: 1,
// assignedAt: "2024-01-15T10:30:00Z",
// tag: { id: 1, name: "JavaScript" }
// }
// ]
// }
When to Use Implicit vs Explicit Many-to-Many
Use implicit (simple) when:
you only need to connect two models
no extra data needed on the relationship
simpler API is preferred
// Simple: just connect posts and categories
model Post {
categories Category[]
}
model Category {
posts Post[]
}
Use explicit when:
need to store when relationship was created
need to track who created the relationship
need any extra metadata about the connection
// Complex: track when user joined team and their role
model UserTeam {
userId Int
teamId Int
role String // Extra field
joinedAt DateTime @default(now()) // Extra field
user User @relation(fields: [userId], references: [id])
team Team @relation(fields: [teamId], references: [id])
@@id([userId, teamId])
}
Self-Relations: Model Relating to Itself
Scenario: Users can follow other users.
Self-relation example:
model User {
id Int @id @default(autoincrement())
name String
// Users I follow
following User[] @relation("UserFollows")
// Users who follow me
followers User[] @relation("UserFollows")
}
Prisma creates implicit join table _UserFollows:
-- users table
id | name
-----|-------
1 | Alice
2 | Bob
3 | Carol
-- _UserFollows (A follows B)
A | B
-----|-----
1 | 2 -- Alice follows Bob
1 | 3 -- Alice follows Carol
2 | 1 -- Bob follows Alice
The "UserFollows" name is a label - you can name it anything:
following User[] @relation("UserFollows")
followers User[] @relation("UserFollows")
// Both must have the SAME name to connect them
Explicit self-relation with extra data:
model User {
id Int @id @default(autoincrement())
name String
following Follow[] @relation("Follower")
followers Follow[] @relation("Following")
}
model Follow {
followerId Int
followingId Int
createdAt DateTime @default(now())
follower User @relation("Follower", fields: [followerId], references: [id])
following User @relation("Following", fields: [followingId], references: [id])
@@id([followerId, followingId])
}
Understanding Relation Names
Relation names are needed when you have multiple relations between same models.
Example: Posts have an author AND an editor:
model User {
id Int @id @default(autoincrement())
name String
authoredPosts Post[] @relation("PostAuthor")
editedPosts Post[] @relation("PostEditor")
}
model Post {
id Int @id @default(autoincrement())
title String
authorId Int
author User @relation("PostAuthor", fields: [authorId], references: [id])
editorId Int?
editor User? @relation("PostEditor", fields: [editorId], references: [id])
}
Without names, Prisma can't tell which foreign key connects to which relation.
The name can be anything descriptive:
@relation("PostAuthor") // Good
@relation("AuthorPosts") // Good
@relation("WrittenBy") // Good
@relation("abc123") // Valid but unclear
Both sides must use the exact same name:
// ✓ Correct - same name on both sides
author User @relation("PostAuthor", fields: [...])
authoredPosts Post[] @relation("PostAuthor")
// ✗ Wrong - different names
author User @relation("PostAuthor", fields: [...])
authoredPosts Post[] @relation("AuthoredPosts") // Mismatch!
Optional vs Required Relations
Relations can be optional or required:
model Post {
id Int @id
// Required: every post MUST have an author
authorId Int // No ?
author User @relation(fields: [authorId], references: [id])
// Optional: post MAY have an editor
editorId Int? // With ?
editor User? @relation(fields: [editorId], references: [id])
}
Rule: If foreign key is optional (Int?), relation must be too (User?):
Foreign Key
Relation Field
Valid?
authorId Int
author User
✓ Both required
editorId Int?
editor User?
✓ Both optional
authorId Int
author User?
✗ Mismatch
editorId Int?
editor User
✗ Mismatch
For arrays, never use ?:
model User {
posts Post[] // ✓ Correct: array is never optional
// posts Post[]? // ✗ Wrong: syntax error
}
An empty array [] represents "no posts" - no need for ?.
Cascade Delete and Update Actions
Control what happens when related record is deleted/updated:
model Post {
id Int @id
authorId Int
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
}
model User {
id Int @id
posts Post[]
}
Available actions:
Action
What Happens
Cascade
Delete user → delete all their posts
SetNull
Delete user → set posts.authorId to NULL
Restrict
Cannot delete user if they have posts
NoAction
Database decides (usually same as Restrict)
SetDefault
Set to default value (if defined)
Example behaviors:
// Delete user → delete their posts
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
// Delete user → set authorId to NULL (requires authorId to be Int?)
author User? @relation(fields: [authorId], references: [id], onDelete: SetNull)
// Prevent user deletion if they have posts
author User @relation(fields: [authorId], references: [id], onDelete: Restrict)
onUpdate works similarly but for ID changes (less common):
model User {
id Int @id @default(autoincrement())
email String @unique
posts Post[]
comments Comment[]
}
model Post {
id Int @id @default(autoincrement())
title String
authorId Int
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
comments Comment[]
tags Tag[]
}
model Comment {
id Int @id @default(autoincrement())
text String
postId Int
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
userId Int
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}
model Tag {
id Int @id @default(autoincrement())
name String @unique
posts Post[]
}
E-commerce:
model Customer {
id Int @id @default(autoincrement())
email String @unique
orders Order[]
}
model Order {
id Int @id @default(autoincrement())
customerId Int
customer Customer @relation(fields: [customerId], references: [id])
items OrderItem[]
total Decimal
}
model Product {
id Int @id @default(autoincrement())
name String
price Decimal
orderItems OrderItem[]
}
model OrderItem {
id Int @id @default(autoincrement())
orderId Int
order Order @relation(fields: [orderId], references: [id], onDelete: Cascade)
productId Int
product Product @relation(fields: [productId], references: [id])
quantity Int
price Decimal
}
Organization hierarchy:
model User {
id Int @id @default(autoincrement())
email String @unique
memberships Membership[]
}
model Organization {
id Int @id @default(autoincrement())
name String
memberships Membership[]
}
model Membership {
id Int @id @default(autoincrement())
userId Int
organizationId Int
role String
joinedAt DateTime @default(now())
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
@@unique([userId, organizationId])
}
Common Mistakes and How to Fix Them
Mistake 1: Forgetting @unique on one-to-one
// ✗ Wrong - missing @unique
model Profile {
userId Int
user User @relation(fields: [userId], references: [id])
}
// ✓ Correct - has @unique
model Profile {
userId Int @unique
user User @relation(fields: [userId], references: [id])
}
Mistake 2: Mismatched optional markers
// ✗ Wrong - foreign key required but relation optional
model Post {
editorId Int // Required
editor User? // Optional - mismatch!
}
// ✓ Correct - both match
model Post {
editorId Int? // Optional
editor User? // Optional
}
Mistake 3: Wrong side has foreign key
// ✗ Wrong - foreign key on "one" side
model User {
id Int @id
postId Int // Wrong! Can only reference ONE post
post Post @relation(fields: [postId], references: [id])
}
// ✓ Correct - foreign key on "many" side
model Post {
id Int @id
authorId Int
author User @relation(fields: [authorId], references: [id])
}
model User {
id Int @id
posts Post[] // One user has many posts
}
Mistake 4: Not using arrays on one-to-many
// ✗ Wrong - not an array
model User {
posts Post // Wrong! Should be Post[]
}
// ✓ Correct - array for multiple posts
model User {
posts Post[]
}
Mistake 5: Ambiguous relations without names
// ✗ Wrong - Prisma can't tell which is which
model Post {
authorId Int
author User @relation(fields: [authorId], references: [id])
editorId Int
editor User @relation(fields: [editorId], references: [id])
}
// ✓ Correct - use relation names
model Post {
authorId Int
author User @relation("PostAuthor", fields: [authorId], references: [id])
editorId Int
editor User @relation("PostEditor", fields: [editorId], references: [id])
}
model User {
authoredPosts Post[] @relation("PostAuthor")
editedPosts Post[] @relation("PostEditor")
}
Practice Examples
Exercise 1: Movies and directors (one-to-many)
model Director {
id Int @id @default(autoincrement())
name String
movies Movie[]
}
model Movie {
id Int @id @default(autoincrement())
title String
directorId Int
director Director @relation(fields: [directorId], references: [id])
}
Exercise 2: Student and student card (one-to-one)
model Student {
id Int @id @default(autoincrement())
name String
studentCard StudentCard?
}
model StudentCard {
id Int @id @default(autoincrement())
cardNumber String @unique
studentId Int @unique
student Student @relation(fields: [studentId], references: [id])
}
Exercise 3: Students and courses (many-to-many)
model Student {
id Int @id @default(autoincrement())
name String
courses Course[]
}
model Course {
id Int @id @default(autoincrement())
name String
students Student[]
}
Exercise 4: Students and courses with enrollment date (explicit many-to-many)
model Student {
id Int @id @default(autoincrement())
name String
enrollments Enrollment[]
}
model Course {
id Int @id @default(autoincrement())
name String
enrollments Enrollment[]
}
model Enrollment {
studentId Int
courseId Int
enrolledAt DateTime @default(now())
grade String?
student Student @relation(fields: [studentId], references: [id])
course Course @relation(fields: [courseId], references: [id])
@@id([studentId, courseId])
}
Reading Data with Prisma
Understanding Read Operations
Reading (retrieving) data means fetching records from your database.
Prisma provides several methods for reading data:
Method
Returns
Use When
findUnique()
Single record or null
You have a unique identifier (ID, email)
findUniqueOrThrow()
Single record or throws error
Record must exist, want error if not found
findFirst()
First matching record or null
You want one record matching criteria
findFirstOrThrow()
First record or throws error
Record must exist, want error if not found
findMany()
Array of records (may be empty)
You want multiple records
count()
Number
You only need to count records
aggregate()
Aggregated data
You need sum, average, min, max
All examples assume this schema:
model User {
id Int @id @default(autoincrement())
email String @unique
name String
age Int
isActive Boolean @default(true)
createdAt DateTime @default(now())
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
content String
published Boolean @default(false)
views Int @default(0)
authorId Int
author User @relation(fields: [authorId], references: [id])
}
findUnique: Get One Record by Unique Field
Use findUnique() when you know a unique value (ID, email, etc.).
Basic example - find by ID:
const user = await prisma.user.findUnique({
where: {
id: 1
}
})
console.log(user)
// Returns:
// { id: 1, email: 'alice@example.com', name: 'Alice', age: 25, ... }
// or null if not found
Same as findUnique(), but throws error instead of returning null.
Example:
try {
const user = await prisma.user.findUniqueOrThrow({
where: { id: 9999 } // Doesn't exist
})
} catch (error) {
console.log(error.message)
// "No User found"
}
Use when the record must exist (otherwise it's a bug):
// If user doesn't exist, something is seriously wrong
const currentUser = await prisma.user.findUniqueOrThrow({
where: { id: session.userId }
})
findFirst: Get First Matching Record
Use findFirst() when you want the first record matching criteria.
Unlike findUnique(), you can filter by any field:
const user = await prisma.user.findFirst({
where: {
name: 'Alice' // Not unique - might have multiple Alices
}
})
// Returns the first Alice found (or null)
Multiple conditions:
const user = await prisma.user.findFirst({
where: {
name: 'Alice',
age: 25,
isActive: true
}
})
// Returns first user matching ALL conditions
With ordering:
const newestUser = await prisma.user.findFirst({
where: {
isActive: true
},
orderBy: {
createdAt: 'desc' // Newest first
}
})
// Returns the most recently created active user
Returns null if no match found:
const user = await prisma.user.findFirst({
where: { age: 150 } // Unlikely to exist
})
console.log(user) // null
const users = await prisma.user.findMany({
where: {
age: 25,
isActive: true,
name: 'Alice'
// All conditions must be true
}
})
Comparison operators:
const users = await prisma.user.findMany({
where: {
age: {
gt: 18, // Greater than
lte: 65 // Less than or equal
}
}
})
// Users between 19 and 65 years old
const user = await prisma.user.findUnique({
where: { id: 1 },
include: {
posts: {
where: {
published: true // Only published posts
}
}
}
})
Include with ordering:
const user = await prisma.user.findUnique({
where: { id: 1 },
include: {
posts: {
orderBy: {
createdAt: 'desc' // Newest first
},
take: 5 // Only 5 most recent
}
}
})
Nested includes:
const post = await prisma.post.findUnique({
where: { id: 1 },
include: {
author: {
include: {
posts: true // Include author's other posts too
}
}
}
})
console.log(post.author.posts) // All posts by this author
Combining select and include
You can not use select and include at the same level:
// ✗ Error - can't use both
const user = await prisma.user.findUnique({
where: { id: 1 },
select: { name: true },
include: { posts: true } // Error!
})
Solution: Use select with relation selects:
const user = await prisma.user.findUnique({
where: { id: 1 },
select: {
name: true,
email: true,
posts: true // Include posts via select
}
})
// Or with nested select:
const user = await prisma.user.findUnique({
where: { id: 1 },
select: {
name: true,
email: true,
posts: {
select: {
id: true,
title: true
// Only these post fields
}
}
}
})
Rule of thumb:
Use include for simple "give me everything" scenarios
Use select when you need precise control over fields
Sorting Results with orderBy
Use orderBy to sort results.
Single field, ascending:
const users = await prisma.user.findMany({
orderBy: {
name: 'asc' // A to Z
}
})
const users = await prisma.user.findMany({
orderBy: [
{ age: 'desc' }, // Sort by age first (oldest first)
{ name: 'asc' } // Then by name (A to Z)
]
})
Sort by related field:
const posts = await prisma.post.findMany({
orderBy: {
author: {
name: 'asc' // Sort posts by author name
}
}
})
Sort by relation count:
const users = await prisma.user.findMany({
orderBy: {
posts: {
_count: 'desc' // Users with most posts first
}
}
})
Pagination: skip and take
Use skip and take for pagination.
take (limit):
const users = await prisma.user.findMany({
take: 10 // Get first 10 users
})
skip (offset):
const users = await prisma.user.findMany({
skip: 20, // Skip first 20 users
take: 10 // Then take 10 (users 21-30)
})
const users = await prisma.user.findMany({
distinct: ['age', 'isActive'],
select: {
age: true,
isActive: true
}
})
// Unique combinations of age and isActive
Null and Undefined Handling
Filter for null values:
// Find users with no name
const users = await prisma.user.findMany({
where: {
name: null
}
})
// Find users WITH a name
const users = await prisma.user.findMany({
where: {
name: {
not: null
}
}
})
Check if field is set:
const users = await prisma.user.findMany({
where: {
name: {
isSet: true // Name field has been set (not undefined)
}
}
})
Raw Queries
When Prisma's query builder isn't enough, use raw SQL.
Raw query with $queryRaw:
const users = await prisma.$queryRaw`
SELECT * FROM "User" WHERE age > 25
`
console.log(users) // Array of raw database rows
With parameters (safe from SQL injection):
const minAge = 25
const users = await prisma.$queryRaw`
SELECT * FROM "User" WHERE age > ${minAge}
`
// Prisma safely escapes the parameter
Type-safe raw queries:
import { Prisma } from '@prisma/client'
const users = await prisma.$queryRaw`
SELECT * FROM "User" WHERE age > 25
`
// users is typed as User[]
Unsafe raw query (use with caution):
const tableName = 'User'
const users = await prisma.$queryRawUnsafe(
`SELECT * FROM "${tableName}"`
)
// Only use $queryRawUnsafe when you need dynamic table/column names
async function getOrCreateUser(email: string) {
let user = await prisma.user.findUnique({
where: { email }
})
if (!user) {
user = await prisma.user.create({
data: { email, name: 'New User' }
})
}
return user
}
Fetch with default value:
const user = await prisma.user.findUnique({
where: { id: 1 }
}) ?? { id: 0, name: 'Guest', email: 'guest@example.com' }
// If user not found, use default object
Performance Tips
Select only needed fields:
// ✓ Good - only fetch what you need
const users = await prisma.user.findMany({
select: { id: true, email: true }
})
// ✗ Bad - fetches all fields
const users = await prisma.user.findMany()
Use pagination for large datasets:
// ✓ Good - paginate
const users = await prisma.user.findMany({
take: 100,
skip: 0
})
// ✗ Bad - fetches all records (could be millions)
const users = await prisma.user.findMany()
Use count instead of fetching when possible:
// ✓ Good - just count
const userCount = await prisma.user.count()
// ✗ Bad - fetches all records just to count
const users = await prisma.user.findMany()
const userCount = users.length
Avoid N+1 queries with include:
// ✗ Bad - N+1 query problem
const users = await prisma.user.findMany()
for (const user of users) {
const posts = await prisma.post.findMany({
where: { authorId: user.id }
})
console.log(user.name, posts.length)
}
// ✓ Good - single query with include
const users = await prisma.user.findMany({
include: {
posts: true
}
})
for (const user of users) {
console.log(user.name, user.posts.length)
}
Use indexes for frequently filtered fields:
// Add to schema
model User {
email String @unique
age Int
@@index([age]) // Index for frequent age queries
}
Error Handling
Handle not found:
const user = await prisma.user.findUnique({
where: { id: 9999 }
})
if (!user) {
throw new Error('User not found')
}
// Or use findUniqueOrThrow
const user = await prisma.user.findUniqueOrThrow({
where: { id: 9999 }
})
// Automatically throws if not found
await prisma.MODEL_NAME.create({
data: {
// Field assignments go here
},
select: {
// Optional: choose which fields to return
},
include: {
// Optional: include related data in response
}
})
Only data is required. select and include are optional.
The data object contains field assignments using this pattern:
data: {
fieldName: value, // Set literal value
fieldName: { ... }, // Set with operation or relation
relationName: { ... } // Create/connect related records
}
Example schema for this chapter:
model User {
id Int @id @default(autoincrement())
email String @unique
name String
age Int?
role Role @default(USER)
isActive Boolean @default(true)
createdAt DateTime @default(now())
profile Profile?
posts Post[]
}
model Profile {
id Int @id @default(autoincrement())
bio String
userId Int @unique
user User @relation(fields: [userId], references: [id])
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
authorId Int
author User @relation(fields: [authorId], references: [id])
tags Tag[]
}
model Tag {
id Int @id @default(autoincrement())
name String @unique
posts Post[]
}
enum Role {
USER
ADMIN
MODERATOR
}
Basic Field Assignment Syntax
Rule: Required fields (no ? or @default) MUST be provided.
Field assignment patterns:
Field Type in Schema
Required in create()?
Syntax
name String
Yes
name: "Alice"
age Int?
No (optional)
age: 25 or omit
isActive Boolean @default(true)
No (has default)
isActive: false or omit
id Int @id @default(autoincrement())
No (auto-generated)
Cannot set (Prisma generates it)
createdAt DateTime @default(now())
No (has default)
createdAt: new Date() or omit
Examples of field assignments:
// String
name: "Alice"
// Number
age: 25
// Boolean
isActive: true
// Date
createdAt: new Date()
createdAt: new Date("2024-01-01")
// Enum
role: "ADMIN" // or Role.ADMIN if imported
// Null (only for optional fields)
age: null
// Array (PostgreSQL/MongoDB)
tags: ["javascript", "typescript"]
Rule: You CANNOT set fields that have @default(autoincrement()) or @updatedAt.
// ✗ Error - cannot set auto-increment ID
data: {
id: 5, // Error!
name: "Alice"
}
// ✓ Correct - let Prisma generate ID
data: {
name: "Alice"
}
Complete Syntax: Required vs Optional Fields
When creating a record, you must understand which fields are required:
Required fields: Fields without ?, without @default(), and not auto-generated.
model User {
id Int @id @default(autoincrement()) // Not required (auto)
email String @unique // REQUIRED
name String // REQUIRED
age Int? // Not required (optional)
role Role @default(USER) // Not required (has default)
}
When creating a record with relations, you have three operations:
Operation
When to Use
Syntax Pattern
create
Create new related record(s)
relationName: { create: { ... } }
connect
Link to existing record(s)
relationName: { connect: { id: 1 } }
connectOrCreate
Connect if exists, create if not
relationName: { connectOrCreate: { ... } }
Important: Relation operations apply to the relation field (e.g., author, posts), NOT the foreign key field (e.g., authorId).
One-to-Many: Creating with Related Records
Scenario: User (one) has many Posts (many).
Pattern 1: Create user with new posts
// Syntax structure:
await prisma.user.create({
data: {
// User fields
email: "...",
name: "...",
// Relation field (array for one-to-many)
posts: {
create: [
{ /* post 1 data */ },
{ /* post 2 data */ }
]
}
}
})
// Actual example:
await prisma.user.create({
data: {
email: "alice@example.com",
name: "Alice",
posts: {
create: [
{ title: "First Post", content: "Hello" },
{ title: "Second Post", content: "World" }
]
}
}
})
// Creates: 1 user + 2 posts
// Posts automatically get authorId set to new user's id
Pattern 2: Create single related record (not array)
// For single record, use object instead of array:
posts: {
create: { title: "First Post", content: "Hello" }
}
// Both work, but single object is cleaner for one record
Pattern 3: Connect to existing posts
await prisma.user.create({
data: {
email: "alice@example.com",
name: "Alice",
posts: {
connect: [
{ id: 1 }, // Connect to post with id 1
{ id: 2 } // Connect to post with id 2
]
}
}
})
// Prerequisite: Posts with id 1 and 2 must already exist
// Changes their authorId to point to new user
Pattern 4: Mix create and connect
await prisma.user.create({
data: {
email: "alice@example.com",
name: "Alice",
posts: {
create: [
{ title: "New Post", content: "Fresh content" }
],
connect: [
{ id: 5 } // Also connect existing post
]
}
}
})
// Creates 1 new post AND connects to existing post 5
Rule: For one-to-many, the relation field accepts arrays ([]).
One-to-One: Creating with Single Related Record
Scenario: User (one) has one Profile (one).
Pattern 1: Create user with new profile
// Syntax structure:
await prisma.user.create({
data: {
// User fields
email: "...",
name: "...",
// Relation field (single object for one-to-one)
profile: {
create: {
/* profile data */
}
}
}
})
// Actual example:
await prisma.user.create({
data: {
email: "alice@example.com",
name: "Alice",
profile: {
create: {
bio: "Software developer"
}
}
}
})
// Creates: 1 user + 1 profile
// Profile automatically gets userId set to new user's id
Pattern 2: Connect to existing profile
await prisma.user.create({
data: {
email: "alice@example.com",
name: "Alice",
profile: {
connect: { id: 3 } // Single object, not array
}
}
})
// Prerequisite: Profile with id 3 must exist
// Sets profile's userId to new user's id
Rule: For one-to-one, the relation field accepts single object, NOT arrays.
When creating from the "many" side (e.g., Post), you can set the foreign key OR use relation syntax.
Method 1: Set foreign key directly
await prisma.post.create({
data: {
title: "My Post",
content: "Content here",
authorId: 1 // Set foreign key directly
}
})
// Prerequisite: User with id 1 must exist
Method 2: Use connect
await prisma.post.create({
data: {
title: "My Post",
content: "Content here",
author: {
connect: { id: 1 } // Connect to user via relation
}
}
})
// Same result as Method 1, but uses relation syntax
Method 3: Create new parent
await prisma.post.create({
data: {
title: "My Post",
content: "Content here",
author: {
create: {
email: "alice@example.com",
name: "Alice"
}
}
}
})
// Creates both post AND user
Rule: You can EITHER set authorId OR use author: { connect/create }, but NOT both.
// ✗ Error - conflicting operations
data: {
title: "My Post",
authorId: 1, // Setting foreign key
author: {
connect: { id: 1 } // Also trying to connect via relation
}
}
// ✓ Choose one approach
data: {
title: "My Post",
authorId: 1 // Either this
}
// Or
data: {
title: "My Post",
author: { connect: { id: 1 } } // Or this
}
const result = await prisma.user.createMany({
data: [...]
})
console.log(result)
// { count: 3 }
// Note: createMany does NOT return the created records
// It only returns count of records created
Skip duplicates:
await prisma.user.createMany({
data: [
{ email: "alice@example.com", name: "Alice" },
{ email: "alice@example.com", name: "Alice2" }, // Duplicate email
{ email: "bob@example.com", name: "Bob" }
],
skipDuplicates: true // Skip the duplicate instead of erroring
})
// Creates Alice and Bob, skips duplicate Alice
// Returns: { count: 2 }
Important limitations of createMany:
Limitation
Explanation
No nested creates
Cannot use create, connect, etc. in relations
No return of created records
Only returns { count: n }, not the actual records
No select/include
Cannot choose fields to return (because nothing is returned)
// Use createMany when:
// - Creating many simple records
// - No relations needed
// - Performance is important (single query)
await prisma.user.createMany({
data: users // Array of user data
})
// Use multiple create when:
// - Need to create with relations
// - Need to get created records back
// - Need select/include
for (const userData of users) {
await prisma.user.create({
data: {
...userData,
posts: { create: [...] }
}
})
}
Selecting Return Data
By default, create() returns the entire created record.
// If user exists: update it
// If user doesn't exist: create it
const user = await prisma.user.upsert({
where: {
email: "alice@example.com"
},
update: {
name: "Alice Updated"
},
create: {
email: "alice@example.com",
name: "Alice"
}
})
// No error, handles both cases
Understanding Data Validation
Prisma validates data types before sending to database.
Type validation:
// ✗ Error - wrong type
await prisma.user.create({
data: {
email: "alice@example.com",
name: "Alice",
age: "25" // Error! Should be number, not string
}
})
// ✓ Correct
await prisma.user.create({
data: {
email: "alice@example.com",
name: "Alice",
age: 25 // Number
}
})
Required field validation:
// ✗ Error - missing required field
await prisma.user.create({
data: {
email: "alice@example.com"
// Missing required field: name
}
})
// Error: "Argument `name` is missing"
Enum validation:
// ✗ Error - invalid enum value
await prisma.user.create({
data: {
email: "alice@example.com",
name: "Alice",
role: "SUPERADMIN" // Not in enum
}
})
// ✓ Correct - valid enum value
await prisma.user.create({
data: {
email: "alice@example.com",
name: "Alice",
role: "ADMIN" // Valid: USER | ADMIN | MODERATOR
}
})
Validation happens at TypeScript level (if using TypeScript) AND at Prisma level.
Working with JSON Fields
If you have a Json field in your schema, you can store any valid JSON.
model User {
id Int @id @default(autoincrement())
metadata Json?
}
await prisma.MODEL_NAME.update({
where: {
// How to find the record (must use unique field)
},
data: {
// What to update
},
select: {
// Optional: which fields to return
},
include: {
// Optional: include related data in response
}
})
Only where and data are required.
Example schema for this chapter:
model User {
id Int @id @default(autoincrement())
email String @unique
name String
age Int?
role Role @default(USER)
isActive Boolean @default(true)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
profile Profile?
posts Post[]
}
model Profile {
id Int @id @default(autoincrement())
bio String
userId Int @unique
user User @relation(fields: [userId], references: [id])
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
views Int @default(0)
authorId Int
author User @relation(fields: [authorId], references: [id])
tags Tag[]
}
model Tag {
id Int @id @default(autoincrement())
name String @unique
posts Post[]
}
enum Role {
USER
ADMIN
MODERATOR
}
The where Clause: Finding Records to Update
Rule: The where clause in update() MUST use a unique field.
Valid unique fields:
Fields marked with @id
Fields marked with @unique
Combinations marked with @@unique
Update by ID:
await prisma.user.update({
where: {
id: 1 // id is unique (@id)
},
data: {
name: "Alice Updated"
}
})
await prisma.user.update({
where: { id: 1 },
data: {
profile: {
connect: { id: 5 } // Single object, not array
}
}
})
// Profile 5 now has userId = 1
Disconnect profile:
await prisma.user.update({
where: { id: 1 },
data: {
profile: {
disconnect: true // For one-to-one, just true
}
}
})
// Profile's userId is set to null (if optional)
Create new profile:
await prisma.user.update({
where: { id: 1 },
data: {
profile: {
create: {
bio: "New bio"
}
}
}
})
// Creates new profile connected to user
Delete existing profile:
await prisma.user.update({
where: { id: 1 },
data: {
profile: {
delete: true // For one-to-one, just true
}
}
})
// Deletes the user's profile from database
Update existing profile:
await prisma.user.update({
where: { id: 1 },
data: {
profile: {
update: {
bio: "Updated bio"
}
}
}
})
// Updates the user's existing profile
// Throws error if user has no profile
Upsert profile (update if exists, create if not):
await prisma.user.update({
where: { id: 1 },
data: {
profile: {
upsert: {
create: {
bio: "New bio"
},
update: {
bio: "Updated bio"
}
}
}
}
})
// If user has profile: updates bio
// If user has no profile: creates one
Rule: One-to-one uses single objects, one-to-many uses arrays.
// Post currently has tags: [1, 2, 3]
await prisma.post.update({
where: { id: 1 },
data: {
tags: {
set: [
{ id: 4 },
{ id: 5 }
]
}
}
})
// Post now has tags: [4, 5]
// Tags 1, 2, 3 are disconnected from this post
Create and connect new tags:
await prisma.post.update({
where: { id: 1 },
data: {
tags: {
create: [
{ name: "newtag1" },
{ name: "newtag2" }
]
}
}
})
// Creates 2 new tags and connects them to post
Connect by unique field:
await prisma.post.update({
where: { id: 1 },
data: {
tags: {
connect: [
{ name: "javascript" }, // Connect by unique name
{ name: "typescript" }
]
}
}
})
// Connects to tags with these names (must exist)
ConnectOrCreate tags:
await prisma.post.update({
where: { id: 1 },
data: {
tags: {
connectOrCreate: [
{
where: { name: "javascript" },
create: { name: "javascript" }
},
{
where: { name: "newTag" },
create: { name: "newTag" }
}
]
}
}
})
// Connects to "javascript" if exists, creates if not
// Connects to "newTag" if exists, creates if not
Updating Nested Relations
You can update relations of relations (nested updates).
Update user's posts' tags:
await prisma.user.update({
where: { id: 1 },
data: {
posts: {
update: [
{
where: { id: 5 },
data: {
title: "Updated Title",
tags: {
connect: [{ id: 1 }]
}
}
}
]
}
}
})
// Updates post 5's title
// Connects tag 1 to post 5
upsert() updates record if it exists, creates if it doesn't.
Syntax structure:
await prisma.MODEL_NAME.upsert({
where: {
// How to find record (must use unique field)
},
update: {
// What to update if record exists
},
create: {
// What to create if record doesn't exist
}
})
Example:
const user = await prisma.user.upsert({
where: {
email: "alice@example.com"
},
update: {
name: "Alice Updated"
},
create: {
email: "alice@example.com",
name: "Alice New"
}
})
// If user with this email exists: updates name to "Alice Updated"
// If user doesn't exist: creates with name "Alice New"
await prisma.MODEL.updateMany({
where: { anyField: value }, // Can use any field
data: { field: value } // No relations allowed
})
// Returns: { count: n }
Upsert:
await prisma.MODEL.upsert({
where: { uniqueField: value },
update: { field: value },
create: { field: value }
})
Deleting Data with Prisma
Understanding Delete Syntax Structure
Prisma provides two main delete methods:
Method
Deletes
Returns
delete()
Single record (must exist)
Deleted record or throws error
deleteMany()
Multiple records (zero or more)
{ count: n }
Basic delete() structure:
await prisma.MODEL_NAME.delete({
where: {
// How to find the record (must use unique field)
},
select: {
// Optional: which fields to return from deleted record
},
include: {
// Optional: include related data in response
}
})
Only where is required.
Example schema for this chapter:
model User {
id Int @id @default(autoincrement())
email String @unique
name String
createdAt DateTime @default(now())
profile Profile?
posts Post[]
}
model Profile {
id Int @id @default(autoincrement())
bio String
userId Int @unique
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
authorId Int
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
tags Tag[]
}
model Tag {
id Int @id @default(autoincrement())
name String @unique
posts Post[]
}
The where Clause: Finding Records to Delete
Rule: The where clause in delete() MUST use a unique field.
Delete by ID:
await prisma.user.delete({
where: {
id: 1 // id is unique (@id)
}
})
// After delete, the record is gone from database
// But you get back what was deleted
const deleted = await prisma.user.delete({
where: { id: 1 }
})
// Can use the deleted data
console.log(`Deleted user: ${deleted.name}`)
What happens to related records when you delete depends on onDelete in schema.
Available onDelete actions:
Action
Behavior
Cascade
Delete related records automatically
SetNull
Set foreign key to NULL in related records
Restrict
Prevent deletion if related records exist
NoAction
Database decides (usually same as Restrict)
SetDefault
Set foreign key to default value
Cascade example:
model User {
id Int @id
posts Post[]
}
model Post {
id Int @id
authorId Int
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
}
// Delete user
await prisma.user.delete({
where: { id: 1 }
})
// Result: User is deleted
// All posts with authorId = 1 are ALSO deleted automatically
SetNull example:
model Post {
id Int @id
authorId Int? // Must be optional for SetNull
author User? @relation(fields: [authorId], references: [id], onDelete: SetNull)
}
// Delete user
await prisma.user.delete({
where: { id: 1 }
})
// Result: User is deleted
// Posts with authorId = 1 now have authorId = null
Restrict example:
model Post {
id Int @id
authorId Int
author User @relation(fields: [authorId], references: [id], onDelete: Restrict)
}
// Try to delete user who has posts
await prisma.user.delete({
where: { id: 1 }
})
// Throws error: Cannot delete user with existing posts
// Code: P2003
// Message: "Foreign key constraint failed"
Default behavior (no onDelete specified):
If onDelete not specified, behavior depends on database
Most databases default to Restrict or NoAction
Always specify onDelete explicitly for clarity
Deleting with One-to-Many Relations
Scenario: User has many Posts.
Delete parent (with Cascade):
// Schema has: onDelete: Cascade
await prisma.user.delete({
where: { id: 1 }
})
// Deletes user + all their posts automatically
Delete parent (with SetNull):
// Schema has: onDelete: SetNull
// authorId must be Int? (optional)
await prisma.user.delete({
where: { id: 1 }
})
// Deletes user
// Posts now have authorId = null
Delete parent (with Restrict):
// Schema has: onDelete: Restrict
// Must delete posts first
await prisma.post.deleteMany({
where: { authorId: 1 }
})
// Then delete user
await prisma.user.delete({
where: { id: 1 }
})
// Or handle error:
try {
await prisma.user.delete({
where: { id: 1 }
})
} catch (error) {
if (error.code === 'P2003') {
console.log('Cannot delete: user has posts')
}
}
Delete child record:
await prisma.post.delete({
where: { id: 1 }
})
// Deletes only the post
// User remains unchanged
Delete all children:
await prisma.post.deleteMany({
where: { authorId: 1 }
})
// Deletes all posts by user 1
// User remains unchanged
Deleting with One-to-One Relations
Scenario: User has one Profile.
Delete parent (with Cascade):
model Profile {
userId Int @unique
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}
await prisma.user.delete({
where: { id: 1 }
})
// Deletes user + their profile automatically
Delete parent (with SetNull):
model Profile {
userId Int? @unique // Must be optional
user User? @relation(fields: [userId], references: [id], onDelete: SetNull)
}
model Post {
postTags PostTag[]
}
model Tag {
postTags PostTag[]
}
model PostTag {
postId Int
tagId Int
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
tag Tag @relation(fields: [tagId], references: [id], onDelete: Cascade)
@@id([postId, tagId])
}
// Delete post
await prisma.post.delete({
where: { id: 1 }
})
// PostTag entries with postId = 1 are deleted (cascade)
// Tags remain
// Delete tag
await prisma.tag.delete({
where: { id: 1 }
})
// PostTag entries with tagId = 1 are deleted (cascade)
// Posts remain
Many-to-many deletions never delete the other side automatically:
Deleting a post never deletes tags
Deleting a tag never deletes posts
Only the join table entries are removed
deleteMany: Deleting Multiple Records
deleteMany() deletes all records matching criteria.
Syntax structure:
await prisma.MODEL_NAME.deleteMany({
where: {
// Filter criteria (can use ANY fields, not just unique)
}
})
await prisma.post.deleteMany({
where: {
published: false,
createdAt: {
lt: new Date('2024-01-01')
}
}
})
// Deletes all unpublished posts created before 2024
Delete with comparison operators:
await prisma.post.deleteMany({
where: {
views: {
lt: 10 // Less than 10 views
}
}
})
// Deletes all posts with few views
Delete all records (no where clause):
await prisma.user.deleteMany()
// Deletes ALL users (use with extreme caution!)
// Same as: deleteMany({ where: {} })
Delete returns count, not records:
const result = await prisma.user.deleteMany({
where: { age: { lt: 18 } }
})
console.log(result)
// { count: 12 }
// Cannot do select or include with deleteMany
// Cannot get the deleted records back
No error if nothing deleted:
const result = await prisma.user.deleteMany({
where: { age: 200 } // No users match
})
console.log(result)
// { count: 0 }
// No error thrown (unlike delete())
Delete by relation filter:
// Delete all users who have no posts
await prisma.user.deleteMany({
where: {
posts: {
none: {}
}
}
})
// Delete all posts by specific author
await prisma.post.deleteMany({
where: {
author: {
email: "alice@example.com"
}
}
})
Deleting Related Records First
When onDelete: Restrict, you must delete related records first.
// This doesn't exist in Prisma - you can't do:
// await prisma.user.delete({
// where: { id: 1 },
// data: {
// posts: { deleteMany: {} }
// }
// })
// Instead use Cascade or manual deletion
Soft Delete Pattern
Soft delete = mark as deleted instead of actually deleting.
Add deletedAt field to schema:
model User {
id Int @id @default(autoincrement())
email String @unique
name String
deletedAt DateTime?
}
Soft delete implementation:
// "Delete" user
await prisma.user.update({
where: { id: 1 },
data: {
deletedAt: new Date()
}
})
// User is marked as deleted but still in database
Filter out soft-deleted records:
// Get active users only
const activeUsers = await prisma.user.findMany({
where: {
deletedAt: null
}
})
// Get deleted users
const deletedUsers = await prisma.user.findMany({
where: {
deletedAt: {
not: null
}
}
})
Restore soft-deleted record:
await prisma.user.update({
where: { id: 1 },
data: {
deletedAt: null
}
})
// User is restored
Hard delete soft-deleted records:
// Permanently delete records deleted over 30 days ago
const thirtyDaysAgo = new Date()
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30)
await prisma.user.deleteMany({
where: {
deletedAt: {
lt: thirtyDaysAgo
}
}
})
Global soft delete with middleware:
// Automatically filter soft-deleted records
prisma.$use(async (params, next) => {
// Intercept findMany, findFirst, findUnique
if (params.action === 'findMany' || params.action === 'findFirst') {
// Add deletedAt: null filter
params.args.where = {
...params.args.where,
deletedAt: null
}
}
// Intercept delete and convert to update
if (params.action === 'delete') {
params.action = 'update'
params.args.data = { deletedAt: new Date() }
}
if (params.action === 'deleteMany') {
params.action = 'updateMany'
params.args.data = { deletedAt: new Date() }
}
return next(params)
})
Error Handling
Record not found:
try {
await prisma.user.delete({
where: { id: 9999 }
})
} catch (error) {
if (error.code === 'P2025') {
console.log('Record to delete does not exist')
}
}
// Alternative: check first
const user = await prisma.user.findUnique({ where: { id: 9999 } })
if (user) {
await prisma.user.delete({ where: { id: 9999 } })
} else {
console.log('User not found')
}
Foreign key constraint (Restrict):
try {
await prisma.user.delete({
where: { id: 1 }
})
} catch (error) {
if (error.code === 'P2003') {
console.log('Cannot delete: user has related records')
// Could be posts, profile, etc.
}
}
// Delete old records in batches
async function deleteOldRecords() {
const batchSize = 1000
let deletedCount = 0
while (true) {
const result = await prisma.post.deleteMany({
where: {
createdAt: {
lt: new Date('2020-01-01')
}
},
take: batchSize
})
deletedCount += result.count
if (result.count < batchSize) {
break // No more records to delete
}
// Optional: add delay to reduce database load
await new Promise(resolve => setTimeout(resolve, 100))
}
console.log(`Deleted ${deletedCount} records`)
}
Delete with progress tracking:
async function deleteWithProgress() {
// Count total first
const total = await prisma.post.count({
where: { published: false }
})
let deleted = 0
const batchSize = 100
while (deleted < total) {
const result = await prisma.post.deleteMany({
where: { published: false },
take: batchSize
})
deleted += result.count
console.log(`Progress: ${deleted}/${total}`)
if (result.count === 0) break
}
}
After creating or modifying models in schema.prisma
After pulling changes from version control
When Prisma Client types seem out of sync
Automatically runs after prisma migrate dev
Automatically runs after prisma db push
Watch mode (regenerate on schema changes):
npx prisma generate --watch
# Watches schema.prisma and regenerates on changes
# Useful during development
Example workflow:
# 1. Add a model to schema.prisma
# model User {
# id Int @id @default(autoincrement())
# email String @unique
# }
# 2. Generate client
npx prisma generate
# 3. Now you can use it in code
# const user = await prisma.user.create({ ... })
npx prisma generate
# Generates to src/generated/client instead of node_modules
Common issues:
# Error: "Cannot find module '@prisma/client'"
# Solution: Run npx prisma generate
# Error: "Type 'X' does not exist on PrismaClient"
# Solution: You added a model but didn't regenerate
# Run: npx prisma generate
prisma migrate dev: Development Migrations
Purpose: Create and apply migrations during development.
Basic syntax:
npx prisma migrate dev
What it does (in order):
Detects schema changes
Creates a new migration file (SQL)
Prompts you to name the migration
Applies migration to database
Runs prisma generate automatically
Runs seed script (if configured)
Example workflow:
# 1. Add User model to schema.prisma
# 2. Run migrate dev
npx prisma migrate dev
# Output:
# Prisma schema loaded from prisma/schema.prisma
# Datasource "db": PostgreSQL database "mydb"
#
# ✔ Enter a name for the new migration: … add_user_model
#
# The following migration(s) have been created and applied:
#
# migrations/
# └─ 20250115120000_add_user_model/
# └─ migration.sql
#
# Your database is now in sync with your schema.
#
# ✔ Generated Prisma Client
-- CreateTable
CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"email" TEXT NOT NULL,
"name" TEXT NOT NULL,
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);
-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
Name the migration:
# Interactive (prompts for name)
npx prisma migrate dev
# With name flag
npx prisma migrate dev --name add_user_model
Skip seed:
npx prisma migrate dev --skip-seed
Skip generate:
npx prisma migrate dev --skip-generate
Create migration without applying:
npx prisma migrate dev --create-only
# Creates migration file but doesn't apply it
# Useful for reviewing/editing SQL before applying
When to use:
Development environment only
After modifying schema.prisma
Creates migration history
Safe for team collaboration (commit migrations to git)
Do NOT use in production - use prisma migrate deploy instead.
prisma migrate deploy: Production Migrations
Purpose: Apply pending migrations in production/staging.
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "production_db"
2 migrations found in prisma/migrations
Applying migration `20250115120000_add_user_model`
Applying migration `20240116080000_add_posts`
The following migrations have been applied:
migrations/
└─ 20250115120000_add_user_model/
└─ migration.sql
└─ 20240116080000_add_posts/
└─ migration.sql
All migrations have been successfully applied.
Docker example:
FROM node:18
WORKDIR /app
COPY package*.json ./
RUN npm ci
COPY prisma ./prisma
COPY . .
# Run migrations on container start
CMD npx prisma migrate deploy && npm start
When to use:
Production deployments
Staging environments
CI/CD pipelines
After pulling migrations from git
prisma migrate reset: Reset Database
Purpose: Drop database, recreate it, and apply all migrations.
Basic syntax:
npx prisma migrate reset
What it does (in order):
Drops the database
Creates a new database
Applies all migrations from prisma/migrations/
Runs prisma generate
Runs seed script (if configured)
⚠️ WARNING: Destroys all data!
# Prompts for confirmation:
# ? Are you sure you want to reset your database?
# All data will be lost. › (y/N)
Skip confirmation:
npx prisma migrate reset --force
# Dangerous! Resets without asking
# Use in automated scripts only
Skip seed:
npx prisma migrate reset --skip-seed
Skip generate:
npx prisma migrate reset --skip-generate
When to use:
Development environment only
When database is in inconsistent state
To start fresh with clean data
After major schema changes
For testing purposes
NEVER use in production!
Example workflow:
# Made a mistake in migration, want to start over
# 1. Delete bad migration file
rm -rf prisma/migrations/20250115120000_bad_migration
# 2. Reset database
npx prisma migrate reset
# 3. Fix schema.prisma
# 4. Create correct migration
npx prisma migrate dev --name correct_migration
prisma migrate status: Check Migration Status
Purpose: Check which migrations have been applied.
Basic syntax:
npx prisma migrate status
Example output (all applied):
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "mydb"
Status
- 3 migrations applied
- No pending migrations
Database schema is up to date!
Status
- 3 migrations applied
- Database schema drift detected
The database schema is not in sync with your Prisma schema.
Run `npx prisma migrate dev` to update your database.
When to use:
Before deploying to production
Debugging migration issues
Checking if database is in sync
In CI/CD to verify migrations
CI/CD example:
# GitHub Actions
- name: Check migration status
run: npx prisma migrate status
- name: Apply migrations if needed
run: npx prisma migrate deploy
prisma db push: Push Schema Without Migrations
Purpose: Sync database with schema without creating migration files.
Basic syntax:
npx prisma db push
What it does:
Reads schema.prisma
Compares with current database schema
Applies changes directly to database
Does NOT create migration files
Runs prisma generate automatically
Difference from migrate dev:
Aspect
migrate dev
db push
Creates migrations
Yes
No
Migration history
Yes
No
Can rollback
Yes (delete migration)
No
Production ready
Yes
No
Speed
Slower
Faster
Best for
Production workflow
Prototyping
Example workflow:
# 1. Add model to schema.prisma
model User {
id Int @id @default(autoincrement())
email String @unique
}
# 2. Push to database
npx prisma db push
# Output:
# The following changes will be applied:
#
# [+] Table: User
# [+] Column: User.id
# [+] Column: User.email
# [+] Index: User.email (unique)
#
# ✔ Database synchronized with Prisma schema.
# ✔ Generated Prisma Client
Accept data loss:
# If schema change would lose data, Prisma warns you
npx prisma db push --accept-data-loss
# Forces changes even if data will be lost
Skip generate:
npx prisma db push --skip-generate
Force reset:
npx prisma db push --force-reset
# Drops and recreates database before pushing
# Like migrate reset but without migrations
When to use:
Rapid prototyping
Early development (schema still changing frequently)
Personal projects
When you don't need migration history
When NOT to use:
Production databases
Team projects (others need migrations)
When you need to rollback changes
When you need migration history
prisma db pull: Introspect Existing Database
Purpose: Generate Prisma schema from existing database.
# 1. You have existing database with tables
# 2. Initialize Prisma
npx prisma init
# 3. Update DATABASE_URL in .env
# 4. Pull schema from database
npx prisma db pull
# 5. schema.prisma now has your models!
# 6. Generate client
npx prisma generate
-- users table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL
);
-- posts table
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
user_id INTEGER REFERENCES users(id)
);
After db pull:
// schema.prisma (generated)
model users {
id Int @id @default(autoincrement())
email String @unique @db.VarChar(255)
name String @db.VarChar(255)
posts posts[]
}
model posts {
id Int @id @default(autoincrement())
title String @db.VarChar(255)
user_id Int
users users @relation(fields: [user_id], references: [id])
}
Force overwrite:
npx prisma db pull --force
# Overwrites schema.prisma without asking
# Loses any manual changes you made
Print to stdout (don't modify file):
npx prisma db pull --print
# Shows what would be generated without writing to file
After pulling, you typically:
Rename models (e.g., users → User)
Rename fields (e.g., user_id → userId)
Add @@map() to preserve database names
// Cleaned up version
model User {
id Int @id @default(autoincrement())
email String @unique @db.VarChar(255)
name String @db.VarChar(255)
posts Post[]
@@map("users")
}
model Post {
id Int @id @default(autoincrement())
title String @db.VarChar(255)
userId Int @map("user_id")
user User @relation(fields: [userId], references: [id])
@@map("posts")
}
# 1. Initialize Prisma
npx prisma init
# 2. Update DATABASE_URL in .env
# 3. Add models to schema.prisma
# 4. Create first migration
npx prisma migrate dev --name init
# 5. Open Studio to verify
npx prisma studio
Adding new model:
# 1. Add model to schema.prisma
# 2. Create migration
npx prisma migrate dev --name add_posts
# 3. Use in code (client auto-generated)
# 1. Add/modify models in schema.prisma
# 2. Push directly to database
npx prisma db push
# 3. Test in Studio
npx prisma studio
# 4. Repeat until satisfied
# 5. Later, create proper migrations
npx prisma migrate dev --name finalized_schema