Skip to content

SQLite Spickzettel

In sich geschlossene, serverlose, konfigurationsfreie SQL-Datenbank-Engine.

01

Erste Schritte

CLI-Grundlagen

SQLite speichert eine ganze Datenbank in einer einzigen Datei. Die sqlite3-CLI öffnet oder erzeugt bei Bedarf eine .sqlite/.db-Datei. Anders als MySQL/PostgreSQL gibt es keinen Serverprozess — Anwendungen binden die SQLite-Bibliothek direkt ein. .dump exportiert die volle Datenbank als Text-SQL für Backup oder 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;"

Dot-Befehle

Dot-Befehle werden von der sqlite3-CLI interpretiert, nicht von der SQL-Engine — sie brauchen nie ein Semikolon und sind über Programmier-APIs nicht verfügbar. .mode steuert die Ausgabeformatierung (versuch 'column' mit .header on für lesbare interaktive Nutzung). .schema zeigt CREATE-Anweisungen; .fullschema ergänzt Indizes, Views und Trigger. .help nutzen, um mehr zu entdecken.

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

Datenbankdateien verwalten

ATTACH DATABASE lässt eine Verbindung bis zu 10 Datenbankdateien gleichzeitig als Schemas abfragen (main, temp, plus angehängte). Der :memory:-URI erzeugt eine reine In-Memory-Datenbank, die beim Schließen verschwindet — ideal für Tests oder Scratch-Berechnungen. URI-Dateinamen (file:...) aktivieren read-only, shared-cache und andere Modi. Jede angehängte DB ist eine separate Datei auf der Platte.

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;

Header & Ausgabeformatierung

Die Ausgabeformatierung betrifft nur die CLI, nie die Daten selbst. 'column'-Modus mit .headers on ist für Menschen am lesbarsten. 'list' (Standard) ist am besten für Shell-Pipelines. 'json' und 'csv' machen SQLite zu einem praktischen Konverter. .output leitet Ergebnisse in eine Datei um; zurückschalten mit .output stdout nicht vergessen. .mode box/table zeichnen ASCII-Rahmen.

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

Skripte & SQL-Dateien ausführen

.read führt eine SQL-Datei innerhalb einer offenen CLI-Session aus; Umleitung mit < läuft vor dem interaktiven Prompt. --bail stoppt den Batch beim ersten Fehler (sonst läuft SQLite weiter, was Fehlschläge verdecken kann). .parameter (3.44+) bindet benannte Parameter aus der Shell und vermeidet String-Interpolation. Skripte in der Entwicklung immer mit .echo on plausibilisieren.

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

Tabellen & Datentypen

Tabellen erstellen

INTEGER PRIMARY KEY ist ein Alias für die rowid — sie zählt automatisch hoch und ist der schnellstmögliche Schlüssel. AUTOINCREMENT ändert den Algorithmus, sodass gelöschte IDs nie wiederverwendet werden (etwas langsamer; meist unnötig). DEFAULT (expr) erlaubt beliebige Ausdrücke wie datetime('now'). CREATE TABLE AS SELECT erzeugt eine vorab mit Abfrageergebnissen gefüllte Tabelle, kopiert aber keine Constraints oder Indizes.

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';

Storage-Klassen & Type Affinity

SQLite nutzt dynamische Typisierung: Jede Spalte kann jede der 5 Storage-Klassen halten. 'Type affinity' versucht, Werte zu konvertieren, wenn möglich ('42' in einer INTEGER-Spalte wird zu Ganzzahl 42), wirft aber nie bei Nichtübereinstimmung. STRICT-Tabellen (3.37+) stellen die traditionelle strenge Typprüfung pro Spalte wieder her. typeof() meldet die tatsächlich gespeicherte Klasse. Diese Flexibilität ist mächtig, erfordert aber Disziplin — STRICT für neue Schemata nutzen, wo Datenintegrität zählt.

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

Häufige Datentypen

SQLite hat keine nativen DATE/TIME- oder BOOLEAN-Typen — Zeitstempel als ISO-8601-TEXT ('YYYY-MM-DD HH:MM:SS') speichern, für Sortierbarkeit, und über Datums-/Zeitfunktionen manipulieren. Booleans sind Ganzzahlen 0/1. VARCHAR(n)-Längengrenzen werden ignoriert (nur Kompatibilität). NUMERIC-Affinity versucht zuerst INTEGER, dann REAL und erhält exakte Dezimalen, wenn möglich. BLOB speichert rohe Bytes unverändert.

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
);

Tabellen ändern

SQLite's ALTER TABLE ist bewusst begrenzt für eine serverlose Engine. ADD COLUMN ist O(1) (kein Tabellenumbau). RENAME COLUMN (3.25+) und DROP COLUMN (3.35+) sind moderne Bequemlichkeiten. Für Typwechsel oder Constraint-Ergänzungen das 12-Schritte-Tabellenneubau-Muster nutzen (neu anlegen, kopieren, löschen, umbenennen, Indizes neu bauen). Foreign Keys müssen während Neubauten temporär deaktiviert sein — siehe 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

Constraints

Constraints erzwingen Datenintegrität auf Engine-Ebene. PRIMARY KEY impliziert NOT NULL und UNIQUE. CHECK darf auf jede Spalte der Zeile referenzieren. ON CONFLICT-Klauseln (OR IGNORE / OR REPLACE / OR ABORT / OR FAIL / OR ROLLBACK) steuern die Konfliktauflösung pro Anweisung — OR REPLACE löscht die konfligierende Zeile und fügt ein, was ihre rowid zurücksetzt. Für UPSERT-Semantik ON CONFLICT ... DO UPDATE bevorzugen (siehe CRUD-Sektion).

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

CRUD-Operationen

INSERT

Mehrzeilen-INSERT ist dramatisch schneller als Zeile-für-Zeile-Inserts in einer Schleife — bündeln. INSERT ... SELECT kopiert Daten zwischen Tabellen. last_insert_rowid() liefert die rowid des jüngsten INSERT auf der aktuellen Verbindung (nicht Transaktion). Für Apps parametrisierte Inserts bevorzugen (siehe Integrationssektion), um SQL-Injection und Quotierungs-Ärger zu vermeiden. DEFAULT nutzen, um explizit den Defaultwert einer Spalte zu nehmen.

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) ist SQLite's Idiom für 'einfügen oder aktualisieren, falls vorhanden' — weit sicherer als INSERT OR REPLACE, das die bestehende Zeile löscht (feuert DELETE-Trigger und setzt die rowid zurück). 'excluded' verweist auf die zum Einfügen vorgeschlagene Zeile. DO NOTHING überspringt stillschweigend. Konfliktziele können jedes UNIQUE- oder PRIMARY-KEY-Constraint sein. Das korrekte Muster für Zähler, last-seen-Updates und idempotente Imports.

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

