Prisma ORM

Why Prisma?#

ORMs (Object-Relational Mappers) bridge the gap between your code and your database. Instead of writing raw SQL, you work with objects and methods. Prisma takes this further with exceptional TypeScript integration.

What makes Prisma different:

Feature	Traditional ORMs	Prisma
Type safety	Manual types, often drift	Auto-generated from schema
Schema	Code-first or annotation-based	Dedicated schema file
Autocomplete	Limited	Full IntelliSense
Migrations	Often clunky	Built-in, version-controlled
Query building	Method chaining	Intuitive object syntax

When Prisma shines:

TypeScript projects where type safety matters
Teams that want readable, maintainable database code
Projects needing robust migration workflows
When developer experience is a priority

When to consider alternatives:

Complex raw SQL requirements
NoSQL databases (Prisma supports SQL databases)
Very high-performance needs with micro-optimizations

Setup#

bash

npm install prisma @prisma/client
npx prisma init

This creates:

prisma/schema.prisma - Your data model definition
.env - Database connection string

Database URL format:

# PostgreSQL
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

# MySQL
DATABASE_URL="mysql://user:password@localhost:3306/mydb"

# SQLite (great for development)
DATABASE_URL="file:./dev.db"

Schema Definition#

The schema file is your single source of truth. It defines your data models, relationships, and database configuration:

prisma

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

datasource db {
  provider = "postgresql"  // postgresql, mysql, sqlite, sqlserver
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?                    // ? means nullable
  password  String
  role      Role     @default(USER)
  posts     Post[]                     // One-to-many relation
  profile   Profile?                   // One-to-one relation
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt        // Auto-updates on change

  @@index([email])                     // Index for faster lookups
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  avatar String?
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId Int    @unique                // Foreign key
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  Int                        // Foreign key
  tags      Tag[]                      // Many-to-many relation
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId])
  @@index([published])
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]                         // Many-to-many (implicit join table)
}

enum Role {
  USER
  ADMIN
}

Schema concepts explained:

@id - Primary key
@default() - Default value (can use functions like now(), autoincrement(), uuid())
@unique - Ensures no duplicates
@updatedAt - Automatically updates timestamp on any change
@relation - Defines how models connect
@@index - Creates database index for faster queries
? after type - Field is nullable/optional

Migrations#

Prisma migrations version-control your database schema. Every schema change creates a new migration file.

bash

# Development: Create migration from schema changes
npx prisma migrate dev --name init
# This: generates SQL, applies it, regenerates client

# Production: Apply pending migrations
npx prisma migrate deploy

# Reset database (DELETES ALL DATA - dev only!)
npx prisma migrate reset

# Generate client without migration
npx prisma generate

Migration workflow:

Edit schema.prisma
Run prisma migrate dev --name describe_change
Prisma generates SQL migration
Migration is applied to dev database
Client is regenerated with new types
Commit the migration file to git

Why this matters: Your database schema is now version-controlled alongside your code. Rolling back? Checkout the old commit and migrate.

Prisma Client#

The client is your database interface. It's auto-generated from your schema:

javascript

// src/lib/prisma.js
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis;

// Prevent multiple instances in development (hot reloading)
export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'error', 'warn']
    : ['error'],
});

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

Why the globalThis pattern? In development with hot reloading, each reload creates a new Prisma instance, potentially exhausting database connections. This pattern reuses the existing instance.

CRUD Operations#

Create#

javascript

// Create single record
const user = await prisma.user.create({
  data: {
    email: 'user@example.com',
    name: 'John Doe',
    password: hashedPassword,
  },
});

// Create with nested relation (creates both user and profile)
const userWithProfile = await prisma.user.create({
  data: {
    email: 'user@example.com',
    name: 'John Doe',
    password: hashedPassword,
    profile: {
      create: {
        bio: 'Hello world',
      },
    },
  },
  include: {
    profile: true,  // Return the created profile too
  },
});

