SQLite : la base de données embarquée pour admins et DevOps

SQLite stocke une base de données complète — tables, index, données — dans un seul fichier. Pas de serveur à installer, pas de port à ouvrir, pas de configuration à maintenir. Vous créez un fichier .db, vous requêtez dessus en SQL standard, et c’est terminé. Pour un script d’inventaire, un outil CLI interne ou un cache local d’application, SQLite est souvent le choix le plus pragmatique.

Ce guide couvre l’installation, l’utilisation en CLI et en Python, le mode WAL (Write-Ahead Logging) pour la concurrence, la sauvegarde et la restauration, les fonctionnalités modernes (STRICT tables, JSONB, colonnes générées), les limites réelles en production et un comparatif actionnable SQLite vs PostgreSQL pour savoir quand migrer.

Ce que vous allez apprendre

Installer SQLite et créer une base depuis le terminal
Utiliser la CLI sqlite3 pour requêter, inspecter et exporter des données
Manipuler une base SQLite depuis Python avec le module sqlite3 de la bibliothèque standard
Activer le mode WAL pour améliorer les lectures concurrentes
Sauvegarder et restaurer une base proprement (y compris avec WAL activé)
Identifier les limites de SQLite et savoir quand passer à PostgreSQL

Dans quel contexte ?

SQLite intervient dès qu’on a besoin de stocker des données structurées localement, sans la complexité d’un SGBD client-serveur :

script d’inventaire : collecter les informations de serveurs dans une base locale pour générer des rapports
outil CLI interne : stocker la progression, les scores ou l’historique d’un outil en ligne de commande
cache local d’application : persister des résultats d’API pour éviter les appels réseau répétés
stockage de configuration ou d’état : remplacer un fichier JSON/YAML quand les requêtes deviennent nécessaires
application monoposte : un outil de suivi, un gestionnaire de notes, un petit ERP local
traitement embarqué / edge / IoT : base de données sur un Raspberry Pi, un capteur ou un terminal de caisse
petit backend mono-instance : API web légère sans accès concurrent massif en écriture

SQLite est la base de données la plus déployée au monde — elle est intégrée dans Android, iOS, tous les navigateurs web, Python, PHP et des centaines d’autres logiciels. Le projet évolue encore activement et intègre des fonctionnalités modernes (typage strict, JSON natif, colonnes générées).

Ce que SQLite n’est PAS

Avant d’aller plus loin, posons les limites clairement :

SQLite n’est pas un serveur de base de données. Il n’écoute sur aucun port, n’accepte aucune connexion réseau. C’est une bibliothèque embarquée dans votre application.
SQLite ne gère pas plusieurs écrivains simultanés. Un seul processus peut écrire à la fois. Les autres attendent (ou échouent avec SQLITE_BUSY).
SQLite n’a pas de système de rôles ni de permissions SQL. La sécurité repose sur les permissions du fichier sur le système de fichiers.
SQLite ne remplace pas PostgreSQL (ni MySQL) pour une application réseau multi-utilisateur avec forte concurrence d’écriture.

Si votre besoin implique plusieurs utilisateurs distants, des droits fins par rôle, de la haute disponibilité ou de la réplication intégrée — passez directement à PostgreSQL.

Installation

Debian / Ubuntu

sudo apt update && sudo apt install -y sqlite3

RHEL / Fedora / AlmaLinux

sudo dnf install -y sqlite

macOS

brew install sqlite

Vérification

sqlite3 --version

3.46.1 2024-08-13 09:16:08 ...

Premiers pas en CLI

Créer une base et une table

sqlite3 inventaire.db

CREATE TABLE serveurs (
    id INTEGER PRIMARY KEY,
    hostname TEXT NOT NULL UNIQUE,
    ip TEXT NOT NULL,
    os TEXT NOT NULL,
    cpu_cores INTEGER NOT NULL,
    ram_gb REAL NOT NULL,
    env TEXT NOT NULL DEFAULT 'dev'
);

Insérer des données

