SQLite

17. August 2024

SQLite ist eine kleine (leichtgewichtige) SQL-Datenbank-Engine, die ohne einen Datenbankserver auskommt. Die Datenbank ist in C geschrieben und bekannt für ihre Einfachheit und Portabilität.

Die komplette Datenbank (Schema, Daten) ist in einer einzigen Datei gespeichert. Diese Datei kann bis 281 TB groß sein. Sprich hier wird eher die Hardware zum Problem werden, bevor SQLite an seine Grenzen stößt 😜.

Anders als bei Client-/Serverdatenbanken wie MySql, MsSql, PostgreSQL, … benötigt man für den Einsatz keine Konfiguration (Zero Configuration), um die Datenbank zu verwenden.

Dennoch werden alle wichtigen Elemente einer Datenbank unterstützt:

ACID-Transaktionen (Atomicity, Consistency, Isolation, Durability)
Index, Foreign Keys (mit Einschränkungen), Trigger
Erweiterbarkeit

SQLite ist plattformunabhängig und läuft sowohl auf Windows, Linux, macOS als auch auf allen möglichen anderen Plattformen wie iOS und Android. Auch viele IoT-Geräte setzen auf SQLite, da es sich dabei um eine super leichtgewichtige Datenbank handelt. Es wird gemunkelt, dass auf der gesamten Welt ca. eine Trillion (1.000.000.000.000) SQLite-Datenbanken im Einsatz sind, was schon recht beachtlich ist, wenn man betrachtet, dass auf der Welt nur ca. 8 Milliarden Menschen leben.

SQLite unterstützt viele Lesezugriffe, aber immer nur einen Schreibzugriff. Dies klingt auf den ersten Blick nicht optimal, sollte aber im Normalfall kein großes Problem darstellen, wenn Transaktionen für das Schreiben kurz gehalten werden und die „busy_timeout“ (siehe bei Pragmas) passend gesetzt ist.

Aber schauen wir uns ein paar Besonderheiten an.

Schema

SQLite kennt nur 5 Datentypen:

Integer: Ganzzahlen
Real: Fließkommazahlen
Text
Blob: Binärdaten
NULL: kein Wert

Bei der Anlage einer Tabelle bzw. Spalten können zwar alle möglichen Typen angegeben werden, aber diese haben reinen Informationscharakter.

Die Datentypen einer Spalte sind nicht strikt, d. h., ich kann egal wie immer alles in einer Spalte speichern. Beispielsweise kann in einer Zeile in der Spalte A eine Nummer gespeichert sein, in der Nächsten ein Text.

Somit muss bei der Erstellung auch nicht unbedingt angegeben werden, um was für einen Datentyp es handelt. Die folgenden zwei Statements führen somit zum gleichen Ergebnis:

create table person (
  firstname, 
  lastname, 
  age)

create table person (
  firstname nvarchar(100),
  lastname nvarchar(100),
  age int)

Seit der Version 3.37.0 (Ende 2021) gibt es eine neue Option namens „strict“, mit der Tabellen erstellt werden können. Mit dieser Option wird sichergestellt, dass in jeder Zelle nur Daten gespeichert werden, die der Definition der Datenbank entsprechen.

Foreign Keys müssen in diesem Kontext noch explizit erwähnt werden. Diese werden unterstützt, aber sie sind standardmäßig nicht aktiviert. Diese müssen mit Hilfe eines Pragma-Statements aktiviert werden (siehe unten). Außerdem ist es zum aktuellen Zeitpunkt (3.46.1) nicht möglich, Foreign Keys auf bestehenden Tabellen zu ändern. Das ist leider ein Punkt, der nicht ganz optimal ist.

Pragmas

Pragmas sind spezielle Anweisungen, um die Konfiguration der Datenbank anzupassen und das Verhalten (Speicheroptionen, Integrität, Performance, …) zu steuern.

Die meisten Pragmas müssen beim Aufbau der Verbindung angegeben werden, da diese nicht persistiert werden.

