Laut Wikipedia ist “Durch die Integration in Mozilla Firefox, in den verbreiteten Mobiltelefon-Betriebssystemen (Android, iOS, Symbian OS, Windows Phone) sowie der Integration in jeder PHP-Installation ist SQLite das verbreitetste und meistverwendete Datenbanksystem der Welt.”[1]

Zwar sind Symbian OS und Windows Phone längst Geschichte und auch PHP hat SQLite seit Version 5.3 nur noch als Erweiterung eingebunden, dennoch ist SQLite auch heute noch sehr oft anzutreffen.

Ihr Hauptentwickler Dr. Richard Hipp hat in einem Talk die Geschichte von SQLite sehr eindrücklich geschildert[2].

Die Executive Summary[3] liest sich nicht weniger eindrucksvoll:

Full-featured SQL
Billions and billions of deployments
Single-file database
Public domain source code
All source code in one file (sqlite3.c)
Small footprint
Max DB size: 140 terabytes (247 bytes)
Max row size: 1 gigabyte
Faster than direct file I/O
Aviation-grade quality and testing
Zero-configuration
ACID transactions, even after power loss
Stable, enduring file format
Extensive, detailed documentation
Long-term support

Höchste Zeit also, sich SQLite genauer anzusehen!

Im Rahmen eines kleineren Go Projektes galt es eine Ablage für schemalose Daten zu implementieren. Die Datenbank sollte sich - im Idealfall - embeddeden lassen, wenig Abhängigkeiten haben und einen geringen Performance Overhead mit sich bringen.

Alle Beispiele lassen sich auch im zugehörigem Github Repository finden[4].

Ein einfacher Einstieg

Um SQLite in Go verwenden zu können, müssen wir nur einen geeigneten SQLite Driver einbinden. Wir verwenden hier github.com/mattn/go-sqlite3[5].
Dieses Modul implementiert das Standard Go database/sql Interface. Dies bedeutet, dass keinerlei weitere Abhängigkeiten benötigt werden. Das erstellte Binary enthält alle Funktionen, um SQLite Funktionalitäten zu benutzen.

Es braucht zwei Imports um mit SQLite zu arbeiten:

…
import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
)
…

Nachdem Erstellen einer SQLite Datenbank können wir ebenfalls auch eine einfache Tabelle erstellen:

func main() {
    db, err := sql.Open("sqlite3", "simple.sqlite")
    if err != nil {
        log.Panic(err)
    }

    sqlStmt := "CREATE TABLE data (id TEXT not null primary key, content TEXT);"
    _, err := db.Exec(sqlStmt)
    if err != nil {
        log.Panic(err)
    }
}

Dies erstellt die Tabelle data mit zwei Feldern id und content, wobei id der primary key ist.

Es bietet sich an, ebenfalls einen Index auf Primary Key Fields zu legen:

sqlStmt = "CREATE UNIQUE INDEX idx_data_id ON data(id);"
_, err = db.Exec(sqlStmt)
if err != nil {
    return err
}

Die nun erstellte Datenbank wird in der Datei simple.sqlite persistiert. Wir können diese Datei ebenfalls mit dem SQLite CLI Tool öffnen:

$ sqlite3 simple.sqlite
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .headers ON
sqlite> .mode column
sqlite> select * from sqlite_master;
type        name        tbl_name    rootpage    sql
----------  ----------  ----------  ----------  --------------------------------------------------------------
table       data        data        2           CREATE TABLE data (id TEXT not null primary key, content TEXT)
index       sqlite_aut  data        3
sqlite>

Die Tabelle sqlite_master, welche hier abgefragt wird, nimmt eine Sonderrolle bei SQLite ein. In dieser Tabelle sind alle Metadaten der Datenbank enthalten. Möchte man also überprüfen, ob eine Tabelle in der Datenbank schon vorhanden ist, lässt sich dies am besten mit einer Query gegen sqlite_master machen.

Die beiden Settings .headers ON und .mode column führen dazu, dass der Output von sqlite3 ein wenig schöner formatiert ist.

CRUD Funktionen

CRUD (Create, Update, Read, Delete) Funktionen lassen sich mit Go in SQLite wie bei jeder beliebigen anderen Datenbank umsetzen.

Dabei werden auch erweiterte Funktionen unterstützt, wie zum Beispiel Prepared Statements oder Transactions:

