Skip to content
Hands-on IT IT Drills

Day 10: Databases – shorturl with SQLite

Goal: Replace the in-memory store of shorturl with SQLite – with embedded migrations, transactions and the same contract tests as on Day 9 · Duration: approx. 120 min · Prerequisite: Day 9

Briefing

  • database/sql is the interface; the driver modernc.org/sqlite is pure Go – no CGO, so a static binary stays possible (Day 14).
  • The schema lives as an SQL file in the repo and is built into the binary via embed.
  • PRAGMA user_version remembers which migration ran last; each one runs in a transaction.
  • The server notices nothing: it only knows the interface store.Store.
  • storetest.Run from Day 9 checks the new implementation against the same contract.
server.New(st, ...) --> store.Store
                          |-- Memory  (Day 9, tests)
                          '-- SQLite  (Day 10) --> shorturl.db
                                 ^
               migrations/*.sql (embed) --> user_version

Drill 1: Copy and driver

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

Expected (abbreviated):

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

Why? modernc.org/sqlite is SQLite, machine-translated to Go. mattn/go-sqlite3 is the better-known alternative, but it needs CGO and therefore a C compiler for every target system.

Save your progress? Sign up for free

Drill 2: The schema as a migration

Every schema change is a numbered SQL file. STRICT makes SQLite check types like a “real” database.

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

The runner reads the files from the embedded file system and applies only new ones:

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 applies all files NNN_name.sql whose number is greater than
// PRAGMA user_version – each one in its own transaction.
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 does not support placeholders; v is a checked number.
    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

Expected: no output.

Why? //go:embed packs the SQL files into the binary at compile time – no path chaos in the container. migrate takes an fs.FS so the test can slip in its own file system. defer tx.Rollback() is the Go pattern for transactions: after Commit it does nothing; on every early return it cleans up.

Save your progress? Sign up for free

Drill 3: The SQLite store

Hit uses UPDATE ... RETURNING: incrementing and reading in one statement, so it is atomic – even with 50 parallel calls.

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

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

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

// SQLite stores links persistently in a file – without CGO.
type SQLite struct {
    db *sql.DB
}

var _ Store = (*SQLite)(nil)

// OpenSQLite opens (or creates) the database and migrates it.
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 closes all connections.
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 increments and reads in one statement – atomic without a transaction.
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 reads one row; no row means 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

The test is three lines of glue around the contract tests from Day 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 -- '---'

Expected:

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

Why? errors.AsType (new in Go 1.26) pulls the *sqlite.Error out of the error chain – type-safe instead of errors.As with a pointer variable. busy_timeout makes parallel writers wait briefly instead of failing immediately with SQLITE_BUSY; WAL allows reads during a write. You store times as Unix nanoseconds: unambiguous, sortable, no time-zone guessing.

Save your progress? Sign up for free

Drill 4: Testing transactions

SQLite can roll back even CREATE TABLE. The test proves it with a broken second migration from 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

Expected:

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

Why? The first statement of 002 (CREATE TABLE b) had already run when KAPUTT (German for “broken”) failed – the rollback undid it, and user_version stays 1. Without a transaction you would have a half-migrated schema.

Save your progress? Sign up for free

Drill 5: Switching main to SQLite

New are the flag -db (or SHORTURL_DB) and run, which opens the database and closes it only after the graceful shutdown. serve receives the store instead of building store.NewMemory() itself.

cat > cmd/shorturl/main.go <<'EOF'
// shorturl is a URL shortener with a JSON API, metrics and graceful shutdown.
// From Day 10 on, the links survive a restart (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 opens the database and closes it only after the 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,
    }

    // Kubernetes sends SIGTERM when stopping a pod; SIGINT is Ctrl+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

Expected (abbreviated):

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

Save your progress? Sign up for free

Drill 6: The tool is done

One link, two calls, a restart – the counters are still there:

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

Expected:

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

Why? While the server runs, new writes land in shorturl.db-wal; on a clean close SQLite transfers them into the main file (checkpoint) – after that only shorturl.db is left. The sqlite3 CLI comes preinstalled on macOS and is ideal for taking a look.

Warning: Never back up SQLite files with cp while the server is writing. Use sqlite3 shorturl.db ".backup kopie.db" or VACUUM INTO 'kopie.db'.

Save your progress? Sign up for free

Checkpoint

#!/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"

Expected:

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

Challenge

You want to know when a link was last used. Add a migration 002 with the column last_hit and set it in Hit. An existing shorturl.db must automatically move to version 2 on the next start.

Hint: ALTER TABLE ... ADD COLUMN and one extra parameter in the UPDATE are enough; Link and the API stay unchanged.

Solution

cat > internal/store/migrations/002_last_hit.sql <<'EOF'
-- Time of the last call (Unix nanoseconds), NULL = never
ALTER TABLE links ADD COLUMN last_hit INTEGER;
EOF

In internal/store/sqlite.go, replace the method Hit:

// Hit increments, records the time and reads – in one 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;'

Expected:

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

Save your progress? Sign up for free

Enterprise Note

  • SQLite means: one writer, one replica, the file on a PersistentVolume (not on NFS). For multiple replicas use Postgres with pgx – thanks to store.Store and storetest, that is a swap of one file.
  • Write migrations forward-only and backward-compatible (add a column first, remove the old one later) so that old and new pods run against the same schema during the rollout.
  • Larger teams use a migration tool such as goose or golang-migrate; the principle of “numbered SQL files in the repo” stays the same.
  • Automate backups (Litestream streams the WAL to S3) and test the restore regularly.
  • Every query gets the request context: if the client aborts, the query aborts too.