Depuis que j'ai commencé à faire du backend, j'ai retenu deux points concernant les base de données :

• PostgreSQL est presque toujours un bon choix par défaut pour commencer
• MongoDB est presque toujours un mauvais choix, pour commencer ou non

Cette année, j'ai eu l'occasion de sortir de ma zone de confort et manger du MySQL tous les jours. Et Postgres, c'est quand même pas mal.

Les enums

Imaginons que vous ayez un champ pour lequel les valeurs sont limitées et connues.

Une première solution est d'utiliser une colonne de type text (ou varchar) et vous déplacez la contrainte qu'est l'enum côté applicatif. Logiquement, le webservice est le seul point d'entrée pour écrire dans la base. Sauf quand vous bossez sur une migration un peu délicate, ou quand, par le plus grand des mystères cosmiques, vous avez un bug qui vous oblige à corriger la donnée à la main.

Avoir cette validation directement gérée par la base de donnée est donc un petit plus pour se prémunir de fâcheuses typos. S'ajoute à ça un potentiel gain de place en terme de stockage, j'imagine.

Prenons un cas simple inspiré de la documentation de MySQL :

CREATE TABLE shirts (
    name VARCHAR(40) NOT NULL,
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large') NOT NULL
);

Avec Postgres, on a une définition similaire à quelques détails près :

CREATE TYPE shirt_size AS ENUM ('x-small', 'small', 'medium', 'large', 'x-large');

CREATE TABLE shirts (
    name text,
    size shirt_size
);

Tout fonctionne, tout le monde est heureux. Jusqu'au jour où il faut ajouter une ou plusieurs tailles de T-shirt.

Depuis Postgres 9.1, la modification peut se faire plutôt simplement :

ALTER TYPE shirt_size ADD VALUE 'xx-small' BEFORE 'x-small';
ALTER TYPE shirt_size ADD VALUE 'xx-large' AFTER 'x-large';

Et maintenant, la même avec MySQL :

ALTER TABLE shirts
    MODIFY size ENUM('xx-small', 'x-small', 'small', 'medium', 'large', 'x-large', 'xx-large');

En 2023, même avec la dernière version on est donc obligé de se refaire toute la série de valeur alors qu'on souhaite juste ajouter deux valeurs.

Pire encore, on lock la table le temps de passer toute la migration et comme on redéfinit toute l'enum, on est obligé de revalider chaque ligne avec le nouveau schéma. En l'état, quasi-impossible de faire du zero-downtime quand on veut passer ce genre de migration.

Seul levier sur lequel on peut jouer est le type d'algo à utiliser pour le DDL : bon courage.

Sur le papier, c'est déjà un bon début mais certaines contraintes font que ça ne fonctionnera pas à tout les coups, et on se retrouve à essayer d'anticiper le comportement de MySQL en tombant souvent à côté de la plaque :

Modifying the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values may be performed instantly or in place, as long as the storage size of the data type does not change. For example, adding a member to a SET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this requires a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.

En plus d'un an, on n'a toujours pas trouvé une manière pérenne de jouer ce genre de migration sans se taper une plâtrée d'erreur dûes au lock sur la table impactée.

Conclusion : le coût opérationnel d'utiliser des enums avec MySQL, contrairement à Postgres, est tellement important qu'il vaut mieux s'abstenir et utiliser un simple champ texte.

Strict ou pas strict, tel est le WTF

Pour quelqu'un comme moi qui aime le typage fort et statique, un des avantages à utiliser une base de données SQL est dans l'auto-documentation qu'un schéma de données peut fournir.

En reprenant l'exemple précédent :

mysql> DESCRIBE shirts;

+-------+----------------------------------------------------+------+-----+---------+-------+
| Field | Type                                               | Null | Key | Default | Extra |
+-------+----------------------------------------------------+------+-----+---------+-------+
| name  | varchar(40)                                        | YES  |     | NULL    |       |
| size  | enum('x-small','small','medium','large','x-large') | YES  |     | NULL    |       |
+-------+----------------------------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Et un jour de grand soleil, c'est le drame : alerte en prod', le bout de code en charge de désérialiser les lignes de cette table échoue pour une raison surprenante.

Dans les grandes lignes, le code vous dira gentiment :

Euh, t'es gentil mais qu'est-ce que je fais d'une 'size' qui est une chaîne vide ?

En vérifiant rapidement en base, on constate bien le problème :

mysql> select distinct size from shirts;
+-------+
| size  |
+-------+
| large |
|       |
+-------+
2 rows in set (0.00 sec)

Une petite recherche sur votre moteur préféré, et on commence à comprendre quel est le souci.

Tiré de la documentation de MySQL :

Empty or NULL Enumeration Values

An enumeration value can also be the empty string ('') or NULL under certain circumstances:

If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numeric value 0. See Index Values for Enumeration Literals for details about the numeric indexes for the enumeration values.

If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.

If an ENUM column is declared to permit NULL, the NULL value is a valid value for the column, and the default value is NULL. If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values. 

Donc si on résume, selon la configuration de la base le comportement n'est pas le même concernant le fait d'insérer des valeurs d'enum invalides:

• par défaut, avec le mode strict activé, l'opération échoue et on rollback
• si le mode strict est désactivé, MySQL insére une chaîne vide et l'erreur tombe dans les limbes de l'oubli pour venir nous hanter plus tard

Pour vérifier ce petit bout de config :

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

Lors du setup de ma base de test, la config par défaut ressemblait à :

mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Ici, c'est l'absence de la valeur STRICT_TRANS_TABLES qui est responsable de ce comportement que certains qualifieraient de foireux.

Et quand je demande au reste du plateau pourquoi la base de prod a une configuration aussi merdique (pas en ces termes évidemment, derrière chaque décision un peu stupide se cache une motivation raisonnable) :

Question bonus : que se passe-t-il lorsqu'on réactive le mode strict avec des valeurs invalides ?

set sql_mode ='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Réponse : rien. MySQL garde les chaînes vides, ce qui paraît avec un peu de recul, une sage décision.

La résolution du problème a posteriori se fait en deux étapes :

• changer la config pour celle par défaut, et donc un peu plus strict
• modifier les lignes impactées, ce qui a posteriori est casse-gueule étant donné qu'on vient juste de perdre de la donnée

Un mot pour la fin ?

Tabouret. T-A-B-O-U-R-E-T. Tabouret.