// Create many (bulk insert)
const result = await prisma.user.createMany({
  data: [
    { email: 'user1@example.com', name: 'User 1', password: hash1 },
    { email: 'user2@example.com', name: 'User 2', password: hash2 },
  ],
  skipDuplicates: true,  // Don't fail on unique constraint violation
});
// Returns: { count: 2 }

Read#

javascript

// Find by unique field (id, email, etc.)
const user = await prisma.user.findUnique({
  where: { id: 1 },
});
// Returns null if not found

// Find first matching (when field isn't unique)
const firstAdmin = await prisma.user.findFirst({
  where: { role: 'ADMIN' },
});

// Find with related data
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,     // Include all posts
    profile: true,   // Include profile
  },
});

// Select specific fields (reduces data transfer)
const userPartial = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    email: true,
    name: true,
    posts: {
      select: {
        id: true,
        title: true,
      },
    },
  },
});
// Only returns the selected fields - password excluded

// Find many with filters, sorting, pagination
const users = await prisma.user.findMany({
  where: {
    role: 'ADMIN',
    email: {
      contains: '@company.com',  // LIKE '%@company.com%'
    },
  },
  orderBy: {
    createdAt: 'desc',
  },
  take: 10,   // LIMIT
  skip: 0,    // OFFSET
});

include vs select:

include - Get all fields PLUS relations
select - Get ONLY the specified fields (more efficient)

Filtering#

Prisma's filter syntax is intuitive:

javascript

// Comparison operators
const adults = await prisma.user.findMany({
  where: {
    age: { gt: 18 },      // greater than
    // gte: greater than or equal
    // lt: less than
    // lte: less than or equal
    // not: not equal
  },
});

// String filters
const gmailUsers = await prisma.user.findMany({
  where: {
    email: {
      contains: '@gmail.com',     // LIKE '%@gmail.com%'
      startsWith: 'john',         // LIKE 'john%'
      endsWith: '.com',           // LIKE '%.com'
      mode: 'insensitive',        // Case-insensitive
    },
  },
});

// Logical operators
const activeAdmins = await prisma.user.findMany({
  where: {
    AND: [
      { role: 'ADMIN' },
      { active: true },
    ],
    // Also: OR, NOT
  },
});

// Filter by relation
const postsFromAdmins = await prisma.post.findMany({
  where: {
    author: {
      role: 'ADMIN',
    },
  },
});

Update#

javascript

// Update single record
const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    name: 'Jane Doe',
  },
});

// Update nested relation
const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    profile: {
      upsert: {                    // Update if exists, create if not
        create: { bio: 'New bio' },
        update: { bio: 'Updated bio' },
      },
    },
  },
  include: { profile: true },
});

// Update many
const result = await prisma.post.updateMany({
  where: {
    authorId: 1,
    published: false,
  },
  data: {
    published: true,
  },
});
// Returns: { count: 5 }

// Upsert: create if not exists, update if exists
const user = await prisma.user.upsert({
  where: { email: 'user@example.com' },
  create: {
    email: 'user@example.com',
    name: 'New User',
    password: hashedPassword,
  },
  update: {
    name: 'Updated User',
  },
});

Delete#

javascript

// Delete single
const user = await prisma.user.delete({
  where: { id: 1 },
});

// Delete many
const result = await prisma.post.deleteMany({
  where: {
    published: false,
    createdAt: {
      lt: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000), // 30 days ago
    },
  },
});
// Returns: { count: 10 }

Transactions#

When you need multiple operations to succeed or fail together:

javascript

// Batch operations (all or nothing)
const [user, post] = await prisma.$transaction([
  prisma.user.create({
    data: { email: 'user@example.com', name: 'User', password: hash }
  }),
  prisma.post.create({
    data: { title: 'First Post', authorId: 1 }
  }),
]);

// Interactive transaction (with logic)
const result = await prisma.$transaction(async (tx) => {
  // Deduct from sender
  const sender = await tx.account.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } },
  });

  // Check if they have enough
  if (sender.balance < 0) {
    throw new Error('Insufficient funds');
    // Transaction is rolled back automatically
  }

  // Add to receiver
  const receiver = await tx.account.update({
    where: { id: receiverId },
    data: { balance: { increment: amount } },
  });

  return { sender, receiver };
});

