Utiliser SQLite en C# dans une application métier — mode WAL, contrôle d'accès exclusif, prévention de la corruption, et quand choisir EF Core

Dans l’article précédent, « Comment choisir l’emplacement de stockage des données locales d’une application Windows », j’écrivais que SQLite est le premier choix pour stocker des données métier et un historique qui ne cessent de croître. Cette décision reste valable, mais au moment de l’intégrer concrètement, d’autres hésitations apparaissent. Lors des consultations, on entend souvent : « En cherchant SQLite sur NuGet, plusieurs packages apparaissent, et je ne sais pas lequel installer », « Ça fonctionne, mais database is locked apparaît de temps en temps. Je contourne le problème avec des nouvelles tentatives, mais est-ce vraiment la bonne approche ? », ou encore « Est-il acceptable de sauvegarder en copiant simplement le fichier de la base ? ».

Ce sont là des questions par lesquelles on passe forcément en exploitant SQLite pendant plusieurs années dans une application métier, et le genre de sujet qui évite bien des difficultés plus tard si on les traite dès la conception initiale. Cet article passe en revue, en prenant Microsoft.Data.Sqlite comme base, le choix de la bibliothèque, la chaîne de connexion et le pooling, le fonctionnement du mode WAL, la manière de traiter SQLITE_BUSY, les pièges du mapping de types, la prévention de la corruption et les sauvegardes, ainsi que le choix entre SQLite nu et EF Core — l’ensemble des points que je vérifie systématiquement lors d’une revue de conception.

1. La conclusion d’abord

• La bibliothèque à utiliser pour un nouveau développement est en principe Microsoft.Data.Sqlite (ou le fournisseur SQLite d’EF Core construit par-dessus). C’est un produit différent de System.Data.SQLite — ni la chaîne de connexion ni le comportement dans le détail ne sont compatibles —, gardez donc toujours à l’esprit lequel des deux un exemple trouvé en ligne suppose.¹
• Activez le mode WAL dès la toute première version publiée. Il augmente la concurrence entre lectures et écritures et fait disparaître la grande majorité des database is locked. Le réglage est persisté dans le fichier de base lui-même, mais ne fonctionne pas sur un partage réseau.²
• database is locked (SQLITE_BUSY) n’est pas quelque chose à masquer en ajoutant du traitement d’erreur : c’est le signe qu’il faut revoir la conception. Microsoft.Data.Sqlite retente automatiquement jusqu’au délai d’expiration (30 secondes par défaut)³, mais la vraie solution consiste à consolider le chemin d’écriture en un seul.
• Lorsque vous effectuez un grand nombre de petits INSERT, le simple fait de les regrouper dans une transaction explicite gagne deux à trois ordres de grandeur en vitesse. Si SQLite « semble lent », soupçonnez d’abord la granularité de vos commits.
• SQLite ne possède en réalité que quatre types de stockage — INTEGER / REAL / TEXT / BLOB —, et DateTime, Guid et decimal sont tous stockés en TEXT. decimal en particulier se compare et se trie selon les règles des chaînes de caractères, il est donc plus sûr de stocker les montants en entier, dans la plus petite unité monétaire.⁴
• Ne sauvegardez jamais en copiant simplement le fichier pendant que l’application tourne. Utilisez VACUUM INTO ou l’API Backup (SqliteConnection.BackupDatabase). Supprimer manuellement les fichiers -wal / -shm est également strictement interdit.⁵
• SQLite nu ne prend pas en charge le chiffrement. Le mot-clé Password de la chaîne de connexion n’a d’effet que si vous avez intégré une bibliothèque native de la famille SQLCipher⁶ ; pour une petite quantité d’informations sensibles, envisagez d’abord une protection par DPAPI plutôt que le chiffrement complet de la base.

2. Choisir une bibliothèque — des noms similaires, des contenus différents

Il existe plusieurs packages pour utiliser SQLite depuis .NET, et la première difficulté vient de la proximité trompeuse de leurs noms. Voici comment ils se distinguent :

Microsoft.Data.Sqlite est maintenu par l’équipe d’EF Core et constitue également le socle sur lequel repose le fournisseur SQLite d’EF Core.¹ Le package NuGet embarquant le binaire natif (le moteur SQLite lui-même), aucune étape de distribution supplémentaire n’est nécessaire pour les postes clients, et NuGet absorbe lui-même les différences x86/x64/ARM64.

