Skip to content

SQLite Aide-mémoire

Moteur de base de données SQL autonome, sans serveur et sans configuration.

01

Premiers pas

Bases du CLI

SQLite stocke une base entière dans un seul fichier. Le CLI sqlite3 ouvre ou crée un fichier .sqlite/.db à la demande. Contrairement à MySQL/PostgreSQL, pas de processus serveur — les applications lient directement la bibliothèque SQLite. .dump exporte toute la base en SQL texte pour backup ou migration.

sqlite
# open/create a database file
sqlite3 mydb.sqlite

# at the sqlite> prompt
.help              # show help
.tables            # list tables
.schema users      # show CREATE statement for users
.databases         # list attached databases
.dump              # dump entire DB as SQL text
.quit              # exit

# execute SQL from a file
sqlite3 mydb.sqlite < script.sql

# execute a query from command line
sqlite3 mydb.sqlite "SELECT COUNT(*) FROM users;"

Commandes point (dot commands)

Les dot commands sont interprétés par le CLI sqlite3, pas le moteur SQL — ils n'exigent jamais de point-virgule et ne sont pas disponibles via les API de programmation. .mode contrôle le formatage de sortie (essayez 'column' avec .header on pour un usage interactif lisible). .schema montre les CREATE ; .fullschema ajoute index, vues et triggers. Utilisez .help pour en découvrir plus.

sqlite
-- dot commands are CLI-only, NOT SQL (no semicolon needed)
.help                 -- list all dot commands
.tables               -- list all tables
.schema               -- schema for all tables
.schema users         -- schema for the users table
.fullschema           -- schema incl. indexes, triggers, views
.indices users        -- list indexes on users
.header on            -- show column headers
.mode column          -- column-aligned output
.mode list            -- default, pipe-separated
.mode csv             -- CSV output
.mode json            -- JSON output
.show                 -- show current settings
.width 10 20 30       -- set column widths
.timer on             -- show query execution time

Gestion du fichier de base

ATTACH DATABASE permet à une connexion d'interroger jusqu'à 10 fichiers de base simultanément comme schémas (main, temp, plus attachées). L'URI :memory: crée une base purement en mémoire qui disparaît à la fermeture — idéal pour des tests ou calculs jetables. Les noms de fichiers URI (file:...) activent les modes lecture seule, cache partagé, etc. Chaque base attachée est un fichier séparé sur disque.

sqlite
-- attach another database file to the current connection
ATTACH DATABASE 'archive.sqlite' AS archive;
DETACH DATABASE archive;

-- list attached databases (main + attached)
.databases

-- the in-memory database (never written to disk)
sqlite3 :memory:

-- temporary database (deleted when connection closes)
sqlite3 ""          -- empty arg => temp DB

-- open read-only
sqlite3 "file:mydb.sqlite?mode=ro" --readonly

-- query the underlying page size / file format
PRAGMA page_size;
PRAGMA journal_mode;

En-têtes et formatage de sortie

Le formatage de sortie n'affecte que le CLI, jamais les données. Le mode 'column' avec .headers on est le plus lisible pour l'humain. 'list' (défaut) est le meilleur pour les pipelines shell. 'json' et 'csv' font de SQLite un convertisseur pratique. .output redirige les résultats vers un fichier ; pensez à revenir avec .output stdout. .mode box/table dessine des bordures ASCII.

sqlite
-- readable interactive mode
.headers on
.mode column
.width 15 20 10
SELECT id, name, email FROM users LIMIT 5;

-- pipe-separated (good for scripts)
.mode list
.separator "|"
SELECT id, name FROM users;

-- boxed table output (sqlite 3.36+)
.mode box
.mode table

-- JSON output for piping to other tools
.mode json
SELECT id, name FROM users;

-- write output to a file
.output users.txt
SELECT id, name FROM users;
.output stdout

Exécuter des scripts et fichiers SQL

.read exécute un fichier SQL dans une session CLI ouverte ; une redirection avec < l'exécute avant l'invite interactive. --bail arrête le lot à la première erreur (sinon SQLite continue, ce qui peut masquer des échecs). .parameter (3.44+) lie des paramètres nommés depuis le shell, évitant l'interpolation de chaînes. Vérifiez toujours vos scripts avec .echo on en développement.

sqlite
-- run a .sql file from the shell
sqlite3 mydb.sqlite ".read setup.sql"
-- or piped in
sqlite3 mydb.sqlite < setup.sql

-- inside the CLI
.read setup.sql

-- stop on first error (shell flag)
sqlite3 mydb.sqlite --bail < script.sql

-- echo commands as they run
.echo on
.read migrations.sql

-- show EXPLAIN before each statement (debug)
.explain on

-- parameterize from the shell (sqlite 3.44+)
sqlite3 mydb.sqlite \
  -cmd ".parameter set @min 18" \
  "SELECT name FROM users WHERE age >= @min"
02

Tables et types de données

Créer des tables

INTEGER PRIMARY KEY est un alias du rowid — auto-incrémenté et la clé la plus rapide possible. AUTOINCREMENT change l'algorithme pour que les IDs supprimés ne soient jamais réutilisés (légèrement plus lent ; généralement inutile). DEFAULT (expr) autorise des expressions arbitraires comme datetime('now'). CREATE TABLE AS SELECT crée une table préchargée avec les résultats de la requête mais ne copie ni contraintes ni index.

sqlite
CREATE TABLE users (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT    NOT NULL UNIQUE,
  email    TEXT    NOT NULL UNIQUE,
  age      INTEGER CHECK (age >= 0),
  role     TEXT    NOT NULL DEFAULT 'user',
  created  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS orders (
  id        INTEGER PRIMARY KEY,
  user_id   INTEGER NOT NULL,
  total     REAL    NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

-- quick table from a query
CREATE TABLE archive AS
  SELECT * FROM orders WHERE created < '2024-01-01';

Classes de stockage et affinité de type

SQLite utilise le typage dynamique : toute colonne peut contenir n'importe laquelle des 5 classes de stockage. « L'affinité de type » tente de convertir les valeurs quand possible (ex. '42' dans une colonne INTEGER devient l'entier 42) mais n'échoue jamais sur un désaccord. Les tables STRICT (3.37+) rétablissent une vérification rigide traditionnelle par colonne. typeof() rapporte la classe réellement stockée. Cette flexibilité est puissante mais exige de la discipline — utilisez STRICT pour les nouveaux schémas où l'intégrité compte.

sqlite
-- SQLite has 5 storage classes (not strict types):
--   NULL, INTEGER, REAL, TEXT, BLOB
CREATE TYPE demo...  -- not supported

-- columns have 'type affinity', not enforced types
CREATE TABLE t (
  a INTEGER,   -- affinity INTEGER
  b TEXT,      -- affinity TEXT
  c REAL,      -- affinity REAL
  d BLOB,      -- affinity BLOB (no conversion)
  e            -- no affinity (accepts anything)
);

-- this WORKS despite 'INTEGER' column (dynamic typing)
INSERT INTO t (a) VALUES ('hello');
SELECT typeof(a) FROM t;   -- 'text'

-- STRICT tables (SQLite 3.37+) actually enforce types
CREATE TABLE strict_t (a INTEGER, b TEXT) STRICT;
INSERT INTO strict_t VALUES ('hi', 1);   -- error

Types de données courants

SQLite n'a pas de types DATE/TIME ou BOOLEAN natifs — stockez les timestamps en TEXT ISO-8601 ('YYYY-MM-DD HH:MM:SS') pour la triabilité et utilisez les fonctions date/heure pour les manipuler. Les booléens sont des entiers 0/1. Les limites de longueur VARCHAR(n) sont ignorées (compatibilité seulement). L'affinité NUMERIC essaie INTEGER d'abord, puis REAL, en préservant les décimales exactes quand possible. BLOB stocke les octets bruts à l'identique.

sqlite
-- integers
CREATE TABLE numerics (
  small  INTEGER,        -- 1/2/4/8 bytes depending on value
  big    INTEGER,        -- up to 64-bit
  -- no separate BIGINT/SMALLINT, all map to INTEGER affinity
  flag   BOOLEAN         -- stored as 0 (false) or 1 (true)
);

-- text & blobs
CREATE TABLE blobs (
  name   TEXT,           -- variable-length UTF-8, no length limit
  data   BLOB,           -- raw bytes, no conversion
  note   VARCHAR(255)    -- length ignored, affinity TEXT
);

-- real numbers
CREATE TABLE floats (
  price  REAL,           -- 8-byte IEEE float
  precise NUMERIC(10,2)  -- affinity NUMERIC, decimal preserved if exact
);

-- dates: SQLite has no native DATE type — store as TEXT (ISO-8601)
CREATE TABLE events (
  ts TEXT  -- 'YYYY-MM-DD HH:MM:SS' recommended
);

Modifier des tables

L'ALTER TABLE de SQLite est volontairement limité pour un moteur sans serveur. ADD COLUMN est O(1) (pas de réécriture). RENAME COLUMN (3.25+) et DROP COLUMN (3.35+) sont des commodités modernes. Pour des changements de type ou ajout de contraintes, utilisez le pattern de reconstruction de table en 12 étapes (créer la nouvelle, copier, supprimer, renommer, reconstruire les index). Les clés étrangères doivent être temporairement désactivées pendant les reconstructions — voir PRAGMA legacy_alter_table.

sqlite
-- add a column (fast, appends to end)
ALTER TABLE users ADD COLUMN bio TEXT;

-- rename a column (SQLite 3.25+)
ALTER TABLE users RENAME COLUMN name TO username;

-- rename a table
ALTER TABLE users RENAME TO accounts;

-- drop a column (SQLite 3.35+)
ALTER TABLE users DROP COLUMN deprecated_field;

-- SQLite CANNOT: change a column type, add constraints to
-- an existing column, or reorder columns. Workaround:
-- 1) create new table with desired schema
-- 2) INSERT INTO new SELECT * FROM old
-- 3) DROP old; ALTER TABLE new RENAME TO old
-- 4) recreate indexes/triggers

Contraintes

Les contraintes garantissent l'intégrité des données au niveau moteur. PRIMARY KEY implique NOT NULL et UNIQUE. CHECK peut référencer toute colonne de la ligne. Les clauses ON CONFLICT (OR IGNORE / OR REPLACE / OR ABORT / OR FAIL / OR ROLLBACK) contrôlent la résolution de conflit par instruction — OR REPLACE supprime la ligne en conflit puis insère, ce qui réinitialise son rowid. Pour la sémantique UPSERT, préférez ON CONFLICT ... DO UPDATE (voir section CRUD).

sqlite
CREATE TABLE products (
  id         INTEGER PRIMARY KEY,
  sku        TEXT    NOT NULL UNIQUE,
  name       TEXT    NOT NULL,
  price      REAL    NOT NULL CHECK (price >= 0),
  stock      INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category   TEXT    CHECK (category IN ('a','b','c')),

  -- composite unique constraint
  UNIQUE (name, category),

  -- table-level check
  CHECK (price * stock < 1000000)
);

-- name a constraint for clearer error messages
CREATE TABLE t (
  x INTEGER CONSTRAINT x_positive CHECK (x > 0)
);

-- conflict handling
INSERT OR IGNORE INTO products (sku, name, price) VALUES (...);
INSERT OR REPLACE INTO products (...) VALUES (...);
UPDATE OR ABORT products SET stock = stock - 1 WHERE id = 1;
03

Opérations CRUD

INSERT

L'INSERT multi-lignes est drastiquement plus rapide que des inserts par ligne dans une boucle — batchez-les. INSERT ... SELECT copie des données entre tables. last_insert_rowid() retourne le rowid du dernier INSERT de la connexion courante (pas la transaction). Pour les apps, préférez les inserts paramétrés (voir section Intégration) pour éviter l'injection SQL et les maux de guillemets. Utilisez DEFAULT pour prendre explicitement la valeur par défaut d'une colonne.

sqlite
-- single row
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

-- multiple rows in one statement (fast)
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

-- insert from a query
INSERT INTO archive_users
SELECT * FROM users WHERE active = 0;

-- explicit NULL / default
INSERT INTO users (username, email, age)
VALUES ('eve', '[email protected]', DEFAULT);

-- return the new rowid (handy in apps)
INSERT INTO users (username) VALUES ('frank');
SELECT last_insert_rowid();   -- returns the new id

UPSERT (ON CONFLICT)

ON CONFLICT (UPSERT) est l'idiome SQLite du « insérer ou mettre à jour si existant » — bien plus sûr qu'INSERT OR REPLACE, qui supprime la ligne existante (déclenchant les triggers DELETE et réinitialisant le rowid). 'excluded' désigne la ligne proposée à l'insertion. DO NOTHING saute en silence. Les cibles de conflit peuvent être toute contrainte UNIQUE ou PRIMARY KEY. C'est le pattern correct pour compteurs, mises à jour de dernier-vu et imports idempotents.

sqlite
-- insert, but update on conflict
INSERT INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]')
ON CONFLICT(id) DO UPDATE SET
  email  = excluded.email,
  username = excluded.username;

-- ignore on conflict
INSERT INTO users (id, username)
VALUES (1, 'alice')
ON CONFLICT(id) DO NOTHING;

-- conflict on a specific unique constraint
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]')
ON CONFLICT(email) DO UPDATE SET
  username = excluded.username;

UPDATE

Incluez toujours WHERE sauf intention réelle de mettre à jour chaque ligne — SQLite n'a pas de mode dry-run. UPDATE ... FROM (3.33+) et UPDATE ... RETURNING (3.35+) rapprochent SQLite de l'ergonomie de PostgreSQL. RETURNING est inestimable pour les apps exigeant l'état post-mise-à-jour sans SELECT séparé. CASE permet des mises à jour en masse en une instruction avec logique par ligne, bien plus rapide que ligne par ligne.

sqlite
-- basic update (ALWAYS use WHERE in production!)
UPDATE users SET age = 31 WHERE id = 1;

-- multiple columns
UPDATE users
SET age = age + 1, role = 'admin'
WHERE username = 'alice';

-- conditional update with CASE
UPDATE products
SET price = CASE
  WHEN category = 'a' THEN price * 0.9
  WHEN category = 'b' THEN price * 0.95
  ELSE price
END;

-- update from another table (subquery)
UPDATE orders
SET total = (SELECT SUM(qty * price)
             FROM items WHERE order_id = orders.id);

-- UPDATE ... RETURNING (SQLite 3.35+)
UPDATE users SET role = 'admin'
WHERE id IN (1,2,3)
RETURNING id, username, role;

DELETE

