Many to many et transactions fr FR

Résumé : Découvrez comment gérer des relations de base de données complexes (many-to-many) et garantir l'intégrité des données à l'aide de transactions SQL, sans vous appuyer sur un ORM.

Les relations many-to-many

Dans StartER, la ressource par défaut item est simple : un utilisateur possède plusieurs items (une relation 1-à-N). Mais les applications réelles nécessitent souvent des relations many-to-many (M:N) (plusieurs-à-plusieurs).

Imaginons que nous souhaitions introduire une ressource group.

Un user peut appartenir à plusieurs group.
Un group peut comporter plusieurs user.

La table de jointure

Les bases de données relationnelles ne peuvent pas stocker directement les relations M:N. Au lieu de cela, vous devez utiliser une troisième table appelée table de jointure (ou table d'association) qui se situe entre les deux entités.

Tip

Convention de nommage : c'est une bonne pratique très répandue dans l'industrie de nommer les tables de jointure en combinant les noms des deux entités au singulier et par ordre alphabétique. Parce que 'g' vient avant 'u', la table doit être nommée group_user (et non user_group).

Notre schéma ressemblerait à ceci :

CREATE TABLE "group" (
  id INTEGER PRIMARY KEY NOT NULL,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE group_user (
  group_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  PRIMARY KEY (group_id, user_id),
  FOREIGN KEY (group_id) REFERENCES "group" (id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE
);

Le problème ACID

Lorsqu'un utilisateur crée un nouveau groupe, vous devez faire deux choses :

Insérer le nouveau groupe dans la table group.
Insérer un lien dans la table group_user pour faire du créateur un membre du groupe.

// 1. Créer le groupe
const { lastInsertRowid: groupId } = database
  .prepare('INSERT INTO "group" (name) VALUES (?)')
  .run(name);

// 2. Ajouter le créateur au groupe
const { lastInsertRowid: groupUserId } = database
  .prepare("INSERT INTO group_user (group_id, user_id) VALUES (?, ?)")
  .run(groupId, creatorUserId);

Que se passe-t-il si la deuxième requête échoue ? (ex: le user_id n'existe pas, ou le serveur plante exactement entre les deux requêtes). La base de données contiendra un group qui n'a aucun membre. C'est ce qu'on appelle un enregistrement "orphelin", et cela brise l'intégrité des données.

Nous avons besoin que les deux requêtes réussissent complètement, ou échouent complètement. C'est ce que garantissent les propriétés ACID (Atomicité, Cohérence, Isolation, Durabilité), et plus spécifiquement l'Atomicité.

Les transactions SQL

Une transaction regroupe plusieurs instructions SQL en une seule opération "tout ou rien".

Parce que StartER utilise du SQL brut (Zero-Magic) via node:sqlite, implémenter une transaction est incroyablement simple. Vous enveloppez vos requêtes entre BEGIN et COMMIT. Si un problème survient, vous interceptez l'erreur (catch) et exécutez ROLLBACK pour tout annuler.

Exemple de code

Voici comment vous écririez la fonction create dans src/express/modules/group/groupRepository.ts :

create(name: string, creatorUserId: string) {
  // 1. Démarrer la transaction
  database.prepare("BEGIN").run();

  try {
    // 2. Exécuter les requêtes
    const { lastInsertRowid: groupId } = database
      .prepare('INSERT INTO "group" (name) VALUES (?)')
      .run(name);

    const { lastInsertRowid: groupUserId } = database
      .prepare("INSERT INTO group_user (group_id, user_id) VALUES (?, ?)")
      .run(groupId, creatorUserId);

    // 3. Si tout a réussi, valider (commit) les changements dans la base de données
    database.prepare("COMMIT").run();

    return { groupId, groupUserId };
  } catch (error) {
    // 4. Si n'importe quelle requête a jeté une erreur, annuler la transaction entière
    database.prepare("ROLLBACK").run();
    
    // Relancer l'erreur pour que l'Action Express puisse l'attraper et renvoyer une 500
    throw error; 
  }
}

Important

Mettez toujours votre ROLLBACK à l'intérieur d'un bloc catch et relancez (re-throw) l'erreur. Si vous oubliez le ROLLBACK, la connexion à la base de données risque de rester bloquée dans l'état de transaction, provoquant le gel de votre application lors des requêtes suivantes.

Lire des données many-to-many (JOINs)

Créer des enregistrements en toute sécurité n'est que la moitié de la bataille. C'est lors de la lecture de données many-to-many en SQL brut que les développeurs regrettent généralement la magie des ORMs.

Lorsque vous souhaitez récupérer un groupe et ses utilisateurs, vous avez deux manières principales de le faire en SQL "Zero-Magic".

Note

Dans un souci de simplicité, les exemples suivants s'appuient sur l'inférence de types de TypeScript pour le type de retour. Dans une application réelle, vous devriez définir explicitement les types de retour (ex: GroupWithUsers) dans votre fichier types.ts pour garantir une sécurité de typage stricte de bout en bout !

Approche 1 : la méthode à deux requêtes (recommandé)

La façon la plus propre et la plus lisible de structurer cela dans votre Repository est de simplement exécuter deux requêtes séparées et de combiner les résultats en JavaScript.

getGroupWithUsers(groupId: number) {
  // 1. Récupérer le groupe
  const group = database.prepare('SELECT * FROM "group" WHERE id = ?').get(groupId);
  if (!group) return null;

  // 2. Récupérer les utilisateurs associés en utilisant un INNER JOIN
  const users = database.prepare(`
    SELECT user.* 
    FROM user
    JOIN group_user ON user.id = group_user.user_id
    WHERE group_user.group_id = ?
  `).all(groupId);

  // 3. Assembler et renvoyer l'objet complet
  return { 
    ...group, 
    users 
  };
}

Pourquoi c'est mieux : C'est incroyablement facile à lire. Vous n'obtenez pas de lignes en double, et le tableau users est naturellement formaté comme une liste d'objets utilisateur.

Approche 2 : l'agrégation JSON en une seule requête (avancé)

Si vous devez absolument le faire en un seul aller-retour vers la base de données, SQLite moderne possède de puissantes fonctions JSON intégrées (comme json_group_array) qui vous permettent d'agréger les lignes jointes dans un tableau JSON directement à l'intérieur de la requête SQL.

getGroupWithUsers(groupId: number) {
  const row = database.prepare(`
    SELECT 
      "group".id, 
      "group".name,
      json_group_array(
        json_object('id', user.id, 'email', user.email)
      ) as users
    FROM "group"
    LEFT JOIN group_user ON "group".id = group_user.group_id
    LEFT JOIN user ON group_user.user_id = user.id
    WHERE "group".id = ?
    GROUP BY "group".id
  `).get(groupId);

  // SQLite renvoie `users` comme une chaîne JSON, vous devez donc la parser
  if (row) {
    row.users = JSON.parse(row.users);
  }

  return row;
}

Pourquoi c'est plus délicat : Si le groupe n'a aucun utilisateur, le LEFT JOIN de SQLite pourrait résulter en [{"id": null, "email": null}], ce qui nécessite une logique de nettoyage supplémentaire en JavaScript. C'est pourquoi l'Approche 1 est généralement le modèle "Zero-Magic" préféré !

Résumé

En utilisant BEGIN, COMMIT et ROLLBACK, vous vous assurez que les opérations complexes sur plusieurs tables maintiennent une intégrité parfaite des données. Vous avez maintenant les outils pour construire des équipes, des rôles, des projets collaboratifs, ou n'importe quelle autre fonctionnalité many-to-many !