PRAGMA cache_size
Legt die Größe des Page-Cache in Seiten fest, um den Zugriff auf die Festplatte zu minimieren. Der Standardwert sind 2000 Seiten.
PRAGMA case_sensitive_like
Bestimmt, ob der LIKE-Operator zwischen Groß- und Kleinschreibung unterscheidet.
PRAGMA foreign_keys
Aktiviert oder deaktiviert die Unterstützung für Fremdschlüssel.
PRAGMA journal_mode
Legt den Journal-Modus (z. B. DELETE, TRUNCATE, WAL) fest, der für Transaktionen verwendet wird und aussagt, wie Transaktionen protokolliert werden sollen. Der Standardwert ist DELETE.
Die Empfehlung des SQLite-Teams ist der WAL, da sich dieser Modus sehr positiv auf Schreibvorgänge auswirkt und Locking-Themen minimiert. Mehr Details dazu weiter unten.
⇒ bleibt beim Ändern persistiert.
PRAGMA synchronous
Bestimmt die Synchronisationsstufe für Transaktionen (z. B. FULL, NORMAL, OFF). Bei FULL werden Änderungen bei jeder Transaktion auf die Festplatte geschrieben, bei NORMAL nur dann, wenn es die Integrität der Datenbank erfordert, und bei OFF werden die Daten zu einem späteren Zeitpunkt auf der Festplatte persistiert. Dies hat einen direkten Einfluss auf die Performance beim Ändern von Daten – wobei FULL am Langsamsten, dafür aber am Sichersten ist und OFF am Schnellsten, dafür steigt das Risiko für Datenverlust.
PRAGMA temp_store
Bestimmt, wo temporäre Tabellen gespeichert werden (z. B. in RAM oder auf der Festplatte).
PRAGMA encoding
Gibt die Textkodierung der Datenbank an (z. B. UTF-8, UTF-16).
⇒ bleibt beim Ändern persistiert
PRAGMA integrity_check
Überprüft die Integrität der Datenbank und gibt das Ergebnis der Prüfung zurück. Diese Operation kann zeitaufwendig sein.
PRAGMA page_size
Legt die Seitenhöhe der Datenbank fest. Diese ist standardmäßig 4 KB.
⇒ bleibt beim Ändern persistiert, kann allerdings nur beim Erstellen der Datenbank festgelegt werden
PRAGMA locking_mode
Bestimmt den Sperrmodus (NORMAL oder EXCLUSIVE) der Datenbank. Der Standardwert ist NORMAL und bedeutet, dass mehrere Lesezugriffe möglich sind, aber immer nur ein Schreibzugriff. Bei EXCLUSIVE wird, sobald eine Transaktion gestartet wird, die Datenbank gesperrt und nur noch der Halter der Transaktion kann lesen und schreiben.
PRAGMA busy_timeout
Hiermit kann die Zeit in Millisekunden angegeben werden, die SQLite bei einer Sperre wartet, bevor ein Fehler ausgegeben wird (SQLITE_BUSY). Standardmäßig ist dieser Wert 0.
PRAGMA optimize
Führt ein ANALYZE für Tabellen aus, die ggf. davon profitieren.
PRAGMA analysis_limit
Definiert die Anzahl der Zeilen, die für die Analyse von Indizes und Statistiken berücksichtigt werden sollen.

Hier ein Beispiel für die Pragmas, die direkt nach dem Aufbau einer Verbindung gesetzt werden können/sollen:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = on;

Und folgende Pragmas können/sollen beim Beenden der Verbindung gemacht werden:

PRAGMA analysis_limit=1000;
PRAGMA optimize;

Statistiken

Statistiken werden benötigt, damit der Query-Planer passende Entscheidungen treffen kann, ob für eine Abfrage ein Index besser geeignet ist oder bspw. ein Full-Table-Scan.

Es wird allerdings empfohlen die Statistiken auch regelmäßig manuell zu aktualisieren (wie zuvor erwähnt bspw. beim Beenden einer Verbindung oder täglich) oder nach der Erstellung eines Index.

Dafür gibt es mehrere Möglichkeiten:

PRAGMA optimize;
ANALYZE;
ANALYZE table_name;
ANALYZE index_name;

Wie bei den Pragmas geschrieben, ist es ggf. sinnvoll, eine „analysis_limit“ zu setzen, um die Performance für die Analyse zu optimieren. Die „PRAGMA optimize“-Variante wird grundsätzlich empfohlen, da SQLite prüft, bei welchen Tabellen ein ANALYZE sinnvoll ist.

Vaccum

Werden Daten in SQLite gelöscht, so wird dieser beim Speichern nicht freigegeben, sondern die Pages als leer markiert und für spätere Operationen wiederverwendet.

Soll aufgrund einer großen Löschaktion der Speicher freigegeben werden, so kann die VACCUM-Operation ausgeführt werden:

VACCUM

