J'écris encore du SQL

Préambule

Aujourd’hui, j’aimerais vous parler d’un proof-of-concept que je viens tout récemment d’appliquer dans mon travail. Mais avant ça, j’aimerais raconter comment j’en suis venu à m’intéresser au sujet dont je m’apprête à vous parler.

J’ai toujours aimé le SQL. Ce langage à la fois simple et élégant permettant d’effectuer des requêtes au sein d’une base de données, comme lui on lui parlait directement.
Quand j’étais à l’école, c’est peut-être la première fois qu’en informatique j’avais l’impression de parler une autre langue. Une base de données nous fournira toujours les informations que l’on veut, pour peu qu’on sache comment les lui demander.

Je suis alors devenu développeur, et je me suis rappelé que si j’ai appris à communiquer avec une base de données c’était surtout pour l’appliquer au sein même de mes programmes. Inclure des requêtes SQL dans un code source, donc.
Comme tout étudiant j’ai connu la joie d’écrire des SELECT * et INSERT INTO dans des chaînes de caractères concaténées à des valeurs que je passais dans des formulaires, et l’émerveillement de voir s’afficher les données de ma base dans mes pages web.

Comme beaucoup d’étudiants, j’ai mis mes premiers sites web en ligne pour les montrer fièrement à tout le monde.
Puis comme tous ceux qui on fait la même chose que moi, j’ai découvert la joie de me faire poutrer ma base de données à grands coups d’injections SQL.
Comment ça, concaténer des strings SQL, c’est pas bien ?

J’ai alors découvert comment faire de la préparation de requêtes.
Nous nous sommes tous heurtés aux mêmes problèmes :

C’est rébarbatif
On peut facilement faire des erreurs
La peur de laisser passer une injection est toujours présente
Il faut faire le mapping avec nos objets avec attention

Pour ce dernier point, je parlerai “d’objet” dans le cas général et le sens philosophique du terme, bien que je m’apprête à illustrer la problématique avec le langage Go qui n’est pas orienté objet.

Je n’étais pas le seul à avoir remarqué cette nécessité de mapper des structures / classes avec des tables de bases de données, d’autres l’ont remarqué bien avant moi. Ce sont ces gens qui ont créé les ORM (Object-Relational Mapping), permettant de répondre à ce besoin.

Les avantages des ORM sont immenses :

On manipule directement des objets
On abstrait totalement la couche SQL, même plus besoin d’écrire du SQL
Facilite grandement la sérialisation / déserialisation
Opérations CRUD simplifiées
La gestion des relations est simplifiée
Compatibilité entre plusieurs bases de données (PostgreSQL, MySQL, …)

Il y a également des inconvénients incoutournables :

Perte de performance
Contrôle plus limité
Debugging parfois plus compliqué

Bien que ces inconvénients existent, les avantages sont bien trop évidents pour passer à côté. Le principe des ORM est largement adopté dans à peu près tous les langages de programmation.

J’adorais le langage SQL, mais je dois me faire une raison : écrire du SQL à notre époque est obsolète. Plus personne ne fait ça. C’est du moins ce que je pensais.
Cela fait des années que je parle avec mes bases de données via des ORM, et je m’y suis accomodé. Mais je dois vous avouer que je suis nostalgique de l’époque où je parlais la langue de mes bases de données.

Récemment au travail, nous avons été confrontés à certains problèmes liés à ce manque de contrôle qu’entraîne parfois les ORM, notamment lorsque nous devions changer le schéma de la base de données.
C’est alors qu’un collègue est venu nous présenter une technologie que je ne connaissais pas encore : SQLC.

SQLC et les ORMs

SQLC est un outil qui génère automatiquement du code partir de requêtes SQL écrites manuellement.
Il a été créé pour combiner la sécurité et la performance du SQL brut avec la facilité d’utilisation d’un ORM, tout en évitant la surcharge de traitement et la perte de contrôle que peuvent introduire ces derniers.

Initialement écrit en Go pour du langage Go, SQLC supporte aujourd’hui plusieurs autres langages.
Ici, nous en parlerons essentiellement en Go, et nous utiliserons à titre comparatif l’ORM GORM.

Prenons un schéma de base de données très simple :

1
2
3
4
5
6
7
8


+---------------------+          +------------------+
|         book        |          |      author      |
+---------------------+          +------------------+
| ID         (PK)     |     +--> | ID      (PK)     |
| Title               |     |    | Email            |
| PublicationDate     |     |    | Bio              |
| AuthorID   (FK)     | ----+    |                  |
+---------------------+          +------------------+

À première vue ça ressemble un peu à un retour en arrière : on utilise de nouveau des requêtes préparées écrites directement dans notre code source.

Dans le cas des ORM, le SQL est totalement invisible.
Ajouter une ligne dans une base de données prend littéralement une ligne de code :

