SQLite en production : une plateforme entière dans un seul fichierSQLite in production: a whole platform in a single file

Le réflexe, pour une plateforme qui gère des commandes, des factures et des versements, est de poser un serveur de base de données à côté. djplays n’en a pas. Toutes les données tiennent dans un fichier SQLite, placé hors de la racine web, et ce fichier est la base de production, pas une maquette.The reflex, for a platform handling orders, invoices and payouts, is to stand up a database server alongside. djplays does not have one. All the data sits in a SQLite file, placed outside the web root, and that file is the production database, not a prototype.

Les chiffres aident à situer la chose. La base pèse 17 Mo et contient 35 tables et 61 index. Autour d’elle, le dossier de données complet fait 112 Mo, et les sauvegardes 52 Mo : la base est donc la plus petite partie de l’ensemble, loin derrière les images et les fichiers déposés. Ce rapport est utile à garder en tête quand on parle de taille.The figures help put it in perspective. The database weighs 17 MB and contains 35 tables and 61 indexes. Around it, the complete data folder comes to 112 MB, and the backups to 52 MB: the database is therefore the smallest part of the whole, far behind the images and uploaded files. That ratio is worth keeping in mind when size comes up.

Le fichier hors de la racine webThe file outside the web root

C’est la première décision, et c’est la seule qui ne se discute pas. Une base SQLite est un fichier : si ce fichier se trouve dans l’arborescence servie par le serveur web, il existe potentiellement une adresse qui le télécharge, et un seul téléchargement emporte alors la totalité des données. Hors de la racine, aucun chemin HTTP ne mène au fichier, quelle que soit l’erreur de configuration commise plus tard.This is the first decision, and the only one that is not up for debate. A SQLite database is a file: if that file sits inside the tree served by the web server, there is potentially a URL that downloads it, and a single download then carries off all the data. Outside the root, no HTTP path leads to the file, whatever configuration mistake gets made later.

Le prix de cette protection est modeste mais réel. Il faut que le processus qui sert les pages puisse lire et écrire ce fichier, donc une question de droits à régler une fois, et un chemin à tenir dans la configuration. Là où un serveur de base de données s’atteint par un hôte, un port et des identifiants, SQLite s’atteint par un chemin de fichier, et ce chemin est devenu un élément de configuration comme un autre.The price of that protection is modest but real. The process serving the pages has to be able to read and write that file, so there is a permissions question to settle once, and a path to maintain in the configuration. Where a database server is reached through a host, a port and credentials, SQLite is reached through a file path, and that path has become a configuration item like any other.

Un schéma créé au premier appelA schema created on first call

Il n’y a pas de fichier SQL à importer, pas d’étape d’installation, pas de procédure à suivre avant de pouvoir utiliser le site. Au premier appel, le code crée le schéma s’il n’existe pas. La conséquence est directe : un environnement neuf se fabrique en exécutant le site, et non en reproduisant une suite de commandes dont on espère qu’elle est à jour.There is no SQL file to import, no installation step, no procedure to follow before the site can be used. On the first call, the code creates the schema if it does not exist. The consequence is direct: a fresh environment is built by running the site, not by reproducing a sequence of commands you hope is up to date.

Ce que ça change vraiment, c’est la place de la vérité. Le schéma de référence n’est pas un fichier exporté un jour depuis la production, puis vaguement suivi : c’est le code. Il n’y a donc pas de dérive possible entre les deux, ni de question du type savoir quelle version du dump correspond à quelle version du site. La définition des tables est versionnée avec le reste, et elle se lit.What that really changes is where the truth lives. The reference schema is not a file exported from production one day and then loosely maintained: it is the code. There can therefore be no drift between the two, and no question along the lines of which version of the dump matches which version of the site. The table definitions are versioned with everything else, and they can be read.

