Simplifiez vos requêtes SQL grâce à la programmation orientée objets.

On a tous écrit un jour du code de ce genre-là :

const sql = 'SELECT * FROM steak WHERE steak.weight >= ? ORDER BY steak.weight DESC LIMIT ?';
const params = [req.params.minWeight, req.params.limit];

db.all(sql, params, (err, rows) => {
    res.json(rows);
});

Ou même pire, du code de ce style-là :

// Dangereux, ne faite jamais ça !
const sql = "SELECT * FROM steak WHERE steak.weight >= " + req.params.minWeight + " ORDER BY steak.weight DESC LIMIT " + req.params.limit;

db.all(sql, params, (err, rows) => {
    res.json(rows);
});

C’est assez fréquent quand on veut communiquer avec une base de données, puisque l’on doit passer par le langage de son SGBD (souvent un dérivé du langage SQL), donc c’est normal que l’on soit obligé d’écrire nos requêtes directement dans une chaîne de caractères pour pouvoir ensuite les envoyer à notre BDD.

Mais on ne va pas se mentir : c’est moche. C’est vraiment très moche.

Bon déjà, on repassera sur l’idée d’écrire du code dans des chaînes de caractères, déjà parce que ce n’est pas vraiment fait pour ça, mais aussi parce que la plupart des IDE et éditeurs de texte ne vont pas pouvoir vous aider pour  créer, debugger et rendre lisibles vos requêtes. Ils ne font pas la différence entre deux chaînes de caractères, même si l’une contient du SQL, et l’autre la recette des crêpes bretonnes.

Résultat : pas de coloration syntaxique ni d’auto-complétion, ce qui rend le code encore plus compliqué à lire et à écrire.

Ajoutez à cela des conventions de nommages pas respectées, des caractères spéciaux et des concaténations, et on se retrouve rapidement avec du code qui va vous faire regretter l’époque des cartes perforées.

Voici quelques délicieux exemples (âmes sensibles s’abstenir).

const sql = "select steak.id, name, weight from steak join animal on animal.id = steak.animal_id where eatable = 1 order by name";

const sql = "select sum(weight), name from steak join animal on animal.id = steak.animal_id join cooking on cooking.id = steak.cooking_id where type = 'Blue' or type = 'Rare' group by name;";

const sql = "select (select count(steak.id) from steak join animal on animal.id = steak.animal_id where name = 'Beef') as Steaks2Boeuf, count(steak.id) as SteaksTotal from steak where weight between 50 and 1000;";

Bon, évidemment je grossis le trait en mettant les mots-clés en minuscule et en oubliant les noms de table en préfixe, mais vous voyez le principe, ça part très facilement dans tous les sens.

Mais alors pourquoi est ce qu’on irait se tirer une balle dans le pied en écrivant nos requêtes de cette manière ? La raison est simple : vouloir faire au plus court.

Quand on commence à coder, nos premières requêtes sont toujours des trucs du genre “SELECT * FROM animal” ou “SELECT id FROM steak WHERE weight >= 100”, bref, des petites requêtes inoffensives qui passent plutôt bien sous forme de chaînes de caractères.

Mais au fur et à mesure que le projet avance, on va commencer à faire fasse à des requêtes qui impliquent des paramètres, des conditions, des boucles, des notions de SQL toujours plus complexes… Et avant qu’on s’en rende compte, notre code ne ressemble déjà plus à rien.

Mais si ce n’était qu’un souci de propreté, ça “”””“passerait”“”””, mais la conséquence, c’est que le code devient extrêmement difficile à maintenir.

Déjà pour écrire les requêtes, c’est une galère puisqu’il faut connaître assez bien le SQL pour ne pas faire d’erreurs, et Dieu sait qu’une virgule oubliée, c’est vite fait. Il faut aussi s’assurer que vos requêtes soient saines pour éviter les problèmes d’injection SQL. Et le tout sans l’aide de votre IDE, comme à la vieille époque.

