5 exercices · Solutions masquées
Créez un module db.js qui exporte un Pool configuré via variables d'environnement, avec gestion des erreurs, SSL conditionnel (production), et une méthode healthCheck() qui retourne { ok: true, latency: Xms }.
// db.js
const { Pool } = require('pg');
const pool = new Pool({
host: process.env.PG_HOST || 'localhost',
port: parseInt(process.env.PG_PORT || '5432'),
database: process.env.PG_DATABASE || 'myapp',
user: process.env.PG_USER || 'postgres',
password: process.env.PG_PASSWORD,
max: parseInt(process.env.PG_POOL_MAX || '10'),
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
ssl: process.env.NODE_ENV === 'production'
? { rejectUnauthorized: false } : false
});
pool.on('error', (err) => {
console.error('[DB] Unexpected error on idle client:', err);
});
async function healthCheck() {
const start = Date.now();
try {
await pool.query('SELECT 1');
return { ok: true, latency: Date.now() - start };
} catch (err) {
return { ok: false, error: err.message };
}
}
module.exports = { pool, healthCheck };Créez un UserRepository avec les méthodes : findById(id), findByEmail(email), create(data), update(id, data), delete(id) et findAll({ limit, offset, role }). Toutes les requêtes doivent être paramétrées.
// repositories/userRepository.js
const { pool } = require('../db');
class UserRepository {
async findById(id) {
const { rows } = await pool.query(
'SELECT id, email, name, role, created_at FROM users WHERE id = $1', [id]
);
return rows[0] || null;
}
async findByEmail(email) {
const { rows } = await pool.query(
'SELECT * FROM users WHERE email = lower($1)', [email]
);
return rows[0] || null;
}
async create({ email, name, role = 'user' }) {
const { rows } = await pool.query(
`INSERT INTO users (email, name, role, created_at)
VALUES (lower($1), $2, $3, NOW()) RETURNING id, email, name, role, created_at`,
[email, name, role]
);
return rows[0];
}
async update(id, data) {
const sets = [], values = [];
let i = 1;
if (data.name !== undefined) { sets.push(`name = $${i++}`); values.push(data.name); }
if (data.role !== undefined) { sets.push(`role = $${i++}`); values.push(data.role); }
if (data.email !== undefined) { sets.push(`email = lower($${i++})`); values.push(data.email); }
if (!sets.length) return null;
sets.push(`updated_at = NOW()`);
values.push(id);
const { rows } = await pool.query(
`UPDATE users SET ${sets.join(', ')} WHERE id = $${i} RETURNING *`, values
);
return rows[0] || null;
}
async delete(id) {
const { rowCount } = await pool.query('DELETE FROM users WHERE id = $1', [id]);
return rowCount > 0;
}
async findAll({ limit = 20, offset = 0, role } = {}) {
const values = [limit, offset];
let where = '';
if (role) { where = 'WHERE role = $3'; values.push(role); }
const { rows } = await pool.query(
`SELECT id, email, name, role, created_at FROM users ${where}
ORDER BY created_at DESC LIMIT $1 OFFSET $2`, values
);
return rows;
}
}
module.exports = new UserRepository();Implémentez une fonction placeOrder(userId, items) qui crée une commande dans une transaction : vérifier le stock de chaque article, décrémenter les stocks, créer la commande et ses lignes, tout en faisant un ROLLBACK complet si un article est en rupture.
const { pool } = require('./db');
async function placeOrder(userId, items) {
const client = await pool.connect();
try {
await client.query('BEGIN');
let total = 0;
const checkedItems = [];
for (const item of items) {
const { rows } = await client.query(
'SELECT id, name, price, stock FROM products WHERE id = $1 FOR UPDATE',
[item.productId]
);
if (!rows[0]) throw new Error(`Produit ${item.productId} introuvable`);
if (rows[0].stock < item.qty) {
throw new Error(`Stock insuffisant pour "${rows[0].name}" (dispo: ${rows[0].stock})`);
}
checkedItems.push({ ...rows[0], qty: item.qty });
total += rows[0].price * item.qty;
}
// Créer la commande
const { rows: [order] } = await client.query(
`INSERT INTO orders (user_id, total, status, created_at)
VALUES ($1, $2, 'pending', NOW()) RETURNING id`,
[userId, total]
);
// Lignes de commande + décrémentation stock
for (const item of checkedItems) {
await client.query(
'INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES ($1,$2,$3,$4)',
[order.id, item.id, item.qty, item.price]
);
await client.query(
'UPDATE products SET stock = stock - $1 WHERE id = $2',
[item.qty, item.id]
);
}
await client.query('COMMIT');
return { orderId: order.id, total };
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}Créez un trigger PostgreSQL qui notifie sur le canal 'order_update' à chaque changement de statut d'une commande. Côté Node.js, créez un listener qui log les changements et, si le statut passe à 'shipped', envoie un email simulé.
-- Trigger PostgreSQL
CREATE OR REPLACE FUNCTION notify_order_status() RETURNS trigger AS $$
BEGIN
IF OLD.status IS DISTINCT FROM NEW.status THEN
PERFORM pg_notify('order_update', json_build_object(
'orderId', NEW.id,
'userId', NEW.user_id,
'oldStatus', OLD.status,
'newStatus', NEW.status
)::text);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_order_status_notify
AFTER UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order_status();// Node.js listener
const { pool } = require('./db');
async function startOrderListener() {
const client = await pool.connect();
await client.query('LISTEN order_update');
console.log('Listening for order updates...');
client.on('notification', async (msg) => {
try {
const data = JSON.parse(msg.payload);
console.log(`Order #${data.orderId}: ${data.oldStatus} → ${data.newStatus}`);
if (data.newStatus === 'shipped') {
await sendShippingEmail(data.userId, data.orderId);
}
} catch (err) {
console.error('Notification error:', err);
}
});
client.on('error', () => {
client.release(true);
setTimeout(startOrderListener, 5000); // reconnexion
});
}
async function sendShippingEmail(userId, orderId) {
console.log(`[EMAIL] Shipping notification → user ${userId}, order #${orderId}`);
}
startOrderListener();Créez une route Express GET /export/orders qui streame toutes les commandes d'une année en format CSV (sans charger tout en mémoire) en utilisant pg-query-stream. La réponse doit inclure les en-têtes CSV corrects.
const express = require('express');
const { QueryStream } = require('pg-query-stream');
const { pool } = require('./db');
const router = express.Router();
router.get('/export/orders', async (req, res) => {
const year = parseInt(req.query.year) || new Date().getFullYear();
res.setHeader('Content-Type', 'text/csv; charset=utf-8');
res.setHeader('Content-Disposition', `attachment; filename="orders-${year}.csv"`);
// En-tête CSV
res.write('id,user_id,total,status,created_at\n');
const client = await pool.connect();
try {
const query = new QueryStream(
`SELECT id, user_id, total, status, created_at
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = $1
ORDER BY created_at`,
[year],
{ highWaterMark: 100 }
);
const stream = client.query(query);
stream.on('data', (row) => {
res.write(`${row.id},${row.user_id},${row.total},${row.status},${row.created_at.toISOString()}\n`);
});
stream.on('end', () => {
res.end();
client.release();
});
stream.on('error', (err) => {
client.release(true);
if (!res.headersSent) res.status(500).json({ error: err.message });
});
} catch (err) {
client.release(true);
res.status(500).json({ error: err.message });
}
});
module.exports = router;