When to use transactions:

Money transfers (debit one, credit another)
Creating related records that must exist together
Any operation where partial success is worse than failure

Pagination#

Offset Pagination#

Simple but has performance issues with large datasets:

javascript

async function getUsers(page = 1, limit = 20) {
  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { createdAt: 'desc' },
    }),
    prisma.user.count(),
  ]);

  return {
    users,
    meta: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
    },
  };
}

Cursor Pagination#

Better for large datasets and infinite scroll:

javascript

async function getUsersCursor(cursor, limit = 20) {
  const users = await prisma.user.findMany({
    take: limit + 1,  // Get one extra to check if more exist
    ...(cursor && {
      skip: 1,        // Skip the cursor itself
      cursor: { id: cursor },
    }),
    orderBy: { id: 'asc' },
  });

  const hasMore = users.length > limit;
  const items = hasMore ? users.slice(0, -1) : users;
  const nextCursor = hasMore ? items[items.length - 1].id : null;

  return { items, nextCursor, hasMore };
}

Why cursor pagination? Offset pagination gets slower as pages increase (database still scans previous rows). Cursor pagination is consistent regardless of how deep you go.

Service Layer Pattern#

Keep database logic in services, not controllers:

javascript

// src/services/userService.js
import { prisma } from '../lib/prisma.js';
import { hashPassword } from '../utils/crypto.js';

export const UserService = {
  async findById(id) {
    return prisma.user.findUnique({
      where: { id },
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
        createdAt: true,
        // Explicitly exclude password
      },
    });
  },

  async findByEmail(email) {
    return prisma.user.findUnique({
      where: { email },
    });
  },

  async create(data) {
    const hashedPassword = await hashPassword(data.password);
    return prisma.user.create({
      data: {
        ...data,
        password: hashedPassword,
      },
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
      },
    });
  },

  async update(id, data) {
    if (data.password) {
      data.password = await hashPassword(data.password);
    }
    return prisma.user.update({
      where: { id },
      data,
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
      },
    });
  },

  async delete(id) {
    return prisma.user.delete({
      where: { id },
    });
  },

  async list({ page = 1, limit = 20, role } = {}) {
    const where = role ? { role } : {};

    const [users, total] = await prisma.$transaction([
      prisma.user.findMany({
        where,
        skip: (page - 1) * limit,
        take: limit,
        orderBy: { createdAt: 'desc' },
        select: {
          id: true,
          email: true,
          name: true,
          role: true,
          createdAt: true,
        },
      }),
      prisma.user.count({ where }),
    ]);

    return { users, total, page, limit };
  },
};

Why services?

Controllers stay thin (handle HTTP, call services)
Business logic is testable without HTTP
Easy to reuse across different routes
Clear separation of concerns

Prisma Studio#

Visual database browser - incredibly useful for development:

bash

npx prisma studio
# Opens at http://localhost:5555

You can:

Browse all tables
Add/edit/delete records
Filter and search
View relationships

Key Takeaways#