Et c’est pas fini, puisqu’au moment de devoir debugger votre requête (6 mois plus tard), vous allez passer plus de temps à vous demander qui a écrit cette #”(_&=$ plutôt qu’à réellement corriger quoi que ce soit.

const sql = "SELECT id, type as cuisson, weight as poids from steak JOIN cooking on cooking.id = steak.cooking_id WHERE (type = 'Raw' and weight >= 250 or (type = 'Well-done' and weight >= 80) ORDER BY type dzsc, weight asc LIMIT 25;";

Pour l’anecdote, je me suis retrouvé récemment dans cette situation où je devais debugger une de mes requêtes qui ne marchait plus après avoir fait une modification autre part dans mon code. Une petite requête de pas moins de 50 lignes (>.<”), le tout sous forme de chaîne de caractères, avec concaténations, conditions, boucles… Bref, un max de fun.

Ça m’aura pris plus de 30 minutes avant que je rende compte que le souci venait de 2 colonnes inversées dans le SELECT… 30 minutes pour ça. J’aurais pu rentrer chez moi 30 minutes plus tôt si seulement je n’avais pas eu la super idée de faire cette requête en dur dans mon code. Mais je sais très bien pourquoi j’avais fait ça à l’époque, tout simplement parce que c’était ma manière de travailler (et celle de mes collègues) depuis le début du projet, donc pourquoi changer nos bonnes vieilles habitudes, hein ? C’est à ce moment là que je me suis promis de ne plus jamais avoir à revivre cette situation, jamais !

Si vous pensez que ça mérite de réfléchir à une alternative, et bien j’ai une solution pour vous. Au lieu de créer nos requêtes sous forme de chaînes de caractères comme on le fait d’habitude, on va profiter de la programmation orientée objets pour déléguer le travail à une classe qui va tout faire pour nous. C’est ce que l’on appelle un QueryBuilder.

Fondamentalement, c’est un outil qui va permettre de construire des requêtes en utilisant des objets plutôt que des chaînes de caractères : On ne fait qu’appeler des méthodes en précisant les paramètres, et tout le reste se fait en arrière-plan. Exactement ce que l’on veut en fait.

// SELECT * FROM steak WHERE steak.weight >= 100 ORDER BY steak.weight DESC LIMIT 10;
knex.select('*')
    .from('steak')
    .where('steak.weight', '>=', 250)
    .orderBy('steak.weight', 'DESC')
    .limit(10)

.then((rows) => {
    res.send(rows);
});

Note : cet article ne traitera pas des ORM, qui sont une autre manière de déléguer des requêtes SQL. C’est un sujet différent qui mérite un (voir plusieurs) article à lui tout seul.

Les QueryBuilders ont plusieurs intérêts.

Déjà, étant donné qu’on utilise le langage natif de l’application (et non pas du SQL), notre IDE ne sera plus complètement perdu. Il saura vous proposer les méthodes adéquates (auto-complétion) et tout colorier comme il faut.

Ensuite, quelque chose que j’aime bien, ça va vous permettre d’uniformiser toutes vos requêtes. En utilisant un QueryBuilder, vous vous assurez que l’output est impeccable, les conventions de nommage sont respectées, aucune faute de frappe, la crème de la crème.

Les machines sont meilleures que nous pour uniformiser les choses. Prenez une recette de cuisine, vous demandez à une machine de mettre 10g de sel, elle va mettre 10g à tous les coups, alors que nous autres, pauvres humains, nous risquons mettre 9.99g ou 10.01g, même en faisant très attention. C’est pareil pour les requêtes SQL. Pour nous, c’est vite fait d’oublier le “;” à la fin ou un préfixe quelque part. Le QueryBuilder, lui, ne se trompe jamais (à condition d’en avoir choisi un fonctionnel bien sûr).

Ensuite, la lisibilité du code est grandement améliorée. On ne fait qu’appeler des méthodes, donc à part si vous avez commencé la programmation il y a une heure, vous devriez être capable de comprendre ce qu’il se passe. Voyez par vous-même, ça se lit aussi presque aussi bien que du SQL !

