ORM with Prisma

npm install prisma @prisma/client
npx prisma init

# .env
DATABASE_URL="file:./dev.db"

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

// prisma/schema.prisma

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

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

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  role      String    @default("user")
  posts     Post[]
  comments  Comment[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  slug        String    @unique
  content     String
  published   Boolean   @default(false)
  author      User      @relation(fields: [authorId], references: [id])
  authorId    Int
  tags        Tag[]
  comments    Comment[]
  publishedAt DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

model Comment {
  id        Int      @id @default(autoincrement())
  body      String
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId    Int
  createdAt DateTime @default(now())
}

# Create a migration
npx prisma migrate dev --name init

# Apply migrations in production
npx prisma migrate deploy

# Reset database (development only)
npx prisma migrate reset

npx prisma generate

const { PrismaClient } = require("@prisma/client");
const prisma = new PrismaClient();

// Create a user
const user = await prisma.user.create({
  data: {
    name: "Alice",
    email: "alice@example.com",
  },
});

// Create with a relationship
const post = await prisma.post.create({
  data: {
    title: "Getting Started with Prisma",
    slug: "getting-started-prisma",
    content: "Prisma makes database access easy...",
    author: { connect: { id: user.id } },
    tags: {
      connectOrCreate: [
        { where: { name: "prisma" }, create: { name: "prisma" } },
        { where: { name: "database" }, create: { name: "database" } },
      ],
    },
  },
  include: { author: true, tags: true },
});

// Find many
const users = await prisma.user.findMany({
  where: { role: "user" },
  orderBy: { createdAt: "desc" },
  take: 10,
  skip: 0,
});

// Find one by unique field
const user = await prisma.user.findUnique({
  where: { email: "alice@example.com" },
});

// Find first matching
const admin = await prisma.user.findFirst({
  where: { role: "admin" },
});

// Include relations
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { publishedAt: "desc" },
      include: { tags: true },
    },
  },
});

// Select specific fields
const names = await prisma.user.findMany({
  select: { name: true, email: true },
});

// Complex where conditions
const posts = await prisma.post.findMany({
  where: {
    AND: [
      { published: true },
      {
        OR: [
          { title: { contains: "prisma" } },
          { tags: { some: { name: "database" } } },
        ],
      },
    ],
  },
});

// Comparison operators
const recentUsers = await prisma.user.findMany({
  where: {
    createdAt: { gte: new Date("2026-01-01") },
  },
});

// Update one
const updated = await prisma.user.update({
  where: { id: 1 },
  data: { name: "Alice Updated" },
});

// Update many
await prisma.user.updateMany({
  where: { role: "user" },
  data: { role: "member" },
});

// Upsert (create if not exists, update if exists)
const user = await prisma.user.upsert({
  where: { email: "bob@example.com" },
  update: { name: "Bob Updated" },
  create: { name: "Bob", email: "bob@example.com" },
});

await prisma.user.delete({ where: { id: 1 } });
await prisma.user.deleteMany({ where: { role: "inactive" } });

const [user, post] = await prisma.$transaction([
  prisma.user.create({
    data: { name: "Charlie", email: "charlie@example.com" },
  }),
  prisma.post.create({
    data: {
      title: "My First Post",
      slug: "my-first-post",
      content: "Hello world!",
      authorId: 1,
    },
  }),
]);

// Interactive transaction
await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: 1 } });
  if (!user) throw new Error("User not found");

  await tx.post.updateMany({
    where: { authorId: user.id },
    data: { published: false },
  });
});

// src/routes/users.js
const express = require("express");
const router = express.Router();
const { PrismaClient } = require("@prisma/client");
const prisma = new PrismaClient();

router.get("/", async (req, res) => {
  const { page = 1, limit = 20, search } = req.query;
  const skip = (parseInt(page) - 1) * parseInt(limit);

  const where = search
    ? {
        OR: [
          { name: { contains: search } },
          { email: { contains: search } },
        ],
      }
    : {};

  const [users, total] = await Promise.all([
    prisma.user.findMany({
      where,
      take: parseInt(limit),
      skip,
      orderBy: { createdAt: "desc" },
      select: { id: true, name: true, email: true, role: true },
    }),
    prisma.user.count({ where }),
  ]);

  res.json({
    users,
    pagination: {
      page: parseInt(page),
      limit: parseInt(limit),
      total,
      pages: Math.ceil(total / parseInt(limit)),
    },
  });
});

module.exports = router;

npx prisma studio

model Project {
  id          Int      @id @default(autoincrement())
  name        String
  description String?
  tasks       Task[]
  createdAt   DateTime @default(now())
}

model Task {
  id        Int      @id @default(autoincrement())
  title     String
  status    String   @default("todo")
  priority  Int      @default(0)
  project   Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  projectId Int
  createdAt DateTime @default(now())
  dueDate   DateTime?
}

// Get project with task statistics
const project = await prisma.project.findUnique({
  where: { id: 1 },
  include: {
    tasks: {
      orderBy: [{ priority: "desc" }, { createdAt: "asc" }],
    },
  },
});

const stats = {
  total: project.tasks.length,
  done: project.tasks.filter((t) => t.status === "done").length,
  inProgress: project.tasks.filter((t) => t.status === "in-progress").length,
  todo: project.tasks.filter((t) => t.status === "todo").length,
};