Schema-first development - Define models in schema.prisma, everything else is generated.
Type safety is automatic - No manual types to maintain. Change schema, regenerate, TypeScript catches breaking changes.
Migrations are version control - Database schema evolves with your code, tracked in git.
Use select for efficiency - Only fetch what you need, especially for API responses (don't leak passwords!).
Transactions for consistency - Multiple operations that must succeed together should be in a transaction.
Services over raw queries - Keep Prisma calls in a service layer for cleaner, more testable code.

The Workflow

bash

# 1. Edit schema
vim prisma/schema.prisma

# 2. Create migration
npx prisma migrate dev --name add_feature

# 3. Use in code (with full type safety)
const users = await prisma.user.findMany()

Schema changes flow through the entire stack automatically.

Why Prisma?#

What makes Prisma different:

Feature	Traditional ORMs	Prisma
Type safety	Manual types, often drift	Auto-generated from schema
Schema	Code-first or annotation-based	Dedicated schema file
Autocomplete	Limited	Full IntelliSense
Migrations	Often clunky	Built-in, version-controlled
Query building	Method chaining	Intuitive object syntax

When Prisma shines:

TypeScript projects where type safety matters
Teams that want readable, maintainable database code
Projects needing robust migration workflows
When developer experience is a priority

When to consider alternatives:

Complex raw SQL requirements
NoSQL databases (Prisma supports SQL databases)
Very high-performance needs with micro-optimizations

Setup#

bash

npm install prisma @prisma/client
npx prisma init

This creates:

prisma/schema.prisma - Your data model definition
.env - Database connection string

Database URL format:

# PostgreSQL
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

# MySQL
DATABASE_URL="mysql://user:password@localhost:3306/mydb"

# SQLite (great for development)
DATABASE_URL="file:./dev.db"

Schema Definition#

The schema file is your single source of truth. It defines your data models, relationships, and database configuration:

prisma

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

datasource db {
  provider = "postgresql"  // postgresql, mysql, sqlite, sqlserver
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?                    // ? means nullable
  password  String
  role      Role     @default(USER)
  posts     Post[]                     // One-to-many relation
  profile   Profile?                   // One-to-one relation
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt        // Auto-updates on change

  @@index([email])                     // Index for faster lookups
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  avatar String?
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId Int    @unique                // Foreign key
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  Int                        // Foreign key
  tags      Tag[]                      // Many-to-many relation
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId])
  @@index([published])
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]                         // Many-to-many (implicit join table)
}

enum Role {
  USER
  ADMIN
}

Schema concepts explained:

@id - Primary key
@default() - Default value (can use functions like now(), autoincrement(), uuid())
@unique - Ensures no duplicates
@updatedAt - Automatically updates timestamp on any change
@relation - Defines how models connect
@@index - Creates database index for faster queries
? after type - Field is nullable/optional

Migrations#

Prisma migrations version-control your database schema. Every schema change creates a new migration file.

bash

# Development: Create migration from schema changes
npx prisma migrate dev --name init
# This: generates SQL, applies it, regenerates client

# Production: Apply pending migrations
npx prisma migrate deploy

# Reset database (DELETES ALL DATA - dev only!)
npx prisma migrate reset

# Generate client without migration
npx prisma generate

Migration workflow:

Edit schema.prisma
Run prisma migrate dev --name describe_change
Prisma generates SQL migration
Migration is applied to dev database
Client is regenerated with new types
Commit the migration file to git

Why this matters: Your database schema is now version-controlled alongside your code. Rolling back? Checkout the old commit and migrate.

Prisma Client#

The client is your database interface. It's auto-generated from your schema:

javascript

// src/lib/prisma.js
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis;

// Prevent multiple instances in development (hot reloading)
export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'error', 'warn']
    : ['error'],
});

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

Why the globalThis pattern? In development with hot reloading, each reload creates a new Prisma instance, potentially exhausting database connections. This pattern reuses the existing instance.

CRUD Operations#

Create#

javascript

// Create single record
const user = await prisma.user.create({
  data: {
    email: 'user@example.com',
    name: 'John Doe',
    password: hashedPassword,
  },
});

// Create with nested relation (creates both user and profile)
const userWithProfile = await prisma.user.create({
  data: {
    email: 'user@example.com',
    name: 'John Doe',
    password: hashedPassword,
    profile: {
      create: {
        bio: 'Hello world',
      },
    },
  },
  include: {
    profile: true,  // Return the created profile too
  },
});

// Create many (bulk insert)
const result = await prisma.user.createMany({
  data: [
    { email: 'user1@example.com', name: 'User 1', password: hash1 },
    { email: 'user2@example.com', name: 'User 2', password: hash2 },
  ],
  skipDuplicates: true,  // Don't fail on unique constraint violation
});
// Returns: { count: 2 }

Read#

javascript

// Find by unique field (id, email, etc.)
const user = await prisma.user.findUnique({
  where: { id: 1 },
});
// Returns null if not found