SQLite n'a pas de TRUNCATE — DELETE FROM table retire toutes les lignes mais déclenche les triggers de ligne et ne réinitialise pas le compteur AUTOINCREMENT (stocké dans sqlite_sequence). DELETE ... RETURNING (3.35+) journalise ce qui a été retiré. Pour les données d'audit, préférez les suppressions douces (une colonne deleted_at) aux suppressions dures. L'espace libéré est réutilisé par les futurs inserts ; VACUUM le rend à l'OS.

sqlite
-- basic delete
DELETE FROM users WHERE id = 1;

-- delete with a subquery condition
DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = 0);

-- DELETE ... RETURNING
DELETE FROM sessions
WHERE expires < datetime('now')
RETURNING id, user_id;

-- delete ALL rows (fast, but fires triggers)
DELETE FROM logs;

-- TRUNCATE equivalent: delete all + reset rowid
DELETE FROM logs;
DELETE FROM sqlite_sequence WHERE name = 'logs';

-- soft delete pattern
UPDATE users SET deleted_at = datetime('now') WHERE id = 1;

Bases de SELECT

SELECT est le cheval de bataille. LIMIT compte OFFSET saute implémente la pagination (la pagination keyset est plus rapide pour de grands offsets — voir section Interrogation). Les alias (AS, optionnel) améliorent la lisibilité et sont requis pour les colonnes calculées dans les vues. DISTINCT replie les lignes identiques ; considérez GROUP BY pour un contrôle plus fin. CASE ajoute de la logique si/sinon aux ensembles de résultats et s'utilise dans toute clause.

sqlite
-- basic select
SELECT id, username, email FROM users;

-- limit and offset
SELECT id, username FROM users LIMIT 10 OFFSET 20;
SELECT id, username FROM users LIMIT 20, 10;  -- offset, count

-- aliasing columns and tables
SELECT u.id AS user_id, u.username AS name
FROM users AS u;

-- expressions
SELECT
  username,
  age,
  age * 365 AS days_alive,
  UPPER(username) AS upper_name
FROM users;

-- distinct rows
SELECT DISTINCT category FROM products;

-- conditional output
SELECT name,
  CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS status
FROM users;
04

Interrogation et filtrage

WHERE et opérateurs

NULL est spécial en SQL — c'est « inconnu », pas une valeur. = NULL retourne toujours NULL (traité comme faux), donc utilisez IS NULL / IS NOT NULL. NULL combiné à AND/OR suit la logique ternaire. BETWEEN est inclusif. IN est un raccourci de l'égalité par OU ; évitez IN (NULL, ...) qui se comporte bizarrement. Utilisez des parenthèses généreusement pour rendre les conditions composées sans ambiguïté.

sqlite
-- comparison operators
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM users WHERE age IN (18, 21, 25);
SELECT * FROM users WHERE age NOT IN (18, 21);

-- NULL handling (NEVER use = NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- logical operators
SELECT * FROM users
WHERE age >= 18 AND age <= 65 AND role = 'admin';

SELECT * FROM users
WHERE role = 'admin' OR role = 'editor';

-- range with multiple conditions
SELECT * FROM orders
WHERE (status = 'shipped' AND total > 100)
   OR (status = 'pending' AND total > 500);

ORDER BY et LIMIT

ORDER BY sans départage unique donne un ordre non déterministe entre exécutions — ajoutez toujours une colonne unique (comme id) comme clé de tri finale. La pagination par OFFSET est O(n) car elle scanne et jette des lignes ; la pagination keyset (WHERE id > last_seen_id) est O(limit) et le bon choix pour les grandes tables. RANDOM() exige un tri complet — réservez-le aux petits ensembles de résultats ou sous-ensembles échantillonnés.

sqlite
-- ascending (default) / descending
SELECT * FROM users ORDER BY username;
SELECT * FROM users ORDER BY created DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;

-- sort with NULLs first or last (SQLite 3.30+)
SELECT * FROM tasks ORDER BY due_date ASC NULLS FIRST;
SELECT * FROM tasks ORDER BY due_date DESC NULLS LAST;

-- pagination: keyset is faster than OFFSET
-- slow (offset scans & discards rows):
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000;
-- fast (keyset):
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;

-- random sample
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;

Matching de motifs

LIKE est insensible à la casse pour l'ASCII par défaut (un piège surprenant — utilisez COLLATE BINARY pour la sensibilité). GLOB est sensible à la casse et supporte des classes de caractères comme [A-Z]. REGEXP n'est pas intégré — il faut enregistrer une fonction ou charger une extension. Pour la recherche plein texte à l'échelle, utilisez FTS5 (voir section Performance) au lieu de LIKE '%terme%' qui ne peut pas utiliser d'index.

sqlite
-- LIKE: case-insensitive by default for ASCII,
--   wildcards % (any chars) and _ (single char)
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_lice';

-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'AL%' COLLATE BINARY;

-- GLOB: Unix shell-style, case-SENSITIVE
--   wildcards * and ?, plus [abc] character classes
SELECT * FROM users WHERE username GLOB 'Al*';
SELECT * FROM users WHERE username GLOB '[A-D]*';

-- REGEXP (requires extension or custom function)
SELECT * FROM users WHERE username REGEXP '^a.*e$';

-- substring match
SELECT * FROM users WHERE INSTR(username, 'lic') > 0;

CASE et logique conditionnelle

CASE est le si/sinon du SQL et marche dans SELECT, WHERE, ORDER BY, GROUP BY et HAVING. La forme « simple » matche une valeur ; la forme « searched » évalue des conditions booléennes dans l'ordre. SUM(CASE WHEN ... THEN 1 ELSE 0 END) est l'idiome classique de pivot/comptage conditionnel. CASE court-circuite au premier WHEN matché, donc l'ordre compte. ELSE passe à NULL si omis.

sqlite
-- simple CASE (value match)
SELECT name,
  CASE role
    WHEN 'admin' THEN 'Administrator'
    WHEN 'editor' THEN 'Editor'
    ELSE 'Regular User'
  END AS role_label
FROM users;

-- searched CASE (conditions)
SELECT name,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END AS age_group
FROM users;

-- CASE in aggregate (conditional count)
SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active_count,
  SUM(CASE WHEN active = 0 THEN 1 ELSE 0 END) AS inactive_count
FROM users;

-- CASE in ORDER BY (custom sort order)
SELECT * FROM products
ORDER BY CASE category
  WHEN 'featured' THEN 0
  WHEN 'new' THEN 1
  ELSE 2
END;

DISTINCT et opérations ensemblistes

UNION ALL est plus rapide qu'UNION car UNION effectue un tri de déduplication — préférez UNION ALL quand les doublons sont impossibles ou acceptables. Toutes les opérations ensemblistes exigent des comptages et types de colonnes compatibles. SQLite applique INTERSECT/EXCEPT avec une précédence supérieure à UNION/UNION ALL ; utilisez des parenthèses pour contrôler l'ordre. ORDER BY dans une requête composée s'applique à tout l'ensemble de résultats, pas au dernier SELECT.

sqlite
-- distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT city, country FROM users;

-- UNION (dedup) and UNION ALL (faster, keeps dups)
SELECT 'user' AS type, id FROM users WHERE active = 1
UNION ALL
SELECT 'admin', id FROM admins;

-- INTERSECT: rows in both
SELECT id FROM users
INTERSECT
SELECT user_id FROM orders;

-- EXCEPT: rows in first but not second
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;

-- combine with ORDER BY (applies to whole result)
SELECT name FROM users WHERE active = 1
UNION
SELECT name FROM archived_users
ORDER BY name;
05

Jointures

INNER JOIN

INNER JOIN ne retourne que les lignes avec correspondance dans les deux tables — les lignes non appariées de chaque côté sont éliminées. USING (col) est un raccourci quand les deux tables ont une colonne du même nom et produit une seule colonne fusionnée. NATURAL JOIN utilise implicitement toutes les colonnes de même nom — pratique mais fragile (un changement de schéma peut altérer le comportement en silence). Préférez un ON explicite pour la clarté.

sqlite
-- only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id;

-- join with additional filter
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100 AND u.active = 1;

-- USING shorthand (when join columns share a name)
SELECT u.username, o.total
FROM users u
JOIN orders o USING (user_id);

-- natural join (joins on all same-named columns — risky)
SELECT * FROM users NATURAL JOIN profiles;

LEFT JOIN

LEFT JOIN préserve chaque ligne de la table de gauche ; les colonnes de droite manquantes deviennent NULL. Le motif « WHERE right.id IS NULL » (anti-join) trouve les lignes sans correspondance — souvent plus clair et plus rapide que NOT IN avec sous-requêtes, surtout quand des NULL interviennent. En agrégeant sur un LEFT JOIN, COUNT(right.id) (pas COUNT(*)) ne compte que les lignes appariées ; un utilisateur sans commandes donne 0, pas 1.

sqlite
-- all users, with their orders (NULLs if no orders)
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

-- find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- multiple left joins
SELECT u.username,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

CROSS JOIN et RIGHT/FULL

CROSS JOIN produit le produit cartésien — utile pour générer des combinaisons (taille × couleur) mais dangereux sur de grandes tables (n×m lignes). SQLite supporte RIGHT JOIN (3.39+) mais il est rarement utilisé ; réécrivez en LEFT JOIN pour la clarté. Pas de FULL OUTER JOIN natif ; émulez avec LEFT JOIN ... UNION ... RIGHT JOIN où l'union déduplique les lignes appariées qui se recouvrent.

sqlite
-- CROSS JOIN: cartesian product (every combination)
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;

-- explicit CROSS JOIN (same as comma join)
SELECT a.name, b.name FROM teams a, teams b;

-- RIGHT JOIN (preserves right table) — supported but unusual
SELECT u.username, o.total
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;

-- SQLite has no FULL OUTER JOIN; emulate with LEFT + UNION:
SELECT u.username, o.id AS order_id
FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id
FROM users u RIGHT JOIN orders o ON o.user_id = u.id;

Self join

Un self join interroge la même table deux fois avec des alias donnant un rôle à chaque copie (employé vs manager, user_a vs user_b). L'astuce 'a.id < b.id' évite de matcher une ligne à elle-même et de produire des paires dupliquées en miroir. Pour les hiérarchies profondes (profondeur arbitraire), une CTE récursive (voir section CTE) est le bon outil — les self joins ne gèrent que des profondeurs fixes.

sqlite
-- employees and their managers (same table)
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  manager_id INTEGER REFERENCES employees(id)
);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;

-- recursive hierarchy (tree traversal) — see CTE section
-- multi-level category paths
SELECT c.name, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

Jointures multi-tables

Les jointures s'enchaînent de gauche à droite ; chaque JOIN voit le résultat accumulé. En mêlant jointures LEFT et INNER, un INNER après un LEFT peut re-filtrer les NULL, annulant le but du LEFT — ordonnez soigneusement. COUNT(DISTINCT col) évite le double comptage sur les jointures un-à-plusieurs. Attention à la multiplicité : joindre deux tables un-à-plusieurs peut produire une explosion de lignes (m × n lignes par parent).

sqlite
-- chain joins across 3+ tables
SELECT u.username, o.id AS order_id, i.name AS item, i.qty
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
WHERE u.active = 1
ORDER BY o.id, i.name;

-- mixing JOIN types
SELECT u.username, o.id AS order_id, p.name AS product
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN products p ON p.id = i.product_id;

-- join with aggregation
SELECT u.username,
  COUNT(DISTINCT o.id) AS orders,
  SUM(i.qty * i.price) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
GROUP BY u.id, u.username;
06

Agrégations et GROUP BY

Bases de GROUP BY