INSERT INTO serveurs (hostname, ip, os, cpu_cores, ram_gb, env) VALUES
    ('web-prod-01', '10.0.1.10', 'Debian 12', 4, 8.0, 'prod'),
    ('db-prod-01',  '10.0.1.20', 'RHEL 9',    8, 32.0, 'prod'),
    ('ci-runner-01','10.0.2.10', 'Ubuntu 24.04', 2, 4.0, 'ci'),
    ('dev-box-01',  '10.0.3.10', 'Fedora 40', 4, 16.0, 'dev');

Requêter

-- Serveurs de production triés par RAM
SELECT hostname, os, ram_gb FROM serveurs
WHERE env = 'prod' ORDER BY ram_gb DESC;

db-prod-01|RHEL 9|32.0
web-prod-01|Debian 12|8.0

Commandes dot utiles

La CLI sqlite3 propose des commandes dot (commençant par .) pour l’administration :

Commande	Rôle
`.tables`	Lister les tables
`.schema serveurs`	Afficher le DDL d’une table
`.headers on`	Afficher les noms de colonnes
`.mode column`	Sortie en colonnes alignées
`.mode csv`	Sortie CSV
`.mode json`	Sortie JSON
`.dump`	Exporter toute la base en SQL
`.quit`	Quitter

Exemple de sortie formatée :

.headers on
.mode column
SELECT hostname, os, ram_gb, env FROM serveurs;

hostname      os            ram_gb  env
------------  ------------  ------  ----
web-prod-01   Debian 12     8.0     prod
db-prod-01    RHEL 9        32.0    prod
ci-runner-01  Ubuntu 24.04  4.0     ci
dev-box-01    Fedora 40     16.0    dev

SQLite avec Python

Le module sqlite3 est inclus dans la bibliothèque standard de Python — aucun pip install nécessaire. C’est le moyen le plus courant de manipuler SQLite depuis un script d’administration.

Connexion et requête de base

import sqlite3
from pathlib import Path

db_path = Path("inventaire.db")

with sqlite3.connect(db_path) as conn:
    conn.row_factory = sqlite3.Row  # accès par nom de colonne
    cursor = conn.execute(
        "SELECT hostname, os, ram_gb FROM serveurs WHERE env = ?",
        ("prod",),
    )
    for row in cursor:
        print(f"{row['hostname']:20s} {row['os']:15s} {row['ram_gb']:.0f} Go")

web-prod-01          Debian 12       8 Go
db-prod-01           RHEL 9          32 Go

Points importants :

with sqlite3.connect(...) : le context manager garantit que la transaction est commitée (ou rollbackée en cas d’erreur) et que la connexion est fermée proprement.
? (parameterized queries) : ne jamais injecter des variables dans une chaîne SQL avec des f-strings ou %s. Toujours utiliser les paramètres liés (? ou :name) pour se protéger des injections SQL.
row_factory = sqlite3.Row : permet d’accéder aux colonnes par nom (row['hostname']) au lieu d’un index numérique.

Créer une table et insérer depuis Python

import sqlite3

with sqlite3.connect("alertes.db") as conn:
    conn.execute("""
        CREATE TABLE IF NOT EXISTS alertes (
            id INTEGER PRIMARY KEY,
            timestamp TEXT NOT NULL DEFAULT (datetime('now')),
            host TEXT NOT NULL,
            severity TEXT NOT NULL CHECK (severity IN ('info','warn','crit')),
            message TEXT NOT NULL
        )
    """)
    conn.executemany(
        "INSERT INTO alertes (host, severity, message) VALUES (?, ?, ?)",
        [
            ("web-prod-01", "crit", "Disque / à 95%"),
            ("db-prod-01",  "warn", "Réplication en retard de 120s"),
            ("ci-runner-01","info", "Build #1847 terminé"),
        ],
    )

Ne jamais construire une requête SQL par concaténation de chaînes :

# ❌ DANGEREUX — injection SQL possible
conn.execute(f"SELECT * FROM serveurs WHERE hostname = '{user_input}'")

# ✅ SÛR — paramètre lié
conn.execute("SELECT * FROM serveurs WHERE hostname = ?", (user_input,))

Fichier, verrous et journalisation

Pour comprendre le mode WAL et les limites de concurrence de SQLite, il faut d’abord comprendre comment fonctionne la base au niveau du fichier.