1
2
3


func CreateAuthor(db *gorm.DB, author *models.Author) error {
    return db.Create(author).Error
}

Cependant, comme dit plus haut, nous voulons nous affranchir des limitations des ORM. Si manipuler des bases de données avec un ORM est aisé avec des exemple triviaux, qu’en est-il lorsque nous voulons exécuter des requêtes complexes ?
Ce n’est pas focément impossible, mais nous oblige parfois à faire des concessions (découpage des requêtes pour déléguer une partie des calculs au langage de programmation, création de vues, etc…)
Au final, les inconvénients induits par les requêtes préparées manuellement, n’y a-t-il pas un autre moyen de les gérer ?

SQLC prend le problème dans l’autre sens : pas de dépendance dans le code, mais un outil en local qui génère du code compatible librairie standard.
A noter que de la génération de code vers d’autres librairies est également possible, mais ici c’est le fait de ne pas avoir de dépendances autres la librairie standard qui nous intéressera.
Le résultat :

On écrit à nouveau du code SQL
On obtient du code 100% compatible librairie standard prêt à l’emploi

Il suffit juste d’ajouter un commentaire au-dessus des requêtes afin de spécifier le nom de la fonction désiré :

author.sql

1
2
3
4
5
6
7
8
9


-- name: createAuthor :one
INSERT INTO author (email, bio)
VALUES ($1, $2)
RETURNING id;

-- name: getAuthor :one
SELECT *
FROM author
WHERE id = $1;  

auhor.sql.go (fichier généré)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


import (
    "context"
    "database/sql"
)

const createAuthor = `-- name: createAuthor :one
INSERT INTO author (email, bio)
VALUES ($1, $2)
RETURNING id
`

type createAuthorParams struct {
    Email string
    Bio   sql.NullString
}

func (q *Queries) createAuthor(ctx context.Context, arg createAuthorParams) (int32, error) {
    row := q.db.QueryRowContext(ctx, createAuthor, arg.Email, arg.Bio)
    var id int32
    err := row.Scan(&id)
    return id, err
}

const getAuthor = `-- name: getAuthor :one
SELECT id, email, bio
FROM author
WHERE id = $1
`

func (q *Queries) getAuthor(ctx context.Context, id int32) (Author, error) {
    row := q.db.QueryRowContext(ctx, getAuthor, id)
    var i Author
    err := row.Scan(&i.ID, &i.Email, &i.Bio)
    return i, err
}

C’est une autre façon d’appréhender le problème : fini d’écrire du boilerplate, plus à craindre de potentielles injections dues à des erreurs humaines.
Des avantages très proches de ceux fournis par les ORM en somme, mais avec un contrôle sur les requêtes et sans perte de performances.
Vous pouvez trouver un example plus complet sur le SQLC Playground.

Ecrire du SQL, c’est cool.

Vous noterez le SELECT * a été transformé en SELECT id, email, bio dans le code généré.
En effet, SQLC remplace SELECT * par une liste explicite de colonnes pour garantir la stabilité du code généré, optimiser les performances et éviter les erreurs liées aux changements de schéma.

Vous remarquerez ensuite que la comparaison entre l’utilisation des fonctions fournies par un ORM et celle des fonctions générées par SQLC est assez similaire.

GORM

1
2
3
4
5
6
7


func GetAuthor(db *gorm.DB, id int64) (*models.Author, error) {
    var author models.Author
    if err := db.First(&author, id).Error; err != nil {
        return nil, err
    }
    return &author, nil
}

SQLC

1
2
3
4
5
6
7


func (q *Queries) GetAuthor(ctx context.Context, id int32) (*Author, error) {
    author, err := q.getAuthor(ctx, id)
    if err != nil {
        return nil, err
    }
    return &author, nil
}

Dans le cas de SQLC, on utilise une structure de type *Queries, qui est une encapsulation de DBTX, une interface qui définit un contrat pour exécuter les requêtes SQL. Tous deux sont générés par SQLC.

Libre à nous de les utiliser comme on veut. Pour ma part, j’ai choisi de rendre les fonctions générées internes (d’où le fait qu’elles commencent par une lowercase), afin de les encapsuler par des fonctions exportées du même nom que je définis comme méthode de *Queries.

Et c’est tout ! Tout aussi simple à utiliser qu’avant, mais avec bien des avantages.

D’ailleurs, des avantages, il y en a d’autres. J’aimerais justement attirer l’attention sur deux d’entre j’apprécie beaucoup.
Premièrement, les modèles sont eux aussi générés par des scripts SQL.

Génération de modèles

Voici les modèles Go générés par mes scripts SQL :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


type Author struct {
    ID    int32
    Email string
    Bio   sql.NullString
}

type Book struct {
    ID              int32
    Title           string
    PublicationDate time.Time
    AuthorID        int32
}