Hiermit wird die Datenbank neu erstellt, was eine aufwendige Operation sein kann und daher mit Bedacht eingesetzt werden sollte.

WAL-Mode

Der WAL-Mode (Write-Ahead Logging) in SQLite ist eine Methode zur Verwaltung von Datenbanktransaktionen, die einige Vorteile gegenüber dem traditionellen Rollback-Modus (DELETE) bietet.

Im WAL-Mode werden alle Schreibvorgänge zuerst in eine separate WAL-Datei geschrieben, bevor sie in die Hauptdatenbankdatei übernommen werden. Dies ermöglicht eine schnellere Ausführung von Schreiboperationen, da die Hauptdatenbank nicht sofort aktualisiert werden muss.

Während im WAL-Mode geschrieben wird, können auch Lesevorgänge gleichzeitig stattfinden. Dies führt zu einer besseren Parallelität, da Lesevorgänge nicht durch Schreibvorgänge blockiert werden.

Explain und Explain Query Plan

Mit „EXPLAIN“ und „EXPLAIN QUERY PLAN“ werden Informationen über die Ausführung von SQL-Abfragen zurückgegeben. Dies kann nützlich bei der Suche nach Performanceproblemen von einzelnen SQL-Abfragen sein.

Mit „EXPLAIN“ werden Low-Level Informationen zurückgeliefert, während „EXPLAIN QUERY PLAN“ diese in einer für den Benutzer besser lesbaren Form zurückgibt.

Folgende Informationen werden u. a. von „EXPLAIN QUERY PLAN“ zurückgeliefert:

SCAN t1
⇒ dies ist ein Full-Table-Scan der Tabelle t1 – sprich es werden alle Zeilen gelesen
SEARCH t1 USING INDEX i1
⇒ Tabelle t1 wird unter Verwendung des Index i1 gelesen
SEARCH t1 USING COVERING INDEX i1
⇒ in diesem Fall stehen alle benötigten Informationen im Index i1 und die Tabelle t1 muss selbst gar nicht gelesen werden

SCAN bedeutet, dass immer alle Zeilen der Tabelle gelesen werden. SEARCH bedeutet, dass nur ein Teilbereich der Zeilen gelesen werden.

Bei der Analyse eine SQL-Statements prüft SQLite, ob es sinnvoll ist, temporär einen Index zu erstellen

Fulltext-Search

SQLite inkludiert die Extension FTS5 standardmäßig. Diese wird für Fulltext-Search verwendet und ermöglicht das performante Suchen von Text in großen Datenmengen sowie Funktionalität wie Ranking, Phrase-Matching, Highlighting, …

Zur Verwendung von FTS5 muss eine „virtual table“ erstellt werden, in die die zu suchenden Daten eingefügt werden:

create virtual table my_table using fts5(content)

In diesem Beispiel wird eine Tabelle „my_table“ mit einer Spalte „content“ erstellt. Zusätzlich kann der Tokenizer angegeben werden:

create virtual table my_table using fts5(content, tokenize='porter')

Anschließend werden die Daten eingefügt:

insert into my_table (rowid, content) select id, content from data;

Nun können Daten gesucht werden 🙂

select rowid from my_table where content match 'text1'
select rowid from my_table where content match 'text1*'
select rowid from my_table where content match 'text1 OR text2'
select rowid from my_table where content match 'text1 AND text2*'

Wichtig: bei Verwendung von AND/OR müssen diese Werte in Großbuchstaben geschrieben werden. In der SQLite-Dokumentation sind noch viele weitere Beispiele für die Verwendung aufgelistet.

Die Suchtabelle wird nicht automatisch befüllt; dies muss manuell gemacht werden bspw. mit Hilfe von Trigger:

CREATE TRIGGER after_insert_data
AFTER INSERT ON data
BEGIN
    INSERT INTO my_table (rowid, content) VALUES (new.id, new.content);
END;

CREATE TRIGGER after_update_data
AFTER UPDATE ON data
BEGIN
    UPDATE my_table SET content = new.content WHERE rowid = old.id;
END;

CREATE TRIGGER after_delete_data
AFTER DELETE ON data
BEGIN
    DELETE FROM my_table WHERE rowid = old.id;
END

Microsoft.Data.Sqlite

Alle folgenden Punkte bzgl. Erweiterbarkeit sind nicht auf „Microsoft.Data.Sqlite“ beschränkt, sondern können auch mit anderen Programmiersprachen gemacht werden. Da mein Anwendungsfall sich aber primär auf .NET konzentriert, habe ich diese Bibliothek verwendet.

