ORM and migrations with Prisma

docker run -d --rm --name booking-pg -e POSTGRES_PASSWORD=test -e POSTGRES_DB=booking -p 127.0.0.1:55817:5432 postgres:17-alpine

mkdir booking && cd booking
npm init -y
npm pkg set type=module
npm install @prisma/client@7.10.0 @prisma/adapter-pg@7.10.0 pg@8.23.1 dotenv@18.0.7
npm install -D prisma@7.10.0 typescript@5.9.3 tsx@4.23.15 @types/node@24 @types/pg@8.23.1
npx prisma init --datasource-provider postgresql --output ../src/generated/prisma --no-skills

// This file was generated by Prisma, and assumes you have installed the following:
// npm install --save-dev prisma dotenv
import "dotenv/config";
import { defineConfig } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  datasource: {
    url: process.env["DATABASE_URL"],
  },
});

DATABASE_URL="postgresql://postgres:test@localhost:55817/booking"

{
  "compilerOptions": {
    "target": "ES2023",
    "module": "ESNext",
    "moduleResolution": "bundler",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "noEmit": true,
    "types": ["node"]
  },
  "include": ["src", "prisma7.config.ts"]
}

import "dotenv/config";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "./generated/prisma/client";

const connectionString = process.env.DATABASE_URL;
if (!connectionString) throw new Error("DATABASE_URL is not set");

// Prisma 7: the client talks to PostgreSQL through a driver adapter (node-postgres)
const adapter = new PrismaPg({ connectionString });

export const prisma = new PrismaClient({
  adapter,
  log: [{ emit: "event", level: "query" }],
});

// Count SQL queries so that N+1 is visible in numbers, not by feel
export const sql = { count: 0, print: false };
prisma.$on("query", (e) => {
  sql.count++;
  if (sql.print) console.log(`  SQL: ${e.query}`);
});

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

datasource db {
  provider = "postgresql"
}

model Client {
  id           Int           @id @default(autoincrement())
  name         String
  phone        String        @unique
  createdAt    DateTime      @default(now()) @map("created_at") @db.Timestamptz(3)
  appointments Appointment[]

  @@map("clients")
}

model Service {
  id           Int           @id @default(autoincrement())
  title        String        @unique
  durationMin  Int           @map("duration_min")
  priceKzt     Int           @map("price_kzt")
  appointments Appointment[]

  @@map("services")
}

model Appointment {
  id        Int      @id @default(autoincrement())
  startsAt  DateTime @map("starts_at") @db.Timestamptz(3)
  clientId  Int      @map("client_id")
  serviceId Int      @map("service_id")
  client    Client   @relation(fields: [clientId], references: [id], onDelete: Cascade)
  service   Service  @relation(fields: [serviceId], references: [id], onDelete: Restrict)

  @@unique([serviceId, startsAt])
  @@index([clientId])
  @@map("appointments")
}

$ npx prisma migrate dev --name init
Loaded Prisma config from prisma7.config.ts.

Prisma schema loaded from prisma\schema.prisma.
Datasource "db": PostgreSQL database "booking", schema "public" at "localhost:55817"

Applying migration `20261012005404_init`

The following migration(s) have been created and applied from new schema changes:

prisma\migrations/
  └─ 20261012005404_init/
    └─ migration.sql

Your database is now in sync with your schema.
┌─────────────────────────────────────────────────────────┐
│  Update available 7.10.0 -> 8.0.0-rc.22                 │
│                                                         │
│  This is a major update - please follow the guide at    │
│  https://pris.ly/d/major-version-upgrade                │
│                                                         │
│  Run the following to update                            │
│    npm i --save-dev prisma@latest                       │
│    npm i @prisma/client@latest                          │
└─────────────────────────────────────────────────────────┘

-- CreateTable
CREATE TABLE "clients" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "clients_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "services" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "duration_min" INTEGER NOT NULL,
    "price_kzt" INTEGER NOT NULL,

    CONSTRAINT "services_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "appointments" (
    "id" SERIAL NOT NULL,
    "starts_at" TIMESTAMPTZ(3) NOT NULL,
    "client_id" INTEGER NOT NULL,
    "service_id" INTEGER NOT NULL,

    CONSTRAINT "appointments_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "clients_phone_key" ON "clients"("phone");