Vous commencez à comprendre le concept de SQLC, ces modèles ont bien évidemment été écrits par des scripts SQL.
Voici donc un script SQL qui permettrait la génération de ces modèles :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


CREATE TABLE author (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    bio TEXT
);

CREATE TABLE book (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    publication_date DATE NOT NULL,
    author_id INT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES author(id) ON DELETE CASCADE
);

Ça fonctionne, mais en entreprise les tables de nos bases de données sont rarement figées, elles évoluent. C’est pour cela que l’on trouvera en genéral plutôt des scripts de migration.
C’est là que ça devient intéressant : si SQLC est capable de générer nos modèles via du SQL, il peut du coup le fait via des scripts de migration directement

Voici les scripts que j’ai réellement utilisé pour obtenir le même résultat :

v0001.sql

1
2
3
4
5
6
7
8
9


-- +migrate Up
CREATE TABLE author (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    bio TEXT
);

-- +migrate Down
DROP TABLE author;

v0002.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- +migrate Up
CREATE TABLE book (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    publication_date DATE NOT NULL,
    author_id INT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES author(id)
);

-- +migrate Down
DROP TABLE book;

v0003.sql

1
2
3
4
5
6
7


-- +migrate Up
ALTER TABLE book DROP CONSTRAINT book_author_id_fkey;
ALTER TABLE book ADD CONSTRAINT book_author_id_fkey FOREIGN KEY (author_id) REFERENCES author(id) ON DELETE CASCADE;

-- +migrate Down
ALTER TABLE book DROP CONSTRAINT book_author_id_fkey;
ALTER TABLE book ADD CONSTRAINT book_author_id_fkey FOREIGN KEY (author_id) REFERENCES author(id);

Chez nous, la gestion des bases de données est effectuée par une équipe dédiée. Ils ont leurs propres repos Git, et nous faisons des PR chez eux pour faire évoluer nos schémas.
Grâce à SQLC, nous pouvons maintenant ajouter directement ces repos via des Git submodules afin de faire en sorte que SQLC aille directement chercher les scripts de ces repos afin de générer nos modèles.
Avant, nous étions obligés de reproduire les modèles de données au sein de notre code, avec les erreurs humaines que ça implique.

Cette méthode permet d’obtenir un lien réel et fiable entre le code de nos microservices et celui des migrations de base de données.
L’équipe en charge des bases de données devient ainsi la réelle source de vérité.

À titre d’exemple, j’ai reproduit ce proof-of-concept à la maison : https://github.com/sebferrer/poc-sqlc ;
et j’ai stocké mes scripts de migration dans un repo dédié : https://github.com/sebferrer/poc-sqlc-db

Faire du Mock

J’aimerais parler d’un autre avantage qui m’a marqué. Le fait de générer son code via SQLC simplifie grandement le mock, et ce pour une bonne raison : c’est nous qui avons écrit le script SQL, donc nous savons exactement à quoi nous attendre.

Cela peut paraître anodin, mais rappelez-vous que si via un ORM nous n’avons aucun contrôle sur le code SQL qui est réellement généré derrière, ça peut rendre les mocks particulièrement fastidieux à écrire.
Je veux mocker la requête SELECT générée derrière mon Get, je dois m’attendre à quoi du coup ? Quelle est la requête exacte que je dois intercepter ?

Prenons l’exemple de GORM :

1

mock.ExpectQuery(regexp.QuoteMeta(`SELECT * FROM "author" WHERE "author"."id" = $1 ORDER BY "author"."id" LIMIT $2`))

Ça fonctionne, c’est cool. Mais il a fallu que je comprenne un minimum comment fonctionne GORM pour savoir que c’est spécifiquement cette requête qu’on doit attendre si je veux faire un mock de ma fonction Get.

Est-ce que mon ORM génère les mots-clés en lowercase ou uppercase ? Est-ce qu’il utilise des quotes, double-quotes ? Est-ce qu’il génère systématiquement le nom de la table devant la colonne associée ? Etc…
Répondre à ces questions implique de bien lire la documentation, voire de carrément aller dans le code source de l’ORM. On peut également se servir des messages d’erreurs de la librairie de mock SQL qu’on utilise, mais il faut reconnaître que ça demande du bricolage.
Et ce, pour chacune des requêtes SQL impliquée dans vos mocks.

Une fois passé à SQLC, le mock ressemblera à ça :

1
2
3


mock.ExpectQuery(regexp.QuoteMeta(`INSERT INTO author (email, bio)
                                   VALUES ($1, $2)
                                   RETURNING id`))

C’est-à-dire exactement la requête que j’ai moi-même écrite à la base (ou presque, cf. l’exemple du SELECT *, mais la requête générée est directement accesible).