GROUP BY replie les lignes partageant les colonnes groupées en une ligne par groupe. Chaque colonne non agrégée du SELECT doit apparaître dans le GROUP BY (SQLite est tolérant et choisit une valeur arbitraire, mais n'en dépendez pas). Grouper par une expression (comme strftime sur une date) est génial pour les rapports par tranches temporelles. ORDER BY s'applique après GROUP BY — utilisez-le pour trier les groupes.

sqlite
-- count users per role
SELECT role, COUNT(*) AS user_count
FROM users
GROUP BY role;

-- multiple aggregates per group
SELECT role,
  COUNT(*)            AS user_count,
  AVG(age)            AS avg_age,
  MIN(age)            AS min_age,
  MAX(age)            AS max_age,
  SUM(age)            AS total_age
FROM users
GROUP BY role;

-- group by multiple columns
SELECT role, active, COUNT(*) AS cnt
FROM users
GROUP BY role, active
ORDER BY role, active;

-- group by expression
SELECT strftime('%Y-%m', created) AS month,
  COUNT(*) AS signups
FROM users
GROUP BY month
ORDER BY month;

Fonctions d'agrégation

COUNT(*) compte les lignes ; COUNT(col) les valeurs non NULL ; COUNT(DISTINCT col) les non NULL uniques. AVG/SUM ignorent les NULL. GROUP_CONCAT joint les valeurs en chaîne (séparateur virgule par défaut). TOTAL() retourne toujours un REAL (0.0 pour une entrée vide), contrairement à SUM() qui retourne NULL sur entrée vide. La clause FILTER (3.30+) est plus propre que CASE pour l'agrégation conditionnelle et peut être plus rapide.

sqlite
SELECT
  COUNT(*)             AS row_count,
  COUNT(email)         AS emails_non_null,
  COUNT(DISTINCT role) AS distinct_roles,
  AVG(age)             AS avg_age,
  SUM(total)           AS revenue,
  MIN(created)         AS first_signup,
  MAX(created)         AS last_signup,
  GROUP_CONCAT(name)   AS all_names,
  GROUP_CONCAT(name, ' | ') AS names_piped,
  TOTAL(price)         AS total_real  -- always returns REAL, 0.0 if empty
FROM orders;

-- aggregate with FILTER (SQLite 3.30+)
SELECT
  COUNT(*)                              AS all_orders,
  COUNT(*) FILTER (WHERE status='paid') AS paid,
  SUM(total) FILTER (WHERE status='paid') AS paid_revenue
FROM orders;

HAVING (filtrer les groupes)

WHERE filtre les lignes d'entrée avant le groupement ; HAVING filtre les groupes après agrégation. Cet ordre compte : un WHERE sur un agrégat est illégal. SQLite permet les références d'alias dans HAVING (et GROUP BY) pour la lisibilité, mais c'est non standard. Le motif « group by X having count(*) > 1 » est la requête classique de recherche de doublons.

sqlite
-- HAVING filters groups (after GROUP BY);
-- WHERE filters rows (before GROUP BY)
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
WHERE status = 'paid'         -- filter rows first
GROUP BY user_id
HAVING COUNT(*) >= 3 AND SUM(total) > 100   -- filter groups
ORDER BY spent DESC;

-- HAVING with aliases (SQLite allows this)
SELECT role, COUNT(*) AS n
FROM users
GROUP BY role
HAVING n > 1;

-- find duplicate usernames
SELECT username, COUNT(*) AS dupes
FROM users
GROUP BY username
HAVING COUNT(*) > 1;

GROUP_CONCAT

GROUP_CONCAT est la fonction d'agrégation de chaînes de SQLite (appelée STRING_AGG ou LISTAGG ailleurs). Le séparateur par défaut est la virgule ; précisez-en un en second argument. ORDER BY dans l'agrégat (3.44+) contrôle l'ordre de concaténation. DISTINCT déduplique avant la jointure. Le résultat a une limite de 1 Go ; pour d'énormes groupes, fetchez les lignes et agréguez dans votre application.

sqlite
-- concatenate values within a group
SELECT role, GROUP_CONCAT(username) AS members
FROM users
GROUP BY role;
-- members: 'alice,bob,carol'

-- custom separator
SELECT role, GROUP_CONCAT(username, ' | ') AS members
FROM users
GROUP BY role;

-- ordered concatenation (SQLite 3.44+)
SELECT role,
  GROUP_CONCAT(username ORDER BY username) AS sorted_members
FROM users
GROUP BY role;

-- distinct values only
SELECT role,
  GROUP_CONCAT(DISTINCT city) AS cities
FROM users
GROUP BY role;

Pivoter les données

SQLite n'a pas de PIVOT natif ; émulez avec SUM(CASE WHEN ...) ou la clause FILTER plus propre. Chaque colonne pivotée teste une valeur de l'attribut de groupement. Ce motif transforme les données longues (normalisées) en rapports larges (croisés). Pour un pivot pleinement dynamique (catégories inconnues), il faut bâtir la chaîne SQL dans le code applicatif — SQL ne peut générer des colonnes à l'exécution.

sqlite
-- manual pivot with SUM(CASE ...)
SELECT user_id,
  SUM(CASE WHEN strftime('%w', created) = '0' THEN 1 ELSE 0 END) AS sun,
  SUM(CASE WHEN strftime('%w', created) = '1' THEN 1 ELSE 0 END) AS mon,
  SUM(CASE WHEN strftime('%w', created) = '2' THEN 1 ELSE 0 END) AS tue,
  SUM(CASE WHEN strftime('%w', created) = '3' THEN 1 ELSE 0 END) AS wed,
  SUM(CASE WHEN strftime('%w', created) = '4' THEN 1 ELSE 0 END) AS thu,
  SUM(CASE WHEN strftime('%w', created) = '5' THEN 1 ELSE 0 END) AS fri,
  SUM(CASE WHEN strftime('%w', created) = '6' THEN 1 ELSE 0 END) AS sat
FROM orders
GROUP BY user_id;

-- pivot with FILTER (cleaner)
SELECT user_id,
  COUNT(*) FILTER (WHERE status='paid')    AS paid,
  COUNT(*) FILTER (WHERE status='pending') AS pending,
  COUNT(*) FILTER (WHERE status='refunded') AS refunded
FROM orders
GROUP BY user_id;
07

Sous-requêtes

Sous-requêtes scalaires

Une sous-requête scalaire retourne exactement une ligne et une colonne ; elle peut apparaître partout où une expression de valeur est valide. Les sous-requêtes corrélées référencent la requête externe et se réexécutent par ligne — puissantes mais potentiellement lentes sur les grandes tables (considérez un JOIN ou une fonction de fenêtre à la place). SQLite optimise bien les sous-requêtes corrélées simples en jointures, mais vérifiez toujours EXPLAIN QUERY PLAN sur les chemins chauds.

sqlite
-- a subquery returning a single value
SELECT username, age
FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- in SELECT list
SELECT username,
  age,
  age - (SELECT AVG(age) FROM users) AS age_diff
FROM users;

-- in HAVING
SELECT role, AVG(age) AS avg_age
FROM users
GROUP BY role
HAVING AVG(age) > (SELECT AVG(age) FROM users);

-- correlated scalar subquery (re-evaluated per row)
SELECT username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

Sous-requêtes IN et NOT IN

Les sous-requêtes IN sont intuitives mais attention aux NULL : si le résultat de la sous-requête contient un NULL, NOT IN évalue à NULL (traité comme faux) pour chaque ligne, retournant un résultat vide. Préférez NOT EXISTS pour les anti-joins avec colonnes nullables — NULL-safe et souvent plus rapide. Le IN multi-colonnes est concis pour matcher des clés composites.

sqlite
-- users who have placed an order
SELECT username FROM users
WHERE id IN (SELECT user_id FROM orders);

-- users who have NOT placed an order
SELECT username FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- DANGER: NOT IN with NULLs returns no rows!
-- if the subquery returns any NULL, NOT IN matches nothing.
-- Safe version using NOT EXISTS:
SELECT username FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- multi-column IN
SELECT * FROM products
WHERE (category, price) IN (
  SELECT category, MIN(price) FROM products GROUP BY category
);

EXISTS et NOT EXISTS

EXISTS teste l'existence de lignes sans fetcher de données — il s'arrête à la première correspondance, donc efficace. NOT EXISTS est la façon NULL-safe de trouver les lignes sans correspondance (contrairement à NOT IN). Pour la meilleure performance, assurez-vous qu'un index existe sur la colonne jointe par la sous-requête corrélée (orders.user_id dans l'exemple). SELECT 1 est conventionnel ; la liste de colonnes est sans importance pour EXISTS.

sqlite
-- EXISTS: true if subquery returns any row
SELECT username FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

-- NOT EXISTS: classic anti-join (NULL-safe)
SELECT username FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- correlated EXISTS — usually efficient with an index on
-- the subquery's join column (orders.user_id here)
SELECT p.name FROM products p
WHERE EXISTS (
  SELECT 1 FROM order_items i
  WHERE i.product_id = p.id AND i.qty > 10
);

Tables dérivées (sous-requête dans FROM)

Une sous-requête dans FROM (table dérivée) est un moyen puissant de préparer des résultats intermédiaires, pré-agréger ou filtrer avant la jointure. Les tables dérivées doivent être aliasées. SQLite matérialise la sous-requête (l'écrit dans une table temporaire) dans la plupart des cas — pour un usage répété, une CTE (clause WITH) est souvent plus claire et référençable plusieurs fois. SQLite n'autorise pas les références corrélées vers une sous-requête FROM.

sqlite
-- treat a query result as a table
SELECT t.role, t.cnt
FROM (
  SELECT role, COUNT(*) AS cnt
  FROM users
  GROUP BY role
) t
WHERE t.cnt > 1;

-- join a derived table
SELECT u.username, t.total_spent
FROM users u
JOIN (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY user_id
) t ON t.user_id = u.id;

-- derived tables must be aliased
SELECT * FROM (SELECT 1 AS x) AS sub;

Corrélées vs non corrélées

Les sous-requêtes non corrélées tournent une fois et peuvent se mettre en cache ; les corrélées se réexécutent par ligne externe (utilisez des index pour les garder rapides). SQLite manque de LATERAL/APPLY ; pour « joindre » un agrégat par ligne, pré-agrégez dans une table dérivée et faites un LEFT JOIN. Quand une sous-requête corrélée performe mal, la réécrire en JOIN avec GROUP BY ou en fonction de fenêtre est le correctif habituel.

sqlite
-- UNCORRELATED: runs once, result cached
SELECT username FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- CORRELATED: references outer row, re-runs per outer row
SELECT u.username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders
FROM users u;

-- lateral-style correlation in a JOIN subquery is NOT
-- supported directly; use a scalar subquery in SELECT or
-- rewrite as a join with GROUP BY:
SELECT u.username, COALESCE(t.n, 0) AS orders
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS n FROM orders GROUP BY user_id
) t ON t.user_id = u.id;
08

Index

Créer des index

Les index accélèrent les lectures mais ralentissent les écritures (chaque INSERT/UPDATE/DELETE met à jour tous les index). L'ordre des colonnes compte : un index composite sur (a, b) aide WHERE a=? et WHERE a=? AND b=?, mais PAS WHERE b=? seul (règle du préfixe gauche). Les index uniques imposent des contraintes. SQLite stocke les index en B-trees. Supprimez les index inutilisés — ils coûtent espace et performance d'écriture.

sqlite
-- single-column index
CREATE INDEX idx_users_email ON users(email);

-- unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- IF NOT EXISTS for idempotent scripts
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- drop an index
DROP INDEX IF EXISTS idx_users_email;

-- list indexes
.indices users
-- or
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

Index partiels

Les index partiels n'indexent que les lignes correspondantes, donc plus petits et plus rapides à maintenir — idéals quand les requêtes filtrent toujours sur un prédicat stable ('active = 1', 'deleted_at IS NULL'). Le WHERE de la requête doit matcher (ou être plus strict que) le WHERE de l'index pour que l'optimiseur l'utilise. Les index partiels uniques imposent l'unicité seulement sur le sous-ensemble — parfait pour les contraintes « une ligne active par parent ».

sqlite
-- index only active users (smaller, faster)
CREATE INDEX idx_active_users ON users(username)
WHERE active = 1;

-- index only unpaid orders
CREATE INDEX idx_unpaid_orders ON orders(user_id)
WHERE status = 'unpaid';

-- unique partial index: one active session per user
CREATE UNIQUE INDEX idx_one_active_session
ON sessions(user_id) WHERE active = 1;

-- query must match the WHERE for the index to be used
SELECT * FROM users WHERE active = 1 AND username = 'alice';

Index d'expression

Les index d'expression indexent le résultat d'une fonction ou expression, permettant des lookups rapides sur valeurs transformées — mais la requête doit utiliser exactement la même expression. Usages courants : recherche insensible à la casse (LOWER), bucketing de dates (strftime), extraction de champs JSON (json_extract). Fonctions déterministes seulement (pas de RANDOM() ni NOW()). Les index d'expression sont puissants mais compliquent les changements de schéma.

sqlite
-- index a function result (must match the query expression exactly)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- query that can use it (must use the same expression)
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- index a date extraction for fast monthly queries
CREATE INDEX idx_orders_month ON orders(strftime('%Y-%m', created));
SELECT * FROM orders WHERE strftime('%Y-%m', created) = '2024-06';

-- index a JSON path
CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';

-- indexed computed value (collation)
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);

Covering et WITHOUT ROWID

Un covering index inclut chaque colonne lue par la requête, donc SQLite satisfait la requête depuis l'index seul (pas de lookup de table). Les tables WITHOUT ROWID font de la PRIMARY KEY un index clusterisé — les lignes se stockent triées par clé, idéal pour les charges clé/valeur et les scans de plage sur la PK. Compromis : pas d'AUTOINCREMENT, pas d'alias rowid, mises à jour un peu plus complexes. Benchmark avant adoption généralisée.

sqlite
-- covering index: includes all columns the query needs,
-- so SQLite never reads the table
CREATE INDEX idx_orders_cover ON orders(user_id, status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 1;
-- ^ fully served by the index

-- WITHOUT ROWID tables: store rows IN the index (clustered)
CREATE TABLE kv (
  key   TEXT PRIMARY KEY,
  value TEXT
) WITHOUT ROWID;

-- the PK becomes the table itself; lookups by key are fast
SELECT value FROM kv WHERE key = 'config:theme';

-- good for key/value lookups and read-heavy tables
-- trade-off: no rowid, some features unsupported

Inspection des index

EXPLAIN QUERY PLAN est l'outil essentiel — « USING INDEX » signifie que l'index est utilisé ; « SCAN » un scan complet de table (souvent mauvais). sqlite_stat1 (peuplé par ANALYZE) détient les statistiques de cardinalité que le planificateur utilise pour choisir les index. REINDEX reconstruit un index enflé ou corrompu sans reconstruire la table. Lancez ANALYZE après les gros chargements pour que le planificateur ait des statistiques fraîches.

sqlite
-- see if an index is used
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';
-- look for 'SEARCH users USING INDEX idx_users_email'

-- list all indexes on a table with their definitions
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

-- index stats (how often each index is used)
SELECT name, idx_scan, idx_read
FROM sqlite_stat1, sqlite_master
WHERE sqlite_stat1.tbl = 'users';

-- rebuild an index (after corruption or bloat)
REINDEX idx_users_email;
REINDEX;  -- rebuild all indexes

-- analyze tables to update query planner stats
ANALYZE;
09

Vues

Créer des vues

Les vues sont des tables virtuelles définies par une requête sauvegardée — elles ne stockent pas de données (sauf vues temp), elles rejouent le SELECT sous-jacent à chaque fois. Utilisez-les pour simplifier des requêtes complexes, imposer des patterns d'accès cohérents ou abstraire les changements de schéma des applications. Les vues sont en lecture seule par défaut dans SQLite (avec des contraintes sur les vues updatables). DROP VIEW n'affecte jamais les tables sous-jacentes.

sqlite
-- a view is a saved SELECT, queried like a table
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE active = 1;

SELECT * FROM active_users WHERE age > 18;

-- view with calculated columns
CREATE VIEW user_summary AS
SELECT u.id, u.username,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- IF NOT EXISTS for idempotent scripts
CREATE VIEW IF NOT EXISTS active_users AS
SELECT id, username FROM users WHERE active = 1;

-- drop a view
DROP VIEW IF EXISTS active_users;

Vues updatables

SQLite autorise INSERT/UPDATE/DELETE sur les vues simples (une table de base, pas de JOIN/agrégats/DISTINCT/GROUP BY). L'opération se transmet à la table sous-jacente. SQLite ne supporte PAS WITH CHECK OPTION, donc une ligne insérée à travers une vue peut ne pas satisfaire le WHERE de la vue et devenir invisible par elle ensuite. Pour des vues complexes nécessitant des écritures, utilisez des triggers INSTEAD OF.

sqlite
-- simple views (one base table, no aggregates/distinct/etc.)
-- are auto-updatable in SQLite
CREATE VIEW active_users AS
SELECT id, username, email, age FROM users WHERE active = 1;

-- INSERT through the view (active defaults/applies)
INSERT INTO active_users (id, username, email) VALUES (1, 'x', '[email protected]');

-- UPDATE through the view
UPDATE active_users SET age = 30 WHERE id = 1;

-- DELETE through the view
DELETE FROM active_users WHERE id = 1;

-- the WITH CHECK OPTION is NOT supported in SQLite, so
-- inserts/updates that don't match the view's WHERE still
-- land in the base table but vanish from the view.

Triggers INSTEAD OF sur vues

Les triggers INSTEAD OF interceptent les écritures vers une vue et exécutent votre logique custom, rendant toute vue updatable. C'est la façon standard de rendre inscriptibles les vues complexes (multi-tables, agrégées). NEW et OLD désignent les lignes entrantes et existantes. Les triggers INSTEAD OF ne se déclenchent que sur les vues, pas les tables de base. À utiliser pour encapsuler de la logique métier ou cacher un schéma normalisé derrière une vue dénormalisée.

sqlite
-- a view joining multiple tables is not auto-updatable,
-- but INSTEAD OF triggers let you define the write behavior
CREATE VIEW user_orders AS
SELECT u.id AS user_id, u.username, o.id AS order_id, o.total
FROM users u LEFT JOIN orders o ON o.user_id = u.id;

-- route INSERTs on the view to the orders table
CREATE TRIGGER trg_user_orders_insert
INSTEAD OF INSERT ON user_orders
FOR EACH ROW
BEGIN
  INSERT INTO orders (id, user_id, total)
  VALUES (NEW.order_id, NEW.user_id, NEW.total);
END;

-- now this works:
INSERT INTO user_orders (user_id, order_id, total)
VALUES (1, 100, 49.99);

Vues temporaires

Les vues temporaires ne vivent que pour la connexion de base courante et se suppriment automatiquement à la déconnexion — parfaites pour préparer des requêtes ad-hoc complexes sans encombrer le schéma partagé. Elles se stockent dans un schéma temp séparé, donc les autres connexions (même au même fichier) ne les voient pas. Les vues TEMP peuvent référencer des tables TEMP, utile pour des pipelines ETL à portée de session.

sqlite
-- TEMP view exists only for the current connection
CREATE TEMP VIEW recent_orders AS
SELECT * FROM orders WHERE created > datetime('now', '-7 days');

-- the view vanishes when the connection closes
-- (no other connection can see it)

-- also valid: CREATE TEMPORARY VIEW
CREATE TEMPORARY VIEW my_scratch AS
SELECT id, username FROM users WHERE role = 'admin';

-- use case: ad-hoc analysis without polluting the schema
SELECT * FROM recent_orders WHERE total > 100;

-- list views
SELECT name, sql FROM sqlite_master
WHERE type = 'view';
SELECT name, sql FROM temp.sqlite_master
WHERE type = 'view';  -- temp views

Vues matérialisées (émulées)

SQLite n'a pas de vue matérialisée native, mais une table ordinaire rafraîchie périodiquement atteint le même but. Pour un rafraîchissement atomique, bâtissez une nouvelle table dans une transaction et renommez-la — les lecteurs voient la vieille version jusqu'au commit, puis la nouvelle. Ajoutez les index après la reconstruction. Les vues matérialisées échangent coût d'écriture/stockage contre vitesse de lecture — idéales pour des agrégats coûteux fréquemment interrogés.

sqlite
-- SQLite has no native materialized views; emulate with a
-- real table that you refresh periodically

-- 1) create the 'materialized' table
CREATE TABLE mv_user_summary AS
SELECT u.id, u.username,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- 2) add an index for fast lookups
CREATE INDEX idx_mv_user_summary_id ON mv_user_summary(id);