SQLite ermöglicht es, eigene Funktionen zu implementieren und zur Verfügung zu stellen.

Scalar Function

Hiermit können Scalar-Funktionen einfach erweitert werden. Das folgende Beispiel erzeugt eine Funktion „maxlength“ und gibt den Text in der gekürzten Variante + „…“ zurück.

await using var conn = new SqliteConnection("Data Source=data.sqlite");
await conn.OpenAsync();

conn.CreateFunction(
  "maxlength",
  (string text, int maxLength) =>
  {
    if (string.IsNullOrEmpty(text))
      return text;

    if (text.Length < maxLength)
      return text;

    return $"{text.Substring(0, maxLength)}...";
  });

await using var cmd = conn.CreateCommand();
cmd.CommandText =
  """
  select maxlength(bezeichnung, 10)
  from data
  """;

Operators

Hiermit können die Operatoren „glob“, „like“, „match“ und „regexp“ überschrieben werden.

Die Verwendung ist ähnlich dem Scalar-Beispiel. Genaue Informationen zu den Parametern sind auf Microsoft Learn ersichtlich.

Aggregate Functions

Hiermit können neue Aggregat-Funktionen erstellt werden. Da dies vermutlich nicht so häufig benötigt wird, habe ich mir das nicht im Detail angesehen und verweise auch hier auf Microsoft Learn.

Backup

Es gibt mehrere Varianten, ein Backup einer SQLite-Datenbank zu erstellen.

.backup

Die einfachste Variante ist der „backup“ Befehl:

sqlite3 my_db.sqlite
.backup my_backup.sq3

Hierbei wird einfach eine Kopie der Datenbank erstellt. Dies ist an und für sich eine recht schnelle Angelegenheit, dauert aber bei größeren Datenbanken dennoch einige Sekunden bzw. ggf. Minuten und ist mitunter nicht immer optimal, da in dieser Zeit keine Schreibzugriffe möglich sind.

Litestream

Daher gibt es andere Varianten wie bspw. Litestream. Damit kann die Datenbank und die Logs in einen lokalen Pfad, ein Cloud-Service (S3, Azure Blob Storage, …) gesichert werden. Nachfolgend ein Beispiel für eine lokale Sicherung und Restore. Wichtig: Litestream funktioniert nur im WAL-Mode!

Als erstes erstellen wir eine litestream.yml.

dbs:
  - path: ./my_db.sqlite
    replicas:
      - path: ./backup

Nun können wir Litestream im „replicate“-Modus starten.

litestream replicate -config ./litestream.yml

Standardmäßig wird 1x pro Sekunde geprüft, ob neue Logs erstellt wurden und wenn ja, werden diese gesichert.

Um einen Restore einer Datenbank zu machen, muss der „restore“ Befehl aufgerufen werden.

litestream restore -config ./litestream.yml ./my_db.sqlite

Das Ganze kann auch mit einem Sidecar Docker gemacht werden. Allerdings müssen der Host und der Container das gleiche Betriebssystem sein. macOS in Kombination mit einem Linux-Container funktioniert nicht. Weitere Informationen dazu sind in der Dokumentation von Litestream.

.dump und .restore

Eine andere Variante ist das Erzeugen eines Dumps (SQL-Befehle) mit der SQLite CLI.

sqlite3 my_db.sqlite
.output dump.sql
.dump
.exit

Sollte die Datenbank kaputt sein, so kann anstatt „dump“ die Funktion „recover“ verwendet werden:

sqlite3 my_db.sqlite
.output dump.sql
.recover
.exit

Um die Datenbank wieder zu erstellen, muss folgendes gemacht werden:

sqlite3 my_new_db.sqlite
.read dump.sql
.exit

Vektordaten

Bis jetzt ist die Unterstützung für das Speichern von Vektordaten (bspw. Embeddings) in vielen Datenbanken noch nicht direkt integriert, sondern nur über Extensions lösbar. Bei SQLite schaut dies nicht anders aus. Hier gibt es die sqlite-vec Extension. Meine Tests mit C# waren erfolgreich, wenn diese auch sehr beschränkt waren (geringe Datenmenge).

Diverses

Größe von Tabellen ausgeben:

select 
  count(*) as pages, 
  sum(pgsize) / 1024 / 1024 as size_mb 
from dbstat 
where name = 'my_table'