knex.select('steak.id', 'steak.weight', 'cooking.type')
    .from('steak')
    .join('cooking', 'cooking.id', 'steak.cooking_id')
    .where('steak.weight', '>=', 250)
    .andWhere('cooking.type', '=', 'Raw')
    .orderBy('weight', 'DESC')
    .limit(10);

Enfin, dernier point important, c’est que le QueryBuilder limite grandement le risque d’injection SQL, et ceux sans que vous n’ayez à faire quoique ce soit de particulier. La plupart des langages possèdent déjà des mesures qui permettent de limiter ce risque, notamment la notion de requête préparée consistant à séparer les paramètres de leurs requêtes pour pouvoir les “désinfecter” (s’assurer qu’ils ne soient pas dangereux).

const sql = "SELECT steak.id, steak.weight, cooking.type FROM steak WHERE steak.weight >= ? AND cooking.type >= ? ORDER BY steak.weight DESC LIMIT ?";
const params = [250, 'Raw', 10];

db.all(sql, params, (err, rows) => {
    res.json(rows);
}); 	

Bien sûr, la plupart des QueryBuilders prennent en charge cette fonctionnalité, mais de la manière la plus fluide possible pour que vous n’ayez même pas à vous en occupez. Certains iront même jusqu’à exécuter les requêtes eux-même, ce qui simplifie encore plus le travail.

knex.select('steak.id', 'steak.weight', 'cooking.type')
    .from('steak')
    .join('cooking', 'cooking.id', 'steak.cooking_id')
    .where('steak.weight', '>=', 250) // Param 1 => 250
    .andWhere('cooking.type', '=', 'Rare') // Param 2 => 'Rare'
    .orderBy('steak.weight', 'DESC')
    .limit(10) // Param 3 => '10'

Donc pour résumer, on a un outil qui nous permet de rendre notre code :

• Plus simple à écrire.
• Uniforme.
• Plus propre et lisible.
• Plus sécurisé.

Alors, qu’est ce qu’on attend ?

Préambule

Bon la théorie, c’est bien sympa, mais passons un peu à la pratique.

Pour la suite des exemples, je vais utiliser Knex.js, qui est le QueryBuilder JavaScript le plus connu. La raison ? Tous simplement parce que c’est un QueryBuilder assez complet qui me permettra de bien expliquer pourquoi c’est super cool, et aussi (et surtout) parce que j’aime bien Node.js. Mais sachez qu’il en existe dans quasiment tous les langages, et le fonctionnement est sensiblement le même à chaque fois.

J’utiliserais aussi le SGBD SQLite parce que c’est super simple à mettre en place et parfait pour une démonstration, mais Knex.js est compatible avec plein d’autres SGBD : MySQL, Postgre, SQL Server…

Maintenant que tout est clarifié, on peut commencer.

Principe

Le principe d’un QueryBuilder est que l’on va instancier un objet qui va contenir tous les outils qui vont nous permettre de créer notre requête. On ne fait qu’appeler des méthodes de cet objet et la requête se construit en arrière-plan. Ils sont en général divisés en 3 parties :

1. Le constructeur (builder) : c’est la classe qui va contenir toutes les méthodes qui vont permettre de générer la requête (select(), from(), where()...).
2. La requête (query) : le résultat du constructeur, elle va contenir la requête SQL générée ainsi que ses paramètres.
3. Le compilateur (compiler) : l’outil qui va exécuter la requête.

Notons que ces 3 parties ne sont pas nécessairement distinct en 3 objets différent. Certains comme Knex.js vont préférer tout mélanger par soucis de simplicité, mais la distinction se fait tout de même en arrière-plan.

Fonctionnement

L’utilisation d’un QueryBuilder va suivre le schéma suivant :

D’abord, on instancie le constructeur (en anglais, builder ou factory), c’est lui qui va nous permettre de construire les requêtes. C’est à ce moment-là que l’on choisi le SGBD que l’on vise, puisque la requête finale générée par le builder sera bien sûr différente en fonction de celui-ci.

