🔷 BD09 — Prisma ORM

1. schema.prisma

npm install prisma @prisma/client
npx prisma init

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

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

model User {
  id        Int      @id @default(autoincrement())
  uuid      String   @default(uuid()) @unique
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?

  @@map("users")
}

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])
  tags       Tag[]
  createdAt  DateTime @default(now())

  @@index([authorId])
  @@map("posts")
}

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

enum Role {
  USER
  ADMIN
  MODERATOR
}

2. Migrations

# Créer et appliquer une migration
npx prisma migrate dev --name init

# Appliquer en production
npx prisma migrate deploy

# Reset complet (dev uniquement)
npx prisma migrate reset

# Synchroniser sans migration (prototypes)
npx prisma db push

# Générer le client après modification du schema
npx prisma generate

# Interface GUI (Prisma Studio)
npx prisma studio

# Introspection d'une base existante
npx prisma db pull

3. Prisma Client CRUD

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient({ log: ['query', 'error'] });

// CREATE
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice Dupont',
    role: 'ADMIN'
  }
});

// READ — findUnique, findFirst, findMany
const byEmail = await prisma.user.findUnique({ where: { email: 'alice@example.com' } });
const admins  = await prisma.user.findMany({
  where: { role: 'ADMIN', deletedAt: null },
  orderBy: { createdAt: 'desc' },
  take: 10,
  skip: 0,
  select: { id: true, name: true, email: true }
});

// UPDATE
const updated = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Martin', updatedAt: new Date() }
});

// UPSERT
const upserted = await prisma.user.upsert({
  where: { email: 'bob@example.com' },
  update: { name: 'Bob' },
  create: { email: 'bob@example.com', name: 'Bob' }
});

// DELETE
await prisma.user.delete({ where: { id: 1 } });

// deleteMany / updateMany
await prisma.post.updateMany({
  where: { authorId: 1, published: false },
  data: { published: true }
});

// count / aggregate
const total = await prisma.post.count({ where: { published: true } });
const stats = await prisma.post.aggregate({
  _count: true, _avg: { viewCount: true }, _max: { createdAt: true }
});

4. Relations & include

// include — charger les relations
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
      include: { tags: true }
    },
    profile: true
  }
});

// select imbriqué
const light = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    _count: { select: { posts: true } }
  }
});

// Relation create imbriquée
const newUser = await prisma.user.create({
  data: {
    email: 'charlie@example.com',
    name: 'Charlie',
    posts: {
      create: [
        { title: 'Premier article', published: true },
        { title: 'Brouillon' }
      ]
    },
    profile: { create: { bio: 'Développeur full-stack' } }
  },
  include: { posts: true, profile: true }
});

5. $transaction

// Transaction séquentielle
const [transfer, log] = await prisma.$transaction([
  prisma.account.update({
    where: { id: 1 },
    data: { balance: { decrement: 100 } }
  }),
  prisma.account.update({
    where: { id: 2 },
    data: { balance: { increment: 100 } }
  })
]);

// Transaction interactive (avec logique)
const result = await prisma.$transaction(async (tx) => {
  const sender = await tx.account.findUnique({ where: { id: 1 } });
  if (sender.balance < 100) throw new Error('Solde insuffisant');

  await tx.account.update({ where: { id: 1 }, data: { balance: { decrement: 100 } } });
  await tx.account.update({ where: { id: 2 }, data: { balance: { increment: 100 } } });

  return tx.transaction.create({
    data: { fromId: 1, toId: 2, amount: 100, status: 'completed' }
  });
}, { timeout: 10000, maxWait: 5000 });

6. $queryRaw & $executeRaw

// Requête SQL brute (retourne les résultats)
const products = await prisma.$queryRaw`
  SELECT p.*, c.name AS category_name
  FROM products p
  JOIN categories c ON c.id = p.category_id
  WHERE p.price BETWEEN ${10} AND ${100}
  ORDER BY p.price ASC
  LIMIT ${20}
`;

// $executeRaw (pas de retour de données)
await prisma.$executeRaw`
  UPDATE products SET updated_at = NOW()
  WHERE category_id = ${categoryId}
`;

7. Middleware Prisma

// Soft delete middleware
prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    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() };
    }
    if (['findUnique','findFirst','findMany'].includes(params.action)) {
      params.args.where = { ...params.args.where, deletedAt: null };
    }
  }
  return next(params);
});

// Logging middleware
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();
  console.log(`${params.model}.${params.action} — ${after - before}ms`);
  return result;
});