Se connecter à SQLite : Python, Node, Go, Java

Une connexion, c'est juste un fichier ouvert

SQLite n'a pas de serveur. Pas de démon à l'écoute sur un port, pas d'hôte à joindre, pas d'identifiants à négocier. « Se connecter », ça veut dire que votre pilote ouvre un fichier sur le disque et commence à en lire et écrire les pages. Voilà tout le modèle mental à retenir.

Chaque langage dispose d'un pilote qui encapsule la bibliothèque C de SQLite. Les formes changent, mais les rouages sont identiques : un chemin vers le fichier de base de données, un appel pour l'ouvrir, un handle sur lequel on exécute les requêtes, et un appel pour fermer une fois le travail terminé.

-- Conceptuellement, chaque pilote fait ceci :
-- 1. Ouvrir ou créer le fichier au chemin indiqué.
-- 2. Obtenir un handle.
-- 3. Exécuter du SQL via des requêtes préparées.
-- 4. Fermer le handle.

La suite de cette page montre à quoi ça ressemble en vrai dans le code, avec les quelques réglages qui valent le coup d'être faits avant votre première requête.

Python : le module sqlite3 de la bibliothèque standard

Python embarque sqlite3 d'office — rien à installer. Voici la structure de base :

-- Python
import sqlite3

conn = sqlite3.connect("app.db")
conn.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
conn.execute("INSERT INTO notes (body) VALUES (?)", ("première note",))
conn.commit()

for row in conn.execute("SELECT id, body FROM notes"):
    print(row)

conn.close()

Quelques points à retenir :

• sqlite3.connect("app.db") crée le fichier s'il n'existe pas. Passez ":memory:" pour une base de données qui ne vit qu'en RAM.
• sqlite3.connect("file:app.db?mode=ro", uri=True) ouvre la base en lecture seule grâce à la forme URI.
• Le ? dans la requête SQL est un placeholder — utilisez le binding de paramètres, jamais de la concaténation de chaînes. Le chapitre suivant approfondit ce sujet.
• conn.commit() est obligatoire, sauf si vous passez par un context manager (with conn:) qui commit automatiquement.

Pour une application qui tourne longtemps, définissez un busy timeout afin que les écritures concurrentes attendent au lieu de planter :

-- Python
conn.execute("PRAGMA busy_timeout = 5000")   -- attendre jusqu'à 5 s
conn.execute("PRAGMA journal_mode = WAL")    -- meilleure concurrence

Node.js : better-sqlite3

Côté Node, plusieurs bibliothèques existent, mais better-sqlite3 reste celle que la plupart des équipes adoptent. Son API est synchrone — ça peut sembler contre-intuitif en Node, mais c'est en réalité plus rapide pour SQLite, puisque les requêtes reviennent en quelques microsecondes.

-- Node.js
const Database = require("better-sqlite3");
const db = new Database("app.db");

db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");

const insert = db.prepare("INSERT INTO notes (body) VALUES (?)");
insert.run("première note");

const rows = db.prepare("SELECT id, body FROM notes").all();
console.log(rows);

db.close();

db.prepare(...) retourne un objet statement réutilisable. On utilise .run() pour les écritures, .all() pour récupérer toutes les lignes et .get() pour n'en récupérer qu'une seule. C'est le même schéma que la plupart des drivers SQL.

Définissez les pragmas au démarrage :

-- Node.js
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("foreign_keys = ON");   -- désactivé par défaut, presque toujours souhaité

foreign_keys = ON mérite qu'on s'y attarde : par défaut, SQLite n'applique pas les clés étrangères tant qu'on ne le demande pas explicitement, et ce connexion par connexion. Si vous oubliez, vos clauses REFERENCES ne servent qu'à faire joli.

Go : database/sql avec un driver

En Go, le package standard database/sql est indépendant du driver. Côté SQLite, on a deux choix courants : modernc.org/sqlite (du Go pur, sans CGO) et github.com/mattn/go-sqlite3 (avec CGO).

-- Go
import (
    "database/sql"
    _ "modernc.org/sqlite"
)

db, err := sql.Open("sqlite", "app.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")
if err != nil { panic(err) }
defer db.Close()

_, err = db.Exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
_, err = db.Exec("INSERT INTO notes (body) VALUES (?)", "première note")

rows, _ := db.Query("SELECT id, body FROM notes")
defer rows.Close()
for rows.Next() {
    var id int; var body string
    rows.Scan(&id, &body)
    fmt.Println(id, body)
}

La query string après le nom du fichier, c'est la façon dont ce driver transmet les pragmas au moment de la connexion. Le format change d'un driver à l'autre, donc pensez à consulter la doc de celui que vous choisissez.

sql.Open n'ouvre pas réellement de connexion : c'est la première requête qui s'en charge. db est en fait un pool de connexions. Pour SQLite, un petit pool suffit largement (voire db.SetMaxOpenConns(1) si votre charge est dominée par les écritures).

Java : JDBC

Le driver de référence, c'est org.xerial:sqlite-jdbc. Les URLs JDBC suivent le format jdbc:sqlite:<path> :

-- Java
import java.sql.*;

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db")) {
    try (Statement st = conn.createStatement()) {
        st.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
        st.execute("PRAGMA journal_mode = WAL");
        st.execute("PRAGMA busy_timeout = 5000");
    }

    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO notes (body) VALUES (?)")) {
        ps.setString(1, "première note");
        ps.executeUpdate();
    }

    try (PreparedStatement ps = conn.prepareStatement("SELECT id, body FROM notes");
         ResultSet rs = ps.executeQuery()) {
        while (rs.next()) System.out.println(rs.getInt(1) + " " + rs.getString(2));
    }
}

