- Présentation d’une méthode qui exploite les fonctions JSON de SQLite pour stocker le document JSON brut tel quel, puis extraire et indexer les champs nécessaires via des colonnes générées virtuelles (virtual generated columns)
- Avec la fonction
json_extract, les données internes au JSON peuvent être manipulées comme des colonnes, avec des requêtes exécutées à la vitesse des index B-tree
- Chaque fois qu’un nouveau modèle de requête est nécessaire, il est possible d’étendre le système en ajoutant des colonnes et des index sans migration de données
- Cette approche permet d’obtenir à la fois la souplesse des données sans schéma et les performances d’une base de données relationnelle
- Elle est mise en avant comme un pattern pratique offrant une structure simple et de hautes performances pour les développeurs qui utilisent SQLite
Combiner SQLite et les fonctionnalités JSON
- SQLite prend en charge les fonctions et opérateurs JSON, ce qui permet de stocker et de manipuler directement des données JSON
- Le document JSON peut être conservé tel quel dans une colonne, et seules les informations nécessaires sont extraites sous forme de colonnes virtuelles
- Cette méthode permet de traiter les données avec souplesse, sans définition préalable de schéma
- L’équipe DB Pro a utilisé SQLite de manière intensive ces derniers mois et a validé ces capacités en conditions réelles
- Lorsqu’il est correctement configuré, SQLite peut être utilisé de façon fiable en production
Colonnes générées virtuelles (Generated Columns)
json_extract permet de définir certaines valeurs du JSON comme colonnes générées virtuelles
- Ces colonnes ne stockent pas réellement les données : elles sont calculées au moment de la requête et utilisables immédiatement
- Aucun backfill ni duplication de données n’est nécessaire
- Il est par exemple possible d’extraire un champ précis d’un document JSON pour le manipuler comme une colonne classique
Ajout d’index et gains de performance
- En ajoutant un index sur une colonne virtuelle, les données JSON peuvent elles aussi être recherchées à la vitesse des index B-tree, comme des colonnes ordinaires
- Une colonne virtuelle indexée offre les mêmes performances qu’une colonne d’une base de données relationnelle
- Cette approche permet d’effectuer des recherches rapides même lorsque les données JSON deviennent volumineuses
Ajouter de nouveaux modèles de requête
- Si, plus tard, il devient nécessaire de rechercher sur un nouveau champ, il suffit d’ajouter une nouvelle colonne virtuelle et un nouvel index
- Exemple : extraire le champ
user_id et créer un index dessus
- Il n’est pas nécessaire de modifier les lignes existantes ni d’effectuer une migration
- Cela permet d’obtenir une extensibilité immédiate des requêtes sans changer la structure des données
Avantages et portée du pattern
- Ce pattern combine la souplesse du stockage JSON sans schéma et les performances d’indexation d’une base relationnelle
- Il n’est pas nécessaire de décider de la stratégie d’indexation dès la phase de conception initiale
- Il est possible d’optimiser au moment voulu en ajoutant colonnes et index selon les besoins
- Il est présenté aux développeurs utilisant SQLite comme une méthode de traitement des données à la fois simple et puissante
- DB Pro annonce d’autres articles à venir sur diverses fonctionnalités de SQLite
Aucun commentaire pour le moment.