const knex = require('knex')({
    // Le nom du SGBD vient ici
    client: 'sqlite3',
    connection: {
        filename: "./databases/food.db"
    }
});

Notez que comme Knex.js va compiler nos requêtes lui-même, il a aussi besoin de nos paramètres de connexion. Mais certains QueryBuilder se contentent simplement de créer les requêtes et vous laisse les exécuter vous-même.

Une fois le builder instancié, on peut commencer à appeler les méthodes de construction de requête. Les méthodes sont généralement nommées de la même manière que les mots clef SQL, ce qui donne du code lisible à en faire rougir Shakespeare.

Quelques exemples de requêtes construites avec Knex.js, et leur équivalent SQL.

// INSERT INTO steak(animal_id, cooking_id, weight) VALUES(?, ?, ?)
knex('steak').insert({
    animal_id: req.body.animal_id, 
    cooking_id: req.body.cooking_id, 
    weight: req.body.weight
})

// UPDATE steak SET weight = ? WHERE id = ?
knex('steak')
    .update('weight', req.body.weight)
    .where('id', '=', req.body.id)

// DELETE FROM steak WHERE id = 12
knex('steak')
    .del()
    .where('id', '=', req.body.id)

// SELECT (SELECT COUNT(steak.id) FROM steak JOIN cooking ON steak.cooking_id = cooking.id WHERE cooking.type = 'Blue' OR cooking.type = 'Rare' OR cooking.type = 'Medium-rare') AS 'ColdSteakNumber', (SELECT COUNT(steak.id) FROM steak) AS 'TotalSteakNumber' FROM steak JOIN animal ON steak.animal_id = animal.id WHERE steak.weight >= 50 AND steak.weight <= 1000 AND animal.eatable = 1 LIMIT 1

let coldSteakNumber = 
knex('steak')
    .count('steak.id')
    .join('cooking', 'steak.cooking_id', 'cooking.id')
    .where('cooking.type', "=", 'Blue')
    .orWhere('cooking.type', "=", 'Rare')
    .orWhere('cooking.type', "=", 'Medium-rare')
.as('ColdSteakNumber');

let totalSteakNumber = 
knex('steak')
    .count('steak.id')
.as('TotalSteakNumber');

knex
.select(coldSteakNumber, totalSteakNumber)
.from('steak')
.join('animal', 'steak.animal_id', 'animal.id')
.where('steak.weight', '>=', 50)
.andWhere('steak.weight', '<=', 1000)
.andWhere('animal.eatable', '=', '1')
.limit(1)

Ensuite, on obtient le fruit de notre “dur labeur”, la requête. On pourrait s’attendre à récupérer simplement la requête que l’on a construit, mais c’est un petit peu plus compliqué que ça.

Pour quasiment tous les QueryBuilders, les paramètres sont séparés de la requête. C’est une mesure de sécurité pour contrer les injections SQL, et beaucoup ne vous laisseront pas voir la requête finale par peur qu’un utilisateur inconscient l’utilise directement sans passer par un requête préparée, ce qui est très dangereux.

Au lieu de ça, vous allez donc avoir d’un coté un modèle de requête de ce type :

"select `steak`.`id`, `steak`.`weight`, `cooking`.`type` from `steak` inner join `cooking` on `cooking`.`id` = `steak`.`cooking_id` where `steak`.`weight` >= ? and `cooking`.`type` = ? order by `weight` DESC limit ?"

Et de l’autre, tous les paramètres bien rangés dans un tableau, qui vont venir remplacer les "?" dans l'ordre.

[ 150, 'Raw', 10 ]

Note : avec Knex.js, vous pouvez visualiser le résultat de la requête (modèle + paramètres) avec la méthode toSQL().

knex.select('steak.id', 'steak.weight', 'cooking.type')
    .from('steak')
    .join('cooking', 'cooking.id', 'steak.cooking_id')
    .where('steak.weight', '>=', 250)
    .andWhere('cooking.type', '=', 'Rare')
    .orderBy('steak.weight', 'DESC')
    .limit(10)

    .then((rows) => {
        res.send(rows);
    });