// Find first matching (when field isn't unique)
const firstAdmin = await prisma.user.findFirst({
  where: { role: 'ADMIN' },
});

// Find with related data
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,     // Include all posts
    profile: true,   // Include profile
  },
});

// Select specific fields (reduces data transfer)
const userPartial = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    email: true,
    name: true,
    posts: {
      select: {
        id: true,
        title: true,
      },
    },
  },
});
// Only returns the selected fields - password excluded

// Find many with filters, sorting, pagination
const users = await prisma.user.findMany({
  where: {
    role: 'ADMIN',
    email: {
      contains: '@company.com',  // LIKE '%@company.com%'
    },
  },
  orderBy: {
    createdAt: 'desc',
  },
  take: 10,   // LIMIT
  skip: 0,    // OFFSET
});

include vs select:

include - Get all fields PLUS relations
select - Get ONLY the specified fields (more efficient)

Filtering#

Prisma's filter syntax is intuitive:

javascript

// Comparison operators
const adults = await prisma.user.findMany({
  where: {
    age: { gt: 18 },      // greater than
    // gte: greater than or equal
    // lt: less than
    // lte: less than or equal
    // not: not equal
  },
});

// String filters
const gmailUsers = await prisma.user.findMany({
  where: {
    email: {
      contains: '@gmail.com',     // LIKE '%@gmail.com%'
      startsWith: 'john',         // LIKE 'john%'
      endsWith: '.com',           // LIKE '%.com'
      mode: 'insensitive',        // Case-insensitive
    },
  },
});

// Logical operators
const activeAdmins = await prisma.user.findMany({
  where: {
    AND: [
      { role: 'ADMIN' },
      { active: true },
    ],
    // Also: OR, NOT
  },
});

// Filter by relation
const postsFromAdmins = await prisma.post.findMany({
  where: {
    author: {
      role: 'ADMIN',
    },
  },
});

Update#

javascript

// Update single record
const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    name: 'Jane Doe',
  },
});

// Update nested relation
const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    profile: {
      upsert: {                    // Update if exists, create if not
        create: { bio: 'New bio' },
        update: { bio: 'Updated bio' },
      },
    },
  },
  include: { profile: true },
});

// Update many
const result = await prisma.post.updateMany({
  where: {
    authorId: 1,
    published: false,
  },
  data: {
    published: true,
  },
});
// Returns: { count: 5 }

// Upsert: create if not exists, update if exists
const user = await prisma.user.upsert({
  where: { email: 'user@example.com' },
  create: {
    email: 'user@example.com',
    name: 'New User',
    password: hashedPassword,
  },
  update: {
    name: 'Updated User',
  },
});

Delete#

javascript

// Delete single
const user = await prisma.user.delete({
  where: { id: 1 },
});

// Delete many
const result = await prisma.post.deleteMany({
  where: {
    published: false,
    createdAt: {
      lt: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000), // 30 days ago
    },
  },
});
// Returns: { count: 10 }

Transactions#

When you need multiple operations to succeed or fail together:

javascript

// Batch operations (all or nothing)
const [user, post] = await prisma.$transaction([
  prisma.user.create({
    data: { email: 'user@example.com', name: 'User', password: hash }
  }),
  prisma.post.create({
    data: { title: 'First Post', authorId: 1 }
  }),
]);

// Interactive transaction (with logic)
const result = await prisma.$transaction(async (tx) => {
  // Deduct from sender
  const sender = await tx.account.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } },
  });

  // Check if they have enough
  if (sender.balance < 0) {
    throw new Error('Insufficient funds');
    // Transaction is rolled back automatically
  }

  // Add to receiver
  const receiver = await tx.account.update({
    where: { id: receiverId },
    data: { balance: { increment: amount } },
  });

  return { sender, receiver };
});

When to use transactions:

Money transfers (debit one, credit another)
Creating related records that must exist together
Any operation where partial success is worse than failure

Pagination#

Offset Pagination#

Simple but has performance issues with large datasets:

javascript

async function getUsers(page = 1, limit = 20) {
  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { createdAt: 'desc' },
    }),
    prisma.user.count(),
  ]);

  return {
    users,
    meta: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
    },
  };
}