-- 3) refresh (full rebuild)
DELETE FROM mv_user_summary;
INSERT INTO mv_user_summary
SELECT u.id, u.username, COUNT(o.id), COALESCE(SUM(o.total), 0)
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- 4) refresh atomically with a name swap
BEGIN;
CREATE TABLE mv_user_summary_new AS SELECT ...;
DROP TABLE mv_user_summary;
ALTER TABLE mv_user_summary_new RENAME TO mv_user_summary;
COMMIT;
10

Triggers

Triggers BEFORE / AFTER

Les triggers BEFORE tournent avant l'écriture de la ligne — pour valider (RAISE ABORT) ou transformer les valeurs NEW. Les AFTER tournent une fois la ligne committée — pour l'audit, effets en cascade ou dénormalisation. FOR EACH ROW est la seule option (pas de triggers au niveau instruction). NEW contient la ligne entrante (INSERT/UPDATE) ; OLD la ligne antérieure (UPDATE/DELETE). La clause WHEN déclenche conditionnellement le trigger.

sqlite
-- AFTER INSERT: audit log
CREATE TRIGGER trg_users_audit
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_audit (user_id, action, at)
  VALUES (NEW.id, 'insert', datetime('now'));
END;

-- BEFORE INSERT: validate / normalize data
CREATE TRIGGER trg_users_lower_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'email cannot be empty')
  WHERE NEW.email = '';
END;

-- AFTER UPDATE: log changes
CREATE TRIGGER trg_users_update_log
AFTER UPDATE ON users
FOR EACH ROW
WHEN OLD.email IS NOT NEW.email
BEGIN
  INSERT INTO email_changes (user_id, old_email, new_email, at)
  VALUES (NEW.id, OLD.email, NEW.email, datetime('now'));
END;

Triggers pour effets en cascade

Les triggers peuvent maintenir automatiquement compteurs dénormalisés, tables de synthèse et timestamps updated_at. Attention : des chaînes de triggers récursives sont possibles (un trigger mettant à jour sa propre table), donc utilisez PRAGMA recursive_triggers pour contrôler cela (défaut OFF pour compatibilité). L'ordre des triggers multiples sur le même événement suit l'ordre de création ; pas de priorité BEFORE/AFTER entre triggers du même timing.

sqlite
-- maintain a denormalized counter
CREATE TRIGGER trg_orders_count_inc
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE users
  SET order_count = order_count + 1
  WHERE id = NEW.user_id;
END;

CREATE TRIGGER trg_orders_count_dec
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  UPDATE users
  SET order_count = order_count - 1
  WHERE id = OLD.user_id;
END;

-- keep an 'updated_at' column fresh
CREATE TRIGGER trg_users_touch
AFTER UPDATE ON users
FOR EACH ROW
WHEN NEW.updated_at IS OLD.updated_at
BEGIN
  UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;

Triggers INSTEAD OF (vues)

Les triggers INSTEAD OF tournent au lieu de l'INSERT/UPDATE/DELETE d'origine sur une vue, permettant de router les écritures vers une ou plusieurs tables sous-jacentes. C'est la façon canonique de rendre inscriptibles les vues complexes. Ils se déclenchent par ligne de la vue. Seuls les triggers INSTEAD OF peuvent se définir sur des vues ; les BEFORE/AFTER exigent des tables de base. Utile pour des couches d'abstraction façon API sur un schéma normalisé.

sqlite
-- make a multi-table view writable
CREATE VIEW order_detail AS
SELECT o.id AS order_id, o.total, u.username, i.product_name
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items i ON i.order_id = o.id;

CREATE TRIGGER trg_order_detail_update
INSTEAD OF UPDATE ON order_detail
FOR EACH ROW
BEGIN
  UPDATE orders SET total = NEW.total WHERE id = NEW.order_id;
  UPDATE order_items SET product_name = NEW.product_name
  WHERE order_id = NEW.order_id;
END;

-- now UPDATE on the view routes to both tables
UPDATE order_detail SET total = 99.99, product_name = 'Widget'
WHERE order_id = 5;

RAISE et gestion des erreurs

RAISE contrôle la propagation d'un échec de trigger : ABORT (le plus courant) annule l'instruction et annule ses changements, ROLLBACK annule toute la transaction, FAIL avorte l'instruction mais garde les instructions réussies antérieures dans la transaction, et IGNORE saute seulement la ligne fautive. RAISE(IGNORE) est une façon élégante de jeter silencieusement les lignes invalides pendant les chargements en masse.

sqlite
-- RAISE aborts the statement with a custom error
CREATE TRIGGER trg_check_age
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.age < 0
BEGIN
  SELECT RAISE(ABORT, 'age must be non-negative');
END;

-- ROLLBACK: undo the whole transaction, statement fails
CREATE TRIGGER trg_rollback_example
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN NEW.balance < 0
BEGIN
  SELECT RAISE(ROLLBACK, 'balance cannot go negative');
END;

-- FAIL: abort the current statement only (like ABORT)
-- IGNORE: skip this row, continue with the rest
CREATE TRIGGER trg_skip_invalid
BEFORE INSERT ON logs
FOR EACH ROW
WHEN NEW.level NOT IN ('info','warn','error')
BEGIN
  SELECT RAISE(IGNORE);
END;

Gérer les triggers

Les triggers se stockent dans sqlite_master (ou temp.sqlite_master pour les triggers TEMP). DROP TRIGGER les retire ; pas d'ALTER TRIGGER — supprimez et recréez. Les triggers TEMP sont locaux à la connexion et disparaissent à la déconnexion, utiles pour le débogage. Pas de moyen intégré de désactiver un trigger temporairement sauf le supprimer ; pour une exécution conditionnelle, utilisez la clause WHEN. Les triggers multiples sur le même événement se déclenchent dans l'ordre de création au sein de leur timing.

sqlite
-- list all triggers
SELECT name, tbl_name, sql FROM sqlite_master
WHERE type = 'trigger';

-- list triggers on a specific table
SELECT name, sql FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'users';

-- drop a trigger
DROP TRIGGER IF EXISTS trg_users_audit;

-- triggers in a TEMP schema (connection-local)
CREATE TEMP TRIGGER trg_temp_log
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  SELECT 'inserted ' || NEW.id;
END;

-- trigger timing order: BEFORE triggers fire in
-- creation order, then AFTER triggers in creation order.
11

Réglages PRAGMA

Bases de PRAGMA

PRAGMA est l'interface de configuration propriétaire de SQLite — les réglages contrôlent journalisation, cache, clés étrangères, intégrité et planificateur de requêtes. Les réglages sont à portée de session (par connexion) sauf indication contraire de la doc ; les régler dans une transaction peut être ignoré. Certains PRAGMA (journal_mode, page_size) ne prennent effet qu'hors transactions ou avant la création de la table. Utilisez PRAGMA compile_options pour voir ce que votre build supporte.

sqlite
-- PRAGMA is SQLite's configuration knob (CLI and APIs)
-- read a setting
PRAGMA journal_mode;
PRAGMA cache_size;
PRAGMA foreign_keys;

-- set a setting (session scope unless noted)
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -20000;   -- negative = KB, positive = pages
PRAGMA temp_store = MEMORY;

-- many PRAGMAs are no-ops below a certain SQLite version
-- check the compile-time options
PRAGMA compile_options;
-- e.g. ENABLE_FTS5, ENABLE_JSON1, MAX_ATTACHED, etc.

-- PRAGMAs are NOT part of SQL standard and are SQLite-only.

Clés étrangères

L'application des clés étrangères est OFF par défaut dans SQLite — un piège notoire pour les venus d'autres bases. Il faut l'activer à chaque connexion avec PRAGMA foreign_keys = ON ; impossible de le régler dans une transaction. Ce réglage existe parce que les vieux schémas (avant le support FK) casseraient sinon. Activez-le toujours en production. defer_foreign_keys aide quand on importe des données dans un ordre violant les dépendances.

sqlite
-- foreign keys are OFF by default in SQLite!
PRAGMA foreign_keys = ON;   -- enable per connection

-- check current state
PRAGMA foreign_keys;

-- inspect FK definitions on a table
PRAGMA foreign_key_list(orders);
-- returns: id, seq, table, from, to, on_update, on_delete, match

-- enable FKs in your app's connection setup, every time
-- (this is a frequent source of bugs when moving from MySQL/PG)

-- deferred FK checking (checked at COMMIT, not per statement)
PRAGMA defer_foreign_keys = ON;
-- or declare DEFERRABLE INITIALLY DEFERRED on the FK

Mode journal et WAL

WAL (Write-Ahead Logging) est le mode journal recommandé pour la plupart des apps : lecteurs et rédacteurs ne se bloquent pas mutuellement, et la durabilité est bonne. Il crée des fichiers satellites -wal et -shm à côté de la base principale. WAL persiste par fichier de base (réglez-le une fois). Utilisez wal_checkpoint(TRUNCATE) pour replier le contenu du WAL dans le fichier principal et récupérer l'espace. Évitez WAL sur filesystems réseau — il exige de la mémoire partagée.

sqlite
-- WAL: write-ahead logging (best for most apps)
PRAGMA journal_mode = WAL;
-- benefits: readers never block writers, writers never block
--           readers, crash recovery is fast
-- trade-off: creates -wal and -shm sidecar files

-- other modes: DELETE (default), TRUNCATE, PERSIST, MEMORY, OFF
PRAGMA journal_mode = DELETE;

-- WAL checkpoint: merge -wal back into the main file
PRAGMA wal_checkpoint;
PRAGMA wal_checkpoint(TRUNCATE);  -- also shrinks the -wal file

-- auto-checkpoint threshold (pages of WAL before auto-checkpoint)
PRAGMA wal_autocheckpoint = 1000;

-- WAL is persistent: setting it once keeps it for that DB file.

Synchronous et durabilité

synchronous contrôle l'agressivité des fsync de SQLite. FULL est le plus sûr (fsync à chaque commit) ; NORMAL est la valeur recommandée avec WAL (fsync au checkpoint, pas par commit, risque de corruption négligeable en pratique) ; OFF risque la corruption à la coupure de courant — seulement pour des données vraiment jetables ou des caches reconstruisibles. page_size doit se régler avant la création du schéma ; le changer ensuite exige un VACUUM.

sqlite
-- how thoroughly SQLite flushes to disk on commit
PRAGMA synchronous;        -- read current value
PRAGMA synchronous = FULL;       -- safest, slowest (default for rollback journal)
PRAGMA synchronous = NORMAL;     -- safe with WAL, fast (recommended for WAL)
PRAGMA synchronous = OFF;        -- fastest; corruption risk on power loss
PRAGMA synchronous = EXTRA;      -- even more careful than FULL

-- trade-off: durability vs speed
-- FULL: fsync at every commit (survives OS crash)
-- NORMAL (WAL): fsync at checkpoint, not every commit
-- OFF: no fsync — fast but may corrupt on power loss

-- page size (set BEFORE creating tables, ideally)
PRAGMA page_size = 4096;
PRAGMA page_size;

Cache, mémoire et temp store

cache_size est le cache de pages en mémoire — plus grand est généralement mieux pour les charges à forte lecture ; les valeurs négatives sont en Ko, positives en pages. temp_store = MEMORY déplace les résultats intermédiaires (tris, tables temp) en RAM, accélérant les grandes agrégations. mmap_size active l'I/O mappé en mémoire pour les lectures, pouvant accélérer les grandes bases en lecture seule. Réglez selon le budget mémoire et le pattern d'accès de votre app.

