5 exercices · Solutions masquées
Créez un schema.prisma pour un réseau social : User (self-referential follows), Post (soft delete), Like (unique user+post), Comment (nested replies). Ajoutez les indexes, les relations, et générez la migration.
// schema.prisma
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id Int @id @default(autoincrement())
email String @unique
username String @unique
name String?
avatar String?
bio String?
posts Post[] @relation("PostAuthor")
likes Like[]
comments Comment[]
followers User[] @relation("UserFollows")
following User[] @relation("UserFollows")
createdAt DateTime @default(now())
@@map("users")
}
model Post {
id Int @id @default(autoincrement())
title String
content String
published Boolean @default(false)
authorId Int
author User @relation("PostAuthor", fields: [authorId], references: [id])
likes Like[]
comments Comment[]
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deletedAt DateTime? // soft delete
@@index([authorId])
@@index([createdAt])
@@map("posts")
}
model Like {
id Int @id @default(autoincrement())
userId Int
postId Int
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
createdAt DateTime @default(now())
@@unique([userId, postId])
@@map("likes")
}
model Comment {
id Int @id @default(autoincrement())
content String
authorId Int
postId Int
parentId Int?
author User @relation(fields: [authorId], references: [id])
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
parent Comment? @relation("CommentReplies", fields: [parentId], references: [id])
replies Comment[] @relation("CommentReplies")
createdAt DateTime @default(now())
@@index([postId])
@@map("comments")
}Avec le schéma ci-dessus, implémentez : (a) créer un utilisateur avec son premier post en une seule opération, (b) liker un post (upsert), (c) obtenir le feed d'un user (posts des follows), (d) paginer les commentaires d'un post avec les replies.
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
// (a) Créer user + premier post
async function registerWithPost(userData, postData) {
return prisma.user.create({
data: {
...userData,
posts: { create: { ...postData, published: true } }
},
include: { posts: true }
});
}
// (b) Liker un post (toggle)
async function toggleLike(userId, postId) {
const existing = await prisma.like.findUnique({
where: { userId_postId: { userId, postId } }
});
if (existing) {
await prisma.like.delete({ where: { id: existing.id } });
return { liked: false };
}
await prisma.like.create({ data: { userId, postId } });
return { liked: true };
}
// (c) Feed utilisateur (posts des follows)
async function getFeed(userId, { page = 1, perPage = 20 } = {}) {
const user = await prisma.user.findUnique({
where: { id: userId },
select: { following: { select: { id: true } } }
});
const followingIds = user.following.map(f => f.id);
return prisma.post.findMany({
where: { authorId: { in: followingIds }, published: true, deletedAt: null },
include: {
author: { select: { id: true, username: true, avatar: true } },
_count: { select: { likes: true, comments: true } }
},
orderBy: { createdAt: 'desc' },
take: perPage,
skip: (page - 1) * perPage
});
}
// (d) Commentaires avec replies
async function getComments(postId, { page = 1, perPage = 20 } = {}) {
return prisma.comment.findMany({
where: { postId, parentId: null },
include: {
author: { select: { username: true, avatar: true } },
replies: {
include: { author: { select: { username: true, avatar: true } } },
take: 3, orderBy: { createdAt: 'asc' }
}
},
orderBy: { createdAt: 'desc' },
take: perPage, skip: (page - 1) * perPage
});
}Implémentez une fonction publishPost(postId, userId) qui : vérifie que l'utilisateur est bien l'auteur, passe le post en published, crée une notification pour tous les abonnés, et incrémente un compteur de posts publiés de l'utilisateur. Le tout en une seule transaction.
async function publishPost(postId, userId) {
return prisma.$transaction(async (tx) => {
// Vérifier ownership
const post = await tx.post.findUnique({
where: { id: postId, deletedAt: null },
select: { id: true, authorId: true, title: true, published: true }
});
if (!post) throw new Error('Post introuvable');
if (post.authorId !== userId) throw new Error('Non autorisé');
if (post.published) throw new Error('Post déjà publié');
// Publier le post
const updated = await tx.post.update({
where: { id: postId },
data: { published: true }
});
// Récupérer les followers
const author = await tx.user.findUnique({
where: { id: userId },
select: { followers: { select: { id: true } } }
});
// Créer les notifications (batch)
if (author.followers.length > 0) {
await tx.notification.createMany({
data: author.followers.map(follower => ({
userId: follower.id,
type: 'new_post',
payload: JSON.stringify({ postId, postTitle: post.title, authorId: userId })
}))
});
}
// Incrémenter le compteur
await tx.userStats.upsert({
where: { userId },
create: { userId, publishedPosts: 1 },
update: { publishedPosts: { increment: 1 } }
});
return { post: updated, notifiedCount: author.followers.length };
}, { timeout: 15000 });
}Créez deux middlewares Prisma : (a) soft delete sur le modèle Post (remplacer delete par update deletedAt + filtrer les requêtes), (b) audit log qui enregistre toutes les mutations (create/update/delete) dans une table AuditLog.
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
// Middleware (a) — Soft delete sur Post
prisma.$use(async (params, next) => {
if (params.model === 'Post') {
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','count'].includes(params.action)) {
params.args.where = { ...params.args.where, deletedAt: null };
}
}
return next(params);
});
// Middleware (b) — Audit log
prisma.$use(async (params, next) => {
const AUDIT_ACTIONS = ['create','update','delete','createMany','updateMany','deleteMany'];
const AUDIT_MODELS = ['User','Post','Like','Comment'];
if (!AUDIT_MODELS.includes(params.model) || !AUDIT_ACTIONS.includes(params.action)) {
return next(params);
}
const before = await getBeforeState(params);
const result = await next(params);
const after = result;
await prisma.auditLog.create({
data: {
model: params.model,
action: params.action,
recordId: params.args?.where?.id?.toString(),
before: before ? JSON.stringify(before) : undefined,
after: after ? JSON.stringify(after) : undefined,
createdAt: new Date()
}
}).catch(err => console.error('Audit log failed:', err));
return result;
});
async function getBeforeState(params) {
if (['update','delete'].includes(params.action) && params.args?.where) {
try {
return await prisma[params.model.toLowerCase()].findUnique({
where: params.args.where
});
} catch { return null; }
}
return null;
}Utilisez $queryRaw pour : (a) une requête full-text PostgreSQL sur les posts, (b) une requête avec window function pour calculer le rang des utilisateurs par nombre de likes reçus, (c) insérer un batch de données via une requête SQL native optimisée.
// (a) Full-text search avec $queryRaw
async function searchPosts(query, { limit = 20, offset = 0 } = {}) {
const tsQuery = query.trim().split(/\s+/).join(' & ');
return prisma.$queryRaw`
SELECT p.id, p.title,
ts_rank(to_tsvector('french', p.title || ' ' || p.content),
to_tsquery('french', ${tsQuery})) AS rank,
p.created_at, u.username AS author
FROM posts p
JOIN users u ON u.id = p.author_id
WHERE p.deleted_at IS NULL
AND p.published = true
AND to_tsvector('french', p.title || ' ' || p.content)
@@ to_tsquery('french', ${tsQuery})
ORDER BY rank DESC
LIMIT ${limit} OFFSET ${offset}
`;
}
// (b) Classement des users par likes reçus
async function getUserLikesRanking() {
return prisma.$queryRaw`
SELECT u.id, u.username, u.avatar,
COUNT(l.id) AS total_likes,
RANK() OVER (ORDER BY COUNT(l.id) DESC) AS rank
FROM users u
LEFT JOIN posts p ON p.author_id = u.id AND p.deleted_at IS NULL
LEFT JOIN likes l ON l.post_id = p.id
GROUP BY u.id, u.username, u.avatar
ORDER BY total_likes DESC
LIMIT 50
`;
}
// (c) Batch insert optimisé
async function bulkCreatePosts(posts) {
const values = posts.map(p =>
`(${p.authorId}, '${p.title.replace(/'/g, "''")}', '${p.content.replace(/'/g, "''")}', NOW())`
).join(',');
return prisma.$executeRaw`
INSERT INTO posts (author_id, title, content, created_at)
VALUES ${Prisma.raw(values)}
ON CONFLICT DO NOTHING
`;
}