Module 06

SQL et Drizzle ORM : gérer les données

Apprenez à structurer, interroger et manipuler des données relationnelles avec SQL puis Drizzle ORM en TypeScript.

Toute application sérieuse repose sur des données persistantes et bien structurées. Ce module vous enseigne les fondamentaux de SQL — création de tables, relations, requêtes — puis vous montre comment Drizzle ORM apporte la sécurité du typage TypeScript à vos interactions avec la base de données.

Module suivant →

Philosophie

Comprendre SQL d'abord, utiliser un ORM ensuite. L'abstraction n'a de valeur que si vous comprenez ce qu'elle abstrait.

Prérequis & Outils

Connaître les bases de TypeScript (types, interfaces, fonctions)
Avoir une application Next.js fonctionnelle

SQLRelationsDrizzle ORMSupabase

Objectifs du module

✦Concevoir un schéma de base de données relationnelle avec clés primaires et étrangères
✦Écrire des requêtes SQL pour lire, filtrer, joindre et agréger des données
✦Utiliser Drizzle ORM pour interagir avec la base de données de manière typée

Ressource complémentaire

SQL et bases de données pour débutants

Ressource complémentaire pour découvrir SQL et les bases de données relationnelles.

Contenu du module

Progression pas à pas.

Chaque leçon s'appuie sur la précédente avec un rythme pensé pour laisser de l'espace à la compréhension, à la pratique et à la révision.

Leçon 1

SQL : tables, relations et requêtes de base

Comprendre la structure d'une base de données relationnelle et savoir créer des tables liées entre elles.

Une base de données relationnelle organise l'information en tables composées de lignes (enregistrements) et de colonnes (champs). Chaque table représente une entité du monde réel : utilisateurs, cours, articles. La clé primaire (PRIMARY KEY) identifie de manière unique chaque ligne, garantissant qu'aucun doublon n'existe.

Les relations entre tables sont établies par les clés étrangères (FOREIGN KEY). Une clé étrangère dans une table référence la clé primaire d'une autre table, créant un lien logique. Par exemple, une table 'progress' contient un champ user_id qui référence la table 'users'. Les types de données (TEXT, INTEGER, BOOLEAN, TIMESTAMP) définissent ce que chaque colonne peut stocker.

Les opérations fondamentales CRUD se traduisent en SQL : INSERT INTO ajoute des données, SELECT les lit, UPDATE les modifie, DELETE les supprime. La clause WHERE filtre les résultats selon des conditions, et ORDER BY les trie. Ces quatre opérations couvrent la grande majorité des interactions avec une base de données.

Exemple : création de tables users et progress avec une relation par clé étrangère.sql

-- Table des utilisateurs
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Table de progression liée aux utilisateurs
CREATE TABLE progress (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER REFERENCES users(id),
  module_slug TEXT NOT NULL,
  completed   BOOLEAN DEFAULT FALSE,
  score       INTEGER,
  updated_at  TIMESTAMP DEFAULT NOW()
);

-- Insérer un utilisateur
INSERT INTO users (name, email)
VALUES ('Marie Dupont', 'marie@example.com');

-- Lire la progression d'un utilisateur
SELECT module_slug, completed, score
FROM progress
WHERE user_id = 1
ORDER BY updated_at DESC;

Exercice pratique

Concevez le schéma SQL d'une plateforme de cours en ligne avec trois tables : users, courses et enrollments.

Créez la table users avec id, name, email et created_at.
Créez la table courses avec id, title, description et level.
Créez la table enrollments qui lie users et courses avec une date d'inscription et un statut.
Écrivez une requête SELECT pour trouver tous les cours auxquels un utilisateur est inscrit.

✓Vous avez réussi si vos trois tables sont correctement liées par des clés étrangères et si votre requête SELECT retourne les cours d'un utilisateur spécifique.

Leçon 2

Requêtes avancées : JOIN, GROUP BY et agrégation

Savoir combiner les données de plusieurs tables et calculer des statistiques avec SQL.

Le JOIN est l'opération qui donne toute sa puissance aux bases relationnelles. INNER JOIN retourne uniquement les lignes qui ont une correspondance dans les deux tables. LEFT JOIN retourne toutes les lignes de la table de gauche, même sans correspondance — les colonnes de la table de droite seront NULL. Cette distinction est cruciale : un LEFT JOIN sur users et progress montrera aussi les utilisateurs qui n'ont aucune progression.

GROUP BY regroupe les lignes ayant la même valeur dans une colonne, permettant d'appliquer des fonctions d'agrégation : COUNT() compte les lignes, SUM() additionne les valeurs, AVG() calcule la moyenne. La clause HAVING filtre les groupes après agrégation, contrairement à WHERE qui filtre les lignes avant. Par exemple, HAVING COUNT(*) > 3 ne garde que les groupes de plus de trois éléments.

Les sous-requêtes (subqueries) permettent d'imbriquer une requête SELECT à l'intérieur d'une autre. Elles sont utiles pour des comparaisons complexes : trouver les utilisateurs dont le score est supérieur à la moyenne, ou les cours qui n'ont aucune inscription. Bien que puissantes, les sous-requêtes peuvent souvent être remplacées par des JOIN pour de meilleures performances.

Exemple : joindre les utilisateurs à leur progression et calculer des statistiques par module.sql

-- Joindre utilisateurs et progression
SELECT
  u.name,
  p.module_slug,
  p.score,
  p.completed
FROM users u
INNER JOIN progress p ON p.user_id = u.id
WHERE p.completed = TRUE
ORDER BY p.score DESC;