Un point de vigilance : les informations écrites pour System.Data.SQLite ne s’appliquent pas telles quelles. Comme il est utilisé depuis longtemps, une grande quantité d’exemples en ligne suppose System.Data.SQLite, et vous rencontrerez des incompatibilités comme celles-ci :

• Les chaînes de connexion ne sont pas compatibles. Des mots-clés comme Version=3, UseUTF16Encoding, ou DateTimeFormat (qui change le format des dates) n’existent pas dans Microsoft.Data.Sqlite et déclenchent une exception s’ils sont spécifiés. Les mots-clés pris en charge se limitent à une courte liste : Data Source / Mode / Cache / Password / Foreign Keys / Default Timeout / Pooling, entre autres.⁶
• Le traitement des types diffère. Par exemple, un Guid est stocké en BLOB par défaut dans System.Data.SQLite, mais en TEXT dans Microsoft.Data.Sqlite. Pendant une période de migration où les deux bibliothèques lisent et écrivent dans le même fichier de base, cette différence se traduit par des incohérences de données.

Microsoft.Data.Sqlite est délibérément conçu comme une couche fine, sans conversion de types ni fonctionnalité pratique qui lui soit propre, si bien que la documentation officielle de SQLite s’applique directement. Si une application existante fonctionne de manière stable avec System.Data.SQLite, il n’est pas nécessaire de forcer une migration ; mais pour du code nouveau, la règle est de privilégier Microsoft.Data.Sqlite, et si vous migrez, de commencer par recenser les incompatibilités ci-dessus comme autant de points de migration.

3. Connexions et chaîne de connexion — le pooling continue de tenir le fichier

La forme de base de la chaîne de connexion se limite à un chemin de fichier. En pratique, ce qui compte, ce sont Mode et Pooling.⁶

using Microsoft.Data.Sqlite;

var builder = new SqliteConnectionStringBuilder
{
    DataSource = dbPath,
    Mode = SqliteOpenMode.ReadWriteCreate  // Valeur par défaut : crée le fichier s'il n'existe pas
};
using var conn = new SqliteConnection(builder.ConnectionString);
conn.Open();

• Mode : la valeur par défaut est ReadWriteCreate (crée le fichier s’il n’existe pas). Dans un cas comme la distribution d’une base maîtresse en lecture seule, spécifier ReadOnly évite les écritures accidentelles dues à un bug ou à une erreur de manipulation.
• Cache : laissez-le normalement à sa valeur par défaut. La documentation indique explicitement que Cache=Shared n’est pas recommandé en combinaison avec le mode WAL, il n’a donc pas sa place dans la politique « WAL par défaut » de cet article.⁶
• Password : si vous le spécifiez, PRAGMA key est envoyé juste après l’ouverture de la connexion, mais la bibliothèque native standard ne prenant pas en charge le chiffrement, rien ne se passe réellement.⁶ Si le chiffrement complet de la base est une exigence, il faut le remplacer par un bundle de la famille SQLCipher (par exemple SQLitePCLRaw.bundle_e_sqlcipher), et le stockage de la clé de chiffrement nécessitera de toute façon DPAPI (chapitre 1).
• Default Timeout : le délai d’expiration des commandes (30 secondes par défaut). C’est la limite supérieure de la fenêtre de nouvelle tentative évoquée au chapitre 5.

Autre point, à propos des API asynchrones. SQLite lui-même n’ayant pas d’E/S asynchrones, les méthodes async comme ExecuteNonQueryAsync s’exécutent en interne de façon synchrone.⁷ L’idée que « passer en async évite de figer l’UI » ne tient donc pas ; il faut explicitement renvoyer les requêtes lourdes vers un thread de travail, par exemple avec Task.Run. Les critères de décision pour async/await sont détaillés dans « Guide pratique de décision async/await en C# ».

3.1 Le piège du pooling — le fichier reste ouvert même après Close

Microsoft.Data.Sqlite a le pooling de connexions activé par défaut depuis la version 6.0.⁶ L’avantage est d’éviter de rouvrir le fichier à chaque Open, mais l’inconvénient est que même après Close / Dispose, la connexion native reste dans le pool et continue de tenir le handle du fichier de base. Résultat, les opérations suivantes échouent avec « le fichier est utilisé par un autre processus » :

