La base de données : SQL

Depuis l'étape 1, tes tâches vivent dans tasks.json. Ça marche pour toi tout seul. Pour un vrai site, ça coince vite :

• Deux requêtes en même temps : deux personnes ajoutent une tâche à la même seconde. Chacune lit le fichier, ajoute sa tâche, réécrit tout. La deuxième écrase la première : une tâche perdue.
• Chercher vite : pour trouver une tâche, tu relis tout le fichier à chaque fois. Avec 100 000 lignes, ton serveur rame.
• Les gros volumes : pour changer une seule tâche, tu réécris le fichier entier.
• Les relations : des utilisateurs qui ont des listes, qui ont des tâches... dans un fichier JSON, ça devient un plat de spaghettis.

Une base de données règle tout ça. Tu lui parles avec le SQL, un langage fait pour ranger et retrouver des données. Les données sont rangées dans des tables, comme un tableur : une ligne par tâche, une colonne par info (id, text, done).

On prend SQLite : toute la base tient dans un seul fichier, et elle est intégrée à Node. Rien à installer, aucun compte à créer. À l'étape 7, tu passeras à Postgres pour la mise en ligne : le SQL que t'apprends ici restera presque le même.

1. Vérifie ta version de Node : il faut Node 22 ou plus récent. Si t'as plus vieux, reprends la version LTS sur nodejs.org.
2. Dans VS Code, installe l'extension SQLite Viewer (de Florian Klampfer). Elle affiche le contenu d'une base comme un tableau.
3. Pour t'échauffer, joue à SQL Murder Mystery (en anglais) : une enquête policière à résoudre avec des requêtes SQL. Tu préfères des exercices guidés ? SQLBolt (en anglais aussi), leçons 1 à 6. Pas besoin de tout finir, reviens-y pendant l'étape.

$ node --version
$ node -e "require('node:sqlite'); console.log('SQLite est prêt')"

• node -e "..." : exécute le bout de code entre guillemets, sans créer de fichier.
• node:sqlite : le module SQLite livré avec Node. Le préfixe node: veut dire « intégré », comme node:fs à l'étape 1.

$ node --version
v24.11.0

$ node -e "require('node:sqlite'); console.log('SQLite est prêt')"
SQLite est prêt

Dans api-taches, crée un fichier db.js à la racine. Il ouvre la base et crée la table tasks si elle n'existe pas encore :

import { DatabaseSync } from "node:sqlite";

export const db = new DatabaseSync("data.db");

db.exec(`
  CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL,
    done INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
  )
`);

• new DatabaseSync("data.db") : ouvre le fichier data.db, et le crée s'il n'existe pas. db est exporté pour que les autres fichiers l'utilisent.
• db.exec(...) : exécute du SQL tel quel. Pratique pour créer des tables.
• CREATE TABLE IF NOT EXISTS : crée la table seulement la première fois. Tu peux relancer sans risque.
• INTEGER PRIMARY KEY AUTOINCREMENT : la base choisit l'id toute seule (1, 2, 3...) et ne le réutilise jamais. Fini Date.now().
• TEXT NOT NULL : du texte, obligatoire. Une tâche sans texte sera refusée par la base elle-même.
• done INTEGER ... DEFAULT 0 : SQLite n'a pas de vrai type booléen. On range 0 pour non, 1 pour oui.
• created_at ... DEFAULT CURRENT_TIMESTAMP : la date de création, remplie automatiquement.

Lance-le une fois, puis ajoute data.db au .gitignore : la base, ce sont des données, pas du code. Chacun a la sienne.

$ node db.js

node_modules/
data.db

Si Node affiche ExperimentalWarning: SQLite is an experimental feature, c'est normal : il prévient juste que le module est récent. Ça marche quand même.

$ node db.js
$ ls
cli.js   db.js         package-lock.json  public         routes     storage.js
data.db  node_modules  package.json       requests.http  server.js  tasks.json

On joue avec la base dans un fichier d'essai, sans toucher au serveur. Crée essais.js :

import { db } from "./db.js";

// On vide la table pour repartir de zéro à chaque lancement
db.exec("DELETE FROM tasks");

