go / sqlite

updated September 27, 2026

I use SQLite when I ship a single-binary web server with an embedded database. It is the low-dependency counterpart to my Postgres setup.

Driver

I use modernc.org/sqlite, a pure-Go driver that does not require Cgo. Without Cgo, cross-compilation is simple.

go mod init server
go get modernc.org/sqlite

Concurrent access to SQLite causes database is locked (5) (SQLITE_BUSY) errors. I configure the connection per David Crawshaw's one process programming notes and cap the pool at one connection.

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"

    _ "modernc.org/sqlite"
)

func initDB(db *sql.DB) error {
    pragmas := []string{
        "PRAGMA temp_store = memory",    // Store temp tables in memory
        "PRAGMA mmap_size  = 268435456", // 256MB memory-mapped I/O
        "PRAGMA cache_size = 10000",     // Cache size in pages
    }

    for _, pragma := range pragmas {
        if _, err := db.Exec(pragma); err != nil {
            return fmt.Errorf("failed to set pragma %s: %w", pragma, err)
        }
    }

    return nil
}

type Server struct {
    db *sql.DB
}

func (s *Server) health(w http.ResponseWriter, r *http.Request) {
    var got int
    if err := s.db.QueryRow("SELECT 1").Scan(&got); err != nil {
        http.Error(w, "Database error", 500)
        return
    }

    w.Write([]byte("OK"))
}

func main() {
    conn := "app.db?" +
        "_busy_timeout=5000&" +  // Avoid immediate lock failures in concurrent access
        "_journal_mode=WAL&" +   // Better concurrency than default rollback journal
        "_synchronous=NORMAL&" + // Faster writes while maintaining crash safety
        "_foreign_keys=ON"       // Enforce referential integrity
    db, err := sql.Open("sqlite", conn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // For a single-process web server,
    // limit connections to prevent lock contention
    db.SetMaxOpenConns(1)
    db.SetMaxIdleConns(1)
    db.SetConnMaxLifetime(0) // Keep connections alive

    if err := initDB(db); err != nil {
        log.Fatal("Failed to initialize database:", err)
    }

    server := &Server{db: db}
    http.HandleFunc("/health", server.health)
    log.Fatal(http.ListenAndServe(":8080", nil))
}

Query organization

SQL lives in .sql files beside the Go code, one statement per file, embedded in the binary:

app/
  queries/
    game_extras_get.sql
    game_extras_upsert.sql
  db.go

A single package holds the whole app, so one loader reads every file at startup into a map, and q reads the map:

//go:embed queries/*.sql
var queriesFS embed.FS

var queries = mustLoadQueries()

func mustLoadQueries() map[string]string {
    entries, err := iofs.ReadDir(queriesFS, "queries")
    if err != nil {
        panic(err)
    }
    out := map[string]string{}
    for _, e := range entries {
        b, err := iofs.ReadFile(queriesFS, "queries/"+e.Name())
        if err != nil {
            panic(err)
        }
        out[strings.TrimSuffix(e.Name(), ".sql")] = string(b)
    }
    return out
}

// q returns the named query; a missing name is a programming error.
func q(name string) string {
    s, ok := queries[name]
    if !ok {
        panic("unknown query " + name)
    }
    return s
}

SQLite takes ? placeholders, and database/sql scans into variables:

if err := h.db.QueryRow(q("game_extras_get"), id).Scan(&js); err != nil {
    return err
}

The Postgres version differs in two places: a package there embeds its own queries/ and binds each file to a var, and pgx takes $1 and maps rows to structs. Here the map costs less than a var per statement, and a name that does not exist panics at the call rather than at startup.

No code generation. The query and the call site sit in one package, so a rename touches the .sql file and the compile errors it causes.

A test closes the gap the map leaves. It reads both directions: every name the code passes to q has a file, and every file has a caller.

for _, e := range entries {
    is.True(refs[strings.TrimSuffix(e.Name(), ".sql")])
}

A dead .sql file fails the test, and so does a typo in a name that only one code path reaches.

Testing

Each test gets its own :memory: database and needs no truncate step.

import (
    "database/sql"
    "net/http"
    "net/http/httptest"
    "testing"

    _ "modernc.org/sqlite"
)

func initTestDB(t *testing.T) (*sql.DB, *Server) {
    t.Helper()

    db, err := sql.Open("sqlite", ":memory:?_foreign_keys=ON")
    if err != nil {
        t.Fatalf("Failed to create test database: %v", err)
    }

    if err := initDB(db); err != nil {
        db.Close()
        t.Fatalf("Failed to initialize test database: %v", err)
    }

    server := &Server{db: db}
    return db, server
}

func TestHealthCheck(t *testing.T) {
    db, server := initTestDB(t)
    defer db.Close()

    req, err := http.NewRequest("GET", "/health", nil)
    if err != nil {
        t.Fatalf("Failed to create request: %v", err)
    }

    rr := httptest.NewRecorder()
    http.HandlerFunc(server.health).ServeHTTP(rr, req)

    if rr.Code != 200 {
        t.Errorf("Expected status 200, got %d", rr.Code)
    }

    if rr.Body.String() != "OK" {
        t.Errorf("Expected body 'OK', got '%s'", rr.Body.String())
    }
}

Upgrades

I upgrade SQLite by updating the Go driver.

go get -u modernc.org/sqlite
go mod tidy

The Go compiler embeds SQLite into the server binary through modernc.org/sqlite. The system has no database server process and no system catalog to migrate.

I rebuild and deploy the binary. To roll back, I deploy the previous binary. go.sum pins the version, so every build stays reproducible.

The SQLite file format has stayed stable since 2004. A new SQLite release reads older database files. An older SQLite release also reads newer database files. An upgrade needs no data migration and no maintenance window.

I update the driver when I update other dependencies. I update earlier when the SQLite changelog lists a correctness fix for a feature I use, such as WAL mode.

The driver bundles a specific SQLite release. Its release notes name the bundled version.

A Postgres major release requires pg_upgrade and planned downtime. The Postgres server owns the data directory and changes the disk layout across major versions. SQLite avoids that maintenance because the library runs inside the application process.

When I outgrow a single instance or need richer SQL, I move to Postgres.

For snapshot backups of the database file, see cmd / r2.

← All articles