• Une fonctionnalité « réinitialiser les données » qui supprime le fichier de base et le recrée
• Une restauration depuis une sauvegarde qui remplace le fichier de base
• Un déplacement du fichier de base lors d’une désinstallation ou d’un traitement d’archivage

La solution consiste à vider le pool juste avant l’opération sur le fichier.

// Libère le pool associé à cette chaîne de connexion et relâche le handle de fichier
SqliteConnection.ClearPool(new SqliteConnection(connectionString));
File.Delete(dbPath);

En mode WAL (chapitre suivant), les fichiers -wal / -shm peuvent également subsister ; pensez à les nettoyer en même temps. Si vous voulez tout libérer à la fermeture de l’application, utilisez SqliteConnection.ClearAllPools() ; et si un outil ponctuel n’a de toute façon pas besoin de pooling, vous pouvez aussi mettre Pooling=False dans la chaîne de connexion. « J’ai fait Close mais je n’arrive pas à supprimer le fichier » est une question fréquente après une migration vers SQLite : intégrez ClearPool dès le départ dans tout traitement utilitaire qui manipule le fichier de base.

4. Le fonctionnement du mode WAL — comprendre ce qui se passe avant de l’utiliser

L’article précédent se contentait d’écrire « activez le mode WAL », alors cette fois-ci j’entre dans le détail de son fonctionnement. Avec la méthode par défaut du journal de rollback, les lectures sont bloquées pendant une écriture, ce qui est la cause principale de database is locked. La méthode WAL (Write-Ahead Logging) écrit les modifications non pas dans le corps de la base mais dans un fichier journal en ajout seul, si bien que les lectures ne bloquent jamais les écritures, et les écritures ne bloquent jamais les lectures.² La configuration typique d’une application métier — un thread d’UI qui affiche un historique pendant qu’un processus en arrière-plan écrit des valeurs mesurées — fonctionne alors telle quelle.

Une fois passé en mode WAL, deux fichiers supplémentaires apparaissent à côté du corps de la base (le contenu validé par les checkpoints) :

Il y a quatre propriétés à retenir.

• Les checkpoints : le processus qui retranscrit le contenu de -wal dans le corps de la base ; par défaut, il s’exécute automatiquement dès que le WAL atteint 1 000 pages (environ 4 Mo).² Si une transaction de lecture longue s’attarde, le checkpoint ne peut pas avancer et -wal grossit ; évitez donc de concevoir une « connexion de lecture ouverte en permanence et transmise entre les composants » — ouvrez-la et fermez-la à l’usage (le pooling rend la réouverture rapide).
• Le réglage est persisté dans la base : PRAGMA journal_mode=WAL n’a besoin d’être exécuté qu’une fois ; il est ensuite enregistré dans le fichier de base lui-même, et la base reste en mode WAL quelle que soit la connexion qui l’ouvre par la suite.² Il n’est pas nécessaire de le réémettre à chaque connexion.
• Les écritures restent une à la fois : ce que WAL améliore, c’est la concurrence entre lecture et écriture — les écritures entre elles restent mutuellement exclusives. Se méprendre sur ce point et penser « avec WAL, je peux écrire librement depuis plusieurs threads » mène droit au SQLITE_BUSY du chapitre 5.
• Ne fonctionne pas sur un partage réseau : le wal-index reposant sur de la mémoire partagée, il ne fonctionne pas entre des processus situés sur des machines différentes.² Comme indiqué dans l’article précédent, il ne faut de toute façon pas placer SQLite sur un partage réseau.

Une remarque opérationnelle : le fichier -wal contient des transactions déjà validées mais pas encore appliquées au corps de la base. « Il suffit de copier uniquement le .db du corps » et « le -wal est un fichier temporaire, on peut le supprimer » sont deux idées fausses : soit les commits les plus récents disparaissent, soit, dans le pire des cas, la base est corrompue.⁵ C’est ce qui relie directement à la discussion du chapitre 7 sur le fait qu’une sauvegarde ne peut pas être une simple copie de fichier.

5. Verrouillage exclusif et SQLITE_BUSY — consolider les écritures en un seul chemin

SQLITE_BUSY (qui apparaît dans les messages d’exception sous la forme database is locked) se produit lorsqu’une autre connexion détient le verrou d’écriture. Le premier point à connaître est que Microsoft.Data.Sqlite retente automatiquement les erreurs busy/locked jusqu’au délai d’expiration de la commande (30 secondes par défaut).³ Autrement dit, une nouvelle tentative maison — catch, pause, puis réexécution côté application — n’est généralement pas nécessaire. Si une exception finit malgré tout par surgir une fois le délai atteint, c’est pour l’une des raisons suivantes :