sqlStmt := fmt.Sprintf(`INSERT INTO data
    (id, content)
    VALUES
    (?, ?)`)

tx, err := db.Begin()
if err != nil {
    log.Panic(err)
}

stmt, err := tx.Prepare(sqlStmt)
if err != nil {
    return err
}
defer stmt.Close()

for i := 1; i < 10; i++ {
    id := fmt.Sprintf("%d", i)
    content := fmt.Sprintf("content #%d %s", i, shortuuid.New())
    log.Printf("Inserting %s: %s", id, content)
    _, err := stmt.Exec(id, content)
    if err != nil {
        log.Panic(err)
    }
}
tx.Commit()

Eine Abfrage der einzelnen Werte erfolgt über das Standard database/sql Pattern: Das Binden von Werten einzelner Columns an einzelne Variablen-Referenzen:

queryStmt := "SELECT id, content FROM data"
rows, err := db.Query(queryStmt)
if err != nil {
    return err
}

for rows.Next() {
    var id, content string
    err = rows.Scan(&row.id, &row.content)
    if err != nil {
        return err
    }
    log.Printf("Result: %s: %s", id, content)
}

Löschen lassen sich Einträge ebenfalls. Bei dem Aufruf von db.Exec(…) wird neben einer error Variable auch immer ein Result zurückgegeben, welches Auskunft über die ändernde Operation gibt:

queryStmt := "DELETE FROM data WHERE id > 5 "
tx, err := db.Begin()
if err != nil {
    return err
}

result, err := db.Exec(queryStmt)
if err != nil {
    return err
}
tx.Commit()

id, _ := result.LastInsertId()
numRows, _ := result.RowsAffected()

log.Printf("LastInsertId %d, RowsAffected: %d", id, numRows)

Dies kann z.B. in einem Logeintrag recht nützlich sein:

2020/02/17 19:33:27 LastInsertId 0, RowsAffected: 4

UPSERT

In manchen Situationen passiert es, dass ein Eintrag, den man gerade schreiben möchte, mit identischer ID schon vorhanden ist.

Führt man bei Go ein INSERT auf den identischen Datensatz aus, wird ein Fehler vom Typ error UNIQUE constraint failed zurückgegeben.

Man möchte nun den vorhandenen Eintrag - der die identische ID hat - stattdessen aktualisieren.

Manche DBMS bietet hierzu bereits eine eingebaute Operation an, ein sogenanntes UPSERT. SQLite schreibt dazu[6]:

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL.

Weiterhin steht dort An UPSERT is an ordinary INSERT statement that is followed by the special ON CONFLICT clause shown above.

Mit eben dieser Kombination aus INSERT und einer ON CONFLICT Behandlung sieht das dann wie folgt aus:

type entry struct {
    id      string
    content string
}

func upsertEntry(db *sql.DB, row *entry) error {

    sqlStmt := fmt.Sprintf(`INSERT INTO data
        (id, content)
        VALUES
        (?, ?)
        ON CONFLICT (id)
        DO UPDATE SET content=?
        WHERE id = ?`)

    tx, err := db.Begin()
    if err != nil {
        return err
    }

    stmt, err := tx.Prepare(sqlStmt)
    defer tx.Commit()

    if err != nil {
        return err
    }
    defer stmt.Close()

    _, err := stmt.Exec(
        row.id, row.content,
        row.content,
        row.id)
    if err != nil {
        return err
    }
}

JSON

Im heutigen Umfeld sind auch immer mehr schemalose Daten anzutreffen.
Es heißt also entweder schemalose Daten auf eine fixe Tabellenstruktur zu mappen, oder in der Lage zu sein, mit beliebigen Datenstrukturen umgehen zu können.

Der letzte Punkt hat dazu geführt, dass NOSQL (Not only SQL) Datenbanken eine große Verbreitung erlebt haben.

Viele NOSQL Datenbanken verwenden das Konzept der Documents (meist JSON), welcher als dedizierter Typ unterstützt wird und innerhalb der Datenbank abgelegt und abgefragt werden kann.

Auch SQLite kann dazu gebracht werden, Documents spezifischer JSON zu unterstützen. Um Erweiterungen neben dem DBMS-Core zu haben, unterstützt SQLite ein Plugin-Konzept - sogenannte Extensions. Damit SQLite eine gewünschte Extension verwendet, muss das Binary mit einem bestimmten Flag kompiliert werden.

