Les fichiers SQLite expliqués : structure, cas d'utilisation et limites clés

Un fichier SQLite est un fichier disque unique qui contient une base de données relationnelle entière — tables, index et schéma compris. La propre documentation de SQLite l'appelle le « fichier de base de données principal » et note que « l'état complet d'une base de données SQLite y est généralement contenu ». Le mot généralement a toute son importance dans cette phrase.

Si l'on vous a déjà remis un fichier .db, .sqlite ou .sqlite3 et que vous vous êtes demandé si vous aviez bien reçu toutes les données, c'est le format qu'il vous faut comprendre correctement.

Ce qu'est réellement un fichier SQLite

SQLite se décrit comme « une bibliothèque intégrée qui implémente un moteur de base de données SQL transactionnel, autonome, sans serveur et sans configuration ». La même page indique que SQLite « n'a pas de processus serveur distinct ». Une application lie la bibliothèque et lit le fichier.

Deux conséquences en découlent. Premièrement, la base de données voyage sous la forme d'un seul artefact, c'est pourquoi tant d'applications distribuent leurs données de cette manière. Deuxièmement, le format doit être extrêmement stable, car ces fichiers survivent aux logiciels qui les ont écrits. SQLite cite un « format de fichier stable et durable » parmi ses caractéristiques principales. Il indique également que le code est dans le domaine public, « libre d'utilisation pour n'importe quel usage, commercial ou privé ».

Il est facile de sous-estimer son ampleur. La propre page de présentation de SQLite indique qu'elle « est la base de données la plus largement déployée au monde, avec plus d'applications que nous ne pouvons en compter ».

Ce que contient le fichier

L'en-tête de 100 octets

Les premiers octets identifient le format. À l'offset 0, le fichier contient une chaîne d'en-tête de 16 octets : SQLite format 3\000. C'est grâce à cette signature que les outils reconnaissent le fichier, quelle que soit son extension.

Le champ suivant est plus important qu'il n'y paraît. À l'offset 16 se trouve un entier de 2 octets contenant « la taille de page de la base de données en octets ». La documentation précise qu'elle « doit être une puissance de deux comprise entre 512 et 32768 inclus, ou la valeur 1 représentant une taille de page de 65536 ». Tous les champs multi-octets de l'en-tête sont stockés avec l'octet le plus significatif en premier.

Deux autres octets suivent aux offsets 18 and 19 : la version d'écriture et la version de lecture du format de fichier. La documentation note que la valeur est « 1 pour l'ancien format ; 2 pour le WAL ».

Des pages, pas des lignes

Sous l'en-tête, le fichier est une pile de pages de taille fixe. La spécification est explicite : « Le fichier de base de données principal se compose d'une ou plusieurs pages. La taille d'une page est une puissance de deux comprise entre 512 et 65536 inclus. Toutes les pages d'une même base de données ont la même taille. »

Les pages sont numérotées à partir de 1, et le numéro de page maximum est 4,294,967,294. Les tables et les index résident à l'intérieur de ces pages sous forme de structures en arbre B, c'est pourquoi un éditeur de texte ne vous montre pas grand-chose.

Les fichiers annexes dont personne ne parle

C'est la partie qui piège souvent les gens. La documentation indique que l'état complet se trouve « généralement » dans un seul fichier. Elle nomme ensuite l'exception. Lors d'une transaction, SQLite « stocke des informations supplémentaires dans un second fichier appelé le 'journal de rollback' ». En mode WAL, ce second fichier est un journal d'écriture anticipée.

Ainsi, une copie effectuée alors que l'application est en cours d'écriture peut ne pas contenir les données validées qui se trouvent encore dans le fichier annexe. Si un collègue vous envoie un fichier .db et rien d'autre, et que les chiffres semblent légèrement obsolètes, c'est la première chose à vérifier.

Comment ouvrir un fichier SQLite

Il existe trois méthodes, et la bonne dépend de ce que vous avez l'intention de faire ensuite.

Le lire avec un visualiseur. Les visualiseurs SQLite de bureau et sur navigateur ouvrent le fichier, listent les tables et vous permettent de parcourir les lignes d'un clic. C'est le moyen le plus rapide de répondre à la question « qu'y a-t-il là-dedans », et cela suffit généralement pour un premier coup d'œil.

L'interroger avec la ligne de commande ou une bibliothèque. Le shell sqlite3 et les liaisons de la bibliothèque standard en Python, Node et la plupart des autres langages lisent directement le format. C'est la voie à suivre lorsque vous connaissez déjà le schéma et que vous recherchez un chiffre précis.

Exporter une table et l'analyser ailleurs. Exportez une table au format CSV et importez-la dans l'outil que votre équipe utilise déjà. Vous perdez les relations entre les tables, ce qui est précisément la chose que le format protégeait. Exportez le résultat de la jointure plutôt que les tables brutes dans la mesure du possible.

Pourquoi un outil peut indiquer que le fichier n'est pas une base de données

La spécification explique ce point. Tout fichier valide commence par une chaîne d'en-tête de 16 octets, SQLite format 3\000. Un lecteur qui ouvre un fichier et ne trouve pas cette signature à l'offset 0 n'a pas affaire à une base de données SQLite.

