Les bases de données

Dans la leçon précédente, tu as vu que le backend parle à une base de données pour lire et écrire des données. Mais pourquoi une base de données et pas simplement un fichier JSON sur le serveur ? Cette leçon répond à cette question, puis te donne les fondations du modèle relationnel : tables, lignes, clés, et les cinq commandes SQL que tu utiliseras partout.

Pourquoi pas des fichiers JSON ?

C'est la première question que tout le monde a. Un fichier JSON est lisible, simple à éditer, et ne nécessite aucun outil supplémentaire. Alors, pourquoi s'embêter ?

Problème 1 : la concurrence. Si deux utilisateurs écrivent en même temps dans le même fichier JSON, tu as un conflit. L'un des deux écrasera les données de l'autre. Sur un seul utilisateur en local, ça passe. Dès que ton app a plusieurs utilisateurs simultanés, tu perds des données.

Problème 2 : les requêtes. Pour trouver tous les articles de Vicente publiés en mai, il faut charger tout le fichier en mémoire, puis filtrer en JavaScript. Avec 1 000 lignes, c'est supportable. Avec 100 000, ton serveur ralentit. Une base de données, elle, exécute ce filtrage côté stockage, sans tout charger.

Problème 3 : l'intégrité. Rien n'empêche quelqu'un de mettre une chaîne de caractères là où un nombre est attendu, ou de réutiliser un identifiant déjà existant. Un fichier JSON n'a pas de contraintes. Une base de données, si : elle refuse les données invalides avant même qu'elles soient écrites.

Problème 4 : les transactions. Imagine un virement bancaire : tu dois débiter un compte et en créditer un autre. Si le serveur plante entre les deux opérations, tu te retrouves avec un débit sans crédit. Une base de données garantit l'atomicité : soit les deux opérations passent ensemble, soit aucune ne passe.

Le modèle relationnel : tables, lignes, colonnes

Une base de données relationnelle est un ensemble de tables. Chaque table a un schéma (un nom de colonne + un type) et des lignes (les données réelles, aussi appelées enregistrements ou records).

Exemple concret avec deux tables :

Schéma ER simplifié : une table users et une table posts.

La table users contient les colonnes id, email et created_at. La table posts contient id, user_id, title, body et created_at. Le lien entre les deux, c'est la clé étrangère user_id.

Ce modèle est appelé relationnel parce que les tables se relient entre elles via ces clés. C'est la fondation de PostgreSQL, MySQL, SQLite, et de tout ce que Supabase utilise sous le capot.

Clés primaires et clés étrangères

Deux concepts que tu croiseras partout :

Clé primaire (primary key) : une colonne dont la valeur identifie chaque ligne de manière unique. Dans users, c'est id. Deux utilisateurs ne peuvent pas avoir le même id. La base de données crée automatiquement un index sur cette colonne pour accélérer les recherches.

Clé étrangère (foreign key) : une colonne qui pointe vers la clé primaire d'une autre table. Dans posts, user_id est une clé étrangère qui référence users.id. Cela crée un lien explicite : un post appartient toujours à un utilisateur existant. Si tu essaies d'insérer un post avec un user_id qui n'existe pas dans users, la base refuse l'opération.

SQL en 5 commandes

SQL (Structured Query Language) est le langage utilisé pour parler à une base de données relationnelle. Il y a cinq opérations fondamentales, souvent résumées par l'acronyme CRUD (Create, Read, Update, Delete) plus la création de table.

-- Créer la table
CREATE TABLE posts (
  id        SERIAL PRIMARY KEY,
  user_id   INT REFERENCES users(id),
  title     TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insérer une ligne
INSERT INTO posts (user_id, title) VALUES (1, 'Hello world');

-- Lire des lignes
SELECT * FROM posts WHERE user_id = 1;

-- Modifier une ligne
UPDATE posts SET title = 'Nouveau titre' WHERE id = 1;

-- Supprimer une ligne
DELETE FROM posts WHERE id = 1;

Quelques mots sur chaque commande :

• CREATE TABLE définit le schéma : les noms de colonnes, leurs types (INT, TEXT, TIMESTAMPTZ), et les contraintes (NOT NULL, REFERENCES). SERIAL est un entier auto-incrémenté par PostgreSQL.
• INSERT INTO ajoute une ligne. Tu précises les colonnes et les valeurs correspondantes.
• SELECT lit des lignes. L'étoile * signifie "toutes les colonnes". La clause WHERE filtre les résultats : seuls les posts de l'utilisateur 1 sont retournés.
• UPDATE modifie des lignes existantes. Sans clause WHERE, toutes les lignes de la table seraient modifiées. Toujours préciser un filtre.
• DELETE supprime des lignes. Même règle : sans WHERE, toute la table est vidée.

Jointures : croiser deux tables en une requête

Une fois que tes données sont dans deux tables séparées, tu as souvent besoin de les croiser. Par exemple : afficher le titre d'un post et l'email de son auteur.

Sans jointure, tu ferais deux requêtes : une pour récupérer les posts, une pour récupérer l'email de chaque auteur. C'est le problème dit "N+1" : 1 requête pour la liste, puis N requêtes pour les détails. Pour 100 posts, ça fait 101 allers-retours vers la base de données.

Avec une jointure, c'est une seule requête :

SELECT posts.title, users.email
FROM posts
JOIN users ON posts.user_id = users.id;

La clause JOIN users ON posts.user_id = users.id dit à la base : "croise les lignes de posts et de users là où user_id correspond à id". Le résultat est un tableau qui combine les colonnes des deux tables.

Un seul aller-retour, peu importe le nombre de lignes.

Index : accélérer les recherches

Par défaut, une requête SELECT WHERE user_id = 1 sur une table de 500 000 lignes va lire toutes les lignes une par une pour trouver celles qui correspondent. C'est un scan complet de la table, en O(n).

Un index change ça. C'est une structure de données interne (souvent un arbre B) qui permet de trouver les lignes correspondantes directement, en O(log n).

La clé primaire crée automatiquement un index. Pour les autres colonnes sur lesquelles tu filtreras souvent, crée un index explicitement :

CREATE INDEX ON posts(user_id);

Après ça, chaque SELECT ... WHERE user_id = X est logarithmique plutôt que linéaire. Sur une table de 500 000 lignes, la différence est de plusieurs ordres de grandeur.

SQL vs NoSQL

Tout ce qui précède s'applique aux bases de données SQL (ou relationnelles). Il existe une autre famille : les bases NoSQL, qui stockent les données différemment.

Transactions : garantir l'atomicité

Une transaction regroupe plusieurs opérations SQL en un bloc indivisible. Soit tout passe, soit rien.

Exemple : transférer 10 euros du compte A vers le compte B.

BEGIN;

UPDATE comptes SET solde = solde - 10 WHERE id = 1;
UPDATE comptes SET solde = solde + 10 WHERE id = 2;

COMMIT;

Si le serveur plante entre les deux UPDATE, PostgreSQL annule automatiquement la transaction incomplète. Le compte A n'est pas débité, le compte B n'est pas crédité. Les données restent cohérentes.

Pour annuler manuellement une transaction en cours (par exemple si une vérification échoue) : ROLLBACK; à la place de COMMIT;.