-- CreateIndex
CREATE UNIQUE INDEX "services_title_key" ON "services"("title");

-- CreateIndex
CREATE INDEX "appointments_client_id_idx" ON "appointments"("client_id");

-- CreateIndex
CREATE UNIQUE INDEX "appointments_service_id_starts_at_key" ON "appointments"("service_id", "starts_at");

-- AddForeignKey
ALTER TABLE "appointments" ADD CONSTRAINT "appointments_client_id_fkey" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "appointments" ADD CONSTRAINT "appointments_service_id_fkey" FOREIGN KEY ("service_id") REFERENCES "services"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

model Appointment {
  id        Int               @id @default(autoincrement())
  startsAt  DateTime          @map("starts_at") @db.Timestamptz(3)
  clientId  Int               @map("client_id")
  serviceId Int               @map("service_id")
  status    AppointmentStatus @default(BOOKED)
  note      String?
  client    Client            @relation(fields: [clientId], references: [id], onDelete: Cascade)
  service   Service           @relation(fields: [serviceId], references: [id], onDelete: Restrict)

  @@unique([serviceId, startsAt])
  @@index([clientId])
  @@map("appointments")
}

enum AppointmentStatus {
  BOOKED
  CANCELLED
  DONE

  @@map("appointment_status")
}

-- CreateEnum
CREATE TYPE "appointment_status" AS ENUM ('BOOKED', 'CANCELLED', 'DONE');

-- AlterTable
ALTER TABLE "appointments" ADD COLUMN     "note" TEXT,
ADD COLUMN     "status" "appointment_status" NOT NULL DEFAULT 'BOOKED';

$ npx prisma migrate dev --name rename_note_to_comment --create-only
Loaded Prisma config from prisma7.config.ts.

Prisma schema loaded from prisma\schema.prisma.
Datasource "db": PostgreSQL database "booking", schema "public" at "localhost:55817"

$ cat prisma/migrations/20261012005439_rename_note_to_comment/migration.sql
/*
  Warnings:

  - You are about to drop the column `note` on the `appointments` table. All the data in the column will be lost.

*/
-- AlterTable
ALTER TABLE "appointments" DROP COLUMN "note",
ADD COLUMN     "comment" TEXT;

-- Rename instead of drop + add: keeps existing data
ALTER TABLE "appointments" RENAME COLUMN "note" TO "comment";

$ npx prisma migrate dev
...
migrations/
  └─ 20261012005439_rename_note_to_comment/
    └─ migration.sql

Your database is now in sync with your schema.

$ docker exec booking-pg psql -U postgres -d booking -c "SELECT id, status, comment FROM appointments"
 id | status | comment
----+--------+---------
  1 | BOOKED | keep me
(1 row)

$ npx prisma generate
✔ Generated Prisma Client (7.10.0) to .\src\generated\prisma in 46ms

$ DATABASE_URL="postgresql://postgres:test@localhost:55817/booking_stage" npx prisma migrate deploy
Loaded Prisma config from prisma7.config.ts.

Prisma schema loaded from prisma\schema.prisma.
Datasource "db": PostgreSQL database "booking_stage", schema "public" at "localhost:55817"

3 migrations found in prisma/migrations

Applying migration `20261012005404_init`
Applying migration `20261012005422_add_status_and_note`
Applying migration `20261012005439_rename_note_to_comment`

The following migration(s) have been applied:

migrations/
  └─ 20261012005404_init/
    └─ migration.sql
  └─ 20261012005422_add_status_and_note/
    └─ migration.sql
  └─ 20261012005439_rename_note_to_comment/
    └─ migration.sql

All migrations have been successfully applied.

$ docker exec booking-pg psql -U postgres -d booking -c "ALTER TABLE clients ADD COLUMN email text"
$ npx prisma migrate dev --name noop
...
Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[*] Changed the `clients` table
  [+] Added column `email`

We need to reset the "public" schema at "localhost:55817"

You may use prisma migrate reset to drop the development database.
All data will be lost.

import { Prisma } from "./generated/prisma/client";
import { prisma } from "./db";

