URL: https://linuxfr.org/news/ecrire-une-appli-web-en-une-journee-avec-sqlpage Title: Écrire une appli web en une journée avec SQLPage Authors: lovasoa Nÿco, Benoît Sibaud, gUI et BAud Date: 2023-06-26T14:22:12+02:00 License: CC By-SA Tags: web, base_de_données, sql et lowcode Score: 3 Aujourd'hui, je souhaite vous présenter le logiciel [SQLPage](https://sql.ophir.dev), un outil open-source (MIT) qui permet de développer des applications web complètes, avec une belle interface graphique et une base de données, entièrement en SQL. Le SQL est un langage très simple, qui permet de faire des recherches dans des base de données. Il est utilisé depuis les années 80, et est encore omniprésent aujourd'hui. Contrairement aux langages de programmation traditionnels, on peut apprendre les bases de SQL en une journée, et commencer à faire des requêtes complexes croisant plusieurs tables de données très rapidement. Dans une application web traditionnelle, on développe aujourd'hui en général trois composants : - un *front-end*, qui gère uniquement l'interface utilisateur, - un *back-end*, qui traite les requêtes du _front-end_ et contient le cœur de la logique de l'application lorsque celle-ci est complexe, - une *base de données* qui va stocker et structurer les données, s'assurant de leur cohérence et de leur bonne organisation. Les deux premiers éléments sont en général ceux sur lesquels les programmeurs passent le plus de temps lors du développement d'une application. Et pourtant, c'est souvent le dernier, la base de données, qui contient la substantifique moelle de l'application ! Ce que propose SQLPage, c'est de s'abstraire complètement du _back-end_ et du _front-end_, et générer toute une application entièrement en SQL. Nous allons voir ici comment c'est possible, avec un exemple concret d'application que nous allons construire ensemble en SQL : à la Tricount.com, une petite application qui permet de gérer ses comptes entre amis. ---- [Site officiel du projet SQLPage](https://sql.ophir.dev) [Code source du projet sur Github ](https://github.com/lovasoa/sqlpage) ---- Est-ce de la sorcellerie ? =========== Tout d'abord, mettons les choses au clair : votre application aura bien un backend et un frontend, il n'y a pas de miracle. Mais pour les applications simples, le frontend est souvent juste un assemblage de composants standards, et le backend qu'une sorte de passe-plats entre le frontend et la base de données. Ce que permet SQLPage, et que nous allons étudier ici c'est : - d'invoquer des composants prédéfinis d'interface graphique en donnant simplement leur nom et quelques paramètres, - de faire le lien entre l'interface graphique et la base de données avec de simples fichiers SQL qui sont exécutés automatiquement lorsque l'utilisateur charge une page. Comment ça marche ? =========== SQLPage est un simple serveur web : c'est un programme qui tourne en continu, attend des requêtes HTTP, et dès qu'il en reçoit une, fournit une réponse. Si SQLPage reçoit une requête vers `/site/contenu.sql?article=42`, il va chercher un fichier nommé `contenu.sql`, dans un dossier nommé `site`. Il va ensuite lire le contenu du fichier, et l'interpréter comme une série de requêtes SQL, qui vont être préparées. Elles seront ensuite exécutées une par une. Si l'une de ces requêtes fait référence à une variable nommée `$article`, la valeur `42` venant de la requête de l'utilisateur lui sera associée. ![architecture sqlpage](https://github.com/lovasoa/SQLpage/blob/main/docs/architecture.png?raw=true) Les requêtes sont envoyées à la base de données, et celle-ci commence à retourner des lignes de données, une par une. Les lignes vont ensuite être analysées *au fil de l'eau* par SQLPage, qui va décider quel composant graphique renvoyer au navigateur web, et quelles données utiliser pour remplir le composant. # Construisons une application Pour rendre tout ce discours plus concret, créons ensemble une petite application, entièrement en SQL, et en vingt minutes. Pour vous donner un avant-goût, voilà ce à quoi nous allons arriver au final | Page d'accueil | Gestion d'utilisateurs | Liste de dépenses | Graphique de dettes | | --- | --- | --- | --- | --- | ![image](https://github.com/lovasoa/SQLpage/assets/552629/377b2aac-ec27-4282-89b9-bd26fd777d9a) | ![image](https://github.com/lovasoa/SQLpage/assets/552629/7a5d46f1-fed8-4e29-a8b6-3f8c19ea63ae) | ![image](https://github.com/lovasoa/SQLpage/assets/552629/3dc90a85-a5c2-44d9-aa33-dbe86470ae39) | ![image](https://github.com/lovasoa/SQLpage/assets/552629/1a8fb8e4-3dad-400a-be54-eb5b54ec901b) Il n'y a pas toutes les fonctionnalités de l'application originelle, mais c'est seulement 83 lignes de code, grâce à tout ce que SQLPage gère automatiquement. Et le résultat est quand même plus joli que l'original. ## Notre application : une application opensource pour faire ses comptes entre amis Nous allons créer une application pour faire ses comptes entre amis. Elle aura les fonctionnalités suivantes : - créer un nouveau compte de dépenses partagé - ajouter des participants et visualiser la liste des participants existants - pour chaque participant : - ajouter une dépense - voir les dépenses des autres - voir combien il doit au reste du groupe ou combien lui est dû ### Première étape : choisir un schéma pour notre base de données Et oui, on ne va pas passer quatre jours à choisir un framework JavaScript, un framework CSS, un ORM, ou autres choses compliquées que l'on fait quand on commence une application web classique. Avec SQLPage, on rentre tout de suite dans le cœur du sujet, et ce qui sera important pour la suite: quelles données stockerons-nous, et sous quelle forme. Ici, je propose le schéma suivant : - une table `expense_group` pour nos comptes de dépenses partagés, avec un identifiant numérique et un nom. - une table `group_member` pour les utilisateurs, avec un identifiant numérique, un nom, et l'identifiant du compte partagé auquel il appartient. - une table `expense` pour les dépenses, avec l'identifiant de l'utilisateur ayant fait la dépense, une description, et un montant. Pour cet exemple, nous ne prendrons pas en compte le cas où une dépense peut ne concerner qu'une partie du groupe; ce sera simple à ajouter dans un second temps. ### Deuxième étape : création de la base de données et lancement de SQLPage C'est parti ! [Téléchargeons SQLPage sur le site officiel](https://github.com/lovasoa/SQLpage/releases). Créons un dossier pour notre application, et dans ce dossier créons la structure de fichiers suivante: ``` ├── sqlpage │   ├── migrations │   │   └── 000_base.sql │   └── sqlpage.json └── sqlpage.bin ``` Nous créons donc les fichiers suivants: - `sqlpage/migrations/000_base.sql` dans lequel nous définirons la structure de notre base de données - `sqlpage/sqlpage.json` dans lequel nous mettrons pour l'instant simplement la ligne suivante: `{"database_url": "sqlite://:memory:"}`. Cela nous permet de travailler avec une base de données temporaire en mémoire. Nous le modifierons plus tard pour nous connecter à une base de données plus pérenne. Intéressons-nous d'abord à `sqlpage/migrations/000_base.sql`. Pour créer la structure de base de données définie plus tôt, utilisons quelques [instructions de création de table](https://www.sqlite.org/lang_createtable.html) : ```sql CREATE TABLE expense_group( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT ); CREATE TABLE group_member( id INTEGER PRIMARY KEY AUTOINCREMENT, group_id INTEGER REFERENCES expense_group(id), name TEXT ); CREATE TABLE expense( id INTEGER PRIMARY KEY AUTOINCREMENT, spent_by INTEGER REFERENCES group_member(id), -- identifiant du membre qui a fait la dépense date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- date et heure de la dépense name TEXT, -- intitulé amount DECIMAL -- montant en euros ); ``` On peut maintenant lancer l'exécutable `sqlpage.bin` (ou `sqlpage.exe` sous Windows 😬) depuis le dossier de notre site. Il doit se lancer, et afficher dans le terminal le message suivant : `Applying migrations from 'sqlpage/migrations [...] Found 1 migrations`. Cela signifie qu'il a créé avec succès notre base de données selon le schéma demandé. En ouvrant la page `http://localhost:8080` sur notre navigateur web, nous devrions voir le message suivant: ![Screenshot 2023-06-28 at 16-46-40 SQLpage](https://github.com/lovasoa/SQLpage/assets/552629/4ae3d1e7-03f2-4200-ae7c-70aca40574b8) ### Troisième étape : création de notre première page web Le moment tant attendu est arrivé : nous allons créer notre première page web et pouvoir l'ouvrir dans notre navigateur. Pour cela, créons un fichier nommé `index.sql` à la racine du dossier de notre site web. À l'intérieur, nous allons écrire une série de requêtes SQL. SQLPage marche de la manière suivante : on fait une première requête pour invoquer un composant graphique, comme une liste, un formulaire, du texte, ou un graphique. Ensuite, on fait une seconde requête pour définir comment peupler notre composant : les éléments de la liste, les champs du formulaire, les paragraphes de texte, ou les points de notre graphique. Dans notre cas, notre premier composant sera un formulaire pour créer un nouveau groupe de dépenses à partager entre amis. Pour cela, nous allons invoquer le composant [`form`](https://sql.ophir.dev/documentation.sql?component=form#component). Dans `index.sql`, écrivons : ```sql SELECT 'form' as component, 'Nouveau compte partagé' as title, 'Créer le compte de dépenses partagé !' as validate; ``` Cela crée un formulaire, vide, que l'on peut déjà voir dans notre navigateur ! Maintenant, ajoutons un champ dans le formulaire. Immédiatement à la suite de la requête précédente, ajoutons: ```sql SELECT 'Nom du compte' AS label, 'shared_expense_name' AS name; ``` Rouvrons notre navigateur, et nous devrions maintenant voir cela : ![sqlpage form](https://github.com/lovasoa/SQLpage/assets/552629/4d33ceb0-480d-4f34-ade6-5eafc3023889) ### Insertion de données dans la base de données Pour l'instant, lorsque l'on clique sur le bouton _Créer le compte de dépenses partagées_, il ne se passe rien. Corrigeons cela ! Toujours dans `index.sql`, à la fin de notre fichier, ajoutons une nouvelle requête SQL : ```sql INSERT INTO expense_group(name) SELECT :shared_expense_name WHERE :shared_expense_name IS NOT NULL; ``` Ici, on utilise une requête de type [`INSERT INTO ... SELECT`](https://www.sqlite.org/lang_insert.html) pour insérer une nouvelle ligne dans la table `expense_group`. On ajoute une clause `WHERE` pour qu'une ligne ne soit insérée que lorsque l'utilisateur a rempli une valeur dans le formulaire, et pas à chaque fois que la page se charge. La variable SQL `:shared_expense_name` sera associée à la valeur que l'utilisateur aura rentré dans le champ de texte que nous avons appelé `shared_expense_name` à l'étape précédente. Maintenant, chaque validation de formulaire crée une nouvelle ligne dans notre base de données. Il est temps de créer notre premier composant dynamique, dont le contenu va dépendre de ce qu'il y a dans notre base de données. Toujours à la suite, dans `index.sql`: ```sql SELECT 'list' as component; SELECT name AS title, 'group.sql?id=' || id AS link FROM expense_group; ``` Ici, nous utilisons un nouvel élément issu de la [bibliothèque standard de SQLPage](https://sql.ophir.dev/documentation.sql): le composant [`list`](https://sql.ophir.dev/documentation.sql?component=list#component). Après l'avoir sélectionné, nous le peuplons avec des données qui viennent de la table `expense_group` de notre base de données. Pour chaque élément de la liste, nous spécifions un lien vers lequel l'utilisateur sera emmené lorsqu'il cliquera dessus. Pour créer ce lien, nous [concaténons](https://www.geeksforgeeks.org/sql-concatenation-operator/) le nom d'un nouveau fichier SQL que nous allons créer, avec une variable qui contient l'identifiant du groupe à afficher. ![Liste dynamique avec SQLPage](https://github.com/lovasoa/SQLpage/assets/552629/8e31bf3c-f126-4789-921e-7a403dd1d31f) ## Amélioration de l'application, création de nouvelles pages Nous avons maintenant vu tous les éléments nécessaires à la construction d'une application. Il ne nous reste plus qu'à les appliquer à la création des pages restantes de notre application opensource. Dans `group.sql`, réutilisons les composants from et list que nous connaissons maintenant : ```sql SELECT 'title' as component, name as contents FROM expense_group WHERE id = $id; INSERT INTO group_member(group_id, name) SELECT $id, :new_member_name WHERE :new_member_name IS NOT NULL; SELECT 'list' as component, 'Membres' as title; SELECT name AS title FROM group_member WHERE group_id=$id; SELECT 'form' as component, 'Ajouter un membre au groupe' as validate; SELECT 'Nom du membre' AS 'label', 'new_member_name' AS name; SELECT 'title' as component, 'Dépenses' as contents SELECT 'form' as component, 'Ajouter une dépense' as title, 'Ajouter' as validate; SELECT 'Description' AS name; SELECT 'Montant' AS name, 'number' AS type; SELECT 'select' as type, 'Dépensé par' AS name, json_group_array(json_object("label", name, "value", id)) as options FROM group_member WHERE group_id = $id; INSERT INTO expense(spent_by, name, amount) SELECT :"Dépensé par", :Description, :Montant WHERE :Montant IS NOT NULL; SELECT 'card' as component, 'Dépenses' as title; SELECT expense.name as title, 'Par ' || group_member.name || ', le ' || expense.date as description, expense.amount || ' €' as footer, CASE WHEN expense.amount > 100 THEN 'red' WHEN expense.amount > 50 THEN 'orange' ELSE 'blue' END AS color FROM expense INNER JOIN group_member on expense.spent_by = group_member.id WHERE group_member.group_id = $id; ``` Nous avons ici créé une seule page, qui contient plusieurs listes et plusieurs formulaires, juste en écrivant nos requêtes SQL les unes après les autres dans notre fichier. Le seul point particulier à noter, qui est différent de ce que nous avons vu avant, est l'utilisation de la fonction sql [`json_group_array`](https://www.sqlite.org/json1.html#jgrouparray) pour remplir la valeur du champ de formulaire à choix multiple, qui prend un tableau json comme valeur. Nous arrivons au résultat suivant : ![screenshot sqlpage](https://github.com/lovasoa/SQLpage/assets/552629/11c86c1e-3bad-43d1-91bb-2e89de171a07) ### Cerise sur le gâteau : calcul des dettes Une fonctionnalité pratique de l'application originelle est le calcul du tableau de dette. L'application fait elle-même le calcul final de qui doit combien. C'est un peu moins trivial que les requêtes classiques de listage de données que l'on a vues jusqu'ici, mais on peut aussi implémenter cela entièrement en SQL. On crée quelques [vues](https://fr.wikipedia.org/wiki/Vue_(base_de_donn%C3%A9es)) qui nous seront utiles pour nos calculs. Dans SQLPage, on ne crée en général pas de fonctions, et on n'importe pas des bibliothèques. Pour construire une fonctionnalité complexe, le plus simple est de construire des vues successives de nos données, dans lesquelles on les groupe et les filtre comme on le suite. Ici, on construit les trois vues simples suivantes, chacune avec sa fonction SQL : - [`members_with_expenses`](https://github.com/lovasoa/SQLpage/blob/main/examples/splitwise/sqlpage/migrations/0001_views.sql#L3-L9), qui va lier nos tables entre elles pour associer les noms des membres à leurs montants de dépenses. - [`average_debt_per_person`](https://github.com/lovasoa/SQLpage/blob/main/examples/splitwise/sqlpage/migrations/0001_views.sql#L12-L16) qui va diviser le montant total dépensé par le groupe par le nombre de participants. - [`individual_debts`](https://github.com/lovasoa/SQLpage/blob/main/examples/splitwise/sqlpage/migrations/0001_views.sql#L19-L27) qui va soustraire la dépense moyenne aux dépenses personnelles de chacun, pour savoir combien il doit ou combien on lui doit. Ici c'est du SQL classique, il n'y a rien qui soit propre à SQLPage. Je vous laisse lire [les 27 lignes de code sur github](https://github.com/lovasoa/SQLpage/blob/main/examples/splitwise/sqlpage/migrations/0001_views.sql). # Conclusion Nous avons vu comment construire une application web complète entièrement en SQL grâce à SQLPage. Nous pouvons maintenant la faire tourner sur un tout petit serveur chez nous, dans le cloud, ou même [en mode sans-serveur](https://github.com/lovasoa/SQLpage#serverless). SQLPage est écrit dans le langage de programmation _rust_ et consomme très peu de resources par rapport à une application web classique, l'application sera donc très peu chère à héberger. ### Pour résumer ce que nous avons vu Nous avons tout d'abord [créé une structure de base de données grâce aux migrations](#toc-premi%C3%A8re-%C3%A9tape-choisir-un-sch%C3%A9ma-pour-notre-base-de-donn%C3%A9es). Ensuite, nous avons affiché des composants graphiques grâce à la [bibliothèque de composants intégrés](https://sql.ophir.dev/documentation.sql) de SQLPage. Enfin, nous avons inséré des données dynamiquement dans notre base de données grâce au système de variables de SQLPage. ### Pour aller plus loin SQLPage est un logiciel libre et gratuit. Si vous rencontrez des problèmes lors de son utilisation, n'hésitez pas à [rapporter un bug ou demander une fonctionnalité sur github](https://github.com/lovasoa/SQLpage/issues), ou à discuter de son utilisation sur les [pages de discussion](https://github.com/lovasoa/SQLpage/discussions). Et si vous cherchez une idée pour vous entraîner... Pourquoi pas un [TapTempo](https://linuxfr.org/wiki/taptempo) entièrement en SQL ?