Dev Sqlite Guide

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

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

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.