PostgreSQL et Node.js : des patterns qui scalent

PostgreSQL est la base de données que la plupart des backends Node.js devraient choisir par défaut. Elle gère les données relationnelles, les documents JSON, la recherche full-text, les requêtes géospatiales et les écritures concurrentes sans broncher. Mais connecter Node.js à Postgres correctement demande plus qu’un pg.query(). Le connection pooling, les migrations, les transactions, les requêtes paramétrées et la stratégie d’indexation font la différence entre un prototype qui fonctionne et un système de production qui reste rapide sous charge.

Se connecter avec un pool, pas un client

Une seule connexion à la base gère une requête à la fois. Sous des requêtes concurrentes, tout se met en file derrière un seul goulot d’étranglement. Un connection pool maintient un ensemble de connexions réutilisables et les distribue selon les besoins.

import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

export async function findUserByEmail(email: string) {
  const result = await pool.query('SELECT id, name, email, role FROM users WHERE email = $1', [
    email,
  ]);
  return result.rows[0] ?? null;
}

Le paramètre max: 20 signifie que jusqu’à 20 requêtes concurrentes peuvent tourner sans attendre. idleTimeoutMillis ferme les connexions inutilisées, empêchant les fuites de ressources. connectionTimeoutMillis échoue rapidement au lieu de bloquer quand la base est injoignable. Utilisez toujours des requêtes paramétrées ($1, $2) au lieu de l’interpolation de chaînes pour prévenir les injections SQL.

Migrations : des changements de schéma versionnés

Les changements de schéma ne devraient jamais être appliqués à la main. Les migrations tracent chaque CREATE TABLE, ALTER COLUMN et CREATE INDEX comme des fichiers versionnés qui s’exécutent dans l’ordre et peuvent être annulés.

import { Pool } from 'pg';

