dev-sqlite-guide

name

dev-sqlite-guide

description

Guide pour écrire des requêtes SQL et concevoir des schémas SQLite avec les bonnes pratiques. À utiliser quand l'utilisateur travaille avec SQLite, écrit des requêtes SQL ou conçoit des schémas de base de données. Se déclenche aussi avec "requête SQL", "schéma SQLite", "base de données SQLite", "migration SQL", "table SQLite", "query SQL". Also triggers on "SQLite schema", "SQLite query", "embedded database".

Guide SQLite

Workflow

1. Identifier le besoin — conception de schéma, écriture de requête, optimisation, migration ou debug.
2. Examiner le contexte — schéma existant, version SQLite (sqlite3 --version), volumes de données attendus.
3. Choisir le pattern adapté — voir sections ci-dessous selon le cas.
4. Livrer du code copiable — requêtes testables, schemas complets, commandes CLI directes.
5. Signaler les pièges — typage dynamique, verrouillage de fichier, absence de types natifs date/bool.

---

Conception de schéma

Table de référence (template)

create table users (
  id      text not null primary key default ('u_' || lower(hex(randomblob(16)))),
  created text not null default (strftime('%Y-%m-%dT%H:%M:%fZ')),
  updated text not null default (strftime('%Y-%m-%dT%H:%M:%fZ')),
  email   text not null unique,
  name    text not null,
  active  integer not null default 1   -- bool : 0/1
) strict;

create trigger users_updated
after update on users
begin
  update users set updated = strftime('%Y-%m-%dT%H:%M:%fZ')
  where id = old.id;
end;

Conventions de nommage

Élément	Convention
Clé primaire	id text + préfixe table (u_, p_, o_) + randomblob(16)
Timestamps	strftime('%Y-%m-%dT%H:%M:%fZ') — ISO 8601 avec ms, toujours UTC
Booléens	integer not null default 0 (0/1) — pas de type bool natif
JSON	text + contrainte check(json_valid(meta)) si nécessaire
Clés étrangères	user_id text not null references users(id)

Activer les foreign keys (obligatoire à chaque connexion)

pragma foreign_keys = on;

SQLite n'active pas les FK par défaut. À mettre dans l'initialisation de chaque connexion.

Migrations

Toujours versionner avec une table dédiée :

create table if not exists migrations (
  version  integer primary key,
  name     text not null,
  applied  text not null default (strftime('%Y-%m-%dT%H:%M:%fZ'))
);

Pattern de migration safe :

begin;
-- modification du schéma
alter table posts add column slug text;
insert into migrations(version, name) values (3, 'add_posts_slug');
commit;

---

Requêtes

Règles de base

• Écrire en minuscules — plus lisible, standard de facto.
• Toujours nommer les colonnes dans les select de production — jamais select *.
• Préférer les CTEs (with) aux sous-requêtes imbriquées profondes.

CTE — lecture claire

with active_users as (
  select id, name, email
  from users
  where active = 1
),
recent_posts as (
  select author_id, count(*) as cnt
  from posts
  where created > date('now', '-30 days')
  group by author_id
)
select u.name, u.email, coalesce(rp.cnt, 0) as posts_last_30d
from active_users u
left join recent_posts rp on rp.author_id = u.id
order by posts_last_30d desc;

Upsert

insert into settings (key, value)
values ('theme', 'dark')
on conflict(key) do update set
  value   = excluded.value,
  updated = strftime('%Y-%m-%dT%H:%M:%fZ');

Pagination (keyset > offset pour les grands volumes)

-- Offset (simple, mais lent sur grands volumes)
select id, title, created from posts
order by created desc
limit 20 offset 40;

-- Keyset (performant, reprend après le dernier élément vu)
select id, title, created from posts
where created < '2026-06-01T12:00:00.000Z'
order by created desc
limit 20;

Recherche — FTS5

-- Création de l'index virtuel
create virtual table posts_fts using fts5(
  title, body, content=posts, content_rowid=id
);