• Une autre connexion (ou un autre processus) détient une transaction longue qui dépasse 30 secondes
• Une transaction commencée en lecture a tenté d’être promue en écriture et est entrée en conflit avec une autre écriture — l’attente ne résout rien, l’échec est donc immédiat. Un traitement de type « lire puis écrire » doit dès le départ être conçu comme une transaction d’écriture
• Un grand nombre de petites écritures affluent simultanément depuis plusieurs threads et se disputent le verrou

Dans aucun de ces cas, « augmenter le nombre de tentatives » ne résout le problème. La solution consiste à raccourcir les transactions et à consolider le chemin d’écriture en un seul.

5.1 Construire une file d’attente d’écriture avec System.Threading.Channels

Pour les données qui proviennent de plusieurs threads (valeurs mesurées, journaux d’opérations, etc.), plutôt que de laisser chaque thread écrire directement dans la base, on les envoie dans une file de laquelle une boucle d’écriture dédiée les traite. En .NET, System.Threading.Channels se prête directement à cet usage.

using System.Globalization;
using System.Threading.Channels;
using Microsoft.Data.Sqlite;

public sealed record Measurement(string DeviceId, double Value, DateTime CreatedAtUtc);

public sealed class MeasurementWriter : IAsyncDisposable
{
    private readonly Channel<Measurement> _channel =
        Channel.CreateBounded<Measurement>(new BoundedChannelOptions(10_000)
        {
            FullMode = BoundedChannelFullMode.Wait  // Bloque le producteur si la file déborde
        });
    private readonly string _connectionString;
    private readonly Task _loop;

    public MeasurementWriter(string connectionString)
    {
        _connectionString = connectionString;
        _loop = Task.Run(WriteLoop);
    }

    // Peut être appelée depuis n'importe quel thread. Ne touche jamais à la base.
    // Si la boucle d'écriture est morte, le producteur le découvre immédiatement via ChannelClosedException
    public ValueTask EnqueueAsync(Measurement m) => _channel.Writer.WriteAsync(m);

    private async Task WriteLoop()
    {
        try
        {
            await WriteLoopCore();
        }
        catch (Exception ex)
        {
            // Propage aux producteurs, via le canal, le fait que l'écrivain est mort
            // (disque plein, etc.). Sans cela, une fois la file pleine, EnqueueAsync
            // attendra indéfiniment et personne ne remarquera la panne
            _channel.Writer.TryComplete(ex);
            throw;
        }
    }