SQLite stocke tout dans un fichier unique sur le disque. Quand un processus écrit, SQLite verrouille le fichier entier — pas une ligne, pas une table, mais tout le fichier. Ce verrou empêche les autres processus d’écrire en même temps.

Par défaut, SQLite utilise un journal rollback : avant chaque écriture, les pages modifiées sont copiées dans un fichier .db-journal. En cas de crash, ce journal permet de restaurer l’état précédent. Le problème : pendant une écriture, les lectures sont aussi bloquées.

C’est ce comportement que le mode WAL change radicalement.

Mode WAL et concurrence

Le Write-Ahead Logging (WAL) inverse le mécanisme de journalisation : au lieu de copier les anciennes pages avant d’écrire, SQLite écrit les modifications dans un fichier séparé (*.db-wal). Les lecteurs continuent à lire l’ancienne version du fichier pendant que l’écrivain travaille.

Activer WAL

PRAGMA journal_mode=WAL;

Depuis Python :

with sqlite3.connect("inventaire.db") as conn:
    conn.execute("PRAGMA journal_mode=WAL")

Le mode est persistant — il est écrit dans le fichier de la base. Vous n’avez pas besoin de le réactiver à chaque connexion.

Ce que WAL change concrètement

Aspect	Journal rollback (défaut)	WAL
Lectures pendant une écriture	Bloquées	Autorisées
Plusieurs lecteurs simultanés	Oui	Oui
Plusieurs écrivains simultanés	Non	Non
Fichiers supplémentaires	`*.db-journal` (temporaire)	`.db-wal` + `.db-shm` (persistants)
Performance en lecture	Bonne	Meilleure

Le mode WAL ne résout pas la limitation fondamentale : un seul processus peut écrire à la fois. Mais il supprime le blocage des lecteurs pendant les écritures, ce qui améliore sensiblement les performances pour les cas d’usage typiques (beaucoup de lectures, peu d’écritures).

Fonctionnalités modernes

SQLite n’est plus le “petit moteur SQL des années 2000”. Les versions récentes intègrent des fonctionnalités qui changent la façon de l’utiliser.

STRICT tables — du vrai typage

Par défaut, SQLite est très permissif : vous pouvez insérer du texte dans une colonne INTEGER sans erreur. Les STRICT tables (disponibles depuis la version 3.37) changent ce comportement :

CREATE TABLE serveurs (
    id INTEGER PRIMARY KEY,
    hostname TEXT NOT NULL,
    cpu_cores INTEGER NOT NULL,
    ram_gb REAL NOT NULL
) STRICT;

Avec STRICT, une insertion comme INSERT INTO serveurs (hostname, cpu_cores, ram_gb) VALUES ('srv01', 'quatre', 8.0) échouera avec une erreur de type. C’est le comportement attendu pour des données d’infrastructure.

Colonnes générées

Les colonnes générées (disponibles depuis la version 3.31) calculent leur valeur automatiquement à partir d’autres colonnes :

CREATE TABLE disques (
    id INTEGER PRIMARY KEY,
    device TEXT NOT NULL,
    total_mb INTEGER NOT NULL,
    used_mb INTEGER NOT NULL,
    pct_used REAL GENERATED ALWAYS AS (100.0 * used_mb / total_mb) STORED
) STRICT;

INSERT INTO disques (device, total_mb, used_mb) VALUES ('/dev/sda1', 102400, 87040);
SELECT device, pct_used FROM disques;

/dev/sda1|85.0

La colonne pct_used est calculée automatiquement — pas besoin de la maintenir manuellement.

JSON et JSONB natifs

SQLite gère le JSON nativement (depuis la version 3.38, et JSONB depuis la version 3.45). C’est utile quand une partie des données est semi-structurée :

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    timestamp TEXT NOT NULL DEFAULT (datetime('now')),
    source TEXT NOT NULL,
    payload TEXT NOT NULL  -- stocke du JSON
) STRICT;

INSERT INTO events (source, payload) VALUES
    ('prometheus', '{"alert":"DiskFull","host":"web-prod-01","pct":95}'),
    ('prometheus', '{"alert":"HighCPU","host":"db-prod-01","pct":88}');

-- Extraire le champ "host" du JSON
SELECT source, json_extract(payload, '$.host') AS host,
       json_extract(payload, '$.alert') AS alert
FROM events;