// Create: plain records
const haircut = await prisma.service.create({
  data: { title: "Haircut", durationMin: 60, priceKzt: 8000 },
});
const manicure = await prisma.service.create({
  data: { title: "Manicure", durationMin: 90, priceKzt: 10000 },
});

// Nested write: a client and their first booking in one transaction
const client = await prisma.client.create({
  data: {
    name: "Aigerim",
    phone: "+77010000001",
    appointments: {
      create: { serviceId: haircut.id, startsAt: new Date("2026-10-20T10:00:00+05:00") },
    },
  },
  include: { appointments: true },
});
console.log("created:", client.name, "bookings:", client.appointments.length);

// Read: relation via include, only needed fields via select
const schedule = await prisma.appointment.findMany({
  where: { startsAt: { gte: new Date("2026-10-20T00:00:00+05:00") } },
  orderBy: { startsAt: "asc" },
  select: {
    id: true,
    startsAt: true,
    status: true,
    client: { select: { name: true } },
    service: { select: { title: true, priceKzt: true } },
  },
});
console.log("schedule:", JSON.stringify(schedule));

// Update: move the booking to another service and leave a comment
const updated = await prisma.appointment.update({
  where: { id: client.appointments[0].id },
  data: { serviceId: manicure.id, comment: "Client asked to switch" },
});
console.log("updated:", updated.serviceId, updated.comment, updated.status);

// Delete: Restrict on the relation does not let us delete a service with bookings
try {
  await prisma.service.delete({ where: { id: manicure.id } });
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError) {
    console.log("delete service:", e.code); // P2003 — foreign key constraint
  } else throw e;
}

// Cascade: deleting a client deletes their bookings (onDelete: Cascade)
await prisma.client.delete({ where: { id: client.id } });
console.log("bookings left:", await prisma.appointment.count());

await prisma.$disconnect();

$ npx tsx src/crud.ts
created: Aigerim bookings: 1
schedule: [{"id":1,"startsAt":"2026-10-20T05:00:00.000Z","status":"BOOKED","client":{"name":"Aigerim"},"service":{"title":"Haircut","priceKzt":8000}}]
updated: 2 Client asked to switch BOOKED
delete service: P2003
bookings left: 0

import { Prisma } from "./generated/prisma/client";
import { prisma } from "./db";

// Move a booking: cancel the old one and create a new one — both or neither
async function reschedule(appointmentId: number, newStartsAt: Date) {
  return prisma.$transaction(async (tx) => {
    const old = await tx.appointment.update({
      where: { id: appointmentId, status: "BOOKED" },
      data: { status: "CANCELLED" },
    });
    // If the slot is taken, create throws P2002 and the update above is rolled back
    return tx.appointment.create({
      data: { clientId: old.clientId, serviceId: old.serviceId, startsAt: newStartsAt },
    });
  });
}

const booking = await prisma.appointment.findFirstOrThrow({
  where: { status: "BOOKED" },
  orderBy: { id: "asc" },
});
const busy = await prisma.appointment.findFirstOrThrow({
  where: { serviceId: booking.serviceId, id: { not: booking.id } },
});

try {
  await reschedule(booking.id, busy.startsAt); // slot is taken by another client
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === "P2002") {
    console.log("slot is taken, transaction rolled back");
  } else throw e;
}
const after = await prisma.appointment.findUniqueOrThrow({ where: { id: booking.id } });
console.log(`booking ${booking.id} status: ${after.status}`);

const moved = await reschedule(booking.id, new Date("2026-10-25T06:00:00Z"));
console.log(`new booking ${moved.id} at ${moved.startsAt.toISOString()}`);
const old = await prisma.appointment.findUniqueOrThrow({ where: { id: booking.id } });
console.log(`old booking ${booking.id} status: ${old.status}`);

await prisma.$disconnect();

$ npx tsx src/seed.ts
clients: 20 bookings: 40
$ npx tsx src/reschedule.ts
slot is taken, transaction rolled back
booking 3 status: BOOKED
new booking 43 at 2026-10-25T06:00:00.000Z
old booking 3 status: CANCELLED

import { prisma } from "./db";