En mémoire : jdbc:sqlite::memory:. En lecture seule : ajoutez ?open_mode=1 ou passez par un objet SQLiteConfig.

PHP : PDO

Pour la connexion SQLite avec PDO, le DSN s'écrit sous la forme sqlite:<chemin> :

-- PHP
$db = new PDO("sqlite:app.db");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
$db->exec("PRAGMA journal_mode = WAL");
$db->exec("PRAGMA busy_timeout = 5000");

$stmt = $db->prepare("INSERT INTO notes (body) VALUES (?)");
$stmt->execute(["première note"]);

foreach ($db->query("SELECT id, body FROM notes") as $row) {
    echo $row["id"] . " " . $row["body"] . "\n";
}

sqlite::memory: pour une base en mémoire. Pensez toujours à mettre ATTR_ERRMODE sur les exceptions — déboguer des erreurs silencieuses, c'est franchement pénible.

Chaîne de connexion SQLite et chemins de fichiers

D'un driver à l'autre, vous croiserez deux variantes de « chaîne de connexion » :

• Chemin classique : app.db, ./data/app.db, /var/lib/myapp/app.db. Les chemins relatifs sont résolus par rapport au répertoire de travail du processus — ce qui, en production, n'est presque jamais ce que vous voulez. Privilégiez les chemins absolus.
• Forme URI : file:app.db?mode=rwc&cache=shared. Pratique pour passer des flags comme mode=ro (lecture seule), mode=rwc (lecture-écriture-création, la valeur par défaut), cache=shared ou encore nolock=1.

Quelques valeurs particulières à connaître :

• :memory: — une base en mémoire privée. Chaque connexion a la sienne.
• file::memory:?cache=shared — une base en mémoire partagée entre plusieurs connexions du même processus.
• "" (chaîne vide) — une base temporaire sur disque, privée, supprimée à la fermeture.

JDBC préfixe l'URI avec jdbc:sqlite:. PDO utilise sqlite:. Les drivers Go et le module sqlite3 de Python acceptent directement le chemin ou l'URI.

Et les pools de connexions ?

SQLite n'autorise qu'un seul écrivain à la fois. À un instant donné, une seule connexion détient le verrou d'écriture ; toutes les autres attendent. Multiplier les écrivains dans un pool n'accélère donc rien — vous fabriquez juste plus de candidats pour le même verrou.

Ceci dit, un petit pool reste utile pour :

• Les lectures concurrentes en mode WAL, où les lecteurs ne se bloquent ni entre eux, ni avec l'écrivain.
• Éviter le head-of-line blocking, où une requête lente fige toute l'application.

Des valeurs raisonnables pour une application web :

• Mode WAL activé.
• busy_timeout de quelques secondes, pour que la contention attende poliment au lieu de partir en erreur.
• Une taille de pool de 1 écrivain + N lecteurs, ou simplement une connexion partagée si le trafic est léger.
• Les clés étrangères activées, sur chaque connexion.

-- À appliquer sur chaque nouvelle connexion :
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;   -- sûr avec WAL ; plus rapide que FULL

synchronous = NORMAL est le réglage habituel avec WAL : durable face aux crashs de l'application, un peu plus permissif sur les crashs de l'OS, mais nettement plus rapide que le FULL par défaut.

Fermer les connexions (et pourquoi ça compte)

Chaque driver expose son propre appel de fermeture : conn.close(), db.Close(), db.close(). Si vous oubliez de fermer, vous fuitez des descripteurs de fichiers et le fichier WAL risque de grossir indéfiniment.

Pour un service qui tourne en continu, le schéma le plus courant consiste à garder une seule connexion (ou un pool) pendant toute la durée de vie du processus, plutôt que d'ouvrir et fermer à chaque requête. Ouvrir une connexion SQLite ne coûte pas cher, mais réappliquer les pragmas à chaque fois, c'est du gaspillage — et facile à oublier au passage.

-- Python — connexion par processus, réutilisée entre les requêtes
DB = sqlite3.connect("app.db", check_same_thread=False)
DB.execute("PRAGMA journal_mode = WAL")
DB.execute("PRAGMA busy_timeout = 5000")
DB.execute("PRAGMA foreign_keys = ON")

En Python par exemple, il faut passer check_same_thread=False si vous comptez partager la connexion entre plusieurs threads — et dans ce cas, prévois un verrou ou un pool pour sérialiser les appels.

La checklist avant la mise en prod

Avant d'envoyer du trafic réel vers une base SQLite :

• Utilisez un chemin absolu pour le fichier de la base.
• Activez le mode WAL (PRAGMA journal_mode = WAL).
• Définis un busy_timeout entre 2 et 10 secondes.
• Activez les clés étrangères, sur chaque connexion.
• Utilisez des requêtes préparées avec des paramètres liés — jamais de concaténation de chaînes.
• Vérifiez que le dossier qui contient la base est accessible en écriture par le processus (en mode WAL, SQLite crée un fichier -wal et un -shm à côté du fichier principal).
• Pensez aux sauvegardes avant d'en avoir besoin — on verra VACUUM INTO et la commande .backup plus loin.

La suite : les migrations

Se connecter, c'est la partie facile. Le vrai défi, c'est de faire évoluer votre schéma dans le temps sans bidouiller à la main les bases en production. Les migrations, c'est ce qui transforme un ALTER TABLE en un processus reproductible et versionné — direction la page suivante.