Créer le projet Supabase et les tables

Créer votre projet Supabase, puis transformer le MLD en tables PostgreSQL avec un script SQL.

Objectifs de la séance

• créer un compte et un projet Supabase ;
• utiliser le SQL Editor pour exécuter un script ;
• écrire des instructions create table avec clés primaires, clés étrangères et contraintes check ;
• créer des index, dont un index unique partiel qui garantit une seule bonne réponse par question ;
• donner un droit de lecture aux rôles utilisés par l'application.

---

Petite vérification

Avant de commencer, vérifiez si vous savez...

1. dans sql/README.md, retrouver le nom de la clé primaire de la table theme et celui de la clé étrangère de la table reponse
2. dire combien de tables contient le modèle du catalogue de questions, et les nommer
3. afficher dans un terminal le nom de la branche Git sur laquelle vous travaillez, et vérifier qu'il s'agit de feature/supabase

📌 Réponse attendue

Vous devez être connecté pour voir le contenu.

Email : Mot de passe :

---

TP pour réfléchir et résoudre des problèmes

Votre mission : créer votre projet Supabase, puis écrire le fichier sql/01_tables.sql qui construit les trois tables du modèle. Vous le rédigerez dans VS Code (pour qu'il soit versionné par Git), puis vous le collerez dans le SQL Editor de Supabase pour l'exécuter.

Étape 1 — Créer le projet Supabase

1. Rendez-vous sur supabase.com et créez un compte (la connexion avec GitHub est possible).
2. Cliquez sur New project. Donnez un nom au projet (par exemple quiz-cyber), choisissez une région d'Europe et générez un mot de passe pour la base de données (notez-le dans un gestionnaire de mots de passe). Laissez les autres options par défaut.
3. Attendez environ deux minutes que le projet soit prêt.
4. Dans le menu de gauche, ouvrez SQL Editor : c'est ici que vous exécuterez vos scripts (un bouton New query ouvre une page blanche, un bouton Run lance le script).

Dans VS Code, créez le fichier sql/01_tables.sql. Sa première instruction commence ainsi :

Bonne pratique - Un projet par application, une région proche

Choisir une région d'Europe réduit le temps de réponse pour vos joueurs et garde les données des utilisateurs européens sur le territoire européen, ce qui facilite le respect du RGPD.

Étape 2 — Créer la table theme

Les thèmes ont pour clé primaire un code lisible. Deux contraintes check protègent les colonnes : le code ne contient que des minuscules et des tirets, le libellé fait entre 2 et 50 caractères.

Bonne pratique - Mettre les règles dans la base

Le code du thème est vérifié par la base elle-même : même si une erreur d'écriture se glisse dans un script ou dans l'application, une valeur comme Mots De Passe sera refusée.

Étape 3 — Créer la table question

Chaque question appartient à un thème : la colonne theme_code est une clé étrangère qui référence theme (code). L'identifiant id est un nombre généré automatiquement.

Bonne pratique - Un index pour les recherches fréquentes

L'application cherchera très souvent les questions « d'un thème et d'un niveau ». L'index question_theme_niveau_idx joue le rôle d'un index de livre : la base trouve ces lignes sans parcourir toute la table.

Étape 4 — Créer la table reponse

Une réponse appartient à une question. Avec on delete cascade, supprimer une question supprime automatiquement ses quatre réponses : il ne reste jamais de réponse « orpheline ».

Bonne pratique - Indexer les clés étrangères

Chaque fois que l'application demandera « les réponses de la question 12 », la base utilisera l'index reponse_question_idx. Une clé étrangère souvent utilisée dans les recherches mérite presque toujours son index.

Étape 5 — Garantir une seule bonne réponse par question

La règle métier « une seule réponse correcte par question » doit être garantie par la base. On utilise pour cela un index unique partiel : il ne porte que sur les lignes où est_correcte vaut true, et interdit d'avoir deux fois la même question_id parmi elles.

Bonne pratique - Une règle importante appartient à la base

Une règle contrôlée seulement dans l'application peut être contournée par n'importe quel autre programme qui parle à la base. Un index unique ou une contrainte check s'applique toujours, quel que soit l'auteur de la requête.

Étape 6 — Donner le droit de lecture à l'application

Votre application accède à la base par l'API de Supabase, avec deux rôles : anon (visiteur non connecté) et authenticated (utilisateur connecté, y compris par connexion anonyme). Sans droit explicite, ces rôles ne peuvent rien lire.

Bonne pratique - Accorder le minimum de droits

On donne seulement le droit de lecture : aucun droit d'écriture n'est accordé volontairement. C'est le principe du moindre privilège : chaque rôle reçoit uniquement ce dont il a besoin.

Collez maintenant le script complet dans le SQL Editor et cliquez sur Run : Supabase affiche « Success. No rows returned ». Ouvrez ensuite Table Editor : vos trois tables sont visibles, vides pour l'instant. Un avertissement signale que la RLS n'est pas activée sur ces tables : vous le réglerez dans la séance 40.

Étape 7 — Enregistrer votre travail

Bonne pratique - Le script est la source de vérité

Si vous modifiez plus tard une table dans l'interface de Supabase, pensez à reporter le changement dans le script : le dépôt doit toujours décrire fidèlement votre base.

📌 Une solution

Vous devez être connecté pour voir le contenu.

Email : Mot de passe :

---

Notions théoriques

Supabase, projet et SQL Editor

Un projet Supabase est une base PostgreSQL complète, hébergée dans le cloud, accompagnée d'une API : l'application envoie des requêtes HTTP à une adresse de la forme https://VOTRE_PROJET.supabase.co, et Supabase les traduit en requêtes SQL. Le SQL Editor du tableau de bord permet d'exécuter directement du SQL, avec les droits d'administrateur.

Créer une table

create table public.nom_de_la_table (
  colonne1  type contraintes,
  colonne2  type contraintes
);

Chaque colonne a un nom, un type et, le plus souvent, des contraintes.

Type	Contenu	Exemple dans le quiz
text	texte de longueur quelconque	enonce, libelle
bigint	grand nombre entier	id, question_id
smallint	petit nombre entier	niveau
boolean	vrai ou faux	est_correcte, est_active

Les contraintes

Une contrainte est une règle que la base fait respecter à chaque insertion et à chaque modification. Si la règle n'est pas respectée, la requête est refusée avec un message d'erreur.

Contrainte	Rôle	Exemple
primary key	identifiant unique et jamais vide	code text primary key
not null	la valeur est obligatoire	libelle text not null
default	valeur utilisée si rien n'est fourni	est_active boolean not null default true
references	clé étrangère vers une autre table	references public.theme (code)
check	condition à respecter	check (niveau between 1 and 3)

Quelques détails utiles :

• generated by default as identity demande à PostgreSQL de numéroter automatiquement la colonne (1, 2, 3...) ;
• on delete cascade supprime automatiquement les lignes « enfants » quand la ligne « parent » disparaît ;
• l'opérateur ~ compare un texte à une expression régulière : '^[a-z-]{2,30}$' accepte entre 2 et 30 caractères, uniquement des lettres minuscules et des tirets ;
• char_length(texte) renvoie le nombre de caractères d'un texte ;
• -- commence un commentaire SQL, jusqu'à la fin de la ligne.

Par exemple, tenter d'enregistrer une question de niveau 4 provoque une erreur du type « new row for relation "question" violates check constraint » : la base refuse la ligne.

Les index

Un index est une structure annexe qui accélère les recherches sur une ou plusieurs colonnes, comme l'index alphabétique d'un livre. Il ralentit très légèrement les écritures, mais accélère fortement les lectures.

• create index ... on table (colonne) : index ordinaire ;
• create unique index : en plus, interdit les doublons ;
• avec where condition, l'index devient partiel : il ne concerne que les lignes qui respectent la condition. Ici, il ne porte que sur les bonnes réponses : deux bonnes réponses pour la même question violeraient l'unicité.

Pourquoi des contraintes côté base ?

Vous validerez aussi les données dans l'application, mais la base de données est la dernière ligne de défense. Dans la suite de la série, la clé qui permet de joindre Supabase sera visible de tous (elle sera dans l'application). N'importe qui pourra donc envoyer des requêtes sans passer par votre application : seules les contraintes de la base arrêteront les données invalides.