Le même mouvement déplace certaines règles de gestion dans la base elle-même. La numérotation des factures, par exemple, est continue par année, et la table porte une contrainte d’unicité sur le couple année et séquence. Ce n’est plus une précaution que du code doit penser à prendre à chaque écriture : c’est une règle que la base refuse de laisser violer, y compris par un script en ligne de commande distrait.The same move pushes some business rules into the database itself. Invoice numbering, for instance, is continuous per year, and the table carries a uniqueness constraint on the year-and-sequence pair. It is no longer a precaution the code has to remember on every write: it is a rule the database refuses to let anyone break, including a careless command-line script.

Le versionnement par PRAGMA user_versionVersioning through PRAGMA user_version

SQLite réserve dans l’entête du fichier un entier que l’application est libre d’utiliser, et qu’on lit ou écrit par PRAGMA user_version. C’est tout le mécanisme : un nombre, rangé dans la base, qui dit quel état du schéma ce fichier porte. Pas de table de suivi des migrations, pas d’outil à installer, pas de convention à respecter en plus.SQLite reserves, in the file header, an integer the application is free to use, read or written through PRAGMA user_version. That is the whole mechanism: one number, stored in the database, saying which schema state this file carries. No migration tracking table, no tool to install, no extra convention to respect.

Sur djplays, cet entier vaut 33 aujourd’hui, et le code contient 35 étapes de migration. Au démarrage, le code lit la version du fichier, applique dans l’ordre les étapes qui lui manquent, puis inscrit la nouvelle version. Si le fichier est à jour, il ne se passe rien, et c’est le cas courant à chaque appel de page.On djplays that integer is 33 today, and the code contains 35 migration steps. At startup, the code reads the file version, applies the steps it is missing in order, then writes the new version. If the file is up to date, nothing happens, and that is the common case on every page call.

• Lire la version inscrite dans le fichier, qui vaut zéro sur une base qui vient de naître.Read the version written in the file, which is zero on a database that has just been created.
• Appliquer dans l’ordre, et seulement elles, les étapes situées au-dessus de cette version.Apply, in order and only them, the steps above that version.
• Inscrire la nouvelle version une fois les étapes passées.Write the new version once the steps have run.
• Ne rien faire du tout quand le fichier est déjà à jour, ce qui est le cas général.Do nothing at all when the file is already up to date, which is the general case.

Une migration qui se rejoue sans dégâtA migration that replays without damage

C’est le point sur lequel j’ai le plus appris, et c’est le seul que je défendrais quelle que soit la base employée. Une migration doit pouvoir être rejouée sans rien casser. Pas parce que c’est élégant, mais parce que la situation arrive : une étape interrompue en cours de route, un fichier restauré depuis une sauvegarde plus ancienne, un environnement de test reparti d’une copie d’hier.This is the point I have learned the most from, and the only one I would defend whatever database is in use. A migration must be replayable without breaking anything. Not because it is elegant, but because the situation does arise: a step interrupted part way through, a file restored from an older backup, a test environment restarted from a copy made yesterday.

Il y a une raison supplémentaire, propre à ce projet : le code du schéma est atteint par plusieurs chemins. Les pages web y passent, et les huit tâches planifiées en ligne de commande aussi, puisqu’elles partagent le même socle. N’importe laquelle peut donc se trouver être celle qui déclenche une migration, à n’importe quelle heure. Une étape qui ne supporterait pas d’être tentée deux fois serait une bombe à retardement.There is an additional reason specific to this project: the schema code is reached by several paths. The web pages go through it, and so do the eight scheduled command-line jobs, since they share the same base. Any one of them may therefore turn out to be the one triggering a migration, at any hour. A step that could not stand being attempted twice would be a time bomb.