Da das Go Plugin sowieso beim ersten Mal aus den Sourcen kompiliert wird, lässt sich dies sehr einfach per Go Build Tag steuern:

go build --tags="json1" main.go

Möchte man die Anwendung nur laufen lassen, geht dies mit dem gleichen Flag:

go run --tags="json1" main.go

Die json1 Extension bietet eine Reihe an Features, welche den Umgang mit JSON Daten sehr vereinfachen[7].

In unserem fiktivem Beispiel wollen wir nun eine Reihe an Tweets abspeichern und hinterher auch Abfragen.

Ein Tweet besteht aus der folgenden JSON Struktur:

{
    "id": "123122000",
    "date": "2020-01-08 08:12:23",
    "author": "dave",
    "text": "hi @alice!",
    "mentions":["alice"]
}

Wir möchten unsere Tweets in einer Datenbank abspeichern, ohne dass wir die Tabellenstruktur bei einer zukünftigen Änderung der JSON Struktur anpassen müssen.
Weiterhin wollen wir zwei Abfragen durchführen:

An der bisherigen Tabellenstruktur benötigen wir keine Anpassungen.
Diese sieht weiterhin wie folgt aus:

CREATE TABLE data (
     id TEXT not null primary key,
     content TEXT
 )

Was sich ändert sind die INSERT Statements. Wir verwenden nun die Funktion json(…), um Datensätze zu kennzeichnen welche als JSON eingefügt werden sollen:

sqlStmt := fmt.Sprintf(`INSERT INTO data
    (id, content)
    VALUES
    (?, json(?))`)

tx, err := db.Begin()
if err != nil {
    return err
}

stmt, err := tx.Prepare(sqlStmt)
if err != nil {
    return err
}
defer stmt.Close()

// utils.GetID gibt einen SHA1 Hash über den Inhalt zurück

id, err := utils.GetID(tweet)
if err != nil {
    return err
}

content, err := json.Marshal(tweet)
if err != nil {
    return err
}

_, err := stmt.Exec(id, string(content))
if err != nil {
    return err
}

Das Marshalling des Tweets in JSON und danach wieder in String ist nicht notwendig, sichert aber ab, dass nur gültiges JSON in der Datenbank abgespeichert wird.

Die ID wird hier direkt aus dem Inhalt (sha1 Hash) abgeleitet.

Führen wir mit dem Standard sqlite3 CLI Tool eine Abfrage durch, so sieht das Ergebnis aus, wie ein normaler String:

$ sqlite3 json.sqlite
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> select * from data;

id                                        content
----------------------------------------  ----------------------------------------------------------------------------------------------------------------------------
c1d08ab7952fc8732b1e195da264c70da60b31d9  {"author":"alice","content":"hello world!","data":"2020-01-01 10:15:23","id":"123123123","mentions":[]}
20b08799979a84daf181e1a6e329591ada11fbc4  {"author":"bob","content":"hi @alice!","data":"2020-01-02 08:12:23","id":"123123200","mentions":["alice"]}
bd3fed9eb4b39c47230d8a7d98d09385c5c9f69f  {"author":"hal","content":"hi @alice, hello @bob!","data":"2020-01-03 15:23:23","id":"123122001","mentions":["alice","bob"]}
sqlite>

Die wichtigsten Funktionen der Erweiterung sind:

In unserem Beispiel schauen wir uns json_extract und json_tree an. Für json_each leite ich gerne an einen passenden Blogpost über die Umsetzung einer one-to-many Beziehung über JSON Mapping in SQLite weiter[8].

Die erste Anforderung ist das Auffinden aller Tweets eines Autors. Im klassischen RDBM System würde man dies sicherlich über eine zusätzliche Spalte author und ein einfaches SELECT umsetzen - geschenkt.
Wir möchten ja nun gerade mit einer variablen Datenstruktur umgehen können.

Die komplette Go Funktion sieht wie folgt aus:

func queryCreated(db *sql.DB, user string) ([]Tweet, error) {
    tweets := make([]Tweet, 0)

    queryStmt := fmt.Sprintf(
        `SELECT content
        FROM data
        WHERE json_extract(content, '$.author')='%s'`, user)
    rows, err := db.Query(queryStmt)
    if err != nil {
        return tweets, err
    }

    for rows.Next() {
        var tweet Tweet
        var content string
        err = rows.Scan(&content)
        if err != nil {
            return tweets, err
        }
        err := json.Unmarshal([]byte(content), &tweet)
        if err != nil {
            return tweets, err
        }
        tweets = append(tweets, tweet)
    }
    return tweets, nil
}