Immer WHERE einschließen, außer du willst wirklich jede Zeile aktualisieren — SQLite hat keinen Trockenlauf-Modus. UPDATE ... FROM (3.33+) und UPDATE ... RETURNING (3.35+) bringen SQLite nahe an PostgreSQLs Ergonomie. RETURNING ist unbezahlbar für Apps, die den Zustand nach dem Update ohne separates SELECT brauchen. CASE ermöglicht Ein-Anweisungs-Massenupdates mit Logik pro Zeile — viel schneller als zeilenweise Updates.

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 hat kein TRUNCATE — DELETE FROM table entfernt alle Zeilen, feuert aber Trigger auf Zeilenebene und setzt den AUTOINCREMENT-Zähler nicht zurück (gespeichert in sqlite_sequence). DELETE ... RETURNING (3.35+) protokolliert, was entfernt wurde. Für Audit-Daten lieber Soft-Deletes (eine deleted_at-Spalte) als harte Löschungen. Frei gewordener Platz wird von künftigen Inserts wiederverwendet; VACUUM holt ihn ans OS zurück.

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;

SELECT-Grundlagen

SELECT ist das Arbeitstier. LIMIT count OFFSET skip implementiert Paginierung (Keyset-Paginierung ist bei großen Offsets schneller — siehe Abfragesektion). Aliasse (AS, optional) verbessern die Lesbarkeit und sind bei berechneten Spalten in Views Pflicht. DISTINCT faltet identische Zeilen zusammen; GROUP BY für feinere Kontrolle erwägen. CASE ergänzt if/else-Logik in Ergebnismengen und ist in jeder Klausel nutzbar.

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

Abfragen & Filtern

WHERE & Operatoren

NULL ist in SQL besonders — es ist 'unbekannt', kein Wert. = NULL liefert immer NULL (als falsch behandelt), also IS NULL / IS NOT NULL verwenden. NULL kombiniert mit AND/OR folgt dreiwertiger Logik. BETWEEN ist inklusiv. IN ist Kurzform für ODER-Gleichheit; IN (NULL, ...) vermeiden, da es sich merkwürdig verhält. Klammern großzügig nutzen, um Verbundbedingungen eindeutig zu machen.

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 & LIMIT

ORDER BY ohne eindeutigen Gleichstands-Tiebreaker liefert über Läufe hinweg nicht-deterministische Reihenfolge — immer eine eindeutige Spalte (wie id) als letzten Sortierschlüssel ergänzen. OFFSET-Paginierung ist O(n), weil er Zeilen scannt und verwirft; Keyset-Paginierung (WHERE id > last_seen_id) ist O(limit) und die richtige Wahl für große Tabellen. RANDOM() verlangt eine volle Sortierung — nur bei kleinen Ergebnismengen oder Stichproben nutzen.

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;

Mustererkennung

LIKE ist für ASCII standardmäßig unabhängig von der Groß-/Kleinschreibung (eine überraschende Falle — COLLATE BINARY für Schreibabhängigkeit). GLOB ist schreibabhängig und unterstützt Zeichenklassen wie [A-Z]. REGEXP ist nicht eingebaut — du musst eine Funktion registrieren oder eine Erweiterung laden. Für Volltextsuche bei Skala FTS5 nutzen (siehe Performance-Sektion) statt LIKE '%term%', das keine Indizes nutzen kann.

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 & bedingte Logik

CASE ist SQLs if/else und funktioniert in SELECT, WHERE, ORDER BY, GROUP BY und HAVING. Die 'einfache' Form matcht einen Wert; die 'gesuchte' Form wertet boolesche Bedingungen der Reihe nach aus. SUM(CASE WHEN ... THEN 1 ELSE 0 END) ist das klassische Pivot-/bedingte-Zähl-Idiom. CASE short-circuited beim ersten passenden WHEN, die Reihenfolge zählt also. ELSE ist standardmäßig NULL, wenn weggelassen.

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 & Mengenoperationen

UNION ALL ist schneller als UNION, weil UNION eine Deduplizierungssortierung durchführt — UNION ALL bevorzugen, wenn Duplikate unmöglich oder akzeptabel sind. Alle Mengenoperationen verlangen kompatible Spaltenanzahlen und -typen. SQLite wendet INTERSECT/EXCEPT mit höherer Priorität an als UNION/UNION ALL; Klammern zur Reihenfolgesteuerung nutzen. ORDER BY in einer Verbundabfrage gilt für die gesamte Ergebnismenge, nicht für das letzte 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

JOINs

INNER JOIN

INNER JOIN liefert nur Zeilen mit Matches in beiden Tabellen — ungetroffene Zeilen beider Seiten fallen weg. USING (col) ist die Kurzform, wenn beide Tabellen eine Spalte gleichen Namens haben, und erzeugt eine einzige verschmolzene Spalte. NATURAL JOIN nutzt implizit alle gleichnamigen Spalten — bequem, aber fragil (eine Schemaänderung kann das Join-Verhalten stillschweigend ändern). Explizites ON für Klarheit bevorzugen.

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 erhält jede Zeile der linken Tabelle; fehlende rechte Spalten werden NULL. Das 'WHERE right.id IS NULL'-Muster (Anti-Join) findet Zeilen ohne Matches — oft klarer und schneller als NOT IN mit Subqueries, besonders wenn NULLs im Spiel sind. Bei Aggregation über einen LEFT JOIN zählt COUNT(right.id) (nicht COUNT(*)) nur getroffene Zeilen; ein Nutzer ohne Bestellungen ergibt 0, nicht 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 & RIGHT/FULL

CROSS JOIN erzeugt das kartesische Produkt — nützlich, um Kombinationen zu erzeugen (Größe × Farbe), aber gefährlich bei großen Tabellen (n×m Zeilen). SQLite unterstützt RIGHT JOIN (3.39+), aber er wird selten genutzt; für Klarheit als LEFT JOIN umschreiben. Einen nativen FULL OUTER JOIN gibt es nicht; mit LEFT JOIN ... UNION ... RIGHT JOIN emulieren, wobei die Union die überlappenden getroffenen Zeilen dedupliziert.

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

Ein Self Join fragt dieselbe Tabelle zweimal ab und nutzt Aliasse, um jeder Kopie eine Rolle zu geben (Mitarbeiter vs. Vorgesetzter, user_a vs. user_b). Der Trick 'a.id < b.id' vermeidet, eine Zeile mit sich selbst zu matchen und gespiegelte Duplikatpaare zu erzeugen. Für tiefe Hierarchien (beliebige Tiefe) ist ein rekursiver CTE (siehe CTE-Sektion) das richtige Werkzeug — Self-Joins beherrschen nur feste Tiefen.

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;

Mehrtabellen-Joins