sqlite
-- page cache (negative = KB, positive = pages)
PRAGMA cache_size = -65536;   -- 64 MB cache
PRAGMA cache_size = 20000;    -- 20000 pages

-- where temp tables and intermediate results live
PRAGMA temp_store = DEFAULT;  -- compile-time default
PRAGMA temp_store = FILE;     -- temp files
PRAGMA temp_store = MEMORY;   -- all temp data in RAM

-- mmap memory-mapped I/O for reads
PRAGMA mmap_size = 268435456;  -- 256 MB

-- soft heap limit for the SQLite library
PRAGMA soft_heap_limit = 100000000;  -- 100 MB

-- store prepared statements in memory between calls
PRAGMA cache_spill;

Intégrité et infos de schéma

integrity_check valide toute la structure et le contenu de la base (lent mais complet) ; quick_check saute la validation du contenu et est bien plus rapide. Lancez integrity_check pendant les backups ou après des crashs. table_info rapporte les métadonnées de colonnes (table_xinfo pour les colonnes cachées comme le rowid de FTS5). index_list et index_info révèlent quels index existent et leurs colonnes. database_list montre les bases attachées.

sqlite
-- full integrity check (slow on large DBs)
PRAGMA integrity_check;
-- returns 'ok' or a list of problems

-- quick check (faster, less thorough)
PRAGMA quick_check;

-- list tables / schema
SELECT name, sql FROM sqlite_master WHERE type = 'table';

-- table info: columns, types, notnull, default, pk
PRAGMA table_info(users);
-- returns: cid, name, type, notnull, dflt_value, pk

-- extended table info (includes hidden columns from FTS/etc.)
PRAGMA table_xinfo(users);

-- index list with origin and unique flag
PRAGMA index_list(users);
PRAGMA index_info(idx_users_email);

-- database file header info
PRAGMA database_list;
12

Fonctions date et heure

date / time / datetime

SQLite n'a pas de type DATE natif ; les dates sont en TEXT ISO-8601 ('YYYY-MM-DD HH:MM:SS'). Les fonctions date/heure acceptent beaucoup de formats d'entrée (ISO, slashes, noms de mois, timestamps Unix via le modificateur 'unixepoch'). 'now' s'évalue une fois par instruction, pas par ligne — chaque ligne d'un UPDATE multi-lignes reçoit le même timestamp, généralement ce qu'on veut. Stockez en UTC, formatez pour l'affichage avec localtime.

sqlite
-- current date/time (UTC by default)
SELECT date('now');              -- '2024-06-15'
SELECT time('now');              -- '14:30:00'
SELECT datetime('now');          -- '2024-06-15 14:30:00'
SELECT julianday('now');         -- 2460476.1042 (days since 4713 BC)

-- parse various formats
SELECT date('2024-06-15');
SELECT date('2024/06/15');
SELECT date('June 15, 2024');
SELECT datetime('2024-06-15 14:30:00.123');
SELECT date('2024-06-15','unixepoch');  -- from Unix timestamp (seconds)

-- Unix timestamp to datetime
SELECT datetime(1718450400, 'unixepoch');
SELECT datetime(1718450400, 'unixepoch', 'localtime');

Formatage strftime

strftime est le formateur de dates universel — les codes de format rappellent le strftime du C. Utilisez-le pour extraire des parties d'une date (année, mois, jour de semaine) pour grouper, ou pour rendre un format d'affichage custom. Pour grouper par mois, strftime('%Y-%m', created) donne 'YYYY-MM' qui trie et groupe naturellement. SQLite n'a pas de DAYNAME()/MONTHNAME() ; combinez strftime('%w', ...) avec un CASE si vous voulez les noms de jours.

sqlite
-- strftime is the most flexible formatter
--   strftime(format, timestring, modifier, modifier, ...)
SELECT strftime('%Y-%m-%d', 'now');          -- '2024-06-15'
SELECT strftime('%Y-%m-%d %H:%M', 'now');    -- '2024-06-15 14:30'
SELECT strftime('%H:%M:%S', 'now');          -- '14:30:00'

-- common format codes
--   %Y year (4-digit)    %m month (01-12)   %d day (01-31)
--   %H hour (00-23)      %M minute (00-59)  %S second (00-59)
--   %j day of year       %w day of week (0=Sun)
--   %W week of year      %p AM/PM           %I hour (01-12)

-- get the day name
SELECT strftime('%w', '2024-06-15');   -- '6' (Saturday)
SELECT strftime('%Y-%m', 'now');       -- month bucket for grouping

Modificateurs (arithmétique)

Les modificateurs s'enchaînent de gauche à droite pour décaler ou aligner des dates. Les intervalles sont '+N unités' / '-N unités' (unités : days, hours, minutes, seconds, months, years). 'start of month/year/day' cale sur la frontière de période — parfait pour les rapports mensuels. 'weekday N' retourne la date du prochain jour de semaine N (aujourd'hui si ça matche). Combiner 'start of month', '+1 month', '-1 day' donne le dernier jour du mois courant — un idiome classique.

sqlite
-- shift a date by an interval
SELECT date('now', '+1 day');           -- tomorrow
SELECT date('now', '-1 month');         -- one month ago
SELECT date('now', '+1 year', '+1 day');-- next year + 1 day
SELECT datetime('now', '+2 hours', '+30 minutes');

-- start of period
SELECT date('now', 'start of month');       -- first day of month
SELECT date('now', 'start of year');        -- first day of year
SELECT date('now', 'start of day');         -- midnight today

-- weekday: next occurrence of a given weekday (0=Sun, 1=Mon, ... 6=Sat)
SELECT date('now', 'weekday 1');            -- next Monday
SELECT date('now', 'weekday 0', '+7 days'); -- Sunday after this one

-- combine modifiers
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- last day of month

Temps Unix et jour julien

Les timestamps Unix sont des entiers/secondes depuis l'epoch ; unixepoch() (3.38+) retourne directement un entier. Le jour julien est un compte continu de jours fractionnaires — soustraire deux valeurs julianday donne le temps écoulé en jours (multipliez par 86400 pour les secondes). Pour les calculs d'âge, diviser par 365,25 approxime les années bissextiles ; pour une précision légale, comparez explicitement les composantes année/mois/jour.

sqlite
-- Unix timestamp (seconds since 1970-01-01 UTC)
SELECT strftime('%s', 'now');                  -- current Unix time (text)
SELECT unixepoch('now');                       -- integer Unix time (SQLite 3.38+)
SELECT datetime(1718450400, 'unixepoch');      -- back to datetime

-- Julian day (fractional days since 4713-01-01 BC)
SELECT julianday('now');                       -- 2460476.6042
SELECT julianday('2024-06-15') - julianday('2024-06-10');  -- 5.0 (days between)

-- compute elapsed time in seconds
SELECT (julianday('now') - julianday('2024-01-01')) * 86400.0 AS seconds_elapsed;

-- human-readable age from a birthdate
SELECT CAST((julianday('now') - julianday('1990-05-20')) / 365.25 AS INT) AS age;

Fuseaux horaires et localtime

SQLite n'a pas de base de fuseaux — 'now' est UTC, et les modificateurs 'localtime'/'utc' s'appuient sur le réglage de fuseau de l'OS hôte. Le pattern recommandé : stockez toujours les timestamps en UTC (datetime('now') est UTC) et convertissez en heure locale seulement à l'affichage avec le modificateur 'localtime'. Cela évite l'ambiguïté entre serveurs de zones différentes et pendant les transitions d'heure d'été. Ne stockez jamais l'heure locale.

sqlite
-- 'now' is always UTC; convert to local time for display
SELECT datetime('now', 'localtime');          -- local wall-clock time
SELECT datetime('now');                       -- UTC

-- convert a stored UTC timestamp to local time
SELECT datetime(created, 'localtime') FROM users;

-- convert local time back to UTC for storage
SELECT datetime('2024-06-15 14:30:00', 'utc');

-- best practice: store UTC, format for display
CREATE TABLE events (ts TEXT DEFAULT (datetime('now')));  -- UTC
-- display:
SELECT datetime(ts, 'localtime') AS local_ts FROM events;

-- timezone is determined by the OS; SQLite has no TZ database.
13

Fonctions de chaînes

Longueur et sous-chaîne

length() compte les caractères pour TEXT et les octets pour BLOB — pour la longueur en octets UTF-8, castez d'abord en BLOB. substr() est indexé à partir de 1 ; un départ négatif compte depuis la fin. SQLite n'a pas de LPAD/RPAD natif — émulez avec concaténation et substr. Attention à la confusion octets-caractères en tronquant du texte multioctet : substr opère sur les caractères, donc sûr, mais le slicing au niveau octet ne l'est pas.

sqlite
SELECT length('hello');              -- 5
SELECT length('日本語');             -- 3 (characters, not bytes)
SELECT length(x'00ff');             -- 2 (blob length in bytes)

SELECT substr('hello world', 7);    -- 'world' (1-indexed)
SELECT substr('hello world', 1, 5); -- 'hello' (length 5)
SELECT substr('hello', -3);         -- 'llo' (negative = from end)
SELECT substr('hello', -3, 2);      -- 'll'

-- byte length (vs character length)
SELECT length('日本語');            -- 3 (characters)
SELECT length(CAST('日本語' AS BLOB)); -- 9 (UTF-8 bytes)

-- left/right pad (workaround; no native LPAD/RPAD)
SELECT substr('0000' || '42', -4, 4);  -- '0042'

Casse et rognage

upper()/lower() n'affectent que les lettres ASCII par défaut — les caractères non ASCII restent inchangés sauf si ICU est chargé. trim() retire les espaces de début/fin par défaut, ou les caractères précisés en second argument. replace() fait du remplacement littéral de sous-chaînes (pas de regex ni motifs). Pas de TRANSLATE() natif pour la substitution d'ensembles de caractères — enchaînez des replace() ou utilisez une fonction custom.

sqlite
SELECT upper('hello');       -- 'HELLO'
SELECT lower('HELLO');       -- 'hello'
SELECT upper('日本語');      -- '日本語' (no case to change)

SELECT trim('  hi  ');            -- 'hi'  (both sides)
SELECT ltrim('  hi  ');           -- 'hi   ' (left only)
SELECT rtrim('  hi  ');           -- '  hi' (right only)

-- trim specific characters
SELECT trim('xxhelloxx', 'x');    -- 'hello'
SELECT ltrim('aaabbb', 'a');      -- 'bbb'

-- replace characters
SELECT replace('a-b-c', '-', '/'); -- 'a/b/c'
SELECT replace('Hello World', 'o', '0'); -- 'Hell0 W0rld'

Concaténation et printf

L'opérateur || est le concaténateur SQL standard, mais il propage NULL (NULL || x = NULL) — enveloppez avec COALESCE quand des NULL sont possibles. printf()/format() apporte le formatage façon C et est la façon la plus propre de bâtir des chaînes avec bourrage, formatage de nombres et séparateurs de milliers. quote() retourne une chaîne quotée et échappée en sécurité pour l'insertion en SQL — utile quand on bâtit du SQL dynamique.

sqlite
-- || operator concatenates
SELECT 'Hello' || ' ' || 'World';      -- 'Hello World'
SELECT username || ' <' || email || '>' FROM users;

-- NULL propagates through || (NULL || 'x' IS NULL)
SELECT 'a' || NULL || 'b';             -- NULL
-- use COALESCE to handle NULLs:
SELECT COALESCE(username, '') || ' <' || COALESCE(email, '') || '>';

-- printf-style formatting (also called format())
SELECT printf('%s has %d orders', 'alice', 5);  -- 'alice has 5 orders'
SELECT format('%,d', 1234567);                   -- '1,234,567' (3.38+)
SELECT printf('%5.2f', 3.14159);                 -- ' 3.14'
SELECT printf('%04d', 42);                       -- '0042'

-- quote() escapes a string for SQL
SELECT quote("It's alive");   -- '''It''s alive'''

Découpage et recherche

instr() trouve la première occurrence d'une sous-chaîne (à partir de 1 ; 0 si absente). SQLite n'a pas de SPLIT_PART natif — émulez avec substr + instr ou une CTE récursive. L'idiome « compter les occurrences » soustrait les longueurs avant/après retrait du délimiteur. char() bâtit une chaîne depuis des points de code ; unicode() retourne le point de code du premier caractère. Pour un découpage complet de texte, chargez un tableau JSON via json_each.

sqlite
-- find position of substring (1-indexed; 0 if not found)
SELECT instr('hello world', 'world');   -- 7
SELECT instr('hello', 'xyz');           -- 0

-- emulate split by getting the Nth field
-- (no native SPLIT_PART; use a CTE or substring + instr)
SELECT
  substr('a,b,c,d', 1, instr('a,b,c,d', ',') - 1) AS first;  -- 'a'

-- count occurrences of a substring
SELECT (length('a,b,c,d') - length(replace('a,b,c,d', ',', ''))) / length(',') AS commas;
-- 3

-- reverse a string
SELECT reverse('hello');   -- 'olleh'

-- character from integer code
SELECT char(65, 66, 67);   -- 'ABC'
SELECT unicode('A');       -- 65

LIKE / GLOB / collations

LIKE est insensible à la casse pour l'ASCII par défaut (surprise fréquente) ; utilisez COLLATE BINARY pour la sensibilité. GLOB est toujours sensible à la casse et supporte des classes de caractères façon shell. NOCASE est une collation intégrée pour la comparaison insensible à la casse ; appliquez-la à l'index d'une colonne pour que les requêtes insensibles utilisent l'index. Pour le repli de casse non ASCII, chargez l'extension ICU ou stockez une colonne copie en minuscules avec un index.

sqlite
-- LIKE: case-insensitive (ASCII), wildcards % and _
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE username LIKE '_lice';

-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'Al%' COLLATE BINARY;

-- make LIKE case-insensitive for non-ASCII too (needs ICU)
-- or store a lowercased copy and query that
SELECT * FROM users WHERE LOWER(username) = LOWER('Alice');

-- GLOB: case-sensitive, Unix-style (* ? [abc] [a-z])
SELECT * FROM users WHERE username GLOB 'A*';
SELECT * FROM users WHERE username GLOB '[A-D]*';

-- built-in collations: BINARY (default), NOCASE, RTRIM
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);
SELECT * FROM users WHERE username = 'ALICE' COLLATE NOCASE;

