// prisma/schema.prisma
datasource db {
provider = “postgresql”  // or mysql, sqlite, sqlserver, mongodb
url      = env(“DATABASE_URL”)
}
generator client {
provider = “prisma-client-js”
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?                    // Optional
  role      Role     @default(USER)    // Enum default
  posts     Post[]                     // Relation
  profile   Profile?                   // Optional 1:1
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
@@index([email])                     // Index
@@map(“users”)                       // Table name
}
enum Role {
USER
ADMIN
MODERATOR
}

Field types: String, Int, BigInt, Float, Decimal, Boolean, DateTime, Json, Bytes.

// One-to-many
model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}
model Post {
id       Int  @id @default(autoincrement())
author   User @relation(fields: [authorId], references: [id])
authorId Int
}
// One-to-one
model User {
id      Int      @id @default(autoincrement())
profile Profile?
}
model Profile {
id     Int  @id @default(autoincrement())
user   User @relation(fields: [userId], references: [id])
userId Int  @unique
}
// Many-to-many (implicit)
model Post {
id   Int   @id @default(autoincrement())
tags Tag[]
}
model Tag {
id    Int    @id @default(autoincrement())
posts Post[]
}

// Create one
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
  },
});
// Create with relation
const user = await prisma.user.create({
data: {
email: ‘alice@example.com’,
posts: {
create: [
{ title: ‘First post’ },
{ title: ‘Second post’ },
],
},
},
include: { posts: true },
});
// Create many
const count = await prisma.user.createMany({
data: [
{ email: ‘bob@example.com’, name: ‘Bob’ },
{ email: ‘carol@example.com’, name: ‘Carol’ },
],
skipDuplicates: true,
});

// Find unique
const user = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
});
// Find first matching
const user = await prisma.user.findFirst({
where: { role: ‘ADMIN’ },
});
// Find many
const users = await prisma.user.findMany({
where: {
role: ‘USER’,
createdAt: { gte: new Date(‘2026-01-01’) },
},
orderBy: { createdAt: ‘desc’ },
take: 10,
skip: 0,
});
// Include relations
const user = await prisma.user.findUnique({
where: { id: 1 },
include: {
posts: { where: { published: true } },
profile: true,
},
});
// Select specific fields
const user = await prisma.user.findUnique({
where: { id: 1 },
select: { email: true, name: true },
});

// Update one
const user = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Updated' },
});
// Update many
const count = await prisma.user.updateMany({
where: { role: ‘USER’ },
data: { role: ‘MEMBER’ },
});
// Upsert (create or update)
const user = await prisma.user.upsert({
where: { email: ‘alice@example.com’ },
update: { name: ‘Alice Updated’ },
create: { email: ‘alice@example.com’, name: ‘Alice’ },
});
// Increment / decrement
await prisma.post.update({
where: { id: 1 },
data: { views: { increment: 1 } },
});

// Delete one
await prisma.user.delete({
  where: { id: 1 },
});
// Delete many
await prisma.user.deleteMany({
where: { role: ‘INACTIVE’ },
});
// Delete all
await prisma.user.deleteMany();

// Comparison
where: { age: { gt: 18 } }       // greater than
where: { age: { gte: 18 } }      // greater or equal
where: { age: { lt: 65 } }       // less than
where: { age: { lte: 65 } }      // less or equal
where: { name: { not: 'Bob' } }
// String
where: { name: { contains: ‘ali’ } }
where: { name: { startsWith: ‘A’ } }
where: { name: { endsWith: ‘ce’ } }
where: { email: { contains: ‘gmail’, mode: ‘insensitive’ } }
// List
where: { id: { in: [1, 2, 3] } }
where: { id: { notIn: [4, 5] } }
// Null
where: { name: null }
where: { name: { not: null } }
// AND / OR / NOT
where: {
AND: [
{ role: ‘ADMIN’ },
{ createdAt: { gte: new Date(‘2026-01-01’) } },
],
}
where: {
OR: [
{ email: { contains: ‘gmail’ } },
{ email: { contains: ‘outlook’ } },
],
}

# Create and apply migration
npx prisma migrate dev --name init
Apply migrations in production
npx prisma migrate deploy
Reset database (delete all data + re-migrate)
npx prisma migrate reset
Generate Prisma Client (after schema changes)
npx prisma generate
Push schema without migration (prototyping)
npx prisma db push
Open Prisma Studio (GUI)
npx prisma studio
Seed database
npx prisma db seed
Format schema file
npx prisma format

// Transaction (all or nothing)
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'a@b.com' } }),
  prisma.post.create({ data: { title: 'Hi', authorId: 1 } }),
]);
// Interactive transaction
await prisma.$transaction(async (tx) => {
const user = await tx.user.findUnique({ where: { id: 1 } });
if (user.balance < 100) throw new Error(‘Insufficient funds’);
await tx.user.update({
where: { id: 1 },
data: { balance: { decrement: 100 } },
});
});
// Raw SQL
const users = await prisma.$queryRaw  SELECT * FROM users WHERE email = ${email};
await prisma.$executeRaw  UPDATE users SET role = 'ADMIN' WHERE id = ${id};

// Count
const count = await prisma.user.count({
  where: { role: 'ADMIN' },
});
// Aggregate
const stats = await prisma.post.aggregate({
_avg: { views: true },
_sum: { views: true },
_max: { views: true },
_count: true,
});
// Group by
const byRole = await prisma.user.groupBy({
by: [‘role’],
_count: true,
orderBy: { _count: { role: ‘desc’ } },
});