Quand on doit mocker toute une batterie de requêtes SQL pour nos tests unitaires, j’ai découvert à quel point c’est plus pratique de n’avoir qu’un simple copié-collé de mes requêtes à faire.
Selon moi, une solution comme SQLC donne une réelle valeur ajoutée à l’utilisation de librairies de mock SQL.

Pour finir, si vous voulez en savoir un peu plus, je vous laisse sur quelques dernières notes comprenant certaines subtilités bonnes à connaître que j’ai remarqué sur SQLC.

Quelques subtilités

Structures de paramètres

SQLC génère des structures Go pour encapsuler les paramètres des requêtes SQL, mais uniquement lorsqu’il y a plus d’un paramètre. Si la requête SQL n’a qu’un seul paramètre, SQLC n’utilise pas de structure et passe directement la valeur correspondante à la fonction.

On a pu remarquer dans l’exemple plus haut qu’une structure a été générée pour pourquoi passer les paramètres nécessaires, afin de pouvoir la passer à la fonction elle aussi générée :

1
2
3
4
5
6


type createAuthorParams struct {
    Email string
    Bio   sql.NullString
}

func (q *Queries) createAuthor(ctx context.Context, arg createAuthorParams) (int32, error)

Ça aurait été la même chose pour la requête SELECT si elle avait eu besoin de plus d’un seul paramètre.
En revanche, si un seul paramètre est nécessaire à la requête, SQLC ne s’embêtera pas à créer une structure pour rien :

1
2
3


-- name: deleteAuthor :exec
DELETE FROM author
WHERE id = $1

1
2
3
4


func (q *Queries) deleteAuthor(ctx context.Context, id int32) error {
    _, err := q.db.ExecContext(ctx, deleteAuthor, id)
    return err
}

Structure de retour

On peut également se demander, comment SQLC gère le type de retour pour renvoyer les données sélectionnées ? C’est assez simple.

Si le SELECT comporte toutes les colonnes de la table, comme un SELECT *, il utilisera directement le type généré pour définir le modèle de données.

1
2
3
4


-- name: getAuthor :one
SELECT *
FROM author
WHERE id = $1

1

func (q *Queries) getAuthor(ctx context.Context, id int32) (Author, error)

Sinon, il génère une structure intermédiaire, nous laissant le libre choix de comment le gérer.

1
2
3
4


-- name: getAuthor :one
SELECT id, email
FROM author
WHERE id = $1;

1
2
3
4
5
6


type getAuthorRow struct {
	ID    int32
	Email string
}

func (q *Queries) getAuthor(ctx context.Context, id int32) (getAuthorRow, error)

Autres subtilités en vrac

Les champs NULL en SQL sont représentés avec sql.NullXxx en Go : Ça permet de gérer les valeurs NULL sans erreur d’exécution.
SQLC ne génère pas de transactions automatiquement : Il faut gérer explicitement BEGIN et COMMIT dans des fonctions Go si on veut exécuter plusieurs requêtes dans une transaction.
Les requêtes INSERT ... RETURNING permettent d’obtenir l’ID d’un nouvel enregistrement directement : SQLC les traduit en QueryRowContext et utilise Scan pour récupérer la valeur retournée.

A noter également que SQLC ne propose pas que de la génération de code utilisant la librairie standars database/sql mais propose également pgx/v4 et pgx/v5.

Conclusion

Je pense avoir à peu près fait le tour de ce que je voulais vous présenter.
J’espère avoir pu transmettre la raison pour laquelle je me suis intéressé à cette approche un peu différente de ce que beaucoup d’entre-nous utilisent couramment.

En conclusion, les ORMs comme GORM offrent une abstraction robuste, facilite les migrations et propose une interface CRUD intuitive, mais nécessite un investissement en apprentissage (bien que léger) et peut introduire une surcharge de performance, notamment avec de grandes bases de données et des opérations complexes.

Le SQL pur, de son côté, offre plus de contrôle sur les requêtes et peut être plus efficace pour les grandes bases de données, mais il demande une bonne maîtrise du SQL et implique une implémentation plus longue dans le code Go.

Je dirais donc : Si la simplicité et la rapidité de développement sont prioritaires, un ORM comme GORM est une bonne option. Si l’optimisation et le contrôle précis des requêtes vous sont essentiels, alors je préconiserais le SQL pur.

L’idée n’est pas de dire qu’une façon de faire est mieux qu’une autre.
J’adore les ORM et je continue d’en utiliser dans beaucoup de mes projets, mais j’avais envie de vous faire découvrir ce principe qui a été l’un de mes coups de coeur de l’année.

Liens utiles

SQLC

GORM

https://github.com/go-gorm/gorm

Comparaison / Benchmark

Mon proof-of-concept

Un Grand merci à Gab qui m’a fait découvrir cette techno et qui m’a donné envie de m’y intéresser !