    private async Task WriteLoopCore()
    {
        using var conn = new SqliteConnection(_connectionString);
        conn.Open();
        var buffer = new List<Measurement>(500);
        while (await _channel.Reader.WaitToReadAsync())
        {
            // Récupère jusqu'à 500 éléments en attente et les écrit dans une seule transaction
            buffer.Clear();
            while (buffer.Count < 500 && _channel.Reader.TryRead(out var m))
                buffer.Add(m);

            using var tx = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = tx;
            cmd.CommandText =
                "INSERT INTO measurement (device_id, value, created_at) " +
                "VALUES ($device, $value, $at)";
            var pDevice = cmd.Parameters.Add("$device", SqliteType.Text);
            var pValue  = cmd.Parameters.Add("$value",  SqliteType.Real);
            var pAt     = cmd.Parameters.Add("$at",     SqliteType.Text);
            foreach (var m in buffer)
            {
                pDevice.Value = m.DeviceId;
                pValue.Value  = m.Value;
                // Spécifie explicitement InvariantCulture pour éviter toute influence du calendrier/des chiffres par défaut de la culture
                pAt.Value     = m.CreatedAtUtc.ToString(
                    "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture);
                cmd.ExecuteNonQuery();
            }
            tx.Commit();
        }
    }

    public async ValueTask DisposeAsync()
    {
        // Ne lève pas d'exception si la boucle d'écriture a déjà fermé le canal suite à un échec
        // (Complete() lèverait une exception en cas de double fermeture, masquant l'exception d'origine)
        _channel.Writer.TryComplete();
        await _loop;  // Vide ce qu'il reste. Si la boucle est morte, l'exception d'origine remonte ici
    }
}

Cette structure élimine par construction les conflits d’écriture, et comme elle regroupe naturellement en lots ce qui s’est accumulé dans la file, elle apporte du même coup l’accélération présentée dans la section suivante. Plusieurs autres détails comptent aussi dans une application métier : vider la file avec DisposeAsync avant de fermer, décider explicitement du comportement en cas de saturation via FullMode, et propager un échec de la boucle d’écriture vers les producteurs via TryComplete(ex) (si l’unique écrivain meurt silencieusement, la panne se manifeste par une attente infinie une fois la file pleine). Cette même idée de « consolider le chemin en un seul » se retrouve aussi dans la conception du verrouillage exclusif pour les échanges par fichier, dans « Bonnes pratiques d’intégration par fichiers et de verrouillage ».

5.2 Regrouper en transactions change l’ordre de grandeur

SQLite effectue une écriture synchrone sur le stockage (fsync) à chaque commit ; insérer les lignes une par une avec des commits implicites plafonne donc à quelques centaines ou quelques milliers de lignes par seconde même sur un SSD, et à quelques dizaines par seconde sur un HDD. Il suffit de regrouper les mêmes INSERT dans une transaction explicite de l’ordre de 1 000 lignes (encadrée par BeginTransaction puis un Commit final) pour atteindre des dizaines, voire des centaines de milliers de lignes par seconde. Un changement de trois lignes, à peine digne du nom de « réglage », change les choses de deux à trois ordres de grandeur.

Une bonne partie des remontées du type « l’import CSV prend 20 minutes » ou « la migration de données au démarrage ne se termine jamais » vient de là, et se résout simplement en passant par des transactions et en réutilisant les paramètres (la forme du code de la section précédente). À l’inverse, une transaction trop longue fait à son tour attendre les autres écritures : en pratique, un bon compromis consiste à viser un commit par « quelques centaines à quelques milliers de lignes, ou quelques centaines de millisecondes ».

6. Les pièges du mapping de types — que faire avec seulement quatre types ?

SQLite ne peut réellement stocker que quatre types — INTEGER / REAL / TEXT / BLOB —, et chaque type .NET est associé à l’un d’entre eux. Voici les principaux mappings de Microsoft.Data.Sqlite :⁴

Les trois types qui retombent en TEXT demandent une attention particulière.

• DateTime : tant que le format et le fuseau horaire du format de type ISO 8601 en TEXT restent cohérents, le tri de chaînes équivaut au tri chronologique, donc il n’y a aucun problème en pratique. À l’inverse, tout casse dès que l’UTC et l’heure locale se mélangent. La seule solution est de décider dès le départ « stockage en UTC, conversion en heure locale uniquement à l’affichage » et de la respecter sur tous les chemins de code ; les fonctions SQLite comme datetime('now') renvoient elles aussi de l’UTC. Autre point : si vous convertissez vous-même en chaîne, passez toujours CultureInfo.InvariantCulture à ToString. Laissé à la culture par défaut, seuls les postes fonctionnant sous une culture non grégorienne (ère japonaise, calendrier bouddhiste, etc.) verront la représentation de l’année changer, cassant à la fois le tri et la lecture (voir l’exemple de code en 5.1).
• Guid : stocké comme une chaîne, la correspondance se fait donc par égalité de chaînes. Si un autre outil ou une autre bibliothèque écrit dans une notation différente (majuscules/minuscules, format BLOB), le rapprochement échoue ; si plusieurs langages ou outils y touchent, documentez la notation comme une spécification formelle.
• decimal : le plus grand piège. Il est stocké en TEXT parce que REAL entraînerait une perte de précision⁴, mais une comparaison comme WHERE amount > 1000 sur une colonne TEXT ne se comporte pas comme prévu, car dans l’ordre des types de SQLite, TEXT est toujours considéré comme supérieur à numeric. Les agrégations comme SUM sont elles aussi converties en interne vers REAL, ce qui fait perdre la précision qui justifiait le choix de decimal.

La recommandation pratique pour decimal est simple : stockez les montants en entier (INTEGER), dans la plus petite unité monétaire. Pour le yen japonais, stockez un long en yens et convertissez uniquement à l’affichage. Avec un entier, la comparaison comme l’agrégation sont à la fois exactes et rapides, et le piège de type disparaît. Si des contraintes de schéma existant vous obligent à conserver decimal tel quel, résignez-vous à faire les comparaisons et les agrégations côté .NET après lecture, plutôt que côté SQL.

Notons enfin que le nom de type écrit dans CREATE TABLE n’est qu’un indice d’« affinité » ; un nom de type maison comme STRING est source d’accidents de conversion implicite. La recommandation officielle est de n’utiliser, même pour les noms de type de colonne, que les quatre types INTEGER / REAL / TEXT / BLOB.⁴

7. Exploitation — ne pas corrompre la base, et pouvoir la restaurer si elle l’est

7.1 quick_check au démarrage

Même si SQLite est protégé par des transactions, la corruption due à une panne disque ou à une manipulation de fichier erronée ne peut jamais être totalement écartée. Pour éviter de continuer à tourner sur une base corrompue et d’aggraver les dégâts, ajoutez une vérification d’intégrité au démarrage. Un integrity_check complet peut prendre du temps sur une grosse base ; au quotidien, la version allégée quick_check suffit.

using var cmd = conn.CreateCommand();
cmd.CommandText = "PRAGMA quick_check";
var result = (string)cmd.ExecuteScalar()!;
if (result != "ok")
{
    // N'écrit plus dans une base corrompue. Bascule en lecture seule et invite à la restauration
    logger.LogError("Corruption de la base de données détectée : {Detail}", result);
    EnterReadOnlyMode(result);
}

La politique consistant à ne pas réparer ni annuler automatiquement en cas de corruption détectée (mais à faire intervenir un opérateur humain) a été présentée dans la section 6.2 de l’article précédent.

7.2 Sauvegardes — pourquoi la copie de fichier ne fonctionne pas

Une simple copie du fichier de base pendant qu’elle tourne peut capturer un mélange d’état intermédiaire de transaction, ce que la documentation officielle de SQLite cite explicitement comme cause de corruption.⁵ En mode WAL, copier uniquement le corps en laissant de côté le -wal (qui contient des commits non encore appliqués au corps — chapitre 4) fait en plus perdre les données les plus récentes.

Il existe deux bonnes méthodes, toutes deux capables de produire un instantané cohérent pendant que l’application tourne.

• VACUUM INTO : en une seule instruction SQL, produit une copie défragmentée et de taille minimale.⁸ L’exemple de code figure dans la section 6.3 de l’article précédent, reportez-vous-y.
• SqliteConnection.BackupDatabase : un wrapper autour de l’API Backup de SQLite, qui copie entre objets de connexion.

using var source = new SqliteConnection($"Data Source={dbPath}");
using var target = new SqliteConnection($"Data Source={backupPath}");
source.Open();
target.Open();
source.BackupDatabase(target);  // Produit un instantané cohérent même pendant que l'application tourne

BackupDatabase comporte toutefois un point de vigilance. L’implémentation actuelle de Microsoft.Data.Sqlite copie aussi vite que possible et bloque les écritures des autres connexions jusqu’à la fin de la copie.⁹ Si vous sauvegardez une grosse base pendant que des mesures ou des opérations utilisateur sont en cours, les écritures de cette période se manifesteront par des SQLITE_BUSY ou un écran figé. Un partage sûr consiste à utiliser VACUUM INTO pour les sauvegardes générationnelles courantes, et à réserver BackupDatabase aux copies croisées avec une base en mémoire, ou à la duplication pendant une plage horaire sans écriture ou lors d’un traitement de maintenance. Si vous utilisez le planificateur de tâches pour une exécution périodique, ne faites pas la copie de fichier depuis l’extérieur : faites exécuter la sauvegarde décrite ci-dessus par l’application elle-même (ou par un petit outil qui ouvre correctement SQLite). La conception de l’exécution périodique elle-même est décrite dans « Exploiter en toute sécurité des tâches planifiées avec le planificateur de tâches ».

7.3 Emplacement et migration

L’emplacement de base du fichier de base est en principe %LOCALAPPDATA%\<nom de la société>\<nom de l'application>, et la gestion des versions de schéma via une migration au démarrage pilotée par PRAGMA user_version constitue la configuration minimale. Les deux ont déjà été présentés avec du code dans l’article précédent (chapitre 3 et section 6.1), ils ne sont donc pas repris ici. Une seule précision : prendre une génération de sauvegarde issue de 7.2 avant d’exécuter une migration transforme le pire des cas — « la migration a échoué et l’application ne démarre plus » — en une simple opération de remplacement de fichier pour la récupération.

8. Quand recourir à EF Core — le seuil de rentabilité de l’ORM

Jusqu’ici, tout a été écrit avec Microsoft.Data.Sqlite nu, mais il existe des cas clairs où le fournisseur SQLite d’EF Core est le bon choix. L’axe de décision est le caractère de l’application.

Si vous choisissez EF Core, vous devez connaître les contraintes propres au fournisseur SQLite.¹⁰

• Reconstruction de table due aux contraintes d’ALTER TABLE : SQLite ne prend pas directement en charge le changement de type ou la suppression d’une colonne, donc les migrations incluant AlterColumn ou DropColumn s’exécutent sous forme de reconstruction : « création d’une nouvelle table → copie des données → suppression de l’ancienne table → renommage ». Cela affecte le temps d’application et l’espace disque utilisé dans les environnements à gros volume de données ; planifiez donc soigneusement les changements de schéma sur les grandes tables.
• Impossible de générer des scripts idempotents : contrairement à SQL Server, il n’est pas possible de générer des scripts de migration avec des branchements if-then. L’application via dbContext.Database.Migrate() au démarrage de l’application est l’approche réaliste.
• Les opérations sur decimal / DateTimeOffset sont évaluées côté client : les particularités de type du chapitre 6 ne disparaissent pas sous EF Core. Toute comparaison autre que l’égalité, ou tout tri, est évalué côté client ; la recommandation de stocker les montants en entier dans la plus petite unité reste donc valable sous EF Core (vous pouvez convertir en long pour le stockage via un value converter).
• WAL est activé par défaut : une base créée par EF Core est en mode WAL dès l’origine⁷, la configuration du chapitre 4 n’est donc pas nécessaire. Comprendre son comportement reste néanmoins indispensable.

Notons aussi que même en adoptant EF Core, utiliser une base SQLite en mémoire pour les tests unitaires de la couche référentiel (repository) est une technique efficace. Comme elle tourne sur le même fournisseur qu’en production, l’écart où « ça passe avec un mock mais échoue sur la vraie base » se réduit. Pour la façon de décider à quelle couche écrire les tests, voir « La frontière entre tests unitaires et tests d’intégration ».

9. Résumé

SQLite est une bibliothèque pour laquelle « la simple intégration prend 30 minutes, mais une exploitation correcte demande de la conception ». Cela dit, la conception nécessaire est bien délimitée, et le contenu de cet article se résume, sous forme de check-list, aux six points suivants.

• La bibliothèque, c’est Microsoft.Data.Sqlite (ou EF Core par-dessus). Ne mélangez pas avec des informations qui supposent System.Data.SQLite
• Activez le mode WAL dès la première version publiée, et comprenez le rôle de -wal / -shm
• Consolidez les écritures en un seul chemin (file d’écriture avec Channels), et regroupez les petits INSERT dans des transactions
• Unifiez DateTime en UTC, et stockez les montants en entier dans la plus petite unité monétaire. Ne comparez ni n’agrégez decimal stocké en TEXT tel quel
• Faites un quick_check au démarrage, et sauvegardez avec VACUUM INTO ou BackupDatabase. Ne copiez jamais le fichier pendant que l’application tourne
• N’oubliez pas SqliteConnection.ClearPool pour tout traitement qui supprime ou remplace le fichier de base

Si vous vous reconnaissez dans une configuration qui masque database is locked en répétant des nouvelles tentatives, ou qui prend ses sauvegardes par simple copie de fichier, prenez le temps de passer en revue les points de cet article avant qu’un incident ne survienne. Chacun de ces correctifs reste, en soi, un petit changement.

Articles connexes

• Comment choisir l’emplacement de stockage des données locales d’une application Windows — tableau de décision SQLite / JSON / registre / Access
• Protéger les informations sensibles dans une application Windows — éviter les paramètres en clair avec DPAPI
• Exploiter en toute sécurité des tâches planifiées avec le planificateur de tâches
• Guide pratique de décision async/await en C# — écrire du code qui ne fige pas l’UI et ne provoque pas d’interblocage

Domaines de conseil connexes

KomuraSoft LLC prend en charge la revue de conception des applications métier intégrant SQLite (conception du verrouillage exclusif, des sauvegardes, des migrations), l’investigation des incidents en production comme database is locked, la corruption de données ou la dégradation des performances, ainsi que l’accompagnement à la migration depuis des sources de données existantes telles qu’Access.

• Conseil technique / revue de conception
• Développement d’applications Windows
• Réutilisation des actifs existants / accompagnement à la migration
• Contact

Références

Articles associés

Articles récents partageant les mêmes étiquettes, pour approfondir des sujets proches.

Sujets associés

Ces pages replacent le sujet dans un contexte plus large de services et de décisions.

Services liés à ce sujet

Cet article est directement lié aux services suivants.

Questions fréquentes

Questions souvent posées lors d’une consultation sur le sujet de cet article.

Pourquoi SQLite affiche-t-il « database is locked » ?

SQLITE_BUSY se produit lorsqu'une autre connexion détient le verrou d'écriture. Microsoft.Data.Sqlite retente automatiquement jusqu'au délai d'expiration de la commande (30 secondes par défaut), une nouvelle tentative maison n'est donc généralement pas nécessaire. Si l'exception survient malgré tout, la cause est l'une des suivantes : une transaction longue de plus de 30 secondes détenue par une autre connexion, un conflit lors de la promotion d'une lecture en écriture au sein d'une même transaction, ou une multitude de petites écritures venant de plusieurs threads qui se disputent le verrou — et augmenter le nombre de tentatives ne résout aucun de ces cas. La solution de fond consiste à raccourcir les transactions et à consolider le chemin d'écriture en un seul, par exemple via une file d'attente construite avec System.Threading.Channels. Activer le mode WAL élimine par ailleurs la majeure partie des blocages entre lecture et écriture.

Quelle bibliothèque choisir pour utiliser SQLite en C# ?

Pour un nouveau développement, la base est Microsoft.Data.Sqlite, le fournisseur ADO.NET maintenu par Microsoft (ou le fournisseur SQLite d'EF Core construit par-dessus). Le package NuGet embarque le moteur SQLite natif, donc aucune étape de distribution supplémentaire n'est nécessaire. L'ancien System.Data.SQLite est un produit différent, incompatible aussi bien au niveau de la chaîne de connexion que du traitement des types : par exemple, un Guid est stocké en BLOB par défaut dans System.Data.SQLite, mais en TEXT dans Microsoft.Data.Sqlite. Gardez toujours à l'esprit lequel des deux un exemple trouvé en ligne suppose.

Une sauvegarde SQLite peut-elle se faire par simple copie de fichier ?

Une simple copie de fichier pendant que l'application tourne est interdite. Elle risque de capturer un état intermédiaire de transaction, ce que la documentation officielle de SQLite cite explicitement comme cause de corruption. En mode WAL, le fichier -wal contient des validations qui ne sont pas encore reportées dans le fichier principal ; copier seulement le fichier principal fait donc perdre les données les plus récentes. La bonne méthode est VACUUM INTO (une seule instruction SQL qui produit une copie cohérente et de taille minimale) ou SqliteConnection.BackupDatabase. Mais BackupDatabase bloque les écritures des autres connexions jusqu'à la fin de la copie, donc pour les sauvegardes générationnelles courantes, VACUUM INTO est le choix sûr.

Pourquoi les INSERT en masse sont-ils lents dans SQLite ?

La cause est presque toujours la granularité des commits. SQLite effectue une écriture synchrone sur le disque (fsync) à chaque commit ; insérer les lignes une par une avec des commits implicites plafonne donc à quelques centaines ou milliers de lignes par seconde, même sur un SSD. Regrouper simplement les mêmes INSERT dans une transaction explicite de l'ordre de 1 000 lignes (encadrée par BeginTransaction puis un Commit final) permet d'atteindre des dizaines voire des centaines de milliers de lignes par seconde, soit un gain de deux à trois ordres de grandeur. À l'inverse, une transaction trop longue fait attendre les autres écritures : en pratique, il est raisonnable de viser un commit toutes les quelques centaines à quelques milliers de lignes, ou toutes les quelques centaines de millisecondes.

Profil de l’auteur

Page de présentation de l’auteur de l’article.

Go Komura

Représentant de KomuraSoft LLC

Spécialisé dans le développement de logiciels Windows, le conseil technique et l’analyse de pannes, notamment pour les systèmes existants et les incidents difficiles à reproduire.