Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 10: Datenbanken – shorturl mit SQLite

Ziel: Den In-Memory-Speicher von shorturl durch SQLite ersetzen – mit eingebetteten Migrationen, Transaktionen und denselben Vertragstests wie in Tag 9 · Dauer: ca. 120 min · Voraussetzung: Tag 9

Briefing

  • database/sql ist die Schnittstelle, der Treiber modernc.org/sqlite ist reines Go – kein CGO, ein statisches Binary bleibt möglich (Tag 14).
  • Das Schema liegt als SQL-Datei im Repo und wird per embed ins Binary gebaut.
  • PRAGMA user_version merkt sich, welche Migration zuletzt lief; jede läuft in einer Transaktion.
  • Der Server merkt nichts: Er kennt nur das Interface store.Store.
  • storetest.Run aus Tag 9 prüft die neue Implementierung gegen denselben Vertrag.
server.New(st, ...) --> store.Store
                          |-- Memory  (Tag 9, Tests)
                          '-- SQLite  (Tag 10) --> shorturl.db
                                 ^
               migrations/*.sql (embed) --> user_version

Drill 1: Kopie und Treiber

cp -r ~/go-lab/tag09-shorturl ~/go-lab/tag10-shorturl
cd ~/go-lab/tag10-shorturl
go get modernc.org/[email protected]

Erwartet (gekürzt):

go: added github.com/dustin/go-humanize v1.0.1
go: added modernc.org/libc v1.77.1
go: added modernc.org/mathutil v1.7.1
go: added modernc.org/memory v1.12.1
go: added modernc.org/sqlite v1.60.1

Warum? modernc.org/sqlite ist SQLite, maschinell nach Go übersetzt. mattn/go-sqlite3 ist die bekanntere Alternative, braucht aber CGO und damit einen C-Compiler für jedes Zielsystem.

Fortschritt speichern? Kostenlos registrieren

Drill 2: Das Schema als Migration

Jede Schemaänderung ist eine nummerierte SQL-Datei. STRICT lässt SQLite Typen prüfen wie eine „richtige” Datenbank.

mkdir -p internal/store/migrations
cat > internal/store/migrations/001_links.sql <<'EOF'
CREATE TABLE links (
    code    TEXT PRIMARY KEY,
    url     TEXT NOT NULL,
    hits    INTEGER NOT NULL DEFAULT 0,
    created INTEGER NOT NULL -- Unix-Nanosekunden, UTC
) STRICT;
EOF

Der Runner liest die Dateien aus dem eingebetteten Dateisystem und spielt nur neue ein:

cat > internal/store/migrate.go <<'EOF'
package store

import (
    "context"
    "database/sql"
    "embed"
    "fmt"
    "io/fs"
    "path"
    "strconv"
    "strings"
)

//go:embed migrations/*.sql
var migrations embed.FS

// migrate spielt alle Dateien NNN_name.sql ein, deren Nummer größer als
// PRAGMA user_version ist – jede in einer eigenen Transaktion.
func migrate(ctx context.Context, db *sql.DB, fsys fs.FS) error {
    var current int
    if err := db.QueryRowContext(ctx, "PRAGMA user_version").
        Scan(&current); err != nil {
        return err
    }
    files, err := fs.Glob(fsys, "migrations/*.sql") // sortiert
    if err != nil {
        return err
    }
    for _, f := range files {
        num, _, _ := strings.Cut(path.Base(f), "_")
        v, err := strconv.Atoi(num)
        if err != nil {
            return fmt.Errorf("migration %s: Name muss mit Nummer beginnen", f)
        }
        if v <= current {
            continue
        }
        if err := apply(ctx, db, fsys, f, v); err != nil {
            return fmt.Errorf("migration %s: %w", f, err)
        }
    }
    return nil
}

func apply(ctx context.Context, db *sql.DB, fsys fs.FS, f string, v int) error {
    stmt, err := fs.ReadFile(fsys, f)
    if err != nil {
        return err
    }
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback() //nolint:errcheck // nach Commit ein No-op
    if _, err := tx.ExecContext(ctx, string(stmt)); err != nil {
        return err
    }
    // PRAGMA kennt keine Platzhalter; v ist eine geprüfte Zahl.
    q := fmt.Sprintf("PRAGMA user_version = %d", v)
    if _, err := tx.ExecContext(ctx, q); err != nil {
        return err
    }
    return tx.Commit()
}
EOF
go vet ./internal/store

Erwartet: keine Ausgabe.

Warum? //go:embed packt die SQL-Dateien zur Compile-Zeit ins Binary – kein Pfad-Chaos im Container. migrate nimmt ein fs.FS, damit der Test ein eigenes Dateisystem unterschieben kann. defer tx.Rollback() ist das Go-Muster für Transaktionen: Nach Commit tut es nichts, bei jedem frühen return räumt es auf.

Fortschritt speichern? Kostenlos registrieren

Drill 3: Der SQLite-Store

Hit nutzt UPDATE ... RETURNING: Hochzählen und Lesen in einer Anweisung, also atomar – auch bei 50 parallelen Aufrufen.

cat > internal/store/sqlite.go <<'EOF'
package store

import (
    "context"
    "database/sql"
    "errors"
    "time"

    "modernc.org/sqlite"
    sqlite3 "modernc.org/sqlite/lib"
)

// SQLite speichert Links dauerhaft in einer Datei – ohne CGO.
type SQLite struct {
    db *sql.DB
}

var _ Store = (*SQLite)(nil)

// OpenSQLite öffnet (oder erzeugt) die Datenbank und migriert sie.
func OpenSQLite(ctx context.Context, file string) (*SQLite, error) {
    dsn := "file:" + file + "?_pragma=busy_timeout(5000)" +
        "&_pragma=journal_mode(WAL)&_pragma=foreign_keys(1)"
    db, err := sql.Open("sqlite", dsn)
    if err != nil {
        return nil, err
    }
    if err := migrate(ctx, db, migrations); err != nil {
        db.Close() //nolint:errcheck // erster Fehler zählt
        return nil, err
    }
    return &SQLite{db: db}, nil
}

// Close schließt alle Verbindungen.
func (s *SQLite) Close() error { return s.db.Close() }

func (s *SQLite) Create(ctx context.Context, l Link) error {
    _, err := s.db.ExecContext(ctx,
        "INSERT INTO links (code, url, hits, created) VALUES (?, ?, ?, ?)",
        l.Code, l.URL, l.Hits, l.Created.UnixNano())
    if e, ok := errors.AsType[*sqlite.Error](err); ok &&
        e.Code() == sqlite3.SQLITE_CONSTRAINT_PRIMARYKEY {
        return ErrExists
    }
    return err
}

func (s *SQLite) Get(ctx context.Context, code string) (Link, error) {
    return scan(s.db.QueryRowContext(ctx,
        "SELECT code, url, hits, created FROM links WHERE code = ?", code))
}

// Hit zählt hoch und liest in einem Statement – atomar ohne Transaktion.
func (s *SQLite) Hit(ctx context.Context, code string) (Link, error) {
    return scan(s.db.QueryRowContext(ctx, `UPDATE links SET hits = hits + 1
        WHERE code = ? RETURNING code, url, hits, created`, code))
}

func (s *SQLite) Delete(ctx context.Context, code string) error {
    res, err := s.db.ExecContext(ctx, "DELETE FROM links WHERE code = ?", code)
    if err != nil {
        return err
    }
    n, err := res.RowsAffected()
    if err != nil {
        return err
    }
    if n == 0 {
        return ErrNotFound
    }
    return nil
}

// scan liest eine Zeile; keine Zeile heißt ErrNotFound.
func scan(row *sql.Row) (Link, error) {
    var l Link
    var created int64
    err := row.Scan(&l.Code, &l.URL, &l.Hits, &created)
    if errors.Is(err, sql.ErrNoRows) {
        return Link{}, ErrNotFound
    }
    l.Created = time.Unix(0, created).UTC()
    return l, err
}
EOF

Der Test ist drei Zeilen Klebstoff um die Vertragstests aus Tag 9:

cat > internal/store/sqlite_test.go <<'EOF'
package store_test

import (
    "context"
    "path/filepath"
    "testing"

    "example.com/shorturl/internal/store"
    "example.com/shorturl/internal/store/storetest"
)

func TestSQLite(t *testing.T) {
    storetest.Run(t, func(t *testing.T) store.Store {
        s, err := store.OpenSQLite(context.Background(),
            filepath.Join(t.TempDir(), "test.db"))
        if err != nil {
            t.Fatal(err)
        }
        t.Cleanup(func() { s.Close() })
        return s
    })
}
EOF
go mod tidy
go test -race -count=1 -v -run TestSQLite ./internal/store 2>&1 | grep -- '---'

Erwartet:

--- PASS: TestSQLite (0.43s)
    --- PASS: TestSQLite/CreateGet (0.01s)
    --- PASS: TestSQLite/NotFound (0.01s)
    --- PASS: TestSQLite/HitsParallel (0.40s)
    --- PASS: TestSQLite/Delete (0.01s)

Warum? errors.AsType (neu in Go 1.26) holt den *sqlite.Error aus der Fehlerkette – typsicher statt errors.As mit Zeiger-Variable. busy_timeout lässt parallele Schreiber kurz warten statt sofort mit SQLITE_BUSY zu scheitern; WAL erlaubt Lesen während eines Schreibvorgangs. Zeiten speicherst du als Unix-Nanosekunden: eindeutig, sortierbar, ohne Zeitzonen-Raten.

Fortschritt speichern? Kostenlos registrieren

Drill 4: Transaktionen testen

SQLite kann auch CREATE TABLE zurückrollen. Der Test beweist es mit einer kaputten zweiten Migration aus fstest.MapFS:

cat > internal/store/migrate_test.go <<'EOF'
package store

import (
    "context"
    "database/sql"
    "path/filepath"
    "testing"
    "testing/fstest"
)

func version(t *testing.T, db *sql.DB) int {
    t.Helper()
    var v int
    if err := db.QueryRow("PRAGMA user_version").Scan(&v); err != nil {
        t.Fatal(err)
    }
    return v
}

func TestMigrateRollback(t *testing.T) {
    ctx := context.Background()
    db, err := sql.Open("sqlite", filepath.Join(t.TempDir(), "m.db"))
    if err != nil {
        t.Fatal(err)
    }
    defer db.Close()
    fsys := fstest.MapFS{
        "migrations/001_a.sql": {Data: []byte("CREATE TABLE a (x INTEGER);")},
        "migrations/002_b.sql": {Data: []byte(
            "CREATE TABLE b (x INTEGER); KAPUTT;")}, // zweite Anweisung scheitert
    }
    if err := migrate(ctx, db, fsys); err == nil {
        t.Fatal("Fehler erwartet")
    }
    if v := version(t, db); v != 1 {
        t.Errorf("user_version = %d, want 1", v)
    }
    var n int
    err = db.QueryRow("SELECT count(*) FROM sqlite_schema WHERE name = 'b'").Scan(&n)
    if err != nil || n != 0 {
        t.Error("Tabelle b existiert: Rollback hat nicht gegriffen")
    }
    delete(fsys, "migrations/002_b.sql") // repariert: zweiter Lauf ist ein No-op
    if err := migrate(ctx, db, fsys); err != nil || version(t, db) != 1 {
        t.Errorf("zweiter Lauf: err=%v, version=%d", err, version(t, db))
    }
}
EOF
go test -count=1 -v -run TestMigrate ./internal/store 2>&1 | tail -3

Erwartet:

--- PASS: TestMigrateRollback (0.00s)
PASS
ok      example.com/shorturl/internal/store 0.199s

Warum? Die erste Anweisung von 002 (CREATE TABLE b) war schon ausgeführt, als KAPUTT scheiterte – der Rollback hat sie zurückgenommen, user_version bleibt 1. Ohne Transaktion hättest du ein halb migriertes Schema.

Fortschritt speichern? Kostenlos registrieren

Drill 5: main auf SQLite umstellen

Neu sind das Flag -db (oder SHORTURL_DB) und run, das die Datenbank öffnet und erst nach dem Graceful Shutdown schließt. serve bekommt den Store übergeben statt store.NewMemory() selbst zu bauen.

cat > cmd/shorturl/main.go <<'EOF'
// shorturl ist ein URL-Kürzer mit JSON-API, Metriken und Graceful Shutdown.
// Ab Tag 10 überleben die Links einen Neustart (SQLite).
package main

import (
    "cmp"
    "context"
    "errors"
    "flag"
    "log/slog"
    "net/http"
    "os"
    "os/signal"
    "syscall"
    "time"

    "example.com/shorturl/internal/server"
    "example.com/shorturl/internal/store"
    "github.com/prometheus/client_golang/prometheus"
    "github.com/prometheus/client_golang/prometheus/collectors"
)

func main() {
    addr := flag.String("addr", cmp.Or(os.Getenv("SHORTURL_ADDR"), "127.0.0.1:8080"),
        "Listen-Adresse")
    baseURL := flag.String("base-url", "", "öffentliche URL (Default http://ADDR)")
    dbFile := flag.String("db", cmp.Or(os.Getenv("SHORTURL_DB"), "shorturl.db"),
        "SQLite-Datei")
    flag.Parse()
    if *baseURL == "" {
        *baseURL = "http://" + *addr
    }

    log := slog.New(slog.NewJSONHandler(os.Stdout, nil))
    if err := run(*addr, *baseURL, *dbFile, log); err != nil {
        log.Error("Abbruch", "err", err)
        os.Exit(1)
    }
}

// run öffnet die Datenbank und schließt sie erst nach dem Shutdown.
func run(addr, baseURL, dbFile string, log *slog.Logger) error {
    st, err := store.OpenSQLite(context.Background(), dbFile)
    if err != nil {
        return err
    }
    defer st.Close() //nolint:errcheck // beim Beenden best effort
    log.Info("datenbank bereit", "file", dbFile)
    return serve(addr, baseURL, st, log)
}

func serve(addr, baseURL string, st store.Store, log *slog.Logger) error {
    reg := prometheus.NewRegistry()
    reg.MustRegister(collectors.NewGoCollector(),
        collectors.NewProcessCollector(collectors.ProcessCollectorOpts{}))

    srv := &http.Server{
        Addr:              addr,
        Handler:           server.New(st, log, baseURL, reg),
        ReadHeaderTimeout: 5 * time.Second, // Schutz gegen Slowloris
        ReadTimeout:       10 * time.Second,
        WriteTimeout:      10 * time.Second,
        IdleTimeout:       60 * time.Second,
    }

    // SIGTERM schickt Kubernetes beim Pod-Stopp, SIGINT ist Strg+C.
    ctx, stop := signal.NotifyContext(context.Background(),
        os.Interrupt, syscall.SIGTERM)
    defer stop()

    errc := make(chan error, 1)
    go func() {
        log.Info("shorturl startet", "addr", addr, "base_url", baseURL)
        errc <- srv.ListenAndServe()
    }()

    select {
    case err := <-errc: // z. B. Port belegt
        return err
    case <-ctx.Done():
    }
    log.Info("fahre herunter, warte auf laufende Anfragen")
    shutdownCtx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
    defer cancel()
    if err := srv.Shutdown(shutdownCtx); err != nil {
        return err
    }
    if err := <-errc; !errors.Is(err, http.ErrServerClosed) {
        return err
    }
    log.Info("sauber beendet")
    return nil
}
EOF
go vet ./... && go test -race -count=1 ./... 2>&1 | grep -v 'no test files'
go build -o shorturl ./cmd/shorturl

Erwartet (gekürzt):

ok      example.com/shorturl/internal/server    1.283s
ok      example.com/shorturl/internal/store 1.952s

Fortschritt speichern? Kostenlos registrieren

Drill 6: Das Tool ist fertig

Ein Link, zwei Aufrufe, Neustart – die Zähler sind noch da:

rm -f shorturl.db*
./shorturl > server.log &
sleep 1
curl -s localhost:8080/api/links -d '{"url":"https://go.dev","code":"godev"}' \
  | jq -c '{code, hits}'
curl -s -o /dev/null -w '%{http_code}\n' localhost:8080/godev
curl -s -o /dev/null -w '%{http_code}\n' localhost:8080/godev
ls shorturl.db*
kill -TERM %1; sleep 1
jq -c 'select(.msg != "request") | {msg, file}' server.log
./shorturl > server.log &
sleep 1
curl -s localhost:8080/api/links/godev | jq -c '{code, hits}'
kill -TERM %1; sleep 1
sqlite3 -header shorturl.db 'SELECT code, url, hits FROM links; PRAGMA user_version;'

Erwartet:

{"code":"godev","hits":0}
302
302
shorturl.db
shorturl.db-shm
shorturl.db-wal
{"msg":"datenbank bereit","file":"shorturl.db"}
{"msg":"shorturl startet","file":null}
{"msg":"fahre herunter, warte auf laufende Anfragen","file":null}
{"msg":"sauber beendet","file":null}
{"code":"godev","hits":2}
code|url|hits
godev|https://go.dev|2
user_version
1

Warum? Während der Server läuft, liegen neue Schreibvorgänge in shorturl.db-wal; beim sauberen Schließen überträgt SQLite sie in die Hauptdatei (Checkpoint) – danach ist nur noch shorturl.db übrig. Das sqlite3-CLI ist auf macOS vorinstalliert und ideal zum Nachsehen.

Achtung: SQLite-Dateien nie mit cp sichern, während der Server schreibt. Nutze sqlite3 shorturl.db ".backup kopie.db" oder VACUUM INTO 'kopie.db'.

Fortschritt speichern? Kostenlos registrieren

Kontrollpunkt

#!/usr/bin/env bash
check() { if eval "$2" >/dev/null 2>&1; then echo "✔ $1"; else echo "✘ $1"; fi; }
cd ~/go-lab/tag10-shorturl
check "gofmt sauber" "test -z \"\$(gofmt -l .)\""
check "go vet" "go vet ./..."
check "golangci-lint" "~/go-lab/bin/lint run ./..."
check "Tests mit -race" "go test -race -count=1 ./..."
check "ohne CGO baubar" "CGO_ENABLED=0 go build -o /dev/null ./cmd/shorturl"
check "Schema-Version 1" \
  "test \"\$(sqlite3 shorturl.db 'PRAGMA user_version')\" = 1"
check "Link überlebt Neustart" \
  "test \"\$(sqlite3 shorturl.db 'SELECT hits FROM links')\" = 2"

Erwartet:

✔ gofmt sauber
✔ go vet
✔ golangci-lint
✔ Tests mit -race
✔ ohne CGO baubar
✔ Schema-Version 1
✔ Link überlebt Neustart

Challenge

Du willst wissen, wann ein Link zuletzt benutzt wurde. Füge eine Migration 002 mit der Spalte last_hit hinzu und setze sie in Hit. Eine bestehende shorturl.db muss beim nächsten Start automatisch auf Version 2 kommen.

Hinweis: ALTER TABLE ... ADD COLUMN und ein zusätzlicher Parameter im UPDATE reichen; Link und die API bleiben unverändert.

Lösung

cat > internal/store/migrations/002_last_hit.sql <<'EOF'
-- Zeitpunkt des letzten Aufrufs (Unix-Nanosekunden), NULL = nie
ALTER TABLE links ADD COLUMN last_hit INTEGER;
EOF

In internal/store/sqlite.go die Methode Hit ersetzen:

// Hit zählt hoch, merkt sich den Zeitpunkt und liest – in einem Statement.
func (s *SQLite) Hit(ctx context.Context, code string) (Link, error) {
    return scan(s.db.QueryRowContext(ctx, `UPDATE links
        SET hits = hits + 1, last_hit = ?
        WHERE code = ? RETURNING code, url, hits, created`,
        time.Now().UnixNano(), code))
}
go test -race -count=1 ./internal/store && go build -o shorturl ./cmd/shorturl
./shorturl > server.log &
sleep 1
curl -s -o /dev/null -w '%{http_code}\n' localhost:8080/godev
kill -TERM %1; sleep 1
sqlite3 shorturl.db 'PRAGMA user_version;
  SELECT code, hits, last_hit IS NOT NULL FROM links;'

Erwartet:

ok      example.com/shorturl/internal/store 1.440s
302
2
godev|3|1

Fortschritt speichern? Kostenlos registrieren

Enterprise-Notiz

  • SQLite heißt: ein Schreiber, eine Replika, Datei auf einem PersistentVolume (nicht auf NFS). Für mehrere Replikas Postgres mit pgx – dank store.Store und storetest ein Austausch einer Datei.
  • Migrationen nur vorwärts und rückwärtskompatibel schreiben (erst Spalte hinzufügen, später alte entfernen), damit alte und neue Pods während des Rollouts mit demselben Schema laufen.
  • Größere Teams nehmen ein Migrationstool wie goose oder golang-migrate; das Prinzip „nummerierte SQL-Dateien im Repo” bleibt.
  • Backups automatisieren (Litestream streamt das WAL nach S3) und den Restore regelmäßig testen.
  • Jede Abfrage bekommt den Request-context: Bricht der Client ab, bricht auch die Abfrage ab.