prometheus|web-prod-01|DiskFull
prometheus|db-prod-01|HighCPU

Sauvegarde et restauration

Sauvegarde en CLI

La commande .backup de la CLI sqlite3 effectue une copie cohérente de la base, même si des lectures sont en cours :

sqlite3 inventaire.db ".backup inventaire-backup.db"

Autre méthode : exporter un dump SQL complet :

sqlite3 inventaire.db ".dump" > inventaire-dump.sql

Le dump SQL est lisible, versionnable avec Git, et portable entre architectures.

Sauvegarde avec `VACUUM INTO`

VACUUM INTO crée une copie compactée de la base dans un nouveau fichier, en éliminant la fragmentation :

VACUUM INTO '/backup/inventaire-compact.db';

Sauvegarde distante avec `sqlite3_rsync`

L’outil sqlite3_rsync (disponible depuis la version 3.50) permet de synchroniser une base SQLite vers un serveur distant, de manière incrémentale :

sqlite3_rsync inventaire.db user@backup-srv:/backup/inventaire.db

Seules les pages modifiées sont transmises — c’est beaucoup plus efficace qu’un scp du fichier entier sur une base volumineuse.

Restauration

Depuis un fichier .db copié avec .backup ou VACUUM INTO :

# Stopper tout processus qui utilise la base
cp inventaire-backup.db inventaire.db

Depuis un dump SQL :

sqlite3 inventaire-restored.db < inventaire-dump.sql

Précautions avant restauration :

Stopper tous les processus qui accèdent à la base avant de restaurer.
Si WAL est activé, vérifier qu’il n’y a pas de fichiers *.db-wal orphelins dans le répertoire cible. Les supprimer si le fichier .db est un backup propre.
Ne jamais copier un fichier .db brut pendant qu’un processus écrit dedans (le fichier peut être en état incohérent). Utiliser .backup, VACUUM INTO ou sqlite3_rsync à la place.
Après restauration, vérifier l’intégrité :

sqlite3 inventaire.db "PRAGMA integrity_check;"

ok

Limites en production

Soyons directs. SQLite fonctionne très bien en mono-hôte, mais il n’est pas fait pour les cas suivants :

Limite	Détail
Un seul écrivain à la fois	Même avec WAL, les écritures sont sérialisées. Une application web avec 50 écritures/seconde saturera rapidement.
Pas d’accès réseau	SQLite n’écoute sur aucun port. Impossible de s’y connecter depuis un autre serveur.
Pas de rôles ni de permissions SQL	Les droits dépendent uniquement des permissions fichier du système d’exploitation.
Pas de réplication intégrée	`sqlite3_rsync` est un outil externe de synchronisation, pas de la réplication temps réel.
Pas de haute disponibilité	Pas de failover automatique, pas de cluster.
Pas de partitionnement	Pas de sharding ni de partitionnement natif des tables.
Taille pratique	Le format supporte jusqu’à 281 To, mais au-delà de ~1 To les performances se dégradent.
Pas de supervision serveur	Pas de `pg_stat_activity`, pas de slow query log intégré, pas de métriques exposables.

SQLite n’apporte pas ce qu’on attend d’un SGBD serveur : gestion réseau, rôles, HA, réplication, supervision. C’est le prix de la simplicité — et c’est un bon compromis tant que le cas d’usage reste local et mono-instance.

SQLite vs PostgreSQL : quand migrer ?

Critère	SQLite	PostgreSQL
Déploiement	Un fichier, zéro config	Serveur à installer et maintenir
Accès réseau	Local uniquement	Multi-clients réseau
Concurrence d’écriture	Un seul écrivain	Plusieurs écrivains simultanés (MVCC)
Permissions	Fichier OS	Rôles, GRANT, Row-Level Security
Réplication	`sqlite3_rsync` (externe, incrémental)	Streaming replication intégrée
Haute disponibilité	Non	Patroni, pgBouncer, failover automatique
Extensions	Limitées	PostGIS, pg_stat_statements, TimescaleDB…
JSON	`json_extract()`, JSONB	`jsonb`, GIN index, opérateurs avancés
Taille de la base	Confortable jusqu’à ~1 To	Plusieurs To sans problème
Backup	`.backup`, `VACUUM INTO`, `sqlite3_rsync`	`pg_dump`, PITR, WAL archiving
Supervision	Aucune intégrée	`pg_stat_activity`, slow log, exporters Prometheus