Enfin, vient le moment de la compilation. Par chance, Knex.js s’en occupe tout seul. Il suffit d’un petit coup de then() pour qu’il interroge la base de données pour vous et vous retourne le résultat.

knex.select('steak.id', 'steak.weight', 'cooking.type')
    .from('steak')
    .join('cooking', 'cooking.id', 'steak.cooking_id')
    .where('steak.weight', '>=', 250)
    .andWhere('cooking.type', '=', 'Rare')
    .orderBy('steak.weight', 'DESC')
    .limit(10)

    .then((rows) => {
        res.send(rows);
    });

Si votre QueryBuilder ne compile pas les requêtes lui-même, il vous suffit de préparer le modèle de requête, puis de binder les paramètres. Dit comme ça, ça a l’air compliqué, mais comme la plupart des langages prennent en charge toutes ces fonctions nativement, c’est simple comme bonjour. J’explique tout ça dans mon bonus sur le QueryBuilder PHP.

let query = 
knex.select('steak.id', 'steak.weight', 'cooking.type')
    .from('steak')
    .join('cooking', 'cooking.id', 'steak.cooking_id')
    .where('steak.weight', '>=', 150)
    .andWhere('cooking.type', '=', 'Rare')
    .orderBy('steak.weight', 'DESC')
    .limit(10)
    .toSQL()

db.all(query.sql, query.bindings, (err, rows) => {
    res.json(rows);
}); 

Et voila, c’est à peu près tout ce qu’il y a à savoir pour pouvoir utiliser un QueryBuilder. Maintenant, à vous les joies de pouvoir créer, debugger et améliorer vos requêtes les doigts dans le nez sans jamais oublier cette *%#( de virgule qui vous aura fait perdre une heure.

Mais avant de passer à la suite, il y a quelques petites limitations que vous devez connaître sur les QueryBuilders avant de foncer tête baissée.

Limitations

Ce qu’il faut d’abord savoir, c’est que vous ne retrouverez jamais la flexibilité du langage SQL avec un QueryBuilder. Avec SQL, vous pouvez vraiment parcourir votre base de données en long, en large, en travers, à pieds, à vélo, et à cheval en même temps. Même si certains QueryBuilders poussent le concept extrêmement loin (comme Knex.js), il serait un peu prétentieux de dire qu’ils vont complètement remplacer le SQL. Mais attention ! Cela ne veut pas dire qu’il faut tout laisser tomber et repartir sur des “bonnes” vieilles requêtes en chaînes de caractères, surtout pas ! Mon conseil, c’est de garder les requêtes simples pour votre QueryBuilder (99% d’entre elles en fait), et pour le reste, d’utiliser d’autres techniques pour vous simplifier la tâche, comme des scripts SQL, des vues, des fonctions ou des procédures stockées.

La deuxième chose, c’est que peu de QueryBuilders vont prendre en charge les requêtes de manipulation de schéma de base de données (CREATE, ALTER…). Souvenez-vous, les QueryBuilders ne sont pas des ORM, et le but n’est pas de créer et de gérer votre BDD depuis le code (code first).

Et une dernière chose. On aura parlé de tous les avantages des QueryBuilders et de tout le bien que j’en pense, mais il est de mon devoir de vous rappeler que ce sont ne sont ni plus ni moins qu’une couche d’abstraction supplémentaire de votre code (comme les frameworks, les API et les langages objet). Sur une application normale, on ne verrait pas la différence, mais j’aurais tendance à déconseiller leur utilisation si les performances de votre application sont critique et que vous êtes vraiment à la milliseconde prés. Si c’est le cas, le plus optimal serait encore de programmer toutes vos requêtes sur votre SGBD, en faisant des vues, des fonctions et des procédures. Si ce n’est pas le cas, vous pouvez tout de suite passer à la partie suivante !

