Outils en ligne d'aide à la gestion de bases de données relationnelles avec SQL / PostgreSQL
Table des matières
- Introduction
- Avertissement
- Apprendre le langage SQL / dialect PostgreSQL
- Modéliser une base de données
- Visualiser une base de données
- Compiler / valider / vérifier des instructions SQL
- Comprendre et analyser un plan d’exécution
- Bonus
- Conclusion
Introduction
Le mieux, quand on administre une base de données relationnelle SQL / PostgreSQL, est d’utiliser les outils directement fournis par le SGBD.
Parfois (souvent), ce n’est pas pratique, pertinent ou même possible de dégainer “l’artillerie lourde”. Il existe des cas où des outils en ligne font l’affaire : la modélisation ou visualisation d’une base de données, l’analyse d’un plan d’exécution, la conception rapide ou le prototypage de requêtes avancées, etc.
L’objet de cet article consiste à recenser quelques unes de ces situations et solutions (ainsi que des alternatives ou des liens pour aller plus loin).
Avertissement
Ci-dessous, je dresse une liste de ressources et services en ligne pratiques et que j’utilise régulièrement dans un contexte professionnel comme personnel (surtout).
🚨 Soyez hyper vigilants chaque fois que vous utilisez un service en ligne en rapport avec des données (schémas, bases, informations, scripts).
J’ai vérifié et ai confiance dans les services mis en avant, mais je me dédouane de tout mauvais usage qui en serait fait ou conséquences néfastes (fuites de données, exploitations à des fins indésirables, etc.).
Apprendre le langage SQL / dialect PostgreSQL
SQL est un langage relativement simple (pour un usage basique, voire classique) et pratique à connaître. Personnellement, je le place au même rang que HTML, CSS ou JS pour un développeur Web un peu touche-à-tout (je n’ai pas prononcé le mot “full-stack” !).
Évidemment, comme tout langage (informatique, naturel ou conceptuel), bien maîtriser SQL – ses subtilités et un usage un tant soit peu intensif ou avancé – requiert inévitablement du temps, des efforts, etc.
Il existe une quantité de ressources sur le Net pour apprendre tous les langages de ses rêves (🤯 même Brainfuck !!!).
La façon qui me correspond le mieux et me plaît le plus pour découvrir de nouvelles choses (langages, outils, concepts) est de pratiquer, si possible sans avoir rien à installer (encore moins sur mon poste), via des tutoriels interactifs, bien pensés et agréables (comportements, style, effets, etc.).
💡 Par exemple, j’adore et je conseille vivement le site “Learn Git Branching” que j’inflige à chacune des équipes dans lesquelles j’interviens (en vrai, c’est drôle, ludique et ça se fait bien après le daily, en fin de journée, ou pour reprendre le taff en début d’après-midi).
Pour apprendre SQL, j’ai trouvé mon compte sur le site sql-easy.com.
Je trouve que ça pourrait être plus joli encore, et je regrette le manque d’auto-complétion / coloration syntaxique dans la zone de saisie de la réponse.
Mais j’apprécie les exercices et explications proposées. J’ai vraiment eu l’impression de (re-)découvrir des choses et progresser.
Alternatives :
- sql-practice → ~50 questions de différents niveaux (easy, medium, hard) avec auto-complétion, auto-formatting, correction, etc.
- PostgreSQL Exercises → encore des exercices (70+) bien pensés, utilisant la syntaxe PostgreSQL
Modéliser une base de données
👉 DrawSQL
Une activité qui revient souvent, surtout en début de projet ou d’étude de faisabilité / réalisation, est la modélisation de schéma de données.
Il existe une pléthore d’outils de modélisation (aussi appelés “Data Modeling” ou “Entity Relationship Diagram”, a.k.a. ERD) de tout type : client lourd (en Java, Python, Rails, C/C++, etc.) ou service en ligne, mono ou multi dialect(s), gratuit ou payant, libre ou fermé, etc.
Un service que j’affectionne et qui m’a permis de démarrer pas mal de projets perso ces derniers temps (dont 99.99% n’ont jamais vraiment abouti, mais ça, c’est le destin commun des side-projects 🤷♂️) est DrawSQL.
Ci-dessous, la modélisation d’une application Web collaborative de gestion de projets via des tableaux Kanban.
DrawSQL est un service freemium, parfaitement adapté à des petits projets ou des projets qui se lancent. En gros, dans sa version gratuite, c’est surtout fait pour du prototypage. Et c’est ainsi que je m’en sers.
Dans sa version gratuite permet de définir autant de diagrams / bases que l’on souhaite, à partir du moment où on les rend publiques.
La limite de 15 tables (mais autant de colonnes ou relations que l’on souhaite) est contraignante.
De même qu’en version gratuite, on ne dispose d’aucune fonctionnalité collaborative. Il faut lâcher au moins 49$/mois (plan “Growth” pour une équipe de 5 pers.) pour en profiter.
Cela dit, il existe un plan gratuit pour les projets open source ! Pour en bénéficier, il suffit d’en exprimer la demande (en fournissant un minimum d’éléments de preuve, j’imagine).
Sans s’étaler sur toutes les fonctionnalités que proposent la plateforme, j’apprécie tout particulièrement :
- la bibliothèque de diagrammes, qui permet d’être curieux ou de s’inspirer
- la génération de script SQL (export), avec le choix du dialect (cf. exemple)
- la possibilité d’importer une base (dans la limite des 15 tables 😬)
Dans les petits points un peu pénibles :
- la navigation au clavier est pas dingue (notamment l’ajout / édition / suppression de colonnes)
- le SQL généré (pour du PostgreSQL) pourrait être amélioré, notamment tout ce qui est lié aux contraintes ou relations
- la scène et les changements ne sont pas sauvegardés automatiquement, ce qui peut vous mettre dans l’embarras 😭 #Vécu
Je ne suis pas en capacité (ni en envie) d’effectuer un benchmark complet et précis avec le reste des outils disponibles sur le marché. Il y en a tellement. Et les plus gros se valent grosso-modo. Le mieux, c’est de les essayer dans son contexte et par rapport à son besoin pour se faire une meilleure idée.
Alternatives :
- dbdiagram.io → la conception se fait via un DSL, donc du code, pour ceux qui n’aiment pas la souris ou le glisser-déposer
- pgAdmin ERD Tool → client lourd de modélisation de DB contenu dans la célèbre suite pgAdmin
Visualiser une base de données
👉 Azimutt
Je suis tombé récemment (encore 🙏 merci Vincent) sur une prometteuse application en ligne de visualisation de base de données : Azimutt.
Ce type de services est pratique (entre autre) pour :
- intégrer un nouveau-venu sur un projet et lui présenter la base de façon visuelle
- discuter un problème opérationnel avec une personne du métier / produit
- concevoir une modélisation par rapport à un problème donné
- communiquer auprès de partenaires ou personnes susceptibles d’être intéressées par le projet
L’application - open source - est développée en Elm par Loïc Knuchel (@loicknuchel) (principal engineer à Doctolib, au moment où je rédige ces lignes).
📚 J’ai repris le fichier généré par DrawSQL pour générer ce layouts. J’ai dû modifier la façon de définir les index (lors de la création de la table) ainsi que les clés étrangères. Le dataset est disponible ici.
J’ai rencontré quelques petits bugs ou détails pas pratiques :
- quand on clique sur un champ l’objet graphique bouge
- la façon de cacher des colonnes ou de les réordonner n’est pas immédiate
- il manque quelques handlers sur la touche entrée pour l’édition de certains champs
- en cas d’erreur d’un import de données, il faut recharger la page pour être sûr que les prochains fonctionnent correctement
- lorsque l’on supprime une base de données, les tables restent préchargées
- il manque une feature pour disperser les tables dans l’espace visuel (à défaut de pouvoir proposer une organisation intelligente et pertinente)
Mais dans l’ensemble, ça tourne bien. Très bien même !
J’ai réussi à importer et visualiser un vrai schéma d’une vraie base de prod (30 tables). Le résultat est vraiment cool.
📖 Aujourd'hui j'ai découvert (🙏merci @JousseVincent) @azimuttapp, un service en ligne pour y voir plus clair et plus joli dans son schéma de données. Idéal pour les nouveaux-venus d'un projet.
— Jérémy Buget 🇫🇷 🇪🇺 (@jbuget) August 22, 2022
👇 modélisation de la base du projet Carnet de bord ~ https://t.co/CSossRT4wJ pic.twitter.com/1VIaMSAlXW
Compiler / valider / vérifier des instructions SQL
Toujours dans un contexte de lancement de projet ou de maintenance d’un petit projet, un outil assez pratique qui m’a déjà été utile est un éditeur de requêtes SQL. En général, j’utilise OneCompiler.
OneCompiler est un éditeur de code en ligne. La plateforme supporte tout plein de langages / environnements : Java, NodeJS, HTML, Vue 3, PostgreSQL, MongoDB, Redis, etc.
Côté PostgreSQL, le service supporte un grand nombre d’instructions.
Notamment l’instruction d’analyse des plans d’exécution EXPLAIN (ANALYZE, FORMAT [TEXT|JSON|…]) 😻.
Petit bémol : il est nécessaire de précharger le modèle de données (suite d’instructions CREATE TABLE & cie) pour que le traitement aboutisse.
J’aurais aimé pouvoir définir un environnement persistant et configurable.
Peut-être est-ce dans une version payante, qui ne m’a pas sauté aux yeux.
Autres points positifs :
- OneCompiler est totalement gratuit “pour toujours” (remarque : la boîte existe depuis 2018, je ne capte pas leur business model 🤔)
- l’auto-complétion, coloration syntaxique
- la possibilité de sauvegarder et partager ses snippets
- la possibilité de récupérer toutes les commandes exécutées
- la galerie de snipets
- les nombreux outils annexes
L’exemple en capture repart du projet “Qanban” modélisé avec DrawSQL pour la création du schéma. Comme évoqué précédemment, j’ai dû l’adapter au niveau des contraintes et index pour qu’il fonctionne.
Notez la présence et l’exécution de l’instruction EXPLAIN ainsi que le QUERY PLAN en résultat.
⚠️ Comme nous sommes dans un contexte bac à sable, sans données (ou très peu), le plan d’exécution ne reflète pas la réalité. Dans un monde de production avec masse de données, les chemins et choix algorithmiques seraient assurément différents.
Alternatives
- CoderPad → autre éditeur de code PostgreSQL en ligne pour du pur bac-à-sable
- pgweb → client Web open source pour bases PostgreSQL écrit en Go
Comprendre et analyser un plan d’exécution
Parfois, il arrive d’être confronté à un comportement de la base ou une requête récalcitrant(e) en termes de performances.
Dans ce genre de cas, il est fréquent de sortir la carte “Execution Plan”, matérialisée par l’instruction EXPLAIN (ANALYZE, FORMAT [TEXT|JSON|…]).
Cette commande affiche le plan d’exécution que l’optimiseur de PostgreSQL™ engendre pour l’instruction fournie. Le plan d’exécution décrit le parcours de la (des) table(s) utilisée(s) dans la requête – parcours séquentiel, parcours d’index, etc. – . Si plusieurs tables sont référencées, il présente également les algorithmes de jointures utilisés pour rassembler les lignes issues des différentes tables.
Vous avez un exemple de résultat d’un plan d’exécution dans la capture précédente à propos de OneCompiler.
Un outil hyper pratique dont je me suis servi en production à plusieurs reprises est Explain édité et proposé gratuitement par la société Dalibo, spécialisée dans la gestion de bases PostgreSQL.
Attention, tout comme la commande et les concepts / mécanismes mis en jeu, bien tout interpréter et exploiter demande une connaissance approfondie de PostgreSQL ou plus généralement d’un SGBD.
Pour peu, qu’on ait “les bases” #JeanRoucas, alors Explain prend tout son sens et révèle les étapes coûteuses ou les choix algorithmiques décidés par le moteur de requête inadéquats ou surprenants.
Je n’ai pas de grieff particulier contre la plateforme. Tout fonctionne bien 👌. Il y a le bon niveau de fonctionnalités, ni plus ni moins.
À la rigueur, le fait de filer des exec plans à une société m’émeut quelque peu. Mais ça s’arrête là. Comme l’analyse se fait côté front, c’est ok. À la rigueur, je pourrais pousser plus loin l’analyse des requêtes HTTP qui partent. je vous laisse ce plaisir 😉
Exemple de requêtes EXPLAIN :
explain (analyze, format text)
select A.* from "Accounts" as A
where A.id in (
select C.id from "Comments" as C
where C.issue_id in (
select I.id from "Issues" as I
where I.project_id in (
select p.id from "Projects" as p
where p.team_id = 1
)
)
);
Exemple de résultat a.k.a. le QUERY PLAN :
Nested Loop (cost=60.80..61.97 rows=3 width=140) (actual time=0.010..0.017 rows=0 loops=1)
-> HashAggregate (cost=60.66..60.72 rows=6 width=4) (actual time=0.008..0.013 rows=0 loops=1)
Group Key: c.id
Batches: 1 Memory Usage: 24kB
-> Hash Semi Join (cost=37.70..60.64 rows=6 width=4) (actual time=0.005..0.008 rows=0 loops=1)
Hash Cond: (c.issue_id = i.id)
-> Seq Scan on "Comments" c (cost=0.00..20.20 rows=1020 width=8) (actual time=0.003..0.004 rows=0 loops=1)
-> Hash (cost=37.65..37.65 rows=4 width=4) (never executed)
-> Hash Join (cost=19.05..37.65 rows=4 width=4) (never executed)
Hash Cond: (i.project_id = p.id)
-> Seq Scan on "Issues" i (cost=0.00..16.80 rows=680 width=8) (never executed)
-> Hash (cost=19.00..19.00 rows=4 width=4) (never executed)
-> Seq Scan on "Projects" p (cost=0.00..19.00 rows=4 width=4) (never executed)
Filter: (team_id = 1)
-> Index Scan using "Accounts_pkey" on "Accounts" a (cost=0.15..0.21 rows=1 width=140) (never executed)
Index Cond: (id = c.id)
Planning Time: 0.330 ms
Execution Time: 0.115 ms
(18 rows)
ℹ️ Le query plan ci-dessus n’est pas des plus pertinent car je joue ma requête d’analyse sur une base vide. Donc les chemin utilisés sont les plus naïfs (dans ce contexte, ils sont pertinents et efficaces) et les coûts bien trop négligeables.
Le fait de préciser l’option ANALYZE va réellement exécuter la requête et ainsi permettre de déterminer avec exactitude et véracité les chemins empruntés et les coûts (CPU, RAM, temps) induits.
Lorsque l’on fait du debug, c’est plus précis et utile.
La plupart du temps, ça ne pose pas de problème, car ce sont souvent des requêtes de lecture compliquées qui génèrent des problèmes de performance.
Attention toutefois à ne pas déclencher l’exécution d’une requête qui bloque ou fait planter le système 🙃.
Je recommande les ressources (de niveau medium-avancé) suivantes pour aller plus loin dans la compréhension et l’exploitation d’un query plan :
- Use the index, Luke!
- Comprendre EXPLAIN, le livre blanc (format .pdf) de Dalibo, l’éditeur de l’outil
- Using EXPLAIN, la documentation officielle par PostgreSQL
Alternatives :
- Explain by Depesz → l’outil duquel s’est inspiré Dalibo
- pgMustard → un Explai.Dalibo-like, joli mais freemium (vs. full-free pour Dalibo) et qui oblige à passer du
json
Bonus
1/ Comprendre l’attaque par injection SQL
Hacksplaining est une plateforme d’apprentissage en ligne pour les développeurs sur le thème de la sécurité. Ils proposent un grand nombre de ressources très intéressantes, notamment des tutoriels interactifs pour comprendre les failles les plus courantes et classique d’attaque. Une en particulier m’intéresse dans le cadre de cet article : l’injection SQL.
C’est hyper didactique et ludique. Normalement, après cela, vous n’oublierez plus jamais de sécuriser correctement vos requêtes BDD en les sanitizant un max.
// Code faible sensible à l'injection SQL
app.post("/records", (request, response) => {
const data = request.body;
const query = `SELECT * FROM health_records WHERE id = (${data.id})`;
connection.query(query, (err, rows) => {
if(err) throw err;
response.json({data:rows});
});
});
// Code un peu plus robuste (mais qui ne suffit pas à garantir une protection totale de l'application / du système)
app.post("/records", (request, response) => {
const data = request.body;
connection.query('SELECT * FROM health_records where id = ?', [data.id], (err, rows) => {
if(err) throw err;
response.json({data:rows});
});
});
Pour aller plus loin, je vous recommande la lecture sur le sujet de cet article (🇬🇧) : “Node.js SQL Injection Guide: Examples and Prevention”
2/ Optimiser les paramètres d’une base PostgreSQL
👉 PGTune
Délaissons la sécurité pour retourner du côté de la performance.
Un petit service bien pratique qu’il m’est arrivé d’utiliser à titre de conseil / inspiration est PGTune. Il s’agit d’un simple page Web avec un simple formulaire qui permet de déterminer un genre de configuration technique optimale selon les recommandations de PostgreSQL pour une machine donnée.
⚠️ Attention ! Comme ils le mentionnent eux-mêmes, la config qu’ils proposent n’est qu’une piste possible d’optimisation. Il ne faut pas oublier de tenir compte de plein d’autres paramètres (la taille de la base, des tables, leur nature, constitution, le type d’usage, etc.) pour définir une configuration la mieux adaptée.
3/ Ressources pour aller plus loin avec PostgreSQL
Pour finir, je vous partage ce repository GitHub : AwesomePostgres qui contient tout plein de ressources très intéressantes pour progresser dans la pratique et la compréhension de SQL / PostgreSQL.
Bonne lecture !
Conclusion
De tous les outils que j’ai présentés, ceux qui me sont le plus utiles aujourd’hui sont Azimutt et Explain.Dalibo.
Le premier car, en tant que CTO, je me sens responsable d’avoir un minimum de compréhensio métier et technique (dont la modélisation) des équipes avec lesquelles je travaille.
Le second qui m’a déjà sorti plusieurs fois de problèmes de requêtes mal écrites ou de performances (des index oubliés, mal placés, mal formulés, sur des très grosses tables ou bases).
Encore une fois, il faut rester extrêmement vigilant par rapport à tous ces projets en ligne plus ou moins gratuits (“quand c’est gratuit, c’est toi le produit”) car on ne peut jamais savoir à 100% ce que les plateformes font de nos données, ni comment ils exploitent nos comportements.
Et ne jamais - JAMAIS ! - saisir ou téléverser de données de production (à la rigueur des exec plans ou des data ano/pseudo-nymisées).
Et vous ? Que pensez-vous de ce type d’outils ? Quels sont ceux que vous utilisez ? Dans quelles circonstances ? Pour quoi faire ?