-- COLLATE in ORDER BY
SELECT * FROM users ORDER BY username COLLATE NOCASE;
14

Fonctions JSON

Extraire des valeurs

json_extract (et les opérateurs -> / ->>, 3.38+) sont les chevaux de bataille de la lecture JSON. $ est la racine ; $.key accède à une propriété d'objet ; $.arr[i] à un élément de tableau (indexé à 0). ->> retourne un scalaire (texte/entier/réel), -> du JSON (donc '$.b' sur un tableau donne le tableau en JSON). Stockez les champs JSON fréquemment interrogés comme colonnes ordinaires, ou ajoutez un index d'expression sur json_extract pour la performance.

sqlite
-- json_extract: get a value by JSON path
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.a');       -- 1
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.b[1]');    -- 20
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.b');       -- '[10,20,30]'

-- -> returns JSON; ->> returns scalar (text/integer/etc.)
SELECT '{"a": 1, "b": "x"}' -> '$.a';   -- 1 (as JSON)
SELECT '{"a": 1, "b": "x"}' ->> '$.a';  -- 1 (as scalar)
SELECT '{"a": 1, "b": "x"}' ->> '$.b';  -- 'x' (as text)

-- nested paths
SELECT json_extract('{"u": {"name": "alice"}}', '$.u.name');   -- 'alice'

-- array length
SELECT json_array_length('[1, 2, 3]');  -- 3
SELECT json_array_length('{"a": [1,2]}', '$.a');  -- 2

-- get object keys
SELECT json_each.name FROM json_each('{"a":1,"b":2}');  -- 'a', 'b'

Bâtir du JSON

json_object et json_array construisent du JSON depuis des valeurs SQL (les NULL deviennent du JSON null, pas omis). json_group_array et json_group_object (3.38+) sont des fonctions d'agrégation bâtissant du JSON depuis les lignes de requête — inestimables pour produire des API JSON directement depuis SQL. Imbriquez les constructeurs pour bâtir des documents complexes. Utilisez json_quote pour insérer une chaîne en toute sécurité comme littéral de chaîne JSON.

sqlite
-- build a JSON object
SELECT json_object('id', 1, 'name', 'alice', 'active', 1);
-- {"id":1,"name":"alice","active":1}

-- build a JSON array
SELECT json_array(1, 2, 3, 'four', NULL);
-- [1,2,3,"four",null]

-- nest them
SELECT json_object(
  'user', json_object('id', 1, 'name', 'alice'),
  'roles', json_array('admin', 'editor')
);

-- aggregate rows into a JSON array
SELECT json_group_array(username) FROM users WHERE active = 1;
-- ["alice","bob","carol"]

-- aggregate into a JSON object (key:value)
SELECT json_group_object(username, email) FROM users WHERE active = 1;
-- {"alice":"[email protected]","bob":"[email protected]"}

-- quote a string as JSON
SELECT json_quote('hello');   -- '"hello"'

Modifier du JSON

json_set/insert/replace distinguent le comportement insertion-vs-mise-à-jour ; json_remove supprime des chemins. Ils retournent de NOUVELLES valeurs JSON — le JSON de SQLite est immuable, donc pour persister les changements faites un UPDATE de la colonne avec le résultat : UPDATE t SET data = json_set(data, '$.x', 1) WHERE id = 5. json_patch (RFC 7396) fusionne les objets ; les valeurs null du patch suppriment les clés. Pour les gros documents, envisagez de stocker les champs chauds comme colonnes séparées.

sqlite
-- json_set: insert or update a path
SELECT json_set('{"a": 1}', '$.b', 2);
-- {"a":1,"b":2}
SELECT json_set('{"a": 1}', '$.a', 99);
-- {"a":99}

-- json_insert: insert only if path does NOT exist
SELECT json_insert('{"a": 1}', '$.a', 99, '$.b', 2);
-- {"a":1,"b":2}  (a unchanged because it exists)

-- json_replace: update only if path EXISTS
SELECT json_replace('{"a": 1}', '$.a', 99, '$.b', 2);
-- {"a":99}  (b not added because it doesn't exist)

-- json_remove: delete a path
SELECT json_remove('{"a": 1, "b": 2}', '$.b');
-- {"a":1}

-- json_patch: merge per RFC 7396
SELECT json_patch('{"a": 1, "b": 2}', '{"b": 3, "c": 4}');
-- {"a":1,"b":3,"c":4}

Interroger des tableaux JSON

json_each et json_tree sont des fonctions à valeur de table virtuelle qui expandent le JSON en lignes. json_each gère un seul tableau/objet ; json_tree parcourt récursivement toute la structure, exposant les chemins fullkey. Cela débloque les opérations ensemblistes sur les tableaux JSON — filtrage, jointures et agrégation. Pour interroger des tableaux d'objets dans une colonne, utilisez une jointure façon LATERAL : FROM users u, json_each(u.tags) e.

sqlite
-- json_each: virtual table, one row per array element
SELECT value FROM json_each('[10, 20, 30]');
-- 10, 20, 30

-- with index
SELECT key, value FROM json_each('["a","b","c"]');
-- 0,'a'  1,'b'  2,'c'

-- query an array stored in a column
SELECT u.username, e.value AS tag
FROM users u, json_each(u.tags) e
WHERE e.value LIKE 'admin%';

-- aggregate back to JSON
SELECT json_group_array(value)
FROM json_each('[10, 20, 30]')
WHERE value > 15;
-- [20,30]

-- json_tree: recursively walk nested structures
SELECT fullkey, value
FROM json_tree('{"a": {"b": [1, 2]}}');
-- '$','{"a":{"b":[1,2]}}'
-- '$.a','{"b":[1,2]}'
-- '$.a.b[0]',1
-- '$.a.b[1]',2

Indexation et validation JSON

Indexez les expressions json_extract pour des lookups rapides de champs JSON — la requête doit utiliser l'expression identique pour en profiter. json_valid() retourne 1/0 ; utilisez-le dans une contrainte CHECK pour imposer des colonnes JSON seulement. json_type() rapporte le type JSON à un chemin ('object', 'array', 'integer', 'real', 'string', 'boolean', 'null'). Pour de lourdes charges JSON, dénormalisez les champs chauds en colonnes indexées ordinaires et gardez le JSON pour la queue flexible.

sqlite
-- expression index on a JSON field for fast lookups
CREATE INDEX idx_events_type
ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';

-- unique index on a JSON field
CREATE UNIQUE INDEX idx_users_email_json
ON users(json_extract(profile, '$.email'));

-- validate JSON
SELECT json_valid('{"a": 1}');     -- 1
SELECT json_valid('{a: 1}');       -- 0 (unquoted keys)

-- JSON type of a path
SELECT json_type('{"a": 1, "b": [1,2]}', '$.a');  -- 'integer'
SELECT json_type('{"a": 1, "b": [1,2]}', '$.b');  -- 'array'

-- check constraint for valid JSON columns
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  data TEXT CHECK (json_valid(data))
);
15

Fonctions de fenêtre

Bases de la clause OVER

Les fonctions de fenêtre (SQLite 3.25+) calculent sur un ensemble de lignes liées à la ligne courante, sans les replier comme le fait GROUP BY. OVER () signifie « toutes les lignes » ; OVER (ORDER BY ...) définit l'ordre pour le classement. ROW_NUMBER est unique ; RANK laisse des trous après les égalités ; DENSE_RANK non. Les fonctions de fenêtre apparaissent en SELECT et ORDER BY, pas WHERE — filtrez avec une requête externe ou une CTE.

sqlite
-- window functions: aggregate OVER a 'window' of rows
-- without collapsing rows (unlike GROUP BY)
SELECT username, age,
  AVG(age) OVER () AS overall_avg
FROM users;

-- ROW_NUMBER: unique sequential number per row
SELECT username,
  ROW_NUMBER() OVER (ORDER BY created) AS row_num
FROM users;