Joins verketten von links nach rechts; jeder JOIN sieht das akkumulierte Ergebnis. Beim Mischen von LEFT- und INNER-Joins kann ein INNER JOIN nach einem LEFT JOIN NULLs herausfiltern und den LEFT zwecklos machen — Reihenfolge sorgfältig wählen. COUNT(DISTINCT col) vermeidet Doppelzählung über Eins-zu-viele-Joins hinweg. Auf die Join-Vielfachheit achten: zwei Eins-zu-viele-Tabellen zu joinen kann eine Zeilenexplosion erzeugen (m × n Zeilen pro 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

Aggregationen & GROUP BY

GROUP BY-Grundlagen

GROUP BY faltet Zeilen mit gleichen gruppierten Spalten zu einer Zeile pro Gruppe zusammen. Jede nicht-aggregierte Spalte in SELECT muss in GROUP BY auftauchen (SQLite ist tolerant und wählt einen beliebigen Wert, verlass dich aber nicht darauf). Nach einem Ausdruck zu gruppieren (etwa strftime auf einem Datum) ist super für Zeit-Bucket-Berichte. ORDER BY wird nach GROUP BY angewendet — damit Gruppen sortieren.

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;

Aggregatfunktionen

COUNT(*) zählt Zeilen; COUNT(col) zählt Nicht-NULL-Werte; COUNT(DISTINCT col) zählt eindeutige Nicht-NULLs. AVG/SUM ignorieren NULLs. GROUP_CONCAT verklebt Werte zu einem String (Standardtrenner Komma). TOTAL() liefert immer ein REAL (0.0 bei leerer Eingabe), anders als SUM(), das bei leerer Eingabe NULL liefert. Die FILTER-Klausel (3.30+) ist sauberer als CASE für bedingte Aggregation und kann schneller sein.

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 (Gruppen filtern)

WHERE filtert Eingabezeilen vor der Gruppierung; HAVING filtert Gruppen nach der Aggregation. Diese Reihenfolge zählt: Ein WHERE auf einem Aggregat ist illegal. SQLite erlaubt Alias-Referenzen in HAVING (und GROUP BY) für Lesbarkeit, aber das ist nicht standardkonform. Das 'group by X having count(*) > 1'-Muster ist die klassische Duplikatfinder-Abfrage.

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 ist SQLite's String-Aggregationsfunktion (anderswo STRING_AGG oder LISTAGG genannt). Standardtrenner ist das Komma; einen eigenen als zweites Argument angeben. ORDER BY innerhalb des Aggregats (3.44+) steuert die Verkettungsreihenfolge. DISTINCT dedupliziert vor dem Verbinden. Das Ergebnis hat ein 1-GB-Limit; bei riesigen Gruppen lieber Zeilen holen und in deiner Anwendung aggregieren.

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;

Daten pivotieren

SQLite hat kein natives PIVOT; mit SUM(CASE WHEN ...) oder der saubereren FILTER-Klausel emulieren. Jede pivotierte Spalte testet einen Wert des Gruppierungsattributs. Dieses Muster verwandelt lange (normalisierte) Daten in breite (Kreuztabellen-)Berichte. Für voll dynamisches Pivotieren (unbekannte Kategorien) musst du den SQL-String im Anwendungscode bauen — SQL kann keine Spalten zur Laufzeit erzeugen.

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

Subqueries

Skalare Subqueries

Eine skalare Subquery liefert exakt eine Zeile und eine Spalte; sie darf überall stehen, wo ein Wertausdruck gültig ist. Korrelierte Subqueries referenzieren die äußere Abfrage und laufen pro Zeile erneut — mächtig, aber bei großen Tabellen potenziell langsam (stattdessen einen JOIN oder eine Window-Funktion erwägen). SQLite ist gut darin, einfache korrelierte Subqueries in Joins zu optimieren, aber bei heißen Pfaden immer EXPLAIN QUERY PLAN prüfen.

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;

IN & NOT IN-Subqueries

IN-Subqueries sind intuitiv, aber auf NULLs achten: Enthält das Subquery-Ergebnis eine NULL, wird NOT IN für jede Zeile zu NULL (als falsch behandelt) und liefert ein leeres Ergebnis. NOT EXISTS für Anti-Joins mit nullable-Spalten bevorzugen — es ist NULL-sicher und oft schneller. Mehrspalten-IN ist kompakt zum Matchen von Verbundschlüsseln.

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 & NOT EXISTS

EXISTS testet die Zeilenexistenz, ohne Daten zu holen — er stoppt beim ersten Match und ist also effizient. NOT EXISTS ist der NULL-sichere Weg, nicht-getroffene Zeilen zu finden (anders als NOT IN). Für beste Performance sicherstellen, dass ein Index auf der Spalte existiert, über die die korrelierte Subquery joint (orders.user_id im Beispiel). SELECT 1 ist Konvention; die Spaltenliste ist für EXISTS egal.

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
);

Derived Tables (Subquery in FROM)

Eine Subquery in FROM (derived table) ist ein mächtiger Weg, Zwischenergebnisse zu stagen, vorzuaggregieren oder vor dem Join zu filtern. Derived Tables müssen einen Alias haben. SQLite materialisiert die Subquery (schreibt sie in eine temporäre Tabelle) meist — bei wiederholter Nutzung ist ein CTE (WITH-Klausel) oft klarer und mehrfach referenzierbar. SQLite erlaubt keine korrelierten Referenzen in eine FROM-Subquery.

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;

Korreliert vs. unkorreliert

Unkorrelierte Subqueries laufen einmal und lassen sich cachen; korrelierte laufen pro äußerer Zeile erneut (Indizes nutzen, um sie schnell zu halten). SQLite kennt kein LATERAL/APPLY; um ein Aggregat pro Zeile zu 'joinen', in einer derived table voraggregieren und per LEFT JOIN anfügen. Performt eine korrelierte Subquery schlecht, ist das Umschreiben als JOIN mit GROUP BY oder eine Window-Funktion die übliche Lösung.

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

Indizes

Indizes erstellen

Indizes beschleunigen Lesevorgänge, verlangsamen aber Schreibvorgänge (jedes INSERT/UPDATE/DELETE aktualisiert alle Indizes). Die Spaltenreihenfolge zählt: Ein Composite-Index auf (a, b) hilft WHERE a=? und WHERE a=? AND b=?, aber NICHT WHERE b=? allein (Leftmost-Prefix-Regel). Unique-Indizes erzwingen Constraints. SQLite speichert Indizes als B-Bäume. Unbenutzte Indizes löschen — sie kosten Platz und Schreib-Performance.

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';

Partial-Indizes

Partial-Indizes indexieren nur passende Zeilen, sind also kleiner und schneller zu pflegen — ideal, wenn Abfragen immer auf einem stabilen Prädikat filtern ('active = 1', 'deleted_at IS NULL'). Das WHERE der Abfrage muss dem WHERE des Index entsprechen (oder strenger sein), damit der Optimierer ihn nutzt. Eindeutige Partial-Indizes erzwingen Eindeutigkeit nur auf der Teilmenge — perfekt für 'eine aktive Zeile pro Parent'-Constraints.

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';

Ausdrucksindizes

Ausdrucksindizes indexieren das Ergebnis einer Funktion oder eines Ausdrucks und ermöglichen schnelle Lookups auf transformierten Werten — aber die Abfrage muss denselben exakten Ausdruck nutzen. Übliche Einsätze: schreibunabhängige Suche (LOWER), Datum-Bucketing (strftime) und JSON-Feldextraktion (json_extract). Nur deterministische Funktionen (kein RANDOM() oder NOW()). Ausdrucksindizes sind mächtig, machen Schemaänderungen aber kniffliger.

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 & WITHOUT ROWID

Ein Covering-Index schließt jede Spalte ein, die eine Abfrage liest, sodass SQLite die Abfrage allein aus dem Index genügt (kein Tabellenlookup). WITHOUT ROWID-Tabellen machen den PRIMARY KEY zu einem geclusterten Index — Zeilen werden nach Schlüssel sortiert gespeichert, ideal für Key/Value-Workloads und Bereichsscans auf dem PK. Trade-offs: kein AUTOINCREMENT, kein rowid-Aliasing, etwas komplexere Updates. Vor breiter Einführung benchmarken.

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

Index-Inspektion