Trois causes courantes expliquent la plupart des cas. Le transfert du fichier a été incomplet, de sorte que l'en-tête est présent mais le reste est tronqué. Le fichier est chiffré ou encapsulé par une application, de sorte que les premiers octets correspondent à autre chose. Ou bien l'extension est trompeuse, et ce que vous avez réellement reçu est un simple export renommé par quelqu'un qui pensait bien faire.

Quelle taille un fichier SQLite peut-il atteindre

Plus grand que ce que l'on imagine généralement. La page des limites de SQLite indique que la taille maximale d'un fichier de base de données est de 4,294,967,294 pages. Avec la taille de page maximale de 65,536 octets, cela représente une taille de base de données maximale d'environ 281 terabytes.

La page est d'une honnêteté rafraîchissante à propos de ce chiffre. Elle note que la limite supérieure « n'est pas testée car les développeurs n'ont pas accès à un matériel capable d'atteindre cette limite ».

Le nombre de lignes se heurte au même mur. Le maximum théorique est de 2^64 lignes dans une table. La documentation souligne que cette limite « est inatteignable car la taille maximale de la base de données de 281 terabytes sera atteinte en premier ».

Pour le travail pratique, la leçon utile à retenir est à l'opposé d'une limite. Si quelqu'un vous remet un fichier .db en vous prévenant qu'il est volumineux, ce n'est presque certainement pas le format qui vous bloquera. La taille de page choisie lors de la création du fichier, et le fait qu'il contienne ou non des index, affecteront l'expérience bien plus que n'importe quel plafond documenté.

Où rencontre-t-on des fichiers SQLite

• Exports d'applications. Les applications de bureau et mobiles stockent souvent l'historique, les paramètres et les journaux de messages dans un fichier SQLite que vous pouvez copier.
• Transferts d'analyses. Les ingénieurs transmettent un instantané sous la forme d'un fichier unique plutôt que d'accorder un accès à la base de données.
• Appareils et télémétrie. Les systèmes embarqués écrivent localement car il n'y a pas de serveur avec lequel communiquer.
• Archives. La stabilité à long terme du format en fait un choix courant pour les ensembles de données qui doivent rester lisibles pendant des années.
• Composants internes des navigateurs et des outils. De nombreux outils locaux conservent leur état de cette manière, c'est pourquoi l'extension apparaît souvent dans les tickets d'assistance.

À quoi servent les fichiers WAL et de journalisation

Vous avez peut-être copié un fichier .db et trouvé un fichier -wal ou -journal à côté. Ce sont les fichiers annexes décrits par la spécification, et c'est en les supprimant que l'on perd des données.

Le journal de rollback est le mécanisme le plus ancien. Avant de modifier une page, SQLite écrit la version originale de cette page dans le journal. Si l'écriture est interrompue, l'original peut être restauré, ce qui permet à une transaction de survivre à un plantage.

Le journal d'écriture anticipée inverse cette disposition. Les modifications vont d'abord dans le journal et le fichier principal est mis à jour plus tard. L'en-tête indique le mode dans lequel se trouve la base de données. La version d'écriture du format de fichier à l'offset 18 est « 1 pour l'ancien format ; 2 pour le WAL ».

La règle pratique découle directement de la phrase concernant l'état complet. Supposons que la base de données soit en mode WAL et que l'on ne vous remette que le fichier principal. Les modifications validées les plus récentes se trouvent peut-être encore dans le journal que vous n'avez pas reçu.

Ainsi, lorsqu'on vous remet un fichier de base de données, posez-vous deux questions. L'application a-t-elle été fermée proprement lors de la copie, et d'autres éléments l'accompagnaient-ils ? Les deux réponses sont généralement oui, et la seule fois où ce n'est pas le cas est celle où les chiffres diffèrent discrètement de la production.

Fichier SQLite vs CSV vs Parquet

	Fichier SQLite	CSV	Parquet
Structure	Plusieurs tables, un seul fichier	Une seule table, un seul fichier	Une seule table, un seul fichier ou dossier
Types	Stockés avec les données	Déduits par le lecteur	Stockés avec les données
Relations	Conservées, via des clés et des index	Perdues	Perdues
Lisible par l'homme	Non	Oui	Non
Conçu pour être interrogé	Oui, avec SQL	Non	Oui, par des moteurs d'analyse
Échec courant	Fichier annexe de journal ou WAL manquant	Devinette du type et du délimiteur	Prise en charge de la chaîne d'outils

Si vous travaillez régulièrement avec ces formats, nos guides explicatifs sur les fichiers Parquet et les fichiers TSV couvrent le même sujet pour ces deux formats.

Pourquoi les équipes choisissent ce format

Rien à exécuter. Comme SQLite « n'a pas de processus serveur distinct », un transfert se résume à une copie de fichier plutôt qu'à un ticket de provisionnement.

Les types survivent au voyage. Une colonne de date arrive sous forme de date. Quiconque a déjà vu un lecteur CSV transformer un identifiant en notation scientifique en comprend tout l'intérêt.