-- RANK / DENSE_RANK
SELECT username, age,
  RANK()       OVER (ORDER BY age DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank
FROM users;

PARTITION BY

PARTITION BY divise les lignes en groupes pour la fonction de fenêtre, comme GROUP BY mais sans replier — chaque ligne garde son identité tout en recevant un agrégat par groupe. Le motif « top N par groupe » (ROW_NUMBER + WHERE externe rn <= N) est la solution canonique de « top 3 commandes par client », « 5 derniers articles par auteur », etc. Généralement bien plus rapide que les sous-requêtes corrélées.

sqlite
-- per-group ranking / aggregation
SELECT username, role, age,
  ROW_NUMBER() OVER (PARTITION BY role ORDER BY age DESC) AS rank_in_role,
  AVG(age)     OVER (PARTITION BY role)                   AS avg_role_age
FROM users;

-- top N per group (common pattern)
SELECT * FROM (
  SELECT user_id, total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM orders
) WHERE rn <= 3;

-- percent of group total
SELECT username, role,
  age * 1.0 / SUM(age) OVER (PARTITION BY role) AS pct_of_role_total
FROM users;

Spécifications de cadre

Un cadre définit quelles lignes la fonction de fenêtre voit, relativement à la ligne courante. UNBOUNDED PRECEDING..CURRENT ROW donne un total courant. N PRECEDING..N FOLLOWING une fenêtre glissante (moyenne mobile). Le cadre par défaut avec ORDER BY utilise RANGE, qui inclut les lignes homologues (même valeur d'ORDER BY) ; ROWS compte strictement les lignes. Mal comprendre cadres vs ORDER BY est une source courante d'analytique incorrecte.

sqlite
-- RUNNING TOTAL: cumulative sum over rows so far
SELECT username, total,
  SUM(total) OVER (ORDER BY created
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders;

-- MOVING AVERAGE: 3-row window centered on current row
SELECT username, total,
  AVG(total) OVER (ORDER BY created
                   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM orders;

-- default frame for ORDER BY windows:
--   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- default frame (no ORDER BY):
--   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- ROWS vs RANGE: ROWS counts rows; RANGE includes peers.

LAG et LEAD

LAG et LEAD accèdent à d'autres lignes relativement à la courante — essentiels pour l'analyse de séries temporelles (deltas jour sur jour, détection de séries). Le troisième argument optionnel est un défaut pour les lignes hors plage. FIRST_VALUE/LAST_VALUE retournent des valeurs des bords du cadre ; notez que LAST_VALUE exige un cadre explicite UNBOUNDED FOLLOWING sinon il retourne la valeur de la ligne courante (un piège classique).

sqlite
-- compare current row to previous / next row
SELECT username, created,
  LAG(created, 1)  OVER (ORDER BY created) AS prev_created,
  LEAD(created, 1) OVER (ORDER BY created) AS next_created
FROM users;

-- difference from previous value (time-series analysis)
SELECT date, sales,
  sales - LAG(sales, 1) OVER (ORDER BY date) AS day_over_day
FROM daily_sales;

-- LAG with default value and offset
SELECT username, created,
  LAG(username, 3, 'N/A') OVER (ORDER BY created) AS three_back
FROM users;

-- FIRST_VALUE / LAST_VALUE / NTH_VALUE
SELECT username, age,
  FIRST_VALUE(age) OVER (ORDER BY age) AS youngest,
  LAST_VALUE(age)  OVER (ORDER BY age
                         ROWS BETWEEN UNBOUNDED PRECEDING
                         AND UNBOUNDED FOLLOWING) AS oldest
FROM users;

NTILE et percentiles

NTILE(n) découpe les lignes ordonnées en n seaux grossièrement égaux — 4 pour les quartiles, 100 pour les percentiles, 10 pour les deciles. CUME_DIST est la fraction des lignes de valeur <= à la ligne courante ; PERCENT_RANK le rang relatif en fraction 0..1. Ce sont les briques des requêtes statistiques/analytiques. Comme toutes les fonctions de fenêtre, elles ne replient pas les lignes — enveloppez dans une requête externe pour filtrer.

sqlite
-- divide rows into N equal buckets (quartiles, percentiles)
SELECT username, age,
  NTILE(4) OVER (ORDER BY age) AS quartile
FROM users;

-- CUME_DIST: cumulative distribution (0..1)
SELECT username, age,
  CUME_DIST() OVER (ORDER BY age) AS cume_dist
FROM users;

-- PERCENT_RANK: rank as a fraction (0..1)
SELECT username, age,
  PERCENT_RANK() OVER (ORDER BY age) AS pct_rank
FROM users;

-- NTH_VALUE: value at a specific row in the frame
SELECT username, age,
  NTH_VALUE(age, 2) OVER (ORDER BY age) AS second_age
FROM users;
16

CTE (clause WITH)

CTE de base

Une CTE (Common Table Expression) définie avec WITH est une sous-requête nommée et délimitée — elle améliore la lisibilité et peut se référencer plusieurs fois dans la même instruction. SQLite matérialise typiquement les CTE (les écrit dans une table temp), donc la performance est similaire à une table dérivée. Utilisez les CTE pour découper les requêtes complexes en étapes, pré-agréger ou éviter de répéter des sous-requêtes. C'est du SQL standard et portable.

sqlite
-- a CTE is a named temporary result set, scoped to one statement
WITH active_user_count AS (
  SELECT COUNT(*) AS n FROM users WHERE active = 1
)
SELECT username,
  (SELECT n FROM active_user_count) AS total_active
FROM users WHERE active = 1;

-- CTE in a join
WITH user_totals AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
)
SELECT u.username, COALESCE(t.spent, 0) AS spent
FROM users u
LEFT JOIN user_totals t ON t.user_id = u.id
ORDER BY spent DESC;

-- CTEs make complex queries readable; they are syntactic sugar
-- (SQLite often materializes them, like a temp table).

CTE multiples

Plusieurs CTE séparées par virgules peuvent chacune référencer les CTE antérieures du même WITH — idéal pour des pipelines de données par étapes en une instruction. Chaque CTE est une étape logique ; lire de haut en bas reflète le flux de données. Bien plus maintenable que des sous-requêtes imbriquées. SQLite matérialise chaque CTE ; pour des résultats intermédiaires très grands, considérez des tables temp entre instructions plutôt qu'une chaîne géante de CTE.

sqlite
-- chain multiple CTEs, each able to reference earlier ones
WITH
active_users AS (
  SELECT id, username FROM users WHERE active = 1
),
user_orders AS (
  SELECT user_id, COUNT(*) AS n_orders, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
),
labeled AS (
  SELECT au.username,
    COALESCE(uo.n_orders, 0) AS orders,
    COALESCE(uo.spent, 0)    AS spent
  FROM active_users au
  LEFT JOIN user_orders uo ON uo.user_id = au.id
)
SELECT * FROM labeled
WHERE orders > 0
ORDER BY spent DESC;

CTE récursive

Les CTE récursives ont une ancre (cas de base) et un membre récursif joints par UNION (dédup) ou UNION ALL. C'est la façon standard SQL de traverser des arbres (organigrammes, arbres de catégories, commentaires en fils) et graphes. SQLite plafonne la profondeur de récursion (défaut 1000 via les limites SQLITE_MAX_TRIGGER_DEPTH/limit). L'astuce « generate series » est inestimable puisque SQLite manque de GENERATE_SERIES (bien que l'extension series existe).

sqlite
-- a recursive CTE references itself; perfect for hierarchies
WITH RECURSIVE subordinates AS (
  -- anchor: top-level manager
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- recursive: their direct reports, one level deeper
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT depth, id, name FROM subordinates ORDER BY depth, name;

-- generate a series of numbers (no native GENERATE_SERIES)
WITH RECURSIVE cnt(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cnt WHERE n < 10
)
SELECT n FROM cnt;

Requêtes hiérarchiques

Les CTE récursives brillent sur les tables auto-référencées. Le premier exemple bâtit un chemin délimité par slashes de la racine à chaque nœud — utile pour les breadcrumbs. Le second trouve tous les descendants d'un nœud (sous-arbre). Utilisez UNION (pas UNION ALL) si des cycles sont possibles pour prévenir les boucles infinies ; sinon UNION ALL est plus rapide. Pour des arbres très profonds, surveillez la performance — les CTE récursives peuvent être lentes sans index approprié sur la colonne de jointure.

sqlite
-- categories with parent/child self-reference
CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  parent_id INTEGER REFERENCES categories(id)
);

-- full path from root to each node
WITH RECURSIVE cat_path(id, name, path) AS (
  SELECT id, name, name
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, cp.path || ' > ' || c.name
  FROM categories c
  JOIN cat_path cp ON c.parent_id = cp.id
)
SELECT id, path FROM cat_path ORDER BY path;

-- find all descendants of a node
WITH RECURSIVE descendants(id) AS (
  SELECT id FROM categories WHERE id = 5   -- root of subtree
  UNION ALL
  SELECT c.id FROM categories c
  JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM categories WHERE id IN (SELECT id FROM descendants);

Optimisation des CTE

SQLite 3.35+ supporte les indices MATERIALIZED / NOT MATERIALIZED pour contrôler l'évaluation des CTE. MATERIALIZED force une écriture en table temp (bien pour les CTE coûteuses référencées plusieurs fois) ; NOT MATERIALIZED inline la CTE pour que l'optimiseur puisse pousser les prédicats (bien pour les CTE bon marché). Le planificateur choisit généralement bien tout seul ; n'utilisez les indices que si EXPLAIN QUERY PLAN montre un mauvais plan. Les CTE récursives se matérialisent toujours.

sqlite
-- MATERIALIZED: force SQLite to materialize (temp table)
WITH cte AS MATERIALIZED (
  SELECT user_id, SUM(total) AS spent FROM orders GROUP BY user_id
)
SELECT u.username, cte.spent
FROM users u JOIN cte ON cte.user_id = u.id;

-- AS NOT MATERIALIZED: inline the CTE (may run multiple times)
WITH cte AS NOT MATERIALIZED (
  SELECT COUNT(*) FROM users WHERE active = 1
)
SELECT * FROM products WHERE id < (SELECT * FROM cte);

-- choose MATERIALIZED when the CTE is expensive and used
-- multiple times; NOT MATERIALIZED when it's cheap or used
-- once and could benefit from predicate pushdown.

-- recursive CTEs are always materialized.
17

Backup et restauration

.dump (backup texte)

.dump exporte la base en instructions SQL INSERT simples — lisible, diffable et portable entre versions et architectures SQLite. C'est le format de backup le plus compatible. Restaurer rejoue le SQL dans une base neuve. --nosys saute sqlite_sequence et autres tables internes. Pour les grandes bases, .dump est lent et produit des fichiers énormes ; utilisez .backup (copie binaire) ou l'API Online Backup à la place.

sqlite
-- dump the entire database as SQL text (portable)
sqlite3 mydb.sqlite .dump > backup.sql

-- dump a single table
sqlite3 mydb.sqlite ".dump users" > users.sql

-- dump only data (no schema)
sqlite3 mydb.sqlite ".dump" | grep '^INSERT' > data.sql

-- restore from a dump
sqlite3 newdb.sqlite < backup.sql
-- or inside the CLI:
.read backup.sql

-- dump in a specific order to satisfy FK constraints
sqlite3 mydb.sqlite ".dump --nosys" > backup.sql

-- .dump works on attached databases too
ATTACH 'archive.sqlite' AS archive;
.dump archive

.backup (copie binaire)

.backup effectue une copie binaire en ligne sûre via l'API Online Backup de SQLite — elle marche même pendant que des rédacteurs sont actifs, produisant un snapshot transactionnellement cohérent. C'est la façon recommandée de backuper une base en marche. La sortie est un fichier SQLite valide, donc la restauration n'est qu'une copie de fichier. Contrairement à .dump, le format binaire est compatible version/architecture mais illisible pour l'humain.

sqlite
-- binary copy to another file (online, safe with active writers)
sqlite3 mydb.sqlite ".backup 'backup.sqlite'"

-- inside the CLI
.backup backup.sqlite
.backup main backup.sqlite     -- source 'main', target file

-- backup to an attached database
ATTACH 'backup.sqlite' AS bk;
.backup main bk;
DETACH bk;

-- .backup uses the Online Backup API internally: it copies
-- page-by-page and handles concurrent writers safely, so it's
-- the right choice for live production backups.

-- restore = just copy the file back (or use it directly)
cp backup.sqlite mydb.sqlite

API Online Backup

L'API Online Backup (sqlite3_backup_*) est la méthode de backup étalon-or : elle copie une base vivante page par page, gère correctement les écritures concurrentes et produit un snapshot cohérent. sqlite3.Connection.backup() de Python et .backup() de better-sqlite3 en Node l'enveloppent. Elle marche en mode WAL. Une copie brute de fichier peut produire un backup corrompu si une écriture est en cours — ne copiez le fichier que sur une base arrêtée ou avec des snapshots au niveau fichier.

sqlite
-- the sqlite3 CLI's .backup uses this C API; in Python:
import sqlite3
src = sqlite3.connect("mydb.sqlite")
dst = sqlite3.connect("backup.sqlite")
src.backup(dst)
dst.close()
src.close()

-- with progress callback
def progress(remaining, total):
    print(f"{total - remaining}/{total} pages copied")
src.backup(dst, pages=progress)

-- this is the safest backup method: it's online (doesn't block
-- writers), transactionally consistent, and works on WAL-mode
-- databases. Prefer it over raw file copy for production.

-- Node.js (better-sqlite3):
const backup = db.backup('backup.sqlite');
while (!backup.completed) backup.step(-1);
backup.delete();

Attacher des bases

ATTACH DATABASE amène jusqu'à 10 fichiers de base dans une connexion comme schémas nommés (main, temp, plus attachées). On peut interroger, joindre et copier des données entre eux avec des noms qualifiés par schéma (main.users, archive.orders). C'est le socle de l'archivage (déplacer les vieilles lignes vers un fichier d'archive), du sharding et des workflows de fusion. DETACH ferme le schéma mais laisse le fichier intact. Les transactions inter-bases sont atomiques en mode WAL.

sqlite
-- work with multiple DB files in one connection
ATTACH DATABASE 'archive.sqlite' AS archive;
ATTACH DATABASE 'lookup.sqlite' AS lookup;

-- query across databases
SELECT u.username, a.note
FROM main.users u
JOIN archive.old_users a ON u.id = a.id;

-- copy a table between databases
CREATE TABLE archive.users_copy AS
SELECT * FROM main.users;

-- move data: insert into one DB, delete from another
INSERT INTO archive.orders SELECT * FROM main.orders WHERE created < '2023-01-01';
DELETE FROM main.orders WHERE created < '2023-01-01';

-- list attached databases
.databases

-- detach (does NOT delete the file)
DETACH DATABASE archive;

-- limit: 10 attached databases (compile-time SQLITE_MAX_ATTACHED).

Récupération et corruption

integrity_check détecte la corruption structurelle ; .recover (3.29+) est le meilleur outil pour sauver des données d'un fichier corrompu — il extrait les lignes lisibles et saute les mauvaises pages, produisant du SQL à rediriger vers une base neuve. Si .recover échoue, retombez sur .dump en acceptant une perte partielle. La plupart des corruptions viennent d'opérations de fichiers non sûres (copier une DB vivante, filesystems réseau, coupure avec synchronous=OFF). Backupez toujours avant toute tentative de récupération.

sqlite
-- check integrity
PRAGMA integrity_check;
PRAGMA quick_check;

-- recover from a corrupt database (best effort)
sqlite3 corrupt.sqlite ".recover" > recovered.sql
sqlite3 clean.sqlite < recovered.sql

-- .recover extracts everything readable, skipping corrupt pages

-- if .recover fails, try dumping what you can
sqlite3 corrupt.sqlite ".dump" > partial.sql 2>errors.txt
sqlite3 clean.sqlite < partial.sql

-- point-in-time from a backup file
cp nightly_backup.sqlite mydb.sqlite

-- after a crash, WAL is replayed automatically on next open;
-- if stuck, checkpoint manually:
sqlite3 mydb.sqlite "PRAGMA wal_checkpoint(TRUNCATE);"

-- prevent corruption: never overwrite a live DB file,
-- never share a DB on a network filesystem without WAL off.
18

Import et export

Import CSV

.import lit du CSV dans une table ; si la table n'existe pas, SQLite la crée avec toutes les colonnes en TEXT (souvent pas ce qu'on veut — créez d'abord la table avec les bons types). --skip N saute les lignes d'en-tête. .mode csv configure le parseur pour gérer les champs quotés avec virgules et retours à la ligne intégrés. Pour les gros imports, enveloppez dans BEGIN/COMMIT et PRAGMA synchronous=OFF (temporairement) pour un gain de 10x+. Désactivez les index pendant les chargements et recréez ensuite.

sqlite
-- import a CSV file into a table
.mode csv
.import users.csv users
-- ^ creates 'users' table with text columns named from the header row

-- import into an EXISTING table (column order must match)
.mode csv
.import --skip 1 users.csv users   -- --skip 1 to skip header

-- recommended: create the table first with proper types
CREATE TABLE users (id INTEGER, name TEXT, email TEXT);
.mode csv
.import --skip 1 users.csv users

-- from the shell, one-shot
sqlite3 mydb.sqlite ".mode csv" ".import users.csv users"

-- handle quotes/escapes
.mode csv
.separator ,
.import data.csv mytable

Export CSV

.mode csv avec .headers on produit un CSV standard avec ligne d'en-tête — redirigez vers un fichier avec .output. Les flags shell -header -csv plus la redirection le font en une ligne sans entrer dans le CLI. Pour du TSV, utilisez .mode list avec .separator "\t". Pour les gros exports, c'est streamé et économe en mémoire. Pour exporter plusieurs tables, changez .output entre les requêtes.

sqlite
-- export a query to CSV
.mode csv
.headers on
.output users.csv
SELECT id, username, email FROM users;
.output stdout

-- from the shell, one-shot
sqlite3 mydb.sqlite -header -csv \
  "SELECT id, username, email FROM users" > users.csv

-- export all rows of a table
.mode csv
.headers on
.output products.csv
SELECT * FROM products;
.output stdout

-- custom separator
.mode list
.separator "|"
.output users.txt
SELECT id, username FROM users;
.output stdout

Export JSON

.mode json émet un objet JSON par ligne (pas de tableau englobant). Pour un vrai tableau JSON, utilisez json_group_array(json_object(...)) — cela donne un contrôle total sur les noms de champs et l'imbrication, et marche pour des tableaux imbriqués via des sous-requêtes corrélées. C'est un moyen puissant de bâtir des API JSON directement depuis SQL sans ORM. Pour de grands ensembles de résultats, streamez .mode json ligne par ligne plutôt que de bâtir un géant tableau en mémoire.

sqlite
-- export a query as JSON (one JSON object per row)
.mode json
.output users.json
SELECT id, username, email FROM users;
.output stdout

-- build a single JSON array with json_group_array
SELECT json_group_array(json_object(
  'id', id, 'username', username, 'email', email
)) AS users_json
FROM users;
-- [{"id":1,"username":"alice","email":"[email protected]"},...]

-- nested JSON (orders with their items)
SELECT json_object(
  'order_id', o.id,
  'total', o.total,
  'items', (SELECT json_group_array(json_object('name', i.name, 'qty', i.qty))
            FROM order_items i WHERE i.order_id = o.id)
) AS order_json
FROM orders o;

Importer du JSON

SQLite n'a pas de .import pour le JSON — chargez le fichier en TEXT (via code applicatif ou .read avec des INSERT) puis parsez avec json_each/json_extract. json_each(tableau) expand un tableau en lignes pour une insertion ensembliste. Pour de gros fichiers JSON, parsez en streaming dans votre application (ne chargez pas tout le fichier en mémoire). Validez avec json_valid() au chargement et utilisez une contrainte CHECK sur les colonnes JSON pour l'intégrité continue.

sqlite
-- load a JSON file into a text column, then parse
CREATE TABLE raw (data TEXT);
-- (no built-in .import for JSON; load via CLI or app)

-- once loaded, parse with json_each (array) or json_extract (object)
SELECT json_extract(data, '$.name') AS name,
       json_extract(data, '$.age')  AS age
FROM raw;

-- expand a JSON array into rows
INSERT INTO users (username, email)
SELECT json_extract(value, '$.username'),
       json_extract(value, '$.email')
FROM raw, json_each(raw.data);

-- import via Python
import sqlite3, json
db = sqlite3.connect("mydb.sqlite")
with open("users.json") as f:
    for row in json.load(f):
        db.execute("INSERT INTO users(username,email) VALUES(?,?)",
                   (row["username"], row["email"]))
db.commit()

Excel et autres formats

Pour Excel, exportez du CSV (Excel l'ouvre directement) ; .mode excel (3.36+) ajoute un BOM pour que l'UTF-8 s'affiche correctement. .mode markdown produit du markup de table collable pour les docs. .mode insert génère des instructions INSERT — génial pour amorcer des bases de test ou migrer des données entre schémas. .mode quote quotte chaque valeur, utile quand les données contiennent virgules/retours/guillemets susceptibles de troubler d'autres outils. .mode table/box dessine des tables ASCII pour les terminaux.

sqlite
-- Excel: export CSV (Excel opens it) or use .mode excel
.mode csv
.headers on
.output report.csv
SELECT * FROM monthly_report;
.output stdout

-- .mode excel (3.36+): CSV with BOM, friendly to Excel
.mode excel
.output report.csv
SELECT * FROM monthly_report;
.output stdout

-- .mode markdown: GitHub-flavored markdown tables
.mode markdown
SELECT id, username, email FROM users LIMIT 5;

-- .mode insert: generate INSERT statements
.mode insert users
.output seed.sql
SELECT id, username, email FROM users;
.output stdout
-- produces: INSERT INTO "users" VALUES(1,'alice','[email protected]');

-- .mode quote: CSV with all values quoted
.mode quote
.output safe.csv
SELECT * FROM users;
.output stdout
19

Intégration applicative

Python (sqlite3)

sqlite3 de Python est intégré à la bibliothèque standard. Utilisez TOUJOURS les placeholders ? (ou :nom) — jamais de f-strings ni % — pour prévenir l'injection SQL et gérer correctement le quoting. executemany est bien plus rapide que la boucle execute pour les inserts en masse. Réglez row_factory = sqlite3.Row pour l'accès par nom de colonne. Utilisez les gestionnaires de contexte (with conn:) pour commit/rollback automatiques. Pour la concurrence, une connexion par thread (sqlite3 interdit le partage inter-thread par défaut).

sqlite
import sqlite3

# connect (creates the file if missing)
conn = sqlite3.connect("mydb.sqlite")
conn.row_factory = sqlite3.Row  # dict-like rows
cur = conn.cursor()

# parameterized query (NEVER use string formatting!)
cur.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    ("alice", "[email protected]"),
)
conn.commit()

# query with parameters
cur.execute("SELECT id, username FROM users WHERE active = ?", (1,))
for row in cur:
    print(row["id"], row["username"])

# named parameters
cur.execute(
    "SELECT * FROM users WHERE age >= :min_age",
    {"min_age": 18},
)

# bulk insert (fast)
cur.executemany(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    [("bob", "[email protected]"), ("carol", "[email protected]")],
)
conn.commit()
conn.close()

Node.js (better-sqlite3)

better-sqlite3 est synchrone (pas de plomberie callback/Promise) et le driver SQLite Node.js le plus rapide. Les prepared statements se réutilisent — preparez une fois, exécutez beaucoup. .transaction(fn) enveloppe la fonction dans BEGIN/COMMIT (ou ROLLBACK en cas de throw), et est drastiquement plus rapide que l'autocommit par instruction. Utilisez les placeholders ? pour éviter l'injection SQL. Activez WAL pour la concurrence. Pour des API basées Promise, enveloppez les appels ou utilisez sqlite/sqlite3 à la place.

sqlite
const Database = require("better-sqlite3");
const db = new Database("mydb.sqlite");
db.pragma("journal_mode = WAL");

// prepared statement (synchronous, fast)
const insert = db.prepare(
  "INSERT INTO users (username, email) VALUES (?, ?)"
);
const info = insert.run("alice", "[email protected]");
console.log(info.lastInsertRowid);

// query multiple rows
const select = db.prepare("SELECT id, username FROM users WHERE active = ?");
const rows = select.all(1);  // array of objects

// query one row
const one = select.get(1);

// transaction (atomic, fast)
const insertMany = db.transaction((users) => {
  for (const u of users) insert.run(u.username, u.email);
});
insertMany([{username: "bob"}, {username: "carol"}]);

db.close();

URI de connexion et modes

Les noms de fichiers URI (file:...) débloquent les modes lecture seule, cache partagé et mémoire — passez uri=True en Python ou la chaîne file: aux drivers Node. :memory: est par connexion par défaut ; file:name?mode=memory&cache=shared crée une base mémoire nommée partagée entre connexions du même processus (génial pour les tests). Réglez un busy_timeout pour que les rédacteurs attendant brièvement des verrous n'échouent pas immédiatement. Réglez toujours PRAGMA foreign_keys = ON par connexion.

sqlite
# open read-only
sqlite3.connect("file:mydb.sqlite?mode=ro", uri=True)

# open in-memory (vanishes on close)
sqlite3.connect(":memory:")
# or via URI:
sqlite3.connect("file::memory:", uri=True)

# shared in-memory database (visible across connections in same process)
sqlite3.connect("file:memdb1?mode=memory&cache=shared", uri=True)

# with a busy timeout (wait instead of erroring on locks)
conn = sqlite3.connect("mydb.sqlite", timeout=30)

# Node.js: same URI form
const db = new Database("file:mydb.sqlite?mode=ro", { readonly: true });

# Node.js in-memory
const mem = new Database(":memory:");

# Python: per-connection pragmas
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA foreign_keys = ON")

Transactions et concurrence

Les transactions groupent les instructions atomiquement — tout ou rien. SQLite autorise un seul rédacteur à la fois (verrou d'écriture au niveau base), mais le mode WAL permet des lecteurs concurrents. En Python, l'isolation_level par défaut enveloppe le DML dans des transactions implicites (commit requis). Pour un contrôle explicite, réglez isolation_level = None et émettez vous-même BEGIN/COMMIT. Utilisez un busy_timeout (PRAGMA busy_timeout ou timeout de connexion) pour que les rédacteurs attendant un verrou attendent plutôt que de lever immédiatement SQLITE_BUSY.

sqlite
# Python: explicit transaction
conn = sqlite3.connect("mydb.sqlite")
conn.isolation_level = None  # autocommit off -> manual BEGIN
cur = conn.cursor()
cur.execute("BEGIN")
try:
    cur.execute("UPDATE accounts SET bal = bal - 100 WHERE id = 1")
    cur.execute("UPDATE accounts SET bal = bal + 100 WHERE id = 2")
    cur.execute("COMMIT")
except Exception:
    cur.execute("ROLLBACK")
    raise

// Node.js (better-sqlite3) transaction
const transfer = db.transaction((from, to, amt) => {
  db.prepare("UPDATE accounts SET bal = bal - ? WHERE id = ?").run(amt, from);
  db.prepare("UPDATE accounts SET bal = bal + ? WHERE id = ?").run(amt, to);
});
transfer(1, 2, 100);

-- SQLite allows ONE writer at a time; many readers in WAL mode.
-- Use a busy_timeout to wait for locks instead of failing fast.

Prepared statements et sûreté

Les requêtes paramétrées (placeholders ?) sont la pratique de sécurité la plus importante — elles préviennent l'injection SQL et gèrent correctement la conversion de types et l'échappement. N'interpolez JAMAIS de saisie utilisateur dans des chaînes SQL. Les placeholders ne marchent que pour les valeurs ; les identifiants (noms de tables/colonnes) ne peuvent pas être paramétrés — validez-les contre une liste blanche. Pour LIKE, échappez % et _ dans la saisie utilisateur pour que les utilisateurs ne puissent pas injecter des wildcards. Cela s'applique dans chaque langage et driver.

sqlite
-- SQL injection vulnerability (NEVER do this):
-- app: "SELECT * FROM users WHERE name = '" + user_input + "'"
-- user_input = "'; DROP TABLE users; --"
-- result:    SELECT * FROM users WHERE name = ''; DROP TABLE users; --'

-- SAFE: parameterized query (placeholders are NOT string substitution)
-- Python:
cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))
-- Node.js:
stmt.get(user_input);

-- identifiers (table/column names) CANNOT be parameterized;
-- validate against a whitelist:
allowed_tables = {"users", "orders", "products"}
if table not in allowed_tables:
    raise ValueError("bad table")
cur.execute(f"SELECT * FROM {table} WHERE id = ?", (id_,))

-- LIKE with user input: escape wildcards
cur.execute(
    "SELECT * FROM users WHERE name LIKE ? ESCAPE '\'",
    (user_input.replace("\", "\\").replace("%", "\%").replace("_", "\_") + "%",)
)
20

Optimisation des performances

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN est l'outil de réglage n°1. « USING INDEX » = bien ; « SCAN » (scan complet de table) = généralement mauvais sur les grandes tables. Pour les jointures, vérifiez que les colonnes de jointure sont indexées et que le planificateur ne scanne pas la plus grande table. EXPLAIN (sans QUERY PLAN) montre le bytecode VDBE — rarement nécessaire mais utile pour un débogage profond. La commande expérimentale EXPERT peut suggérer des index (3.36+), ou utilisez la dot-command .expert du CLI.

sqlite
-- see how SQLite executes a query (essential for tuning)
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';

-- good: uses an index
--   SEARCH users USING INDEX idx_users_email (email=?)

-- bad: full table scan
--   SCAN users

-- join plan
EXPLAIN QUERY PLAN
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = 1;

-- automated index recommendation (3.36+, experimental)
SELECT * FROM users WHERE email = 'x';
-- then:
EXPLAIN QUERY PLAN SELECT * FROM sqlite_stat1;

-- lower-level: EXPLAIN (bytecode)
EXPLAIN SELECT * FROM users WHERE id = 1;

VACUUM et ANALYZE

VACUUM reconstruit le fichier, récupérant l'espace des suppressions et défragmentant les pages — lancez-le après de grosses suppressions ou périodiquement pour les bases à fortes écritures. VACUUM INTO (3.27+) écrit une copie propre sans verrouiller longtemps la source. ANALYZE met à jour les statistiques sqlite_stat1 que le planificateur utilise pour choisir les index — lancez-le après les gros chargements ou changements de schéma/données. auto_vacuum=INCREMENTAL récupère l'espace de façon incrémentale sans VACUUM complet.

sqlite
-- VACUUM: rebuild the database file, reclaiming free space
VACUUM;
-- after large deletes, this shrinks the file and defrags pages

-- VACUUM INTO: write a defragmented copy to a new file (3.27+)
VACUUM INTO 'clean.sqlite';

-- incremental VACUUM (only with auto_vacuum enabled)
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- free up to 100 pages

-- ANALYZE: update planner statistics for better query plans
ANALYZE;                          -- all tables
ANALYZE users;                    -- one table
ANALYZE users(idx_users_email);   -- one index

-- run ANALYZE after bulk loads or major data changes

Chargement en masse

Pour les chargements en masse : enveloppez dans une seule transaction (le plus gros gain de vitesse — l'autocommit par ligne est catastrophiquement lent), désactivez temporairement synchronous et la journalisation, supprimez et recréez les index, et considérez une augmentation de PRAGMA cache_size. La dot-command .import est plus rapide que des instructions INSERT. Pour des chargements vraiment énormes, considérez le .backup d'une base préconstruite. Restaurez toujours les PRAGMA de sûreté après le chargement des données de production.

sqlite
-- fastest bulk load into a fresh table
PRAGMA synchronous = OFF;     -- risk: power-loss corruption (use only for one-off loads)
PRAGMA journal_mode = MEMORY;
PRAGMA temp_store = MEMORY;

BEGIN;
CREATE TABLE big (id INTEGER, data TEXT);
.import data.csv big
COMMIT;

-- then turn safety back on
PRAGMA synchronous = FULL;
PRAGMA journal_mode = WAL;

-- faster still: drop indexes, load, recreate
DROP INDEX idx_big_data;
-- ... load ...
CREATE INDEX idx_big_data ON big(data);

-- use a transaction (10-100x faster than autocommit per row)
BEGIN;
INSERT INTO big VALUES (1, 'a');
-- ... thousands of inserts ...
COMMIT;

FTS5 (recherche plein texte)

FTS5 est le moteur de recherche plein texte de SQLite — il index le texte pour des requêtes MATCH rapides, bien plus rapide que LIKE '%terme%' qui scanne chaque ligne. Il supporte le booléen (AND/OR/NOT), le préfixe (terme*), la phrase ("...") et le classement par pertinence. Le pattern de contenu externe (content='posts') évite de dupliquer les données. Gardez l'index FTS synchronisé par triggers (INSERT/UPDATE/DELETE sur la table de base). FTS5 est idéal pour la recherche au fil de la frappe, l'autocomplétion et la recherche de documents.

sqlite
-- create a full-text search virtual table
CREATE VIRTUAL TABLE posts_fts USING fts5(
  title, body,
  content='posts', content_rowid='id'
);

-- populate it (keep in sync with triggers or rebuild)
INSERT INTO posts_fts(rowid, title, body)
  SELECT id, title, body FROM posts;

-- search with MATCH (much faster than LIKE '%term%')
SELECT p.* FROM posts p
JOIN posts_fts f ON f.rowid = p.id
WHERE posts_fts MATCH 'sqlite python';

-- ranking by relevance
SELECT p.title, rank
FROM posts p
JOIN posts_fts f ON f.rowid = p.id
WHERE posts_fts MATCH 'sqlite OR python'
ORDER BY rank;

-- prefix and phrase queries
SELECT * FROM posts_fts WHERE posts_fts MATCH 'sql*';        -- prefix
SELECT * FROM posts_fts WHERE posts_fts MATCH '"full text"'; -- exact phrase

Pièges courants et réglages

La plupart des problèmes de performance SQLite viennent de quelques causes : index manquants sur les colonnes WHERE/JOIN/ORDER BY, transactions absentes (l'autocommit par ligne est la cause n°1 des inserts lents), clés étrangères désactivées en silence, et SELECT * sur des tables larges. Activez WAL et les clés étrangères dans chaque setup de connexion. Batchez les gros DELETE pour éviter de longs verrous d'écriture et d'énormes espaces temp. Lancez ANALYZE après les grands changements de données pour que le planificateur choisisse de bons plans.

sqlite
-- 1. Enable foreign keys (OFF by default!)
PRAGMA foreign_keys = ON;

-- 2. Use WAL for concurrency
PRAGMA journal_mode = WAL;

-- 3. Index foreign keys AND join columns
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 4. Avoid SELECT * (more I/O, breaks on schema change)
SELECT id, username FROM users;

-- 5. Use transactions for multi-statement writes
BEGIN;
-- ... several inserts/updates ...
COMMIT;

-- 6. Don't index every column (slows writes)
-- index selectively on WHERE/JOIN/ORDER BY columns.

-- 7. Use LIMIT on exploratory queries
SELECT * FROM big_table LIMIT 10;

-- 8. Batch UPDATEs/DELETEs to avoid long locks
DELETE FROM logs WHERE id IN (
  SELECT id FROM logs WHERE ts < '2023-01-01' LIMIT 10000
);

-- 9. Prefer EXISTS over IN for large subquery results
-- 10. Run ANALYZE after big data changes.

Was this helpful?