En pratique, cela pousse vers des migrations additives et vérifiables : on ajoute une colonne, une table, un index, et on vérifie d’abord qu’ils ne sont pas déjà là. L’ajout de la colonne qui stocke le résultat de la lecture assistée des preuves d’audience, arrivé à l’étape trente-deux, est de cette nature : une colonne JSON de plus sur une table existante, rien à déplacer, rien à reconstruire, et aucune donnée en jeu si l’étape est tentée une seconde fois.In practice, that pushes towards additive and checkable migrations: you add a column, a table, an index, and you first check they are not already there. Adding the column that stores the result of the assisted reading of audience evidence, which landed at step thirty-two, is of that kind: one more JSON column on an existing table, nothing to move, nothing to rebuild, and no data at risk if the step is attempted a second time.

Une migration n’est pas un script qu’on exécute une fois : c’est un bout de code qui sera exécuté dans des conditions qu’on n’a pas prévues, sur un fichier dont on ne connaît pas l’histoire. Elle doit survivre à ça.A migration is not a script you run once: it is a piece of code that will be run in conditions you did not anticipate, on a file whose history you do not know. It has to survive that.

La concurrence en écriture, la vraie limiteWrite concurrency, the real limit

C’est l’objection la plus fréquente, et elle est fondée. SQLite sérialise les écritures : à un instant donné, une seule écriture avance, et les autres attendent. Un serveur de base de données fait mieux, c’est une partie de son métier. Balayer cette objection serait malhonnête, parce qu’elle désigne exactement l’endroit où ce choix cède.This is the most frequent objection, and it is well founded. SQLite serialises writes: at any given instant, one write proceeds and the others wait. A database server does better, and that is part of its job. Brushing that objection aside would be dishonest, because it points exactly at the place where this choice gives way.

Ce qui rend la limite acceptable ici, c’est la forme de la charge. L’essentiel de ce que le site sert est fait pour être lu : les pages de DJs, les classements, les pages de badge publiques. Les écritures sont courtes et ponctuelles : un dépôt de titre, une réponse, une commande, une facture, un passage de tâche planifiée. Les tâches sont d’ailleurs réparties dans l’heure et dans la nuit, à des minutes différentes, ce qui évite qu’elles écrivent toutes au même moment.What makes the limit acceptable here is the shape of the load. Most of what the site serves is meant to be read: DJ pages, charts, public badge pages. Writes are short and occasional: a track submission, a reply, an order, an invoice, a scheduled job run. The jobs are in fact spread through the hour and through the night, at different minutes, which keeps them from all writing at once.

Le point de bascule est identifiable, et c’est important de le dire avant d’y être. Le jour où les écritures simultanées deviennent assez nombreuses pour que l’attente se voie dans les temps de réponse, ou le jour où il faudrait servir le site depuis deux machines, un fichier sur un système de fichiers n’est plus la bonne réponse. SQLite ne se partage pas entre serveurs, et il ne faut pas essayer.The tipping point is identifiable, and it matters to say so before reaching it. The day simultaneous writes become numerous enough for the waiting to show in response times, or the day the site has to be served from two machines, a file on a filesystem is no longer the right answer. SQLite is not shared between servers, and you should not try.

La sauvegarde, et ce qu'un seul fichier changeBackups, and what a single file changes

Une sauvegarde tourne chaque nuit, à 4 h 17, par une tâche planifiée dédiée, et l’ensemble des sauvegardes représente 52 Mo. Il existe aussi un script de restauration, ce qui compte davantage que le script de sauvegarde : une sauvegarde dont le chemin de retour n’est pas écrit est une intention, pas un filet. Le jour où il faut restaurer, on ne veut pas improviser.A backup runs every night at 4:17 am, through a dedicated scheduled job, and the backups together come to 52 MB. There is also a restore script, which matters more than the backup script: a backup whose return path is not written down is an intention, not a safety net. The day a restore is needed, you do not want to improvise.

Le fait que la base soit un seul fichier simplifie réellement cette partie. Il n’y a pas de service à interroger, pas d’export à produire dans un format intermédiaire, pas de version de serveur à faire correspondre entre la machine d’origine et celle de destination. Une copie cohérente du fichier est une base complète, immédiatement utilisable, y compris sur un poste de travail.The fact that the database is a single file genuinely simplifies this part. There is no service to query, no export to produce in an intermediate format, no server version to match between the source machine and the destination. A consistent copy of the file is a complete database, immediately usable, including on a desktop machine.