Les relations survivent également. Plusieurs tables liées restent regroupées dans un seul artefact, de sorte que les jointures qui donnaient du sens aux données restent disponibles.

La durabilité est intégrée dès la conception. SQLite cite les transactions « même après une coupure de courant » parmi ses fonctionnalités clés, c'est pourquoi tant de logiciels embarqués s'appuient sur elle.

Les limites à connaître

Un seul fichier, un seul rédacteur à la fois. Le moteur est embarqué plutôt que servi, le modèle de concurrence est donc différent de celui d'une base de données client-serveur. C'est un choix de conception, pas un défaut, mais cela détermine l'usage idéal du fichier.

La taille de page est fixée à la création. Chaque page d'une base de données a la même taille, et cette taille est enregistrée dans l'en-tête. Vous la choisissez une fois pour toutes.

À nouveau la règle du fichier annexe. Toute routine de copie, de sauvegarde ou de téléchargement qui ne récupère que le fichier principal peut passer à côté de ce qui se trouvait dans le journal ou le journal d'écriture anticipée.

Opacité. Un fichier SQLite ne se parcourt pas du regard comme un fichier CSV. Sa lecture nécessite un outil, ce qui constitue précisément le frein qui bloque de nombreuses analyses.

Comment obtenir des réponses à partir d'un fichier SQLite

La méthode traditionnelle consiste à installer un client, ouvrir le fichier, comprendre le schéma et commencer à écrire du SQL. C'est très bien lorsque vous connaissez déjà les tables. C'est lent lorsqu'on vous a remis le fichier ce matin et que la réunion a lieu cet après-midi.

La voie la plus rapide consiste à poser directement la question. Powerdrill Bloom vous permet de travailler avec vos données en langage naturel et renvoie une réponse accompagnée de sa source. La page d'accueil promet que « chaque chiffre est accompagné de la page, de la ligne et de la donnée qui le sous-tend ». À partir de là, le même espace de travail peut générer des graphiques, des feuilles de calcul ou une courte présentation.

Deux pages associées méritent d'être connues si cela fait partie de votre flux de travail habituel. Chat with Database couvre l'approche conversationnelle des données structurées, et Text to SQL couvre le cas où vous souhaitez obtenir la requête elle-même. Si votre transfert arrive plutôt sous la forme d'un export brut, la page de l'assistant CSV AI assistant couvre cette option.

Une chose de plus que l'en-tête vous apprend

Comme la taille de page se trouve à un offset fixe, vous pouvez apprendre des informations utiles sur un fichier avant même de l'ouvrir correctement. Une base de données créée avec une taille de page de 4,096 octets se comporte différemment d'une base créée avec des pages de 65,536 octets. Ce choix a été fait une fois pour toutes, lors de la création du fichier.

Ce n'est pas un chiffre que l'on modifie à la légère par la suite. Il relève plutôt d'une décision de schéma que d'un simple paramètre.

Conclusion

Un fichier SQLite est une base de données entière dans un seul artefact. Il contient une signature de 16 octets, une taille de page enregistrée à l'offset 16, et une pile de pages de taille fixe contenant vos tables et vos index. Il se transporte facilement, conserve ses types et reste lisible pendant des années.

N'oubliez pas la seule mise en garde sur laquelle la spécification insiste. L'état complet se trouve généralement dans ce fichier. Lors d'une transaction, une partie de celui-ci réside dans un journal de rollback ou un journal d'écriture anticipée à ses côtés. Recherchez le fichier annexe avant de faire confiance à la copie.

Lorsque vous disposez du fichier et que vous avez besoin de la réponse plutôt que du schéma, essayez Powerdrill Bloom et posez votre question directement sur les données.

Foire aux questions

Quelle est la différence entre .db, .sqlite et .sqlite3 ?

Rien sur le plan structurel. Tous trois sont des extensions conventionnelles pour le même format, et le véritable identifiant est la chaîne d'en-tête de 16 octets SQLite format 3\000 au début du fichier.

Comment savoir quelle taille de page un fichier SQLite utilise ?

Elle est enregistrée dans l'en-tête. Un entier de 2 octets à l'offset 16 contient la taille de page en octets. Elle doit être une puissance de deux comprise entre 512 et 32768, ou la valeur 1 représentant 65536.

Un fichier SQLite constitue-t-il la base de données complète ?

Généralement, mais pas toujours. La documentation indique que lors d'une transaction, SQLite conserve des informations supplémentaires dans un journal de rollback. En mode WAL, ces informations vont plutôt dans un journal d'écriture anticipée.

Puis-je ouvrir un fichier SQLite dans Excel ?

Pas directement, car le fichier stocke des pages d'arbre B plutôt que des lignes de texte. La méthode courante consiste à exporter d'abord une table au format CSV, ou à utiliser un outil qui lit le format de la base de données et renvoie les résultats.

L'utilisation commerciale de SQLite est-elle gratuite ?

Oui. SQLite indique que son code est dans le domaine public et qu'il est « libre d'utilisation pour n'importe quel usage, commercial ou privé ».