From Source to Service

SQLite persistence

Lesson 3 of 5

What you'll learn

Use database/sql — Go's standard, driver-agnostic SQL interface
Choose between the cgo (mattn) and pure-Go (modernc.org) SQLite drivers
Apply schema changes with a simple versioned migration pattern

Go's standard library ships database/sql: a generic SQL interface with connection pooling built in. It's like using pg/better-sqlite3 through one common API — you write against *sql.DB, and a driver (imported for its side effect) does the dialect work.

For SQLite there are two serious drivers, and the tradeoff matters for lesson 5:

github.com/mattn/go-sqlite3 — wraps the canonical C library via cgo. Fastest and most battle-tested, but you need a C toolchain, and cross-compiling becomes painful.
modernc.org/sqlite — SQLite translated to pure Go. Slightly slower, but CGO_ENABLED=0 builds and effortless cross-compiles work, which is exactly what a single-static-binary deploy wants.

import (
    "database/sql"
    _ "modernc.org/sqlite" // driver registers itself in init()
)

db, err := sql.Open("sqlite", "file:notes.db")
if err != nil { /* handle */ }

// Placeholders, never string concatenation:
row := db.QueryRow("SELECT id, title FROM notes WHERE id = ?", id)
var n Note
if err := row.Scan(&n.ID, &n.Title); err != nil { /* sql.ErrNoRows? */ }

_, err = db.Exec("INSERT INTO notes (title) VALUES (?)", title)

? placeholders are prepared statements under the hood: the SQL text and the values travel separately, so user input can never rewrite the query. This is the same reason parameterized queries beat template literals in Node — except database/sql gives you no convenient footgun to skip it.

Scan is positional and typed

row.Scan(&n.ID, &n.Title) writes columns into pointers in select-list order. Add a column to the SELECT without adding a Scan target and you get a runtime error, not a compile error. Keep queries and Scans adjacent, and let a test (lesson 4) catch drift.

Migrations without a framework

SQLite stores a schema version for you in the user_version pragma. That plus an ordered slice of SQL is a complete migration system:

var migrations = []string{
    `CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)`,
    `ALTER TABLE notes ADD COLUMN done INTEGER NOT NULL DEFAULT 0`,
}

func migrate(db *sql.DB) error {
    var v int
    db.QueryRow("PRAGMA user_version").Scan(&v)
    for i := v; i < len(migrations); i++ {
        if _, err := db.Exec(migrations[i]); err != nil {
            return fmt.Errorf("migration %d: %w", i+1, err)
        }
        db.Exec(fmt.Sprintf("PRAGMA user_version = %d", i+1))
    }
    return nil
}

Run migrate on startup, before ListenAndServe. Migrations are append-only: never edit an entry that has shipped — add a new one.

Versioned migrations (JS model)

Run it. This models the user_version migration runner: the first boot applies everything, the second applies only what's new, and already-applied migrations never re-run.

Loading editor…

Knowledge check

Why prefer modernc.org/sqlite over mattn/go-sqlite3 for a service you'll cross-compile?

Next: the store and the handlers exist — now prove they work, with table-driven tests and httptest.

Saved on this device. Sign in to sync your progress everywhere.

PreviousProject layout, modules & tooling Next Testing Go services