Day 10: Databases – shorturl with SQLite
Goal: Replace the in-memory store of
shorturlwith SQLite – with embedded migrations, transactions and the same contract tests as on Day 9 · Duration: approx. 120 min · Prerequisite: Day 9
Briefing
database/sqlis the interface; the drivermodernc.org/sqliteis 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_versionremembers which migration ran last; each one runs in a transaction.- The server notices nothing: it only knows the interface
store.Store. storetest.Runfrom 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;
EOFThe 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(¤t); 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/storeExpected: 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
}
EOFThe 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 -3Expected:
--- 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/shorturlExpected (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
cpwhile the server is writing. Usesqlite3 shorturl.db ".backup kopie.db"orVACUUM 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;
EOFIn 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 tostore.Storeandstoretest, 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
gooseorgolang-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.