// Test data: 3 services, 20 clients, two bookings each (fake phone numbers)
await prisma.appointment.deleteMany();
await prisma.client.deleteMany();
await prisma.service.deleteMany();

const services = await prisma.service.createManyAndReturn({
  data: [
    { title: "Haircut", durationMin: 60, priceKzt: 8000 },
    { title: "Manicure", durationMin: 90, priceKzt: 10000 },
    { title: "Massage", durationMin: 60, priceKzt: 15000 },
  ],
});

for (let i = 1; i <= 20; i++) {
  await prisma.client.create({
    data: {
      name: `Client ${i}`,
      phone: `+7701000${String(i).padStart(4, "0")}`,
      appointments: {
        create: [0, 1].map((k) => ({
          serviceId: services[(i + k) % services.length].id,
          startsAt: new Date(Date.UTC(2026, 9, 20 + k, 4 + (i % 8), 0)),
          status: k === 0 ? "DONE" : "BOOKED",
        })),
      },
    },
  });
}
console.log("clients:", await prisma.client.count(), "bookings:", await prisma.appointment.count());
await prisma.$disconnect();

import { prisma, sql } from "./db";

// Bad: 1 query for clients + 1 query per client = N+1
sql.count = 0;
const clients = await prisma.client.findMany({ orderBy: { id: "asc" } });
let total = 0;
for (const c of clients) {
  const bookings = await prisma.appointment.findMany({ where: { clientId: c.id } });
  total += bookings.length;
}
console.log(`loop:    ${clients.length} clients, ${total} bookings, ${sql.count} SQL queries`);

// Good: include loads the relation for all clients at once
sql.count = 0;
sql.print = true;
const withBookings = await prisma.client.findMany({
  orderBy: { id: "asc" },
  include: { appointments: true },
});
sql.print = false;
const total2 = withBookings.reduce((n, c) => n + c.appointments.length, 0);
console.log(`include: ${withBookings.length} clients, ${total2} bookings, ${sql.count} SQL queries`);

await prisma.$disconnect();

$ npx tsx src/n-plus-one.ts
loop:    20 clients, 40 bookings, 21 SQL queries
  SQL: SELECT "public"."clients"."id", "public"."clients"."name", "public"."clients"."phone", "public"."clients"."created_at" FROM "public"."clients" WHERE 1=1 ORDER BY "public"."clients"."id" ASC OFFSET $1
  SQL: SELECT "public"."appointments"."id", "public"."appointments"."starts_at", "public"."appointments"."client_id", "public"."appointments"."service_id", "public"."appointments"."status"::text, "public"."appointments"."comment" FROM "public"."appointments" WHERE "public"."appointments"."client_id" IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20) OFFSET $21
include: 20 clients, 40 bookings, 2 SQL queries

import { prisma } from "./db";

type Row = { title: string; visits: bigint; revenue: bigint };

// Revenue by service: an aggregate over a JOIN is easier and clearer in SQL
const from = new Date("2026-10-01T00:00:00Z");
const rows = await prisma.$queryRaw<Row[]>`
  SELECT s.title, count(*) AS visits, sum(s.price_kzt) AS revenue
  FROM appointments a
  JOIN services s ON s.id = a.service_id
  WHERE a.status = 'DONE' AND a.starts_at >= ${from}
  GROUP BY s.title
  ORDER BY revenue DESC`;

// count() and sum() over integer in PostgreSQL return bigint → BigInt in JS
for (const r of rows) {
  console.log(`${r.title}: ${r.visits} visits, ${Number(r.revenue).toLocaleString("en-US")} KZT`);
}
console.log(typeof rows[0].visits);

await prisma.$disconnect();

$ npx tsx src/report.ts
Massage: 7 visits, 105,000 KZT
Manicure: 7 visits, 70,000 KZT
Haircut: 6 visits, 48,000 KZT
bigint

Error: 
⚠️ We found changes that cannot be executed:

  • Step 0 Added the required column `price_kzt` to the `appointments` table without a default value. There are 40 rows in this table, it is not possible to execute this step.

You can use prisma migrate dev --create-only to create the migration file, and manually modify it to address the underlying issue(s).
Then run prisma migrate dev to apply it and verify it works.