Mit WHERE json_extract(content, '$.author')='%s' wird in jedem Datensatz die Query gegen den author Knoten des content Feldes geprüft. $.author ist hier nichts anderes als eine Pfad Angabe im JSON. Wer jq[9] kennt, dem sollte dies nicht unbekannt sein.

Der nächste Punkt ist etwas schwieriger. Ist author noch ein einzelnes Feld auf der obersten Ebene vom JSON, sind die Werte, gegen die eine Mention selektiert werden soll, die einzelnen Elemente eines Arrays innerhalb des JSONs.

Wäre die komplette JSON Struktur ein Array, würde sich hier auch die Funktion json_each anbieten. Da wir aber weiter unterhalb der JSON Struktur selektieren müssen, verwenden wir hier json_tree:

func queryMentions(db *sql.DB, user string) ([]Tweet, error) {
    tweets := make([]Tweet, 0)

    queryStmt := fmt.Sprintf(
        `SELECT content
        FROM data, json_tree(data.content, '$.mentions')
        WHERE json_tree.value = '%s'`, user)
    rows, err := db.Query(queryStmt)
    if err != nil {
        return tweets, err
    }

    for rows.Next() {
        var tweet Tweet
        var content string
        err = rows.Scan(&content)
        if err != nil {
            return tweets, err
        }
        err := json.Unmarshal([]byte(content), &tweet)
        if err != nil {
            return tweets, err
        }
        tweets = append(tweets, tweet)
    }
    return tweets, nil
}

Die Anwendung der Funktion erfolgt in zwei Schritten. Zuerst wird mit json_tree(data.content, '$.mentions') der Inhalt von data.content.mentions als JSON Tree gemappt. Im Nachfolgendem WHERE lässt sich dann mit json_tree.value = '%s' gegen die einzelnen Werte des Arrays prüfen.
Übrigens: hätte es sich bei mentions nicht um eine Array sondern um eine key->value Map gehandelt, hätten wir ebenfalls einen Check gegen den Key (z.B. json_tree.key = 'user' AND json_tree.value = '%s') machen können.

Fazit

Mir persönlich war SQLite zwar als allgegewärtige, embedded und File-based Datenbank zwar bekannt, mir war aber das Konzept der Extensions bislang unbekannt. Ebenfalls finde ich im Nachhinein die angegebenen Limits Max DB size: 140 terabytes (247 bytes) ziemlich beeindruckend, auch wenn ich persönlich den Verwendungszweck eher auf Single (oder few) Request Systeme begrenzt sehe - also eher Client-basierte Systeme oder Admin Services.

Ein großer Nachteil bei der Verwendung der JSON Extension wird sicherlich irgendwann die sinkende Performance sein, weil es bei SQLite nicht möglich ist, einen Index auf einen JSON Key zu legen[10], sondern immer nur auf eine komplette Column mit SQLite Basistypen[11].
Weiterhin bedeuten C-Bindings (welche bei der dem SQLite Modul Verwendung finden) auch immer eine Einschränkung der Cross-Compile Funktionalität von Go. Es muss zwingend ein passendender C-Compiler auf dem Development-System vorhanden sein. Weiterhin empfiehlt es sich auch, wenn möglich das jeweilige Binary direkt auf der Zielplattform zu erstellen.

Aktuell ist SQLite aber bislang für den eingesetzten Use Case die beste Entscheidung, weil es eine stabile und plattformunabhängige Datenbank zur Verfügung stellt, welche durch die schiere Anzahl der weltweiten Deployments die Produktionstauglichkeit bei Weitem bewiesen hat.

  1. SQLite: Verwendung und Verbreitung  ↩

  2. SQLite: The Database at the Edge of the Network with Dr. Richard Hipp  ↩

  3. About SQLite  ↩

  4. SQLite with Go – Examples  ↩

  5. sqlite3 driver for go using database/sql  ↩

  6. sqlite.org – UPSERT  ↩

  7. The JSON1 Extension  ↩

  8. Modeling one–to–many in SQlite using the JSON1 extension  ↩

  9. jq is a lightweight and flexible command–line JSON processor.  ↩

  10. Modeling one–to–many in SQlite using the JSON1 extension – The downsides  ↩

  11. Datatypes In SQLite Version 3  ↩

TAGS