backend / nodejs

Bases de données et pooling

Leçon 101 exercice

Explication

Ce que vous allez apprendre

Comprendre pourquoi un pool de connexions évite d'ouvrir une connexion par requête
Écrire des requêtes paramétrées pour prévenir l'injection SQL
Utiliser une transaction (BEGIN/COMMIT/ROLLBACK) pour garantir l'atomicité
Obtenir un client dédié du pool avec pool.connect() et le libérer avec release()
Situer un ORM comme Prisma par rapport à ces mêmes principes

Dans quel contexte ?

Une route de transfert d'argent POST /accounts/:id/transfer doit débiter un compte et créditer un autre en une seule opération : si le crédit échoue après que le débit a réussi, de l'argent disparaîtrait purement et simplement. Un autre endpoint, une recherche d'utilisateur par email construite en concaténant directement la valeur reçue dans la requête SQL, expose l'application à une injection SQL classique. Cette leçon couvre les deux problèmes : garantir l'atomicité d'une opération multi-étapes, et sécuriser structurellement les requêtes contre l'injection.

D'abord, pourquoi ne pas ouvrir une connexion à chaque requête

Établir une connexion à une base de données implique plusieurs étapes coûteuses. Une poignée de main réseau (TCP), potentiellement du chiffrement (TLS), et une authentification — un processus qui prend un temps non négligeable.

Ouvrir et fermer une nouvelle connexion à CHAQUE requête HTTP serait donc extrêmement lent. Ça gaspillerait aussi des ressources pour rien.

Le pooling résout exactement ce problème. Un ensemble de connexions déjà établies est maintenu ouvert et réutilisé entre les requêtes, un peu comme une flotte de taxis déjà en service plutôt que d'en fabriquer un nouveau à chaque client.

Une fois les connexions gérées efficacement, un danger bien plus grave nous attend : l'injection SQL. Construire une requête en concaténant directement une valeur venant de l'utilisateur est une des vulnérabilités les plus anciennes et dangereuses du web.

Un utilisateur malveillant peut alors injecter du SQL arbitraire dans ce champ. Il peut ainsi contourner l'authentification ou extraire toute la base de données.

Piège dangereux

Construire une requête SQL en insérant directement la variable email dans la chaîne de caractères permet à un attaquant d'envoyer une valeur comme ' OR '1'='1 pour contourner entièrement la vérification. Toujours utiliser des requêtes paramétrées ($1, $2), jamais de concaténation directe d'une valeur utilisateur dans le SQL.

Les requêtes paramétrées ($1, $2) empêchent structurellement ce problème. La valeur est toujours traitée comme une DONNÉE, jamais comme du code SQL à exécuter — une règle à appliquer systématiquement, sans exception.

Une fois les requêtes sécurisées, il reste un besoin à couvrir : garantir que plusieurs opérations réussissent ENSEMBLE. Transférer de l'argent d'un compte à un autre ne doit jamais débiter un compte sans créditer l'autre.

Une transaction (BEGIN/COMMIT/ROLLBACK) garantit cette atomicité. Un détail technique important : elle nécessite un client DÉDIÉ obtenu via pool.connect(), pas le pool directement.

Méthode	Connexion utilisée	Pour quel usage
`pool.query(...)`	Empruntée puis rendue automatiquement	Une requête isolée, sans transaction
`pool.connect()` puis `client.query(...)`	Un client dédié, à libérer soi-même	Plusieurs requêtes liées (transaction)

Ce client doit impérativement être rendu au pool avec release() dans un bloc finally. L'oublier finit par épuiser le pool de connexions disponibles, un bug sournois qui n'apparaît qu'en charge réelle.

Pour aller plus loin, sache qu'un ORM comme Prisma ajoute une couche de typage au-dessus de ces mêmes principes. Ça accélère le développement, mais reste construit sur exactement ce qu'on vient de voir.

Le piège fréquent à connaître avant de pratiquer : utiliser FOR UPDATE sans en comprendre l'implication bloque la ligne concernée pour les autres transactions concurrentes — indispensable pour éviter des races conditions, mais source de blocages (deadlocks) si mal utilisé.

Commandes & code

Bases de données et pooling

// db.js — pool de connexions PostgreSQL avec le driver "pg"
import pg from "pg";

const { Pool } = pg;

export const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  max: 20,                        // nombre max de connexions simultanées dans le pool
  idleTimeoutMillis: 30_000,       // ferme les connexions inactives après 30s
  connectionTimeoutMillis: 5_000,  // échoue si aucune connexion dispo sous 5s
});

pool.on("error", (err) => {
  console.error("Erreur inattendue sur une connexion inactive du pool:", err);
});

// Requêtes paramétrées — TOUJOURS, jamais de concaténation de string (injection SQL)
export async function getUserByEmail(email) {
  const result = await pool.query(
    "SELECT id, name, email FROM users WHERE email = $1",
    [email]
  );
  return result.rows[0] ?? null;
}

// JAMAIS ceci :
// pool.query(`SELECT * FROM users WHERE email = '${email}'`) // injection SQL possible

// Transactions — plusieurs opérations atomiques via un client dédié du pool
export async function transferBalance(fromId, toId, amount) {
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    const { rows: [sender] } = await client.query(
      "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", // verrou pessimiste
      [fromId]
    );

    if (sender.balance < amount) {
      throw new Error("Solde insuffisant");
    }

    await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amount, fromId]);
    await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, toId]);

    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release(); // rend TOUJOURS la connexion au pool, même en cas d'erreur
  }
}

// Avec un ORM (Prisma) — abstraction typée au-dessus du pooling
// schema.prisma
// model User {
//   id    Int    @id @default(autoincrement())
//   email String @unique
//   posts Post[]
// }

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient({
  log: process.env.NODE_ENV === "development" ? ["query"] : [],
});

export async function getUserWithPosts(id) {
  return prisma.user.findUnique({
    where: { id },
    include: { posts: { orderBy: { createdAt: "desc" } } },
  });
}

export async function createUserWithPost(email, postTitle) {
  return prisma.$transaction(async (tx) => {
    const user = await tx.user.create({ data: { email } });
    await tx.post.create({ data: { title: postTitle, authorId: user.id } });
    return user;
  });
}

// Fermeture propre du pool à l'arrêt du serveur
process.on("SIGTERM", async () => {
  console.log("SIGTERM reçu, fermeture du pool de connexions...");
  await pool.end();
  process.exit(0);
});

Résumé

Le pooling évite d'ouvrir une nouvelle connexion TCP/TLS à chaque requête, coûteuse en latence.
Toujours utiliser des requêtes paramétrées ($1, $2) pour prévenir les injections SQL.
Une transaction nécessite un client dédié (pool.connect()), pas le pool directement, avec release() en finally.
FOR UPDATE verrouille la ligne pour éviter les races conditions lors de lectures/écritures concurrentes.

Exercices pratiques

1 disponible

Mission : de l'argent disparaît lors d'un transfert entre comptes

Objectif : Corriger une opération de transfert non atomique et une injection SQL, puis résoudre un épuisement de pool de connexions causé par un release() manquant.

Contexte

La route POST /accounts/:id/transfer exécute deux requêtes séparées : pool.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]) puis, dans un bloc séparé, la même chose pour créditer toId. Un incident en production montre que lors d'un pic de charge, le débit réussit parfois sans que le crédit ne s'exécute (le serveur redémarre entre les deux requêtes), et de l'argent disparaît. Une autre route de recherche construit sa requête avec pool.query('SELECT * FROM users WHERE email = \'' + req.query.email + '\'').

Résoudre l’exercice →