-- Statistiques par module
SELECT
  p.module_slug,
  COUNT(*)        AS total_completions,
  AVG(p.score)    AS score_moyen,
  MAX(p.score)    AS meilleur_score
FROM progress p
WHERE p.completed = TRUE
GROUP BY p.module_slug
HAVING COUNT(*) > 5
ORDER BY score_moyen DESC;

-- Utilisateurs sans progression (LEFT JOIN)
SELECT u.name, u.email
FROM users u
LEFT JOIN progress p ON p.user_id = u.id
WHERE p.id IS NULL;

Exercice pratique

Écrivez des requêtes analytiques pour un tableau de bord d'administrateur de plateforme de cours.

Écrivez une requête qui affiche chaque utilisateur avec le nombre de modules complétés.
Écrivez une requête qui calcule le score moyen par module, triée du meilleur au moins bon.
Écrivez une requête qui trouve les utilisateurs inscrits mais n'ayant complété aucun module.

✓Vous avez réussi si vos trois requêtes retournent les résultats attendus et si vous avez utilisé LEFT JOIN correctement pour inclure les utilisateurs sans progression.

Leçon 3

Drizzle ORM : base de données typée en TypeScript

Utiliser Drizzle ORM pour définir un schéma, exécuter des requêtes et gérer les migrations avec la sécurité du typage TypeScript.

Un ORM (Object-Relational Mapping) est une couche d'abstraction qui vous permet d'interagir avec la base de données en utilisant le langage de votre application plutôt que du SQL brut. Drizzle ORM se distingue par son approche « SQL-like » : sa syntaxe ressemble volontairement au SQL, ce qui facilite la transition. De plus, Drizzle génère des types TypeScript automatiquement à partir du schéma, détectant les erreurs de requête à la compilation plutôt qu'à l'exécution.

Le schéma Drizzle se définit avec des fonctions comme pgTable qui prennent le nom de la table et un objet décrivant les colonnes : serial() pour un auto-increment, text() pour du texte, integer() pour des nombres entiers, boolean() pour vrai/faux, et timestamp() pour les dates. Les relations (references) sont déclarées directement dans le schéma, et Drizzle génère les types TypeScript correspondants pour vos insertions et sélections.

Les requêtes Drizzle suivent un pattern fluent : db.select().from(users).where(eq(users.email, email)) pour lire, db.insert(users).values({ name, email }) pour écrire. Les migrations avec drizzle-kit (npx drizzle-kit generate puis npx drizzle-kit migrate) synchronisent votre schéma TypeScript avec la base de données réelle. Pour connecter Drizzle à Supabase, il suffit de fournir l'URL de connexion PostgreSQL dans la configuration.

Exemple : schéma Drizzle complet pour une plateforme de cours avec requêtes typées.typescript

// db/schema.ts
import { pgTable, serial, text, integer, boolean, timestamp } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").unique().notNull(),
  createdAt: timestamp("created_at").defaultNow(),
});

export const progress = pgTable("progress", {
  id: serial("id").primaryKey(),
  userId: integer("user_id").references(() => users.id),
  moduleSlug: text("module_slug").notNull(),
  completed: boolean("completed").default(false),
  score: integer("score"),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Requêtes typées
import { db } from "./client";
import { eq } from "drizzle-orm";

// Lire la progression d'un utilisateur
const userProgress = await db
  .select()
  .from(progress)
  .where(eq(progress.userId, 1));

// Insérer une nouvelle progression
await db.insert(progress).values({
  userId: 1,
  moduleSlug: "nextjs-application",
  completed: true,
  score: 85,
});

Exercice pratique

Définissez un schéma Drizzle complet pour une plateforme de cours et écrivez les requêtes principales.

Créez un fichier de schéma avec trois tables : users, courses et enrollments utilisant pgTable.
Définissez les relations entre tables avec references().
Écrivez une requête Drizzle pour inscrire un utilisateur à un cours.
Écrivez une requête Drizzle avec join pour récupérer les cours d'un utilisateur.

✓Vous avez réussi si votre schéma compile sans erreur TypeScript, si les relations sont correctement déclarées, et si vos requêtes retournent des résultats typés.

Projet final

Schéma complet d'une plateforme

Concevez et implémentez le schéma de base de données complet d'une plateforme de cours en ligne avec Drizzle ORM, incluant utilisateurs, cours, inscriptions, progression et commentaires.

Critères de réussite

✓Le schéma contient au moins 4 tables liées par des clés étrangères correctement définies.
✓Les requêtes Drizzle couvrent les opérations CRUD complètes (create, read, update, delete).
✓Au moins une requête utilise un join pour combiner des données de plusieurs tables.
✓Le fichier de configuration drizzle.config.ts est correctement paramétré pour Supabase.
✓Les migrations sont générées avec drizzle-kit et applicables sans erreur.

Guide de réalisation

1Commencez par dessiner le diagramme de relations sur papier avant de coder le schéma Drizzle.
2Testez chaque requête individuellement dans un fichier seed.ts qui peuple la base avec des données de test.
3Utilisez les types inférés par Drizzle (typeof users.$inferSelect) pour typer vos fonctions d'accès aux données.
4Pensez aux cas limites : que se passe-t-il quand on supprime un utilisateur qui a des progressions ?

Consolidez vos acquis avant de poursuivre.

Prenez le temps de revoir les concepts difficiles et de refaire les exercices qui méritent une seconde passe.

← Module précédent Module suivant →