Rôles et droits

PostgreSQL gère des droits (grant) par rôle et par table. Supabase utilise notamment :

• anon : un visiteur qui n'est pas connecté ;
• authenticated : un utilisateur connecté ;
• le propriétaire du projet (dans le SQL Editor), qui a tous les droits.

Les droits ne suffisent pas

Avoir le droit de lire une table ne dit pas quelles lignes on peut lire. C'est le rôle de la RLS (Row Level Security), que vous activerez dans la séance 40.

---

Test de mémorisation/compréhension

Que fait « bigint generated by default as identity primary key » sur la colonne id ?

Elle interdit de modifier la colonne après la première insertion de la ligne

Elle transforme la colonne en texte aléatoire de 32 caractères

Elle copie automatiquement la valeur de la clé étrangère voisine

Elle numérote automatiquement chaque nouvelle ligne et sert d'identifiant

Elle crée une sauvegarde de la table à chaque insertion de ligne

Que provoque « on delete cascade » sur la clé étrangère reponse.question_id ?

Supprimer une question supprime aussi ses réponses

Il devient impossible de supprimer une question qui a des réponses

Supprimer une réponse supprime automatiquement la question correspondante

Toutes les réponses sont archivées dans une table de sauvegarde automatique

La suppression est reportée à la prochaine connexion d'un administrateur