export async function up(pool: Pool) {
  await pool.query(`
    CREATE TABLE users (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      role VARCHAR(50) NOT NULL DEFAULT 'user',
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);
}

export async function down(pool: Pool) {
  await pool.query('DROP TABLE IF EXISTS users');
}

import { Pool } from 'pg';

export async function up(pool: Pool) {
  await pool.query(`
    CREATE TABLE orders (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      total_cents INTEGER NOT NULL,
      status VARCHAR(50) NOT NULL DEFAULT 'pending',
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);

  await pool.query(`
    CREATE INDEX idx_orders_user_id ON orders(user_id)
  `);
}

export async function down(pool: Pool) {
  await pool.query('DROP TABLE IF EXISTS orders');
}

Chaque migration a un up et un down. Le runner suit quelles migrations ont été appliquées dans une table migrations et n’exécute que les nouvelles. Ajouter une colonne, renommer une table, créer un index, tout est versionné et reproductible dans chaque environnement.

Transactions : tout ou rien

Quand plusieurs requêtes doivent réussir ensemble, une transaction garantit l’atomicité. Si une étape échoue, tout est annulé et la base reste cohérente.

export async function createOrder(userId: string, items: CartItem[]) {
  const client = await pool.connect();

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

    const orderResult = await client.query(
      `INSERT INTO orders (user_id, total_cents, status)
       VALUES ($1, $2, 'confirmed')
       RETURNING id`,
      [userId, calculateTotal(items)]
    );

    const orderId = orderResult.rows[0].id;

    for (const item of items) {
      await client.query(
        `INSERT INTO order_items (order_id, product_id, quantity, price_cents)
         VALUES ($1, $2, $3, $4)`,
        [orderId, item.productId, item.quantity, item.priceCents]
      );

      await client.query(`UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1`, [
        item.quantity,
        item.productId,
      ]);
    }

    await client.query('COMMIT');
    return orderId;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Le client.release() dans finally est critique. Sans lui, la connexion reste empruntée au pool indéfiniment, finissant par épuiser toutes les connexions disponibles. La transaction garantit que si la déduction de stock échoue, la commande et ses articles ne sont pas créés non plus.

Une fonction helper supprime le boilerplate de chaque transaction.

export async function withTransaction<T>(fn: (client: PoolClient) => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

export async function createOrder(userId: string, items: CartItem[]) {
  return withTransaction(async (client) => {
    const { rows } = await client.query(
      `INSERT INTO orders (user_id, total_cents) VALUES ($1, $2) RETURNING id`,
      [userId, calculateTotal(items)]
    );
    // ... reste de la logique
    return rows[0].id;
  });
}

Indexation : le plus gros levier de performance

Une requête sans index correspondant scanne chaque ligne de la table. Sur 10 000 lignes, personne ne remarque. Sur 10 millions, la requête prend des secondes au lieu de millisecondes. Les index résolvent ça, mais les mauvais index gaspillent de l’espace disque et ralentissent les écritures.

-- Recherches par correspondance exacte
CREATE INDEX idx_users_email ON users(email);

-- Requêtes par plage et tri
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Index composite pour requêtes filtrées + triées
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Index partiel : indexer uniquement ce qui compte
CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

L’index composite sur (user_id, status) sert les requêtes qui filtrent par utilisateur et statut, ou par utilisateur seul. Il n’aide pas les requêtes qui filtrent par statut seul, car les index composites fonctionnent de gauche à droite.

Les index partiels sont une spécialité de PostgreSQL. L’index des commandes en attente est plus petit et plus rapide qu’un index complet parce qu’il n’inclut que les lignes correspondant à la condition. Si 95% des commandes sont terminées, cet index est 20x plus petit qu’un index complet.

Utilisez EXPLAIN ANALYZE pour vérifier que les requêtes utilisent réellement vos index.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = '...' AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;

La sortie montre si PostgreSQL a choisi un index scan ou un sequential scan, combien de lignes il a examinées, et combien de temps chaque étape a pris. Si vous voyez Seq Scan sur une grande table, il vous manque un index.

Colonnes JSON : le meilleur des deux mondes

Le type jsonb de PostgreSQL permet de stocker des données flexibles à côté de colonnes structurées. Il supporte l’indexation, les requêtes et les mises à jour partielles sans sacrifier le modèle relationnel.

await pool.query(
  `INSERT INTO products (name, price_cents, metadata)
   VALUES ($1, $2, $3)`,
  [
    'Wireless Keyboard',
    7999,
    JSON.stringify({
      brand: 'Acme',
      weight: '450g',
      connectivity: ['bluetooth', 'usb-c'],
    }),
  ]
);

-- Requête dans le JSON
SELECT * FROM products
WHERE metadata->>'brand' = 'Acme';

-- Filtrer sur un tableau imbriqué
SELECT * FROM products
WHERE metadata->'connectivity' ? 'bluetooth';

-- Indexer les champs JSON pour la performance
CREATE INDEX idx_products_brand ON products ((metadata->>'brand'));

La colonne jsonb gère les attributs produit qui varient par catégorie sans nécessiter une table d’attributs séparée ou un pattern EAV. Les claviers ont la connectivité et le poids. Les t-shirts ont la taille et la couleur. Les colonnes structurées (name, price_cents) restent relationnelles et indexées, tandis que les données flexibles vivent en JSON.

Recherche full-text sans Elasticsearch

PostgreSQL intègre une recherche full-text qui couvre la plupart des cas d’usage sans ajouter un service supplémentaire à votre stack.

ALTER TABLE products ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B')
  ) STORED;

CREATE INDEX idx_products_search ON products USING GIN(search_vector);

export async function searchProducts(query: string) {
  const result = await pool.query(
    `SELECT id, name, description,
            ts_rank(search_vector, websearch_to_tsquery('english', $1)) AS rank
     FROM products
     WHERE search_vector @@ websearch_to_tsquery('english', $1)
     ORDER BY rank DESC
     LIMIT 20`,
    [query]
  );
  return result.rows;
}

La colonne générée maintient automatiquement le vecteur de recherche quand les données changent. setweight donne au nom du produit une pertinence plus élevée que la description. websearch_to_tsquery parse les requêtes de recherche naturelles comme “wireless keyboard bluetooth” en une expression de recherche valide. L’index GIN rend ça rapide même sur des millions de lignes.

Conclusion

PostgreSQL gère bien plus que du simple CRUD. Les transactions gardent les données cohérentes. Les index rendent les requêtes rapides. Les colonnes JSON ajoutent de la flexibilité sans abandonner le modèle relationnel. La recherche full-text élimine une dépendance externe. Combiné avec un connection pooling propre et une discipline de migration, PostgreSQL est la colonne vertébrale qui permet aux backends Node.js de scaler du prototype à la production sans changer de base de données en cours de route.