// INSERT : ajouter des lignes
db.exec(`
  INSERT INTO tasks (text) VALUES ('Acheter du pain');
  INSERT INTO tasks (text) VALUES ('Réviser le SQL');
  INSERT INTO tasks (text, done) VALUES ('Installer Node.js', 1);
  INSERT INTO tasks (text) VALUES ('Appeler le plombier');
`);

// SELECT : lire toutes les lignes
const toutes = db.prepare("SELECT * FROM tasks").all();
console.table(toutes);

// WHERE : seulement les tâches faites
const faites = db.prepare("SELECT id, text FROM tasks WHERE done = 1").all();
console.table(faites);

// ORDER BY + LIMIT : les 2 dernières ajoutées
const dernieres = db.prepare("SELECT id, text FROM tasks ORDER BY id DESC LIMIT 2").all();
console.table(dernieres);

// COUNT : combien de tâches à faire ?
const compte = db.prepare("SELECT COUNT(*) AS total FROM tasks WHERE done = 0").get();
console.log("À faire :", compte.total);

$ node essais.js

• INSERT INTO tasks (text) VALUES ('...') : ajoute une ligne. Les colonnes pas citées prennent leur valeur par défaut.
• SELECT * FROM tasks : toutes les colonnes de toutes les lignes. SELECT id, text : seulement ces colonnes.
• WHERE done = 1 : filtre les lignes. C'est le filter du SQL.
• ORDER BY id DESC : trie, du plus grand au plus petit (ASC : l'inverse). LIMIT 2 : pas plus de 2 lignes.
• COUNT(*) AS total : compte les lignes, et nomme le résultat total.
• db.prepare(sql) prépare une requête. .all() renvoie un tableau de lignes, .get() une seule ligne (la première).
• console.table : affiche un tableau d'objets en joli tableau dans le terminal.

Clique sur data.db : SQLite Viewer montre tes 4 lignes. Si tu ne vois pas les dernières, ferme l'onglet et rouvre-le.

$ node essais.js
┌─────────┬────┬───────────────────────┬──────┬───────────────────────┐
│ (index) │ id │ text                  │ done │ created_at            │
├─────────┼────┼───────────────────────┼──────┼───────────────────────┤
│ 0       │ 1  │ 'Acheter du pain'     │ 0    │ '2026-10-08 06:34:50' │
│ 1       │ 2  │ 'Réviser le SQL'      │ 0    │ '2026-10-08 06:34:50' │
│ 2       │ 3  │ 'Installer Node.js'   │ 1    │ '2026-10-08 06:34:50' │
│ 3       │ 4  │ 'Appeler le plombier' │ 0    │ '2026-10-08 06:34:50' │
└─────────┴────┴───────────────────────┴──────┴───────────────────────┘
┌─────────┬────┬─────────────────────┐
│ (index) │ id │ text                │
├─────────┼────┼─────────────────────┤
│ 0       │ 3  │ 'Installer Node.js' │
└─────────┴────┴─────────────────────┘
┌─────────┬────┬───────────────────────┐
│ (index) │ id │ text                  │
├─────────┼────┼───────────────────────┤
│ 0       │ 4  │ 'Appeler le plombier' │
│ 1       │ 3  │ 'Installer Node.js'   │
└─────────┴────┴───────────────────────┘
À faire : 3

Ajoute ces lignes à la fin de essais.js :

// UPDATE : cocher « Acheter du pain »
const maj = db.prepare("UPDATE tasks SET done = 1 WHERE text = 'Acheter du pain'").run();
console.log("Lignes modifiées :", maj.changes);

// DELETE : supprimer les tâches faites
const supp = db.prepare("DELETE FROM tasks WHERE done = 1").run();
console.log("Lignes supprimées :", supp.changes);

console.table(db.prepare("SELECT id, text, done FROM tasks").all());

• UPDATE tasks SET done = 1 WHERE ... : change la colonne done des lignes qui correspondent.
• DELETE FROM tasks WHERE ... : supprime les lignes qui correspondent.
• .run() : pour les requêtes qui ne renvoient pas de lignes. Il renvoie un objet, dont changes : le nombre de lignes touchées. Tu t'en serviras pour les 404.
• On cible par le texte ici, parce que les id changent à chaque lancement. Dans l'API, on ciblera par id.

Le WHERE est ta ceinture de sécurité. Sans lui, la requête touche toute la table, sans te demander confirmation, et il n'y a pas de Ctrl+Z :

-- Ce que tu voulais : cocher une seule tâche
UPDATE tasks SET done = 1 WHERE id = 3;

-- Ce que tu as tapé : TOUTES les tâches sont cochées
UPDATE tasks SET done = 1;

-- Et celle-là vide la table entière
DELETE FROM tasks;

Le DELETE FROM tasks en haut de essais.js, c'est exprès : on veut vider la table d'essai. Dans une vraie appli, jamais.

$ node essais.js
...
À faire : 3
Lignes modifiées : 1
Lignes supprimées : 2
┌─────────┬────┬───────────────────────┬──────┐
│ (index) │ id │ text                  │ done │
├─────────┼────┼───────────────────────┼──────┤
│ 0       │ 2  │ 'Réviser le SQL'      │ 0    │
│ 1       │ 4  │ 'Appeler le plombier' │ 0    │
└─────────┴────┴───────────────────────┴──────┘

Dans ton API, les valeurs viennent de l'utilisateur : req.params.id, req.body.text, req.query.done. La tentation, c'est de les coller dans la requête avec des backticks. Ne fais jamais ça. Regarde pourquoi, dans un fichier injection.js :

import { db } from "./db.js";

// Imagine que ce texte arrive d'un formulaire ou d'une URL
const recherche = "' OR '1'='1";

// DANGER : on colle le texte de l'utilisateur dans la requête
const sql = `SELECT id, text FROM tasks WHERE text = '${recherche}'`;
console.log(sql);
console.table(db.prepare(sql).all());

// LA BONNE FAÇON : un ? à la place de la valeur
const sure = db.prepare("SELECT id, text FROM tasks WHERE text = ?").all(recherche);
console.log("Avec ? :", sure.length, "résultat");

• Le texte « tapé » ferme l'apostrophe et ajoute OR '1'='1', qui est toujours vrai. La requête renvoie tout. C'est une injection SQL : l'utilisateur écrit du SQL à ta place.
• Pire avec un DELETE : si id vaut "0 OR 1=1", la requête DELETE FROM tasks WHERE id = 0 OR 1=1 vide toute la table.
• Le ? est un emplacement. La valeur est envoyée à part, et SQLite la traite toujours comme une donnée, jamais comme du SQL. Quoi qu'elle contienne.

La règle, à partir de maintenant : un ? pour chaque valeur, et les valeurs en arguments de .run(), .get() ou .all(), dans l'ordre :

// Un ? par valeur, dans l'ordre
db.prepare("INSERT INTO tasks (text, done) VALUES (?, ?)").run("Arroser les plantes", 0);

// .get : une seule ligne (ou undefined si rien ne correspond)
const task = db.prepare("SELECT * FROM tasks WHERE id = ?").get(2);

// .all : un tableau de lignes
const rows = db.prepare("SELECT * FROM tasks WHERE done = ? ORDER BY id").all(0);

$ node injection.js
SELECT id, text FROM tasks WHERE text = '' OR '1'='1'
┌─────────┬────┬───────────────────────┐
│ (index) │ id │ text                  │
├─────────┼────┼───────────────────────┤
│ 0       │ 2  │ 'Réviser le SQL'      │
│ 1       │ 4  │ 'Appeler le plombier' │
└─────────┴────┴───────────────────────┘
Avec ? : 0 résultat

Le grand moment. On remplace storage.js par la base dans routes/tasks.js. Les routes, les codes de statut et le JSON { id, text, done } ne changent pas : ton front ne verra aucune différence. Seul l'endroit où dorment les données change. Remplace tout le fichier :

import express from "express";
import { db } from "../db.js";

const router = express.Router();

// SQLite range done en 0 ou 1 : on renvoie un vrai booléen, comme avant
function toTask(row) {
  return { id: row.id, text: row.text, done: row.done === 1 };
}

// Renvoie un message d'erreur, ou null si le texte est valide
function checkText(text) {
  if (typeof text !== "string" || text.trim() === "") {
    return "Le texte est obligatoire";
  }
  if (text.trim().length > 200) {
    return "200 caractères maximum";
  }
  return null;
}

// GET /api/tasks et GET /api/tasks?done=true
router.get("/", (req, res) => {
  let rows;
  if (req.query.done !== undefined) {
    const done = req.query.done === "true" ? 1 : 0;
    rows = db.prepare("SELECT * FROM tasks WHERE done = ? ORDER BY id").all(done);
  } else {
    rows = db.prepare("SELECT * FROM tasks ORDER BY id").all();
  }
  res.json(rows.map(toTask));
});

// GET /api/tasks/3
router.get("/:id", (req, res) => {
  const row = db.prepare("SELECT * FROM tasks WHERE id = ?").get(Number(req.params.id));
  if (!row) {
    return res.status(404).json({ error: "Tâche introuvable" });
  }
  res.json(toTask(row));
});

// POST /api/tasks   corps : { "text": "..." }
router.post("/", (req, res) => {
  const { text } = req.body ?? {};
  const error = checkText(text);
  if (error) {
    return res.status(400).json({ error });
  }
  const result = db.prepare("INSERT INTO tasks (text) VALUES (?)").run(text.trim());
  const row = db.prepare("SELECT * FROM tasks WHERE id = ?").get(result.lastInsertRowid);
  res.status(201).json(toTask(row));
});

// PATCH /api/tasks/3   corps : { "text": "..." } et/ou { "done": true }
router.patch("/:id", (req, res) => {
  const id = Number(req.params.id);
  const { text, done } = req.body ?? {};

  if (text === undefined && done === undefined) {
    return res.status(400).json({ error: "Rien à modifier : envoie text et/ou done" });
  }
  if (text !== undefined) {
    const error = checkText(text);
    if (error) return res.status(400).json({ error });
  }
  if (done !== undefined && typeof done !== "boolean") {
    return res.status(400).json({ error: "done doit valoir true ou false" });
  }

  // COALESCE(?, text) : si on envoie null, la colonne garde sa valeur
  const result = db
    .prepare("UPDATE tasks SET text = COALESCE(?, text), done = COALESCE(?, done) WHERE id = ?")
    .run(
      text === undefined ? null : text.trim(),
      done === undefined ? null : done ? 1 : 0,
      id
    );

  if (result.changes === 0) {
    return res.status(404).json({ error: "Tâche introuvable" });
  }
  const row = db.prepare("SELECT * FROM tasks WHERE id = ?").get(id);
  res.json(toTask(row));
});

// DELETE /api/tasks/3
router.delete("/:id", (req, res) => {
  const result = db.prepare("DELETE FROM tasks WHERE id = ?").run(Number(req.params.id));
  if (result.changes === 0) {
    return res.status(404).json({ error: "Tâche introuvable" });
  }
  res.status(204).end();
});

export default router;

• import { db } from "../db.js" : .. remonte d'un dossier, car db.js est à la racine. L'import de storage.js disparaît.
• toTask(row) : la base renvoie done: 0 ou 1, le front attend true ou false. On convertit à la sortie, et created_at reste en interne.
• result.lastInsertRowid : l'id que la base vient de donner à la nouvelle ligne. On relit la ligne pour la renvoyer avec le 201.
• result.changes === 0 : aucune ligne modifiée ou supprimée, donc cet id n'existe pas : 404. Plus besoin de chercher la tâche avant.
• COALESCE(?, text) : prend la première valeur qui n'est pas null. Si le corps n'a pas de text, on envoie null et la colonne garde son ancienne valeur.
• text === undefined && done === undefined : même règle qu'à l'étape 3, un PATCH vide est refusé.
• ?done=true est filtré par la base avec WHERE, plus avec filter en JavaScript.
• server.js ne bouge pas (sauf ta route /api/stats, voir plus bas). Si une requête SQL plante, l'erreur part dans ton middleware d'erreur de l'étape 3 : 500 en JSON.

Avant de ranger, fais l'exercice /api/stats plus bas : il utilise encore loadTasks. Range ensuite le projet : supprime storage.js et tasks.json (tes anciennes tâches ne passent pas dans la base, tu repars de zéro). cli.js utilisait storage.js : supprime-le aussi, ou fais l'exercice plus bas. Puis lance npm run dev et rejoue ton requests.http.

api-taches/
├── package.json
├── server.js          # ne change pas
├── db.js              # nouveau : la connexion et la table
├── routes/
│   └── tasks.js       # parle à SQLite au lieu de tasks.json
├── public/
│   └── index.html
├── requests.http
├── essais.js          # tes essais des missions 2 à 4
├── injection.js
├── data.db            # créé tout seul, ignoré par Git
└── .gitignore

POST /api/tasks   {"text": "Acheter du pain"}
201 Created
{"id":1,"text":"Acheter du pain","done":false}

POST /api/tasks   {"text": "   "}
400 Bad Request
{"error":"Le texte est obligatoire"}

PATCH /api/tasks/1   {"done": true}
200 OK
{"id":1,"text":"Acheter du pain","done":true}

PATCH /api/tasks/99   {"done": true}
404 Not Found
{"error":"Tâche introuvable"}

GET /api/tasks?done=true
200 OK
[{"id":1,"text":"Acheter du pain","done":true}]

DELETE /api/tasks/1
204 No Content

DELETE /api/tasks/1
404 Not Found
{"error":"Tâche introuvable"}

La vraie force du SQL : relier des tables entre elles. Imagine des tâches rangées dans des listes (« Courses », « Boulot »). On ne recopie pas le nom de la liste dans chaque tâche : on crée une table lists, et chaque tâche garde juste le numéro de sa liste, dans une colonne list_id.

On teste ça à part, dans une base en mémoire, pour ne pas toucher à ta table tasks (tu changeras le schéma pour de vrai à l'étape 6, avec les comptes). Crée relations.js :

import { DatabaseSync } from "node:sqlite";

// Une base en mémoire, juste pour ce test : rien n'est écrit sur le disque
const db = new DatabaseSync(":memory:");

db.exec(`
  CREATE TABLE lists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
  );

  CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL,
    done INTEGER NOT NULL DEFAULT 0,
    list_id INTEGER NOT NULL REFERENCES lists(id)
  );

  INSERT INTO lists (name) VALUES ('Courses'), ('Boulot'), ('Vacances');

  INSERT INTO tasks (text, list_id) VALUES
    ('Acheter du pain', 1),
    ('Acheter des pommes', 1),
    ('Finir la maquette', 2),
    ('Appeler le client', 2);
`);

// JOIN : chaque tâche avec le nom de sa liste
const tasks = db.prepare(`
  SELECT tasks.id, tasks.text, lists.name AS list
  FROM tasks
  JOIN lists ON lists.id = tasks.list_id
  ORDER BY lists.name
`).all();
console.table(tasks);

// Les tâches d'une seule liste
const courses = db.prepare(`
  SELECT tasks.text FROM tasks
  JOIN lists ON lists.id = tasks.list_id
  WHERE lists.name = ?
`).all("Courses");
console.table(courses);

// Une tâche dans une liste qui n'existe pas : refusé
try {
  db.prepare("INSERT INTO tasks (text, list_id) VALUES (?, ?)").run("Fantôme", 42);
} catch (err) {
  console.log("Refusé :", err.message);
}

• ":memory:" : une base qui vit seulement le temps du script. Parfait pour un essai.
• list_id INTEGER NOT NULL REFERENCES lists(id) : une clé étrangère. La base refuse une tâche qui pointe vers une liste qui n'existe pas (le Refusé à la fin).
• JOIN lists ON lists.id = tasks.list_id : colle à chaque tâche la ligne de sa liste. Tu peux alors lire lists.name dans le même résultat.
• tasks.text, lists.name : avec deux tables, on préfixe par le nom de la table pour dire de quelle colonne on parle.
• AS list : renomme la colonne dans le résultat.

$ node relations.js
┌─────────┬────┬──────────────────────┬───────────┐
│ (index) │ id │ text                 │ list      │
├─────────┼────┼──────────────────────┼───────────┤
│ 0       │ 3  │ 'Finir la maquette'  │ 'Boulot'  │
│ 1       │ 4  │ 'Appeler le client'  │ 'Boulot'  │
│ 2       │ 1  │ 'Acheter du pain'    │ 'Courses' │
│ 3       │ 2  │ 'Acheter des pommes' │ 'Courses' │
└─────────┴────┴──────────────────────┴───────────┘
┌─────────┬──────────────────────┐
│ (index) │ text                 │
├─────────┼──────────────────────┤
│ 0       │ 'Acheter du pain'    │
│ 1       │ 'Acheter des pommes' │
└─────────┴──────────────────────┘
Refusé : FOREIGN KEY constraint failed