Cursor Pagination#

Better for large datasets and infinite scroll:

javascript

async function getUsersCursor(cursor, limit = 20) {
  const users = await prisma.user.findMany({
    take: limit + 1,  // Get one extra to check if more exist
    ...(cursor && {
      skip: 1,        // Skip the cursor itself
      cursor: { id: cursor },
    }),
    orderBy: { id: 'asc' },
  });

  const hasMore = users.length > limit;
  const items = hasMore ? users.slice(0, -1) : users;
  const nextCursor = hasMore ? items[items.length - 1].id : null;

  return { items, nextCursor, hasMore };
}

Why cursor pagination? Offset pagination gets slower as pages increase (database still scans previous rows). Cursor pagination is consistent regardless of how deep you go.

Service Layer Pattern#

Keep database logic in services, not controllers:

javascript

// src/services/userService.js
import { prisma } from '../lib/prisma.js';
import { hashPassword } from '../utils/crypto.js';

export const UserService = {
  async findById(id) {
    return prisma.user.findUnique({
      where: { id },
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
        createdAt: true,
        // Explicitly exclude password
      },
    });
  },

  async findByEmail(email) {
    return prisma.user.findUnique({
      where: { email },
    });
  },

  async create(data) {
    const hashedPassword = await hashPassword(data.password);
    return prisma.user.create({
      data: {
        ...data,
        password: hashedPassword,
      },
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
      },
    });
  },

  async update(id, data) {
    if (data.password) {
      data.password = await hashPassword(data.password);
    }
    return prisma.user.update({
      where: { id },
      data,
      select: {
        id: true,
        email: true,
        name: true,
        role: true,
      },
    });
  },

  async delete(id) {
    return prisma.user.delete({
      where: { id },
    });
  },

  async list({ page = 1, limit = 20, role } = {}) {
    const where = role ? { role } : {};

    const [users, total] = await prisma.$transaction([
      prisma.user.findMany({
        where,
        skip: (page - 1) * limit,
        take: limit,
        orderBy: { createdAt: 'desc' },
        select: {
          id: true,
          email: true,
          name: true,
          role: true,
          createdAt: true,
        },
      }),
      prisma.user.count({ where }),
    ]);

    return { users, total, page, limit };
  },
};

Why services?

Controllers stay thin (handle HTTP, call services)
Business logic is testable without HTTP
Easy to reuse across different routes
Clear separation of concerns

Prisma Studio#

Visual database browser - incredibly useful for development:

bash

npx prisma studio
# Opens at http://localhost:5555

You can:

Browse all tables
Add/edit/delete records
Filter and search
View relationships

Key Takeaways#

Schema-first development - Define models in schema.prisma, everything else is generated.
Type safety is automatic - No manual types to maintain. Change schema, regenerate, TypeScript catches breaking changes.
Migrations are version control - Database schema evolves with your code, tracked in git.
Use select for efficiency - Only fetch what you need, especially for API responses (don't leak passwords!).
Transactions for consistency - Multiple operations that must succeed together should be in a transaction.
Services over raw queries - Keep Prisma calls in a service layer for cleaner, more testable code.

The Workflow

bash

# 1. Edit schema
vim prisma/schema.prisma

# 2. Create migration
npx prisma migrate dev --name add_feature

# 3. Use in code (with full type safety)
const users = await prisma.user.findMany()

Schema changes flow through the entire stack automatically.

Why Prisma?#

Setup#

Schema Definition#

Migrations#

Prisma Client#

CRUD Operations#

Create#

Read#

Filtering#

Update#

Delete#

Transactions#

Service Layer Pattern#

Prisma Studio#

Key Takeaways#

Ready to level up your skills?

Prisma ORM

Why Prisma?#

Setup#

Schema Definition#

Migrations#

Prisma Client#

CRUD Operations#

Create#

Read#

Filtering#

Update#

Delete#

Transactions#

Service Layer Pattern#

Prisma Studio#

Key Takeaways#

Ready to level up your skills?