Règle de décision simple

Restez sur SQLite si :

la base est locale (même machine que l’application)
un seul processus écrit à la fois
l’application est mono-instance
la taille reste raisonnable (< 100 Go)
pas besoin de droits fins ou de supervision

Passez à PostgreSQL si :

plusieurs utilisateurs ou services accèdent à la base via le réseau
vous avez de la concurrence d’écriture significative
vous avez besoin de permissions par rôle
vous prévoyez de la haute disponibilité ou de la réplication
la base va croître au-delà de ce qu’un fichier local gère confortablement

En cas de doute : si l’application est déjà multi-serveur ou le deviendra bientôt, partez directement sur PostgreSQL. Migrer une base SQLite vers PostgreSQL fonctionne (dump SQL + adaptations), mais c’est toujours plus simple de partir sur la bonne architecture dès le début.

Bonnes pratiques admin

Permissions du fichier

C’est le point le plus critique en sécurité SQLite pour un admin système. La base n’a aucun système d’authentification — quiconque peut lire le fichier peut lire toutes les données.

# Restreindre l'accès au propriétaire uniquement
chmod 600 inventaire.db
chown app-user:app-group inventaire.db

# Vérifier
ls -la inventaire.db

-rw------- 1 app-user app-group 45056 avr 13 10:30 inventaire.db

Règles :

Le fichier doit appartenir à l’utilisateur système qui exécute l’application.
Jamais de chmod 666 ou chmod 777 sur un fichier de base de données.
Stocker la base dans un répertoire avec des permissions restreintes.
Éviter les montages réseau (NFS, CIFS, SMB) : les verrous POSIX ne sont pas fiables sur ces systèmes de fichiers. SQLite peut corrompre la base sur un montage NFS.

Vérifier l’intégrité

sqlite3 inventaire.db "PRAGMA integrity_check;"

Planifiez cette vérification dans un cron hebdomadaire pour détecter les corruptions tôt.

Optimiser les performances

-- Analyser les statistiques pour l'optimiseur de requêtes
PRAGMA optimize;

-- Compacter la base (récupérer l'espace des suppressions)
VACUUM;

Chiffrement avec SQLCipher

Si la base contient des données sensibles, SQLCipher ajoute un chiffrement AES-256 transparent :

# Installation (Debian/Ubuntu)
sudo apt install sqlcipher

# Créer une base chiffrée
sqlcipher encrypted.db

PRAGMA key = 'votre-passphrase-forte';
CREATE TABLE secrets (id INTEGER PRIMARY KEY, token TEXT NOT NULL);

Sans la passphrase, le fichier est illisible — même avec un accès root au système de fichiers.

À retenir

SQLite = un fichier — pas de serveur, pas de port, pas de configuration
sqlite3 en CLI — .tables, .schema, .mode column, .dump, .backup pour l’administration quotidienne
Python sqlite3 — dans la bibliothèque standard, toujours utiliser les paramètres liés (?) pour éviter les injections SQL
PRAGMA journal_mode=WAL — à activer systématiquement pour les applications avec des lectures concurrentes
Sauvegarder proprement — .backup ou VACUUM INTO, jamais un cp brut pendant l’utilisation
Restaurer — stopper les processus, supprimer les WAL orphelins, vérifier avec PRAGMA integrity_check
STRICT tables — activer sur les nouvelles tables pour un vrai typage
Permissions fichier — chmod 600, propriétaire applicatif, pas de montage NFS
Limite claire — mono-hôte, un écrivain, pas d’accès réseau. Au-delà → PostgreSQL

Prochaines étapes

PostgreSQL : tout savoir sur ce SGBD puissant Quand SQLite ne suffit plus — installation, configuration et administration de PostgreSQL.

MySQL : introduction et administration L'autre grand SGBD open source — comparaison et prise en main.

Les bases de données relationnelles Concepts fondamentaux des SGBDR : tables, SQL, transactions ACID, index.

Python : le langage incontournable du DevOps Aller plus loin avec Python — scripting, automatisation, packaging.