À quoi sert l'index unique partiel « reponse_une_seule_correcte » ?

À accélérer l'affichage de toutes les réponses de la base de données

À empêcher deux réponses identiques dans l'ensemble de la table reponse

À trier automatiquement les réponses dans l'ordre alphabétique

À supprimer les réponses fausses après un certain temps d'inactivité

À garantir qu'une question n'a qu'une seule réponse correcte

Pourquoi mettre des contraintes check dans la base alors que l'application valide déjà les données ?

Parce que l'application ne sait pas vérifier les nombres

Parce que n'importe qui peut envoyer des requêtes à la base sans passer par l'application

Parce que PostgreSQL refuse de créer une table sans contrainte check

Parce que les contraintes rendent l'application plus rapide au démarrage sur mobile

Parce que les contraintes remplacent définitivement la nécessité de sauvegarder la base de données

Que garantit « theme_code text not null references public.theme (code) » ?

Que le thème est affiché en premier dans toutes les listes de questions

Que le code d'un thème est unique dans toute la base de données

Que chaque question est liée à un thème qui existe dans la table theme

Que la colonne theme_code est calculée automatiquement à partir de l'énoncé

Que la table theme est supprimée quand la dernière question disparaît

Que signifie « grant select on public.theme to anon, authenticated » ?

Les visiteurs connectés ou non peuvent lire la table theme

Les visiteurs connectés ou non peuvent modifier ou supprimer les thèmes

Seul le propriétaire du projet peut désormais lire la table theme

La table theme est protégée et devient invisible pour l'API Supabase

Les visiteurs peuvent lire toutes les tables du schéma public sans restriction

Où exécute-t-on un script SQL dans le tableau de bord de Supabase ?

Dans Authentication > Users

Dans Storage > Buckets

Dans Project Settings > General

Dans SQL Editor, avec le bouton Run

Dans Database > Backups, avec le bouton Restore

---

Exercices complémentaires

Exercice A : lire un message d'erreur

Dans le SQL Editor, relancez le script 01_tables.sql une deuxième fois. Lisez le message d'erreur obtenu et expliquez pourquoi PostgreSQL refuse de créer la table theme.

Exercice B : explorer la structure

Dans Table Editor, ouvrez la table question : repérez le type de chaque colonne, la clé primaire, et la relation vers la table theme.

---

Ce qu'il faut retenir

Notion	Résumé
Projet Supabase	Une base PostgreSQL hébergée, avec une API accessible par https://VOTRE_PROJET.supabase.co.
SQL Editor	Page du tableau de bord où l'on exécute ses scripts SQL.
create table	Crée une table : colonnes, types et contraintes.
Contraintes	primary key, not null, default, references, check : la base refuse les données invalides.
Index	Accélère les recherches ; unique, il interdit les doublons ; partiel, il ne concerne que certaines lignes.
grant select	Donne un droit de lecture à un rôle (anon, authenticated).
Défense en profondeur	L'application valide, mais la base reste la dernière ligne de défense.

---

Aperçu de la prochaine séance

Vos tables existent mais elles sont vides. Dans la prochaine séance, vous allez les remplir avec les 27 questions et leurs 108 réponses grâce à des instructions insert, puis vérifier le résultat avec quelques requêtes select count(*).