-- Indexation initiale
insert into posts_fts(posts_fts) values('rebuild');

-- Requête
select p.id, p.title, rank
from posts_fts
join posts p on p.id = posts_fts.rowid
where posts_fts match 'sqlite AND performance'
order by rank;

JSON (SQLite ≥ 3.38)

-- Extraction de champ
select json_extract(meta, '$.country') as country from users;

-- Filtre sur champ JSON
select * from orders
where json_extract(payload, '$.status') = 'paid';

-- Construction
select json_object('id', id, 'name', name) from users;

---

Optimisation & index

Analyser une requête

explain query plan
select * from posts where author_id = 'u_abc' order by created desc;

Créer les bons index

-- Index simple
create index idx_posts_author on posts(author_id);

-- Index composite (ordre = sélectivité décroissante)
create index idx_posts_author_created on posts(author_id, created desc);

-- Index partiel (réduit la taille de l'index)
create index idx_active_users on users(email) where active = 1;

WAL mode (performances en écriture concurrente)

pragma journal_mode = wal;
pragma synchronous = normal;  -- sécurité acceptable, perf ++

Statistiques

pragma table_info(posts);      -- colonnes et types
pragma index_list(posts);      -- index existants
analyze;                       -- mise à jour des stats pour l'optimiseur

---

CLI SQLite — commandes utiles

# Ouvrir / créer
sqlite3 mydb.db

# Import CSV
sqlite3 mydb.db -cmd ".mode csv" ".import data.csv my_table"

# Export CSV
sqlite3 -header -csv mydb.db "select * from users;" > users.csv

# Dump SQL complet
sqlite3 mydb.db .dump > backup.sql

# Exécuter un fichier SQL
sqlite3 mydb.db < schema.sql

---

Garde-fous & anti-patterns

Anti-pattern	Problème	Solution
select * en production	Casse si colonnes ajoutées, lisibilité zéro	Nommer explicitement les colonnes
Table sans strict	SQLite accepte n'importe quel type — bugs silencieux	Toujours strict
offset sur grande table	O(n) — ralentit proportionnellement	Keyset pagination
like '%terme%' sur gros volumes	Full scan	FTS5
FK sans pragma foreign_keys = on	Contraintes ignorées silencieusement	Activer à chaque connexion
Timestamps avec datetime()	Moins précis que strftime (pas de ms)	strftime('%Y-%m-%dT%H:%M:%fZ')
Connexions multiples en écriture sans WAL	Contention — SQLITE_BUSY fréquents	pragma journal_mode = wal
Migrations manuelles non versionnées	Impossible de rejouer ou auditer	Table migrations + transactions
Booléens en text ('true'/'false')	Comparaisons cassées	integer 0/1

---

Critères de décision rapides

• SQLite ou autre SGBD ? — SQLite est idéal pour : applications embarquées, mobile, desktop, prototypes, fichiers de données locaux, tests. Passer à Postgres/MySQL si : accès concurrent massif en écriture, réseau multi-clients, réplication, extensions spécialisées.
• FTS5 ou like ? — Dès que la table dépasse ~10k lignes ou que la recherche est fonctionnelle, FTS5.
• WAL ou DELETE mode ? — WAL par défaut pour toute app avec lectures et écritures simultanées.
• Index composite ou multiple ? — Composite si la requête filtre sur plusieurs colonnes ensemble (respecter l'ordre de la clause where).

Communication Rules — MANDATORY

• Ultra-concise. No filler, no preamble, no pleasantries.
• Never say "happy to help", "sure!", "great question", "let me", or similar.
• Tool first, talk second. Act before explaining.
• Result first. Lead with outcome, not process.
• Stop when done. No summary, no recap, no trailing commentary.
• No politeness wrappers. Direct and blunt.
• Minimum words. If one word works, do not use ten.
• No unsolicited explanations.
• No emoji unless asked.