Choisir un QueryBuilder

Voilà, maintenant que vous savez tout sur les QueryBuilders (je plaisante à peine, c’est vraiment aussi simple que ça), il ne vous reste plus qu’à en implémenter un. Vu que ce n’est qu’un outil, vous pouvez faire ça à n’importe quel moment de votre projet, mais je vous recommande quand même de le faire au plus tôt de manière à passer moins de temps sur le réusinage (pas nécessaire, mais recommandé).

Le plus dur, c’est de choisir le bon. Pour cela, vous pouvez regarder les critères suivant :

• Est-ce qu’il est compatible avec le langage de votre projet ? Bien sûr.
• Est-ce qu’il est compatible avec votre SGBD ? Attention, se limiter à du SQL de base peut vite devenir contraignant.
• Est-ce qu’il prend en charge les injections SQL ? Si ce n’est pas le cas, à éviter à tout prix. Si vous ne savez pas, inspectez bien la documentation, ou bien testez le et vérifiez que les requêtes sortent bien sous forme de modèle + paramètres.
• Est-ce qu’il est riche au niveau fonctionnalités ? S’il est open source, vous pourrez toujours le modifier par la suite, mais le moins vous avez à faire, le mieux c’est.
• Est-ce qu’il est simple à utiliser ? Certains outils ont l’air géniaux sur le papier, mais s’avèrent être de terribles usines à gaz, que ce soit à cause de leur manque de documentation, l’intégration trop compliquée, le manque de flexibilité… Souvenez-vous, le but est transformer un truc pénible en un truc simple, pas en un truc encore plus fastidieux.
• Est-ce qu’il vous plaît ? Si la syntaxe vous donne envie de faire ressortir votre McDo, ce n’est pas bon signe. Les fonctionnalités, c’est une chose, mais il ne faut surtout pas négliger le ressenti personnel.
• Est-ce qu’il plaît aux autres membres de votre équipe ? Pensez à les consulter avant d’intégrer l’outil en mode ninja, ce n’est pas très bien vu en général ;)

Notez que parfois, vous n’avez pas besoin de trop vous casser la tête pour choisir votre QueryBuilder. Il y a des frameworks qui en possèdent un nativement. C’est le cas par exemple pour les frameworks Symfony et Laravel en PHP (et si jamais ils ne vous plaisent pas, vous pouvez toujours les remplacer, ce n’est pas le choix qui manque).

Et si jamais, et je dis bien SI JAMAIS aucun QueryBuilder sur le marché ne correspond à vos attentes mais que vous avez quand même envie d’en utiliser un, vous pouvez toujours le développer vous-même. Pour un développeur expérimenté, c’est une affaire d’une journée pour un outil qui prends en charge toutes les requêtes de base (je sais bien, je l’ai fait). Si vous êtes dans cette situation, pensez à faire partager votre travail sur GitHub :D

Si vous voulez quelques pistes, j’ai testé pour vous 3 QueryBuilders dans 3 langages différents (JS, PHP et C#) qui me paraissent très prometteurs. Vous les trouverez en bonus de cet article accompagné d’explications et d’exemples.

Bon, je pense que vous l’aurez compris, mais j’aime vraiment beaucoup les QueryBuilders. Ils rendent le code plus propre, lisible, maintenable et sécurisé. Bref, tout ce que j’aime.

Mais ce sont loin d’être les seuls outils qui permettent de faire ça. Sur ce blog, vous retrouverez tout un tas d’outils et de stratégies qui vont vous aider à considérablement augmenter la qualité de vos développements. Allez y jeter un œil, vous ne le regretterez pas !

Et si vous avez des choses à ajouter, des remarques ou des suggestions, vous pouvez utiliser l’espace commentaire juste en dessous, je le lirais avec beaucoup d’attention ! Et n’oubliez pas de partager l’article pour en faire profiter à tous ces développeurs qui codent encore leurs requêtes en dur, faisons bouger les choses !

Accéder au bonus : Analyse de 3 QueryBuilders prometteurs.