Le mot important dans la phrase précédente est cohérente. Copier un fichier de base pendant qu’une écriture est en cours ne donne pas forcément une base valide, et c’est là que se juge une sauvegarde : pas au fait qu’elle existe, mais au fait qu’elle produise un fichier exploitable. L’autre limite est le rythme. Une sauvegarde par nuit veut dire, dans le pire des cas, une journée de données à reconstituer autrement, et c’est une décision assumée plutôt qu’un oubli.The important word in that last sentence is consistent. Copying a database file while a write is in progress does not necessarily yield a valid database, and that is where a backup is judged: not on the fact that it exists, but on the fact that it produces a usable file. The other limit is the rhythm. One backup per night means, in the worst case, a day of data to reconstruct some other way, and that is a decision taken deliberately rather than an oversight.

La montée en charge, et le moment de changerScaling up, and the moment to change

Sur la taille, l’inquiétude est largement infondée. Dix-sept mégaoctets pour 35 tables, c’est petit, et SQLite tient des bases de plusieurs ordres de grandeur au-dessus sans broncher. Les 61 index comptent plus que le volume : c’est par eux que les lectures restent rapides quand les tables grossissent, et c’est le travail à faire quelle que soit la base.On size, the worry is largely unfounded. Seventeen megabytes for 35 tables is small, and SQLite handles databases several orders of magnitude larger without flinching. The 61 indexes matter more than the volume: they are what keeps reads fast as the tables grow, and that work has to be done whatever the database.

Les signaux qui diraient qu’il faut changer sont donc ailleurs, et je préfère les nommer maintenant : une attente d’écriture visible dans les temps de réponse, un besoin de servir depuis plusieurs machines, un besoin de bascule automatique en cas de panne, ou une charge d’analyse qui vient concurrencer l’application sur la même base. Aucun de ces signaux n’est une question de mégaoctets.The signals that would say it is time to change are therefore elsewhere, and I would rather name them now: write waiting visible in response times, a need to serve from several machines, a need for automatic failover, or an analytics workload competing with the application on the same database. None of those signals is a question of megabytes.

Et si ce jour arrive, le travail est réel mais pas infini. Le schéma est du SQL, les tables et les contraintes se transposent, et la version du schéma est connue à l’entier près, ce qui évite de deviner d’où l’on part. Il faudra en revanche reprendre une à une les requêtes qui s’appuient sur un comportement propre à SQLite, et revoir le modèle d’accès, parce qu’un serveur distant ne se paie pas au même prix qu’un appel de fonction local.And if that day comes, the work is real but not infinite. The schema is SQL, the tables and constraints transpose, and the schema version is known to the exact integer, which saves guessing where you are starting from. What will be needed, on the other hand, is going through one by one the queries relying on behaviour specific to SQLite, and reviewing the access pattern, because a remote server does not cost the same as a local function call.

C’est tout l’intérêt d’avoir écrit cette limite quelque part. SQLite en production n’est pas un pari sur le fait que la charge n’augmentera jamais : c’est le choix de l’outil qui correspond à la charge d’aujourd’hui, avec le critère de sortie posé d’avance. Beaucoup de projets installent un serveur de base de données pour une charge qu’ils n’auront pas, et paient cette prudence en complexité tous les jours.That is the whole point of having written the limit down somewhere. SQLite in production is not a bet that the load will never grow: it is choosing the tool that matches the load there is today, with the exit criterion set in advance. Plenty of projects install a database server for a load they will never see, and pay for that caution in complexity every single day.

Le projet derrière cette noteThe project behind this note

Tout est parti de djplays, la place de marché. La fiche détaille le contexte, ce qui a été construit et ce que le client en a tiré.It all started with djplays, la place de marché. The project page covers the context, what was built and what the client got out of it.