EXPLAIN QUERY PLAN ist das essenzielle Werkzeug — 'USING INDEX' heißt, der Index wird genutzt; 'SCAN' heißt vollständiger Tabellenscan (meist schlecht). sqlite_stat1 (von ANALYZE gefüllt) hält Kardinalitätsstatistiken, die der Planer zur Indexwahl nutzt. REINDEX baut einen aufgeblähten oder korrumpierten Index neu, ohne die Tabelle umzubauen. ANALYZE nach großen Datenlasten ausführen, damit der Planer frische Statistiken für gute Pläne hat.

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

Views

Views erstellen

Views sind virtuelle Tabellen, definiert durch eine gespeicherte Abfrage — sie speichern keine Daten (außer temp-Views), sie führen das zugrunde liegende SELECT jedes Mal erneut aus. Nutzen, um komplexe Abfragen zu vereinfachen, konsistente Zugriffsmuster zu erzwingen oder Schemaänderungen vor Anwendungen zu abstrahieren. Views sind in SQLite standardmäßig read-only (mit Einschränkungen für aktualisierbare Views). DROP VIEW berührt zugrunde liegende Tabellen nie.

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;

Aktualisierbare Views

SQLite erlaubt INSERT/UPDATE/DELETE auf einfachen Views (eine Basistabelle, kein JOIN/Aggregate/DISTINCT/GROUP BY). Die Operation wird an die zugrunde liegende Tabelle weitergeleitet. SQLite unterstützt KEIN WITH CHECK OPTION, eine durch einen View eingefügte Zeile kann also das WHERE des Views verletzen und danach durch ihn unsichtbar sein. Für komplexe Views, die Schreibvorgänge brauchen, INSTEAD OF-Trigger nutzen.

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.

INSTEAD OF-Trigger auf Views

INSTEAD OF-Trigger fangen Schreibvorgänge an einem View ab und führen deine eigene Logik aus, was jeden View aktualisierbar macht. Der Standardweg, komplexe (Mehrtabellen-, aggregierte-)Views schreibbar zu machen. NEW und OLD verweisen auf die eingehende bzw. bestehende Zeile. INSTEAD OF-Trigger feuern nur auf Views, nicht auf Basistabellen. Nutzen, um gekapselte Geschäftslogik zu implementieren oder ein normalisiertes Schema hinter einem denormalisierten View zu verbergen.

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);

Temporäre Views

Temporäre Views leben nur für die aktuelle Datenbankverbindung und werden beim Trennen automatisch gelöscht — perfekt, um komplexe Ad-hoc-Abfragen zu stagen, ohne das geteilte Schema zu verschmutzen. Sie liegen in einem separaten temp-Schema, andere Verbindungen (selbst zur selben Datei) sehen sie also nicht. TEMP-Views können auf TEMP-Tabellen referenzieren, was für sessionbezogene ETL-Pipelines nützlich ist.

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

Materialized Views (emuliert)

SQLite hat keine native Materialized View, aber eine reguläre Tabelle, die nach Zeitplan erneuert wird, erreicht dasselbe. Für atomares Erneuern eine neue Tabelle in einer Transaktion bauen und umbenennen — Leser sehen die alte Version bis zum Commit, dann die neue. Indizes nach dem Neubau ergänzen. Materialized Views tauschen Schreib-/Speicherkosten gegen Lesetempo — ideal für teure Aggregate, die häufig abgefragt werden.

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

Trigger

BEFORE / AFTER-Trigger

BEFORE-Trigger laufen, bevor die Zeile geschrieben wird — zur Validierung (RAISE ABORT) oder zum Transformieren der NEW-Werte nutzen. AFTER-Trigger laufen, sobald die Zeile committet ist — für Auditing, kaskadierende Effekte oder Denormalisierung. FOR EACH ROW ist die einzige Option (keine Trigger auf Statement-Ebene). NEW hält die eingehende Zeile (INSERT/UPDATE); OLD die vorherige (UPDATE/DELETE). Die WHEN-Klausel feuert den Trigger bedingt.

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;

Trigger für kaskadierende Effekte

Trigger können denormalisierte Zähler, Zusammenfassungstabellen und updated_at-Zeitstempel automatisch pflegen. Vorsicht: rekursive Trigger-Ketten sind möglich (ein Trigger, der seine eigene Tabelle aktualisiert), also mit PRAGMA recursive_triggers steuern (Standard AUS aus Abwärtskompatibilität). Die Triggerreihenfolge bei mehreren Triggern auf demselben Event folgt der Erzeugungsreihenfolge; es gibt keine BEFORE/AFTER-Priorität über Trigger desselben Timings hinweg.

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;

INSTEAD OF-Trigger (Views)

INSTEAD OF-Trigger laufen statt des ursprünglichen INSERT/UPDATE/DELETE auf einem View und lassen dich Schreibvorgänge an eine oder mehrere zugrunde liegende Tabellen routen. Der kanonische Weg, komplexe Views schreibbar zu machen. Sie feuern pro Zeile des Views. Nur INSTEAD OF-Trigger lassen sich auf Views definieren; BEFORE/AFTER-Trigger verlangen Basistabellen. Nützlich für API-artige Abstraktionsschichten über einem normalisierten Schema.

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 & Fehlerbehandlung

RAISE steuert, wie sich ein Triggerfehler fortpflanzt: ABORT (am häufigsten) bricht die Anweisung ab und rollt ihre Änderungen zurück, ROLLBACK rollt die gesamte Transaktion zurück, FAIL bricht die Anweisung ab, behält aber vorherige erfolgreiche Anweisungen in der Transaktion, und IGNORE überspringt nur die betroffene Zeile. RAISE(IGNORE) ist ein eleganter Weg, ungültige Zeilen bei Bulk-Loads stillschweigend fallen zu lassen.

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;

Trigger verwalten

Trigger liegen in sqlite_master (oder temp.sqlite_master für TEMP-Trigger). DROP TRIGGER entfernt sie; ein ALTER TRIGGER gibt es nicht — löschen und neu erzeugen. TEMP-Trigger sind verbindungslokal und verschwinden beim Trennen — nützlich zum Debuggen. Einen Trigger temporär zu deaktivieren gibt es nicht eingebaut, außer ihn zu löschen; für bedingte Ausführung die WHEN-Klausel nutzen. Mehrere Trigger auf demselben Event feuern innerhalb ihres Timings in Erzeugungsreihenfolge.

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

PRAGMA-Einstellungen

PRAGMA-Grundlagen

PRAGMA ist SQLite's eigene Konfigurationsschnittstelle — Einstellungen steuern Journaling, Caching, Foreign Keys, Integrität und den Abfrageplaner. Einstellungen sind sessionbezogen (pro Verbindung), außer die Doku sagt etwas anderes; innerhalb einer Transaktion gesetzt werden sie womöglich ignoriert. Manche PRAGMAs (journal_mode, page_size) greifen nur außerhalb von Transaktionen oder vor der Tabellenerzeugung. PRAGMA compile_options nutzen, um zu sehen, was dein Build unterstützt.

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.

Foreign Keys

Die Foreign-Key-Erzwingung ist in SQLite standardmäßig AUS — eine berüchtigte Falle für Nutzer anderer Datenbanken. Bei jeder Verbindung mit PRAGMA foreign_keys = ON aktivieren; innerhalb einer Transaktion nicht setzbar. Diese Einstellung existiert, weil frühe Schemata (vor FK-Unterstützung) sonst brechen würden. In Produktion immer aktivieren. defer_foreign_keys hilft beim Datenimport in die Dependencies verletzender Reihenfolge.

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

Journal-Modus & WAL

WAL (Write-Ahead Logging) ist der empfohlene Journal-Modus für die meisten Apps: Leser und Schreiber blockieren einander nicht, und die Dauerhaftigkeit ist gut. Er erzeugt -wal- und -shm-Nebendateien neben der Haupt-DB. WAL ist pro Datenbankdatei persistent (einmal setzen). wal_checkpoint(TRUNCATE) nutzen, um WAL-Inhalte zurück in die Hauptdatei zu falten und Platz zurückzugewinnen. WAL auf Netzwerk-Dateisystemen vermeiden — er verlangt geteilten Speicher.

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 & Dauerhaftigkeit

synchronous steuert, wie aggressiv SQLite fsync aufruft. FULL ist am sichersten (jeder Commit macht fsync); NORMAL ist der empfohlene Wert mit WAL (fsync am Checkpoint, nicht pro Commit, mit vernachlässigbarem Korruptionsrisiko in der Praxis); OFF riskiert Datenbankkorruption bei Stromausfall — nur für wirklich wegwerfbare Daten oder wiederaufbaubare Caches. page_size vor der Schemaerzeugung setzen; späteres Ändern verlangt 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, Speicher & Temp Store

cache_size ist der In-Memory-Page-Cache — größer ist generell besser bei leseintensiven Workloads; negative Werte sind KB, positive Seiten. temp_store = MEMORY verlagert Zwischenergebnisse (Sortierungen, temporäre Tabellen) in den RAM und beschleunigt große Aggregationen. mmap_size aktiviert memory-mapped I/O für Lesevorgänge, was große read-only-Datenbanken beschleunigen kann. Nach dem Speicherbudget und dem Zugriffsmuster deiner App tunen.

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;

Integrität & Schema-Info

integrity_check validiert die gesamte Datenbankstruktur und den Inhalt (langsam, aber gründlich); quick_check überspringt die Inhaltsvalidierung und ist viel schneller. integrity_check bei Backups oder nach Crashes ausführen. table_info meldet Spaltenmetadaten (table_xinfo für versteckte Spalten wie FTS5-rowid nutzen). index_list und index_info offenbaren, welche Indizes existieren und ihre Spalten. database_list zeigt angehängte Datenbanken.

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

Datum & Zeitfunktionen

date / time / datetime

SQLite hat keinen nativen DATE-Typ; Daten sind TEXT in ISO-8601 ('YYYY-MM-DD HH:MM:SS'). Die Datums-/Zeitfunktionen akzeptieren viele Eingabeformate (ISO, Schrägstriche, Monatsnamen, Unix-Zeitstempel über den 'unixepoch'-Modifikator). 'now' wird einmal pro Anweisung ausgewertet, nicht pro Zeile — jede Zeile in einem Mehrzeilen-UPDATE bekommt denselben Zeitstempel, was meist gewollt ist. In UTC speichern, für die Anzeige mit localtime formatieren.

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');

strftime-Formatierung

strftime ist der universelle Datumsformatierer — die Formatcodes spiegeln C's strftime wider. Damit Datumsteile extrahieren (Jahr, Monat, Wochentag) fürs Gruppieren oder ein eigenes Anzeigeformat rendern. Für Gruppierung nach Monat liefert strftime('%Y-%m', created) 'YYYY-MM', das natürlich sortiert und gruppiert. SQLite kennt kein DAYNAME()/MONTHNAME(); strftime('%w', ...) mit einem CASE kombinieren, wenn Wochentagsnamen nötig sind.

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

Modifikatoren (Arithmetik)

Modifikatoren verketten von links nach rechts, um Daten zu verschieben oder auszurichten. Intervalle sind '+N Einheiten' / '-N Einheiten' (Einheiten: days, hours, minutes, seconds, months, years). 'start of month/year/day' springt auf die Periodengrenze — perfekt für Monatsberichte. 'weekday N' liefert das Datum des nächsten Wochentags N (heute, wenn heute passt). 'start of month', '+1 month', '-1 day' kombiniert ergibt den letzten Tag des aktuellen Monats — ein klassisches Idiom.

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

Unix-Zeit & Julianischer Tag

Unix-Zeitstempel sind Ganzzahlen/Sekunden seit der Epoche; unixepoch() (3.38+) liefert direkt eine Ganzzahl. Der julianische Tag ist eine kontinuierliche Bruchzählung von Tagen — die Subtraktion zweier julianday-Werte ergibt die verstrichene Zeit in Tagen (mit 86400 multiplizieren für Sekunden). Für Altersberechnungen berücksichtigt eine Division durch 365.25 Schaltjahre ungefähr; für juristische Präzision Jahr/Monat/Tag-Komponenten explizit vergleichen.

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;

Zeitzonen & localtime

SQLite hat keine Zeitzonen-Datenbank — 'now' ist UTC, und die Modifikatoren 'localtime'/'utc' verlassen sich auf die Zeitzoneneinstellung des Host-OS. Das empfohlene Muster: Zeitstempel immer in UTC speichern (datetime('now') ist UTC) und nur zur Anzeigezeit mit dem 'localtime'-Modifikator in lokale Zeit konvertieren. Das vermeidet Mehrdeutigkeit über Server in verschiedenen Zonen und bei Sommerzeit-Übergängen. Lokale Zeit nie speichern.

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

String-Funktionen

Länge & Teilstring

length() zählt Zeichen für TEXT und Bytes für BLOB — für UTF-8-Bytelänge zuerst auf BLOB casten. substr() ist 1-indiziert; negativer Start zählt vom Ende. SQLite hat kein natives LPAD/RPAD — mit String-Konkatenation und substr emulieren. Auf die Byte-vs-Zeichen-Verwechslung achten beim Kürzen von Multibyte-Text: substr arbeitet auf Zeichen und ist also sicher, aber Byte-Level-Slicing ist es nicht.

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'

Schreibweise & Trimmen

upper()/lower() betreffen standardmäßig nur ASCII-Buchstaben — Nicht-ASCII-Zeichen bleiben unverändert, außer ICU ist geladen. trim() entfernt standardmäßig führenden/nachgestellten Whitespace oder bestimmte als zweites Argument gegebene Zeichen. replace() macht wörtliche Teilstring-Ersetzung (kein Regex, keine Muster). Ein natives TRANSLATE() für Zeichensatz-Ersetzung gibt es nicht — replace()-Aufrufe verketten oder eine eigene Funktion nutzen.

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'

Konkatenation & printf

Der ||-Operator ist der Standard-SQL-Konkatenator, aber er propagiert NULL (NULL || x = NULL) — bei möglichen NULLs mit COALESCE umhüllen. printf()/format() bringt C-artige Formatierung und ist der sauberste Weg, Strings mit Auffüllung, Zahlenformatierung und Kommaseparatoren zu bauen. quote() liefert einen sicher quotierten und escapten String zum Einbetten in SQL — nützlich beim Bauen dynamischen SQL.

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'''

Splitten & Suchen

instr() findet das erste Vorkommen eines Teilstrings (1-indiziert; 0, wenn nicht gefunden). SQLite hat kein natives SPLIT_PART — mit substr + instr oder einem rekursiven CTE emulieren. Das 'Vorkommen zählen'-Idiom subtrahiert die Längen vor/nach dem Entfernen des Trenners. char() baut einen String aus Codepoints; unicode() liefert den Codepoint des ersten Zeichens. Für vollständiges Text-Splitten ein JSON-Array via json_each laden.

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 / Kollationen

LIKE ist für ASCII standardmäßig unabhängig von der Groß-/Kleinschreibung (eine häufige Überraschung); COLLATE BINARY für Schreibabhängigkeit. GLOB ist immer schreibabhängig und unterstützt Shell-artige Zeichenklassen. NOCASE ist eine eingebaute Kollation für schreibunabhängigen Vergleich; auf den Index einer Spalte anwenden, damit schreibunabhängige Abfragen den Index nutzen. Für Nicht-ASCII-Schreibfaltung die ICU-Erweiterung laden oder eine kleingeschriebene Kopierspalte mit Index speichern.

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

JSON-Funktionen

Werte extrahieren

json_extract (und die Operatoren -> / ->>, 3.38+) sind die Arbeitstiere fürs JSON-Lesen. $ ist die Wurzel; $.key greift auf eine Objekteigenschaft zu; $.arr[i] auf ein Array-Element (0-indiziert). ->> liefert einen Skalar (text/integer/real), -> JSON (daher '$.b' auf einem Array ergibt das Array als JSON). Häufig abgefragte JSON-Felder als reguläre Spalten speichern oder einen Ausdrucksindex auf json_extract für Performance ergänzen.

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'

JSON bauen

json_object und json_array konstruieren JSON aus SQL-Werten (NULLs werden zu JSON null, nicht weggelassen). json_group_array und json_group_object (3.38+) sind Aggregatfunktionen, die aus Abfragezeilen JSON bauen — unbezahlbar, um JSON-APIs direkt aus SQL zu erzeugen. Konstruktoren verschachteln, um komplexe Dokumente zu bauen. json_quote nutzen, um einen String sicher als JSON-String-Literal einzubetten.

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"'

JSON ändern

json_set/insert/replace unterscheiden Einfüge- vs. Update-Verhalten; json_remove löscht Pfade. Diese liefern NEUE JSON-Werte — SQLite-JSON ist unveränderlich, Änderungen also persistieren, indem du die Spalte mit dem Ergebnis UPDATE-st: UPDATE t SET data = json_set(data, '$.x', 1) WHERE id = 5. json_patch (RFC 7396) verschmilzt Objekte; null-Werte im Patch löschen Schlüssel. Bei großen Dokumenten heiße Felder als separate Spalten erwägen.

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}

JSON-Arrays abfragen

json_each und json_tree sind virtuelle Tabellenwertfunktionen, die JSON in Zeilen expandieren. json_each behandelt ein einzelnes Array/Objekt; json_tree durchläuft rekursiv die gesamte Struktur und legt fullkey-Pfade offen. Das schaltet mengenbasierte Operationen auf JSON-Arrays frei — Filtern, Joinen und Aggregieren. Um Arrays-von-Objekten in einer Spalte abzufragen, einen LATERAL-artigen Join nutzen: 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

JSON-Indexierung & Validierung

json_extract-Ausdrücke indexieren für schnelle JSON-Feld-Lookups — die Abfrage muss den identischen Ausdruck nutzen, um zu profitieren. json_valid() liefert 1/0; in einem CHECK-Constraint nutzen, um JSON-only-Spalten zu erzwingen. json_type() meldet den JSON-Typ an einem Pfad ('object', 'array', 'integer', 'real', 'string', 'boolean', 'null'). Bei JSON-lastigen Workloads heiße Felder in reguläre indexierte Spalten denormalisieren und JSON für den flexiblen Schwanz behalten.

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

Window-Funktionen

OVER-Klausel-Grundlagen

Window-Funktionen (SQLite 3.25+) rechnen über eine Menge von Zeilen, die zur aktuellen Zeile gehören, ohne sie zusammenzufalten wie GROUP BY. OVER () heißt 'alle Zeilen'; OVER (ORDER BY ...) definiert die Ordnung für Rangierungen. ROW_NUMBER ist eindeutig; RANK lässt Lücken nach Gleichständen; DENSE_RANK nicht. Window-Funktionen erscheinen in SELECT und ORDER BY, nicht in WHERE — mit einer äußeren Abfrage oder einem CTE filtern.

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 teilt Zeilen in Gruppen für die Window-Funktion, wie GROUP BY, aber ohne Zusammenfalten — jede Zeile behält ihre Identität und bekommt ein Aggregat pro Gruppe. Das 'Top N pro Gruppe'-Muster (ROW_NUMBER + äußeres WHERE rn <= N) ist die kanonische Lösung für 'Top 3 Bestellungen pro Kunde', 'Neueste 5 Posts pro Autor' usw. Meist deutlich schneller als korrelierte Subqueries.

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;

Rahmen-Spezifikationen

Ein Rahmen definiert, welche Zeilen die Window-Funktion sieht, relativ zur aktuellen Zeile. UNBOUNDED PRECEDING..CURRENT ROW ergibt eine laufende Summe. N PRECEDING..N FOLLOWING ergibt ein gleitendes Fenster (gleitender Durchschnitt). Der Standardrahmen mit ORDER BY nutzt RANGE, das Peer-Zeilen einschließt (gleicher ORDER-BY-Wert); ROWS zählt strikt Zeilen. Rahmen vs. ORDER BY misszuverstehen ist eine häufige Quelle falscher Analytics.

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 & LEAD

LAG und LEAD greifen relativ zur aktuellen Zeile auf andere Zeilen zu — essenziell für Zeitreihenanalyse (Tagesdifferenzen, Serienerkennung). Das optionale dritte Argument ist ein Default für außerhalb liegende Zeilen. FIRST_VALUE/LAST_VALUE liefern Werte der Rahmenränder; beachte, LAST_VALUE braucht einen expliziten UNBOUNDED-FOLLOWING-Rahmen oder liefert den Wert der aktuellen Zeile (eine klassische Falle).

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 & Perzentile

NTILE(n) teilt geordnete Zeilen in n ungefähr gleiche Buckets — 4 für Quartile, 100 für Perzentile, 10 für Dezile. CUME_DIST ist der Anteil der Zeilen mit Wert <= der aktuellen Zeile; PERCENT_RANK ist der relative Rang als 0..1-Anteil. Das sind die Bausteine statistischer/analytischer Abfragen. Wie alle Window-Funktionen falten sie Zeilen nicht zusammen — in einer äußeren Abfrage umhüllen, um zu filtern.

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 (WITH-Klausel)

Einfacher CTE

Ein mit WITH definierter CTE (Common Table Expression) ist eine benannte, abgegrenzte Subquery — er verbessert die Lesbarkeit und lässt sich mehrfach in derselben Anweisung referenzieren. SQLite materialisiert CTEs typisch (schreibt sie in eine temporäre Tabelle), die Performance ähnelt also einer derived table. CTEs nutzen, um komplexe Abfragen in Stufen zu zerlegen, vorzuaggregieren oder Subquery-Wiederholungen zu vermeiden. Sie sind Standard-SQL und portabel.

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).

Mehrere CTEs

Mehrere kommaseparierte CTEs können jeweils frühere CTEs in demselben WITH referenzieren — ideal für gestufte Datenpipelines in einer einzigen Anweisung. Jeder CTE ist ein logischer Schritt; von oben nach unten zu lesen spiegelt den Datenfluss. Weit wartbarer als verschachtelte Subqueries. SQLite materialisiert jeden CTE; bei sehr großen Zwischenergebnissen eher temporäre Tabellen über Anweisungen hinweg erwägen statt einer riesigen CTE-Kette.

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;

Rekursiver CTE

Rekursive CTEs haben einen Anker (Basisfall) und ein rekursives Mitglied, verbunden durch UNION (Deduplizierung) oder UNION ALL. Sie sind der Standard-SQL-Weg, Bäume (Org-Charts, Kategoriebäume, Thread-Kommentare) und Graphen zu durchlaufen. SQLite begrenzt die Rekursionstiefe (Standard 1000 via Limit). Der 'generate series'-Trick ist unbezahlbar, da SQLite kein GENERATE_SERIES hat (die series-Erweiterung existiert allerdings).

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;

Hierarchische Abfragen

Rekursive CTEs glänzen bei selbstreferenzierenden Tabellen. Das erste Beispiel baut einen slash-getrennten Pfad von der Wurzel zu jedem Knoten — nützlich für Breadcrumbs. Das zweite findet alle Nachfahren eines Knotens (Teilbaum). UNION (nicht UNION ALL) nutzen, wenn Zyklen möglich sind, um Endlosschleifen zu verhindern; sonst ist UNION ALL schneller. Bei sehr tiefen Bäumen die Performance beobachten — rekursive CTEs können ohne passende Indizes auf der Join-Spalte langsam sein.

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);

CTE-Optimierung

SQLite 3.35+ unterstützt die Hinweise MATERIALIZED / NOT MATERIALIZED, um die CTE-Auswertung zu steuern. MATERIALIZED erzwingt einen Temp-Tabellen-Schreibvorgang (gut für teure, mehrfach referenzierte CTEs); NOT MATERIALIZED inlined den CTE, sodass der Optimierer Prädikate durchreichen kann (gut für billige CTEs). Der Planer wählt meist gut allein; Hinweise nur, wenn EXPLAIN QUERY PLAN einen schlechten Plan zeigt. Rekursive CTEs werden immer materialisiert.

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 & Wiederherstellung

.dump (Text-Backup)

.dump exportiert die Datenbank als plain SQL-INSERT-Anweisungen — menschenlesbar, diff-fähig und portabel über SQLite-Versionen und Architekturen hinweg. Das kompatibelste Backup-Format. Das Wiederherstellen gibt das SQL in eine frische Datenbank wieder. --nosys überspringt sqlite_sequence und andere interne Tabellen. Bei großen Datenbanken ist .dump langsam und erzeugt riesige Dateien; stattdessen .backup (binäre Kopie) oder die Online-Backup-API nutzen.

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 (binäre Kopie)

.backup macht eine sichere Online-Binärkopie über die SQLite-Online-Backup-API — sie funktioniert sogar, während Schreiber aktiv sind, und erzeugt einen transaktionskonsistenten Snapshot. Der empfohlene Weg, eine laufende Datenbank zu sichern. Die Ausgabe ist eine gültige SQLite-Datei, Restore ist also nur eine Dateikopie. Anders als .dump ist das Binärformat versions- und architekturkompatibel, aber nicht menschenlesbar.

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

Online-Backup-API

Die Online-Backup-API (sqlite3_backup_*) ist der Goldstandard für Backups: Sie kopiert eine live-Datenbank seitenweise, behandelt gleichzeitige Schreibvorgänge korrekt und erzeugt einen konsistenten Snapshot. Pythons sqlite3.Connection.backup() und Node's better-sqlite3 .backup() umhüllen sie. Sie funktioniert im WAL-Modus. Rohe Dateikopie kann ein korrumpiertes Backup liefern, wenn ein Schreibvorgang läuft — Dateikopie nur bei gestoppter Datenbank oder mit Datei-Snapshots.

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();

Datenbanken anhängen

ATTACH DATABASE bringt bis zu 10 Datenbankdateien in eine Verbindung als benannte Schemas (main, temp, plus angehängte). Du kannst sie abfragen, joinen und Daten kopieren mit schemaqualifizierten Namen (main.users, archive.orders). Die Basis für Archivierung (alte Zeilen in eine Archivdatei verschieben), Sharding und Merge-Workflows. DETACH schließt das Schema, lässt die Datei aber intakt. Datenbankübergreifende Transaktionen sind im WAL-Modus atomar.

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).

Wiederherstellung & Korruption

integrity_check erkennt strukturelle Korruption; .recover (3.29+) ist das beste Werkzeug, um Daten aus einer korrumpierten Datei zu retten — es extrahiert lesbare Zeilen und überspringt schlechte Seiten und erzeugt SQL, das du in eine frische Datenbank pipen kannst. Scheitert .recover, auf .dump zurückfallen und Teilverlust akzeptieren. Die meisten Korruptionen stammen von unsicheren Dateioperationen (live-DB kopieren, Netzwerk-Dateisysteme, Stromausfall mit synchronous=OFF). Vor Wiederherstellungsversuchen immer Backup.

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 & Export

CSV-Import

.import liest CSV in eine Tabelle; existiert die Tabelle nicht, erzeugt SQLite sie mit allen TEXT-Spalten (oft nicht gewollt — die Tabelle vorher mit richtigen Typen erzeugen). --skip N überspringt Headerzeilen. .mode csv konfiguriert den Parser für quotierte Felder mit eingebetteten Kommas und Zeilenumbrüchen. Bei großen Imports in BEGIN/COMMIT wickeln und PRAGMA synchronous=OFF (temporär) für 10x+ Beschleunigung. Indizes während Bulk-Loads deaktivieren und danach neu erzeugen.

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

CSV-Export

.mode csv mit .headers on erzeugt eine Standard-CSV mit Headerzeile — mit .output in eine Datei pipen. Die Shell-Flags -header -csv plus Ausgabeumleitung erledigen es in einer Zeile, ohne die CLI zu betreten. Für TSV .mode list mit .separator "\t" nutzen. Bei großen Exporten ist das streambasiert und speichereffizient. Um mehrere Tabellen zu exportieren, .output zwischen Abfragen wechseln.

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

JSON-Export

.mode json emittiert ein JSON-Objekt pro Zeile (kein umhüllendes Array). Für ein ordentliches JSON-Array json_group_array(json_object(...)) nutzen — damit hast du volle Kontrolle über Feldnamen und Verschachtelung, und es funktioniert für verschachtelte Arrays via korrelierte Subqueries. Ein mächtiger Weg, JSON-APIs direkt aus SQL ohne ORM zu bauen. Bei großen Ergebnismengen .mode json Zeile für Zeile streamen statt ein riesiges Array im Speicher zu bauen.

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;

JSON importieren

SQLite hat kein .import für JSON — die Datei als TEXT laden (via App-Code oder .read mit INSERTs), dann mit json_each/json_extract parsen. json_each(array) expandiert ein Array in Zeilen für mengenbasiertes Einfügen. Bei großen JSON-Dateien in deiner Anwendung streamend parsen (die ganze Datei nicht in den Speicher laden). Während des Ladens mit json_valid() validieren und auf JSON-Spalten ein CHECK-Constraint für dauerhafte Integrität setzen.

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 & andere Formate

Für Excel CSV exportieren (Excel öffnet es direkt); .mode excel (3.36+) ergänzt ein BOM, sodass UTF-8 korrekt gerendert wird. .mode markdown erzeugt einfügbare Tabellen-Markup für Doku. .mode insert erzeugt INSERT-Anweisungen — super, um Testdatenbanken zu seeden oder Daten zwischen Schemata zu migrieren. .mode quote quotiert jeden Wert, nützlich, wenn Daten Kommas/Zeilenumbrüche/Anführungszeichen enthalten, die andere Tools verwirren. .mode table/box zeichnen ASCII-Tabellen für Terminals.

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

Anwendungsintegration

Python (sqlite3)

Pythons sqlite3 ist in der Standardbibliothek enthalten. IMMER ?-Platzhalter (oder :name) nutzen — nie f-Strings oder % — um SQL-Injection zu verhindern und Quotierung korrekt zu handhaben. executemany ist viel schneller als execute in einer Schleife für Bulk-Inserts. row_factory = sqlite3.Row setzen für Spaltennamen-Zugriff. Context-Manager nutzen (with conn:) für automatisches Commit/Rollback. Für Nebenläufigkeit eine Verbindung pro Thread (sqlite3 verbietet standardmäßig das Teilen über Threads hinweg).

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 ist synchron (kein Callback-/Promise-Boilerplate) und der schnellste Node.js-SQLite-Treiber. Prepared Statements werden wiederverwendet — einmal vorbereiten, viele Male ausführen. .transaction(fn) wickelt die Funktion in BEGIN/COMMIT (oder ROLLBACK bei throw) und ist dramatisch schneller als Auto-Commit pro Anweisung. ?-Platzhalter nutzen, um SQL-Injection zu vermeiden. WAL für Nebenläufigkeit aktivieren. Für Promise-basierte APIs Aufrufe umhüllen oder sqlite/sqlite3 verwenden.

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();

Verbindungs-URI & Modi

URI-Dateinamen (file:...) schalten read-only, shared-cache und In-Memory-Modi frei — uri=True in Python übergeben oder den file:-String an Node-Treiber. :memory: ist standardmäßig pro Verbindung; file:name?mode=memory&cache=shared erzeugt eine benannte In-Memory-DB, die über Verbindungen im selben Prozess geteilt wird (super für Tests). Einen busy_timeout setzen, damit kurz wartende Schreiber nicht sofort fehlschlagen. Immer PRAGMA foreign_keys = ON pro Verbindung setzen.

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")

Transaktionen & Nebenläufigkeit

Transaktionen gruppieren Anweisungen atomar — alles oder nichts. SQLite erlaubt einen einzigen Schreiber zur Zeit (Datenbank-Schreiblock), aber der WAL-Modus erlaubt gleichzeitige Leser. In Python umhüllt der Default isolation_level DML in impliziten Transaktionen (Commit nötig). Für explizite Kontrolle isolation_level = None setzen und BEGIN/COMMIT selbst senden. Einen busy_timeout nutzen (PRAGMA busy_timeout oder Verbindungs-Timeout), damit kurz auf ein Lock wartende Schreiber warten statt sofort SQLITE_BUSY zu werfen.

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 & Sicherheit

Parametrisierte Abfragen (?-Platzhalter) sind die wichtigste einzelne Sicherheitspraxis — sie verhindern SQL-Injection und handhaben Typkonvertierung/Escaping korrekt. Nutzereingaben NIEMALS in SQL-Strings interpolieren. Platzhalter funktionieren nur für Werte; Bezeichner (Tabellen-/Spaltennamen) lassen sich nicht parametrisieren — gegen eine Whitelist validieren. Bei LIKE % und _ in Nutzereingaben escapen, damit Nutzer keine Wildcards injizieren. Gilt in jeder Sprache und jedem Treiber.

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

Performance-Optimierung

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN ist das Tuning-Werkzeug Nummer 1. 'USING INDEX' = gut; 'SCAN' (vollständiger Tabellenscan) = meist schlecht bei großen Tabellen. Bei Joins prüfen, dass die Join-Spalten indexiert sind und der Planer nicht die größere Tabelle scannt. EXPLAIN (ohne QUERY PLAN) zeigt den VDBE-Bytecode — selten nötig, aber nützlich für tiefes Debuggen. Der experimentelle EXPERT-Befehl kann Indizes vorschlagen (3.36+), oder der .expert-CLI-Dot-Befehl.

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 & ANALYZE

VACUUM baut die Datei neu, holt Platz aus Löschungen zurück und defragmentiert Seiten — nach großen Löschungen oder periodisch bei schreiblastigen Datenbanken ausführen. VACUUM INTO (3.27+) schreibt eine saubere Kopie, ohne die Quelle lange zu sperren. ANALYZE aktualisiert die sqlite_stat1-Statistiken, die der Abfrageplaner zur Indexwahl nutzt — nach Bulk-Loads oder Schema-/Datenänderungen ausführen. auto_vacuum=INCREMENTAL holt Platz schrittweise ohne volles VACUUM zurück.

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

Bulk-Loading

Für Bulk-Loads: in eine einzige Transaktion wickeln (der größte einzelne Beschleuniger — Autocommit pro Zeile ist katastrophal langsam), synchronous und Journaling temporär deaktivieren, Indizes löschen und neu erzeugen, und eine PRAGMA cache_size-Erhöhung erwägen. Der .import-Dot-Befehl ist schneller als INSERT-Anweisungen. Bei wirklich riesigen Lasten das .backup einer vorab gebauten Datenbank erwägen. Sicherheits-PRAGMAs nach dem Laden von Produktionsdaten immer wiederherstellen.

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 (Volltextsuche)

FTS5 ist SQLite's Volltextsuchmaschine — er indexiert Text für schnelle MATCH-Abfragen, weit schneller als LIKE '%term%', das jede Zeile scannt. Er unterstützt boolesche Operatoren (AND/OR/NOT), Präfixe (term*), Phrasen ("...") und Relevanz-Ranking. Das external-content-Muster (content='posts') vermeidet Datenduplizierung. Den FTS-Index über Trigger mit dem Basistabellen synchron halten (INSERT/UPDATE/DELETE). FTS5 ist ideal für Search-as-you-type, Autocomplete und Dokumentsuche.

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

Häufige Fallstricke & Tuning

Die meisten SQLite-Performanceprobleme stammen aus einer Handvoll Ursachen: fehlende Indizes auf WHERE/JOIN/ORDER BY-Spalten, fehlende Transaktionen (Autocommit pro Zeile ist die Ursache Nr. 1 für langsame Bulk-Inserts), still ausgeschaltete Foreign Keys und SELECT * auf breiten Tabellen. WAL und Foreign Keys in jedem Verbindungs-Setup aktivieren. Große DELETEs bündeln, um lange Schreiblocks und riesige temporäre Bereiche zu vermeiden. ANALYZE nach großen Datenänderungen ausführen, damit der Planer gute Pläne wählt.

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?