name:: sqlite-go-best-practices
description:: SQLite best practices that must be applied by default in every Go application using database/sql with SQLite. Covers connection setup with WAL mode, foreign_keys, busy_timeout, pool settings (SetMaxOpenConns), modernc.org/sqlite (pure Go, no CGO), context-aware queries, transactions, migration patterns, prepared statements, and the Scanner interface. Always apply these patterns when creating or modifying any Go application that uses SQLite.
keywords:: sqlite go, golang sqlite, database/sql, modernc sqlite, go database, WAL mode go, go migrations, go prepared statements, go transactions, busy_timeout, foreign_keys, SetMaxOpenConns, QueryContext, ExecContext, sql.Scanner, mattn go-sqlite3, pure go sqlite
license:: MIT

SQLite Best Practices for Go

Name: tessl-labs/sqlite-go-best-practices
Rating: 98.2 (1 reviews)
Author: tessl-labs

Every Go application that uses SQLite needs these defaults from the start. Do not wait to be asked -- apply WAL mode, foreign keys, busy timeout, connection pool limits, and context-aware queries whenever you create or modify a Go service that uses SQLite. These are not optimizations to add later; they are baseline requirements that prevent data corruption, lock contention, and silent bugs.

Production SQLite patterns for Go using database/sql with modernc.org/sqlite (pure Go, no CGO).

Driver Choice: modernc.org/sqlite vs mattn/go-sqlite3

Use modernc.org/sqlite (pure Go) unless you have a specific reason to use CGO:

// RECOMMENDED -- pure Go, no CGO dependency, cross-compiles easily
import _ "modernc.org/sqlite"
// Driver name: "sqlite"

// ALTERNATIVE -- requires CGO, links to C SQLite library
import _ "github.com/mattn/go-sqlite3"
// Driver name: "sqlite3"

modernc.org/sqlite is a transpilation of the C SQLite source to pure Go. It produces the same results as the C version but eliminates CGO build complexity, simplifies cross-compilation, and works in environments where CGO is unavailable (Alpine Docker images, scratch containers, CI without C toolchain).

Use mattn/go-sqlite3 only when you need: CGO-specific SQLite extensions, custom C functions registered via sqlite3_create_function, or if benchmarks show the CGO version is measurably faster for your workload.

Connection Setup

import (
    "context"
    "database/sql"
    "fmt"

    _ "modernc.org/sqlite"
)

func OpenDB(path string) (*sql.DB, error) {
    dsn := fmt.Sprintf(
        "%s?_pragma=journal_mode(WAL)&_pragma=foreign_keys(ON)&_pragma=busy_timeout(5000)&_pragma=synchronous(NORMAL)",
        path,
    )
    db, err := sql.Open("sqlite", dsn)
    if err != nil {
        return nil, fmt.Errorf("open database: %w", err)
    }

    // SQLite only supports one concurrent writer. Setting MaxOpenConns(1)
    // serializes all database access through a single connection, preventing
    // "database is locked" errors. This is the MOST IMPORTANT setting.
    db.SetMaxOpenConns(1)
    db.SetMaxIdleConns(1)
    db.SetConnMaxLifetime(0) // Connections are not closed due to age

    // Verify the connection works and pragmas took effect
    var journalMode string
    if err := db.QueryRow("PRAGMA journal_mode").Scan(&journalMode); err != nil {
        return nil, fmt.Errorf("verify journal_mode: %w", err)
    }
    if journalMode != "wal" {
        return nil, fmt.Errorf("expected WAL journal mode, got %s", journalMode)
    }

    return db, nil
}

For tests: db, _ := OpenDB(":memory:")

Why each pragma matters

Pragma	Value	Why
`journal_mode(WAL)`	WAL	Allows concurrent readers while writing. Without WAL, readers block writers and vice versa.
`foreign_keys(ON)`	ON	SQLite disables foreign key enforcement by default. This must be set per connection -- it is not stored in the database file.
`busy_timeout(5000)`	5000ms	When another connection holds a lock, wait up to 5 seconds instead of immediately returning SQLITE_BUSY.
`synchronous(NORMAL)`	NORMAL	Safe with WAL mode. FULL is the default but adds unnecessary fsync calls when WAL is enabled.

Critical: foreign_keys is per-connection

SQLite's PRAGMA foreign_keys setting is not persistent -- it must be set on every new connection. Since database/sql manages a connection pool, you must set it via the DSN (connection string) so it applies to every connection the pool creates. Never rely on setting it once after sql.Open.

Why SetMaxOpenConns(1)

SQLite supports only one writer at a time. Go's database/sql pool will open multiple connections by default, and each connection gets its own SQLite lock. With multiple connections, concurrent writes produce "database is locked" errors. SetMaxOpenConns(1) forces all operations through a single connection, serializing access and eliminating lock contention.

If you need concurrent reads with writes, use two separate *sql.DB instances: one for writes (MaxOpenConns=1) and one for reads (can have higher MaxOpenConns). Both must connect with the same pragmas.

Migrations

var migrations = []struct {
    version int
    name    string
    sql     string
}{
    {1, "create_notes", `
        CREATE TABLE notes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            body TEXT NOT NULL DEFAULT '',
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now'))
        );
        CREATE INDEX idx_notes_created_at ON notes(created_at);
    `},
    {2, "create_tags", `
        CREATE TABLE tags (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE
        );
        CREATE TABLE note_tags (
            note_id INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
            tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
            PRIMARY KEY (note_id, tag_id)
        );
        CREATE INDEX idx_note_tags_tag_id ON note_tags(tag_id);
    `},
}

func Migrate(ctx context.Context, db *sql.DB) error {
    _, err := db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS _migrations (
        version INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        applied_at TEXT NOT NULL DEFAULT (datetime('now'))
    )`)
    if err != nil {
        return fmt.Errorf("create migrations table: %w", err)
    }

    for _, m := range migrations {
        var exists bool
        err := db.QueryRowContext(ctx,
            "SELECT EXISTS(SELECT 1 FROM _migrations WHERE version = ?)", m.version,
        ).Scan(&exists)
        if err != nil {
            return fmt.Errorf("check migration %d: %w", m.version, err)
        }
        if exists {
            continue
        }

        tx, err := db.BeginTx(ctx, nil)
        if err != nil {
            return fmt.Errorf("begin migration %d: %w", m.version, err)
        }
        defer tx.Rollback()

        if _, err := tx.ExecContext(ctx, m.sql); err != nil {
            return fmt.Errorf("migration %d (%s): %w", m.version, m.name, err)
        }
        if _, err := tx.ExecContext(ctx,
            "INSERT INTO _migrations (version, name) VALUES (?, ?)", m.version, m.name,
        ); err != nil {
            return fmt.Errorf("record migration %d: %w", m.version, err)
        }

        if err := tx.Commit(); err != nil {
            return fmt.Errorf("commit migration %d: %w", m.version, err)
        }
    }
    return nil
}

Migration rules

Run migrations in a transaction so a failed migration leaves the database unchanged
Use integer versions, not timestamps -- simpler ordering and gap detection
Store applied migrations in the database to skip already-applied ones
Use ExecContext with a context so migrations can be cancelled
Add indexes on foreign key columns (note_tags.tag_id) and columns used in WHERE/ORDER BY

Context-Aware Queries (QueryContext / ExecContext)

Always use the Context variants -- QueryContext, QueryRowContext, ExecContext, BeginTx. These allow the caller to cancel long-running queries and propagate deadlines from HTTP handlers.

// CORRECT -- context-aware, cancellable
func GetNoteByID(ctx context.Context, db *sql.DB, id int64) (*Note, error) {
    var n Note
    err := db.QueryRowContext(ctx,
        "SELECT id, title, body, created_at, updated_at FROM notes WHERE id = ?", id,
    ).Scan(&n.ID, &n.Title, &n.Body, &n.CreatedAt, &n.UpdatedAt)
    if err == sql.ErrNoRows {
        return nil, nil // Not found
    }
    if err != nil {
        return nil, fmt.Errorf("get note %d: %w", id, err)
    }
    return &n, nil
}

// WRONG -- no context, cannot be cancelled
func GetNoteByID(db *sql.DB, id int64) (*Note, error) {
    row := db.QueryRow("SELECT ...", id) // No cancellation support
    // ...
}

// List with parameterized query
func ListNotesByTag(ctx context.Context, db *sql.DB, tagName string) ([]Note, error) {
    rows, err := db.QueryContext(ctx, `
        SELECT n.id, n.title, n.body, n.created_at, n.updated_at
        FROM notes n
        JOIN note_tags nt ON nt.note_id = n.id
        JOIN tags t ON t.id = nt.tag_id
        WHERE t.name = ?
        ORDER BY n.created_at DESC`, tagName)
    if err != nil {
        return nil, fmt.Errorf("list notes by tag: %w", err)
    }
    defer rows.Close()

    var notes []Note
    for rows.Next() {
        var n Note
        if err := rows.Scan(&n.ID, &n.Title, &n.Body, &n.CreatedAt, &n.UpdatedAt); err != nil {
            return nil, fmt.Errorf("scan note: %w", err)
        }
        notes = append(notes, n)
    }
    return notes, rows.Err() // Always check rows.Err() after the loop
}

Key rules

Always defer rows.Close() on every QueryContext call -- leaked rows hold the connection
Always check rows.Err() after iterating -- the loop may exit early due to an error
Always use ? placeholders -- never interpolate values into SQL strings
Check sql.ErrNoRows for single-row queries -- it means "not found", not a failure

Transactions

func CreateNoteWithTags(ctx context.Context, db *sql.DB, title, body string, tags []string) (*Note, error) {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return nil, fmt.Errorf("begin transaction: %w", err)
    }
    defer tx.Rollback() // No-op if committed

    result, err := tx.ExecContext(ctx,
        "INSERT INTO notes (title, body) VALUES (?, ?)", title, body)
    if err != nil {
        return nil, fmt.Errorf("insert note: %w", err)
    }
    noteID, _ := result.LastInsertId()

    for _, tag := range tags {
        // Upsert the tag
        _, err := tx.ExecContext(ctx,
            "INSERT INTO tags (name) VALUES (?) ON CONFLICT (name) DO NOTHING", tag)
        if err != nil {
            return nil, fmt.Errorf("upsert tag %q: %w", tag, err)
        }

        // Link note to tag
        _, err = tx.ExecContext(ctx, `
            INSERT INTO note_tags (note_id, tag_id)
            VALUES (?, (SELECT id FROM tags WHERE name = ?))`, noteID, tag)
        if err != nil {
            return nil, fmt.Errorf("link tag %q: %w", tag, err)
        }
    }

    if err := tx.Commit(); err != nil {
        return nil, fmt.Errorf("commit: %w", err)
    }

    return GetNoteByID(ctx, db, noteID)
}

Transaction rules

defer tx.Rollback() immediately after BeginTx -- this is a no-op if Commit() succeeds, but guarantees rollback on any error path
Use tx.ExecContext / tx.QueryRowContext inside the transaction -- never use db directly inside a transaction, or the statement runs outside the transaction on a different connection
Use BeginTx (not Begin) to pass context for cancellation
Keep transactions short -- SQLite locks the database for the duration of a write transaction

The sql.Scanner Interface

Implement sql.Scanner for custom types to handle scanning from database columns:

type NoteStatus string

const (
    StatusDraft     NoteStatus = "draft"
    StatusPublished NoteStatus = "published"
    StatusArchived  NoteStatus = "archived"
)

func (s *NoteStatus) Scan(value interface{}) error {
    str, ok := value.(string)
    if !ok {
        return fmt.Errorf("NoteStatus.Scan: expected string, got %T", value)
    }
    switch NoteStatus(str) {
    case StatusDraft, StatusPublished, StatusArchived:
        *s = NoteStatus(str)
        return nil
    default:
        return fmt.Errorf("NoteStatus.Scan: invalid status %q", str)
    }
}

// Now you can scan directly into the custom type:
// var status NoteStatus
// row.Scan(&status)

Also implement driver.Valuer for inserting custom types:

func (s NoteStatus) Value() (driver.Value, error) {
    return string(s), nil
}

Data Type Conventions

Go type	SQLite type	Notes
`int64`	`INTEGER`	Use for IDs, counts, money (cents)
`string`	`TEXT`	Default for strings
`float64`	`REAL`	Avoid for money -- use INTEGER cents
`time.Time`	`TEXT`	Store as ISO 8601: `datetime('now')`
`[]byte`	`BLOB`	Binary data
`bool`	`INTEGER`	SQLite has no native bool; 0/1

Money as INTEGER cents

Store monetary values as integer cents (price_cents INTEGER NOT NULL). This avoids floating-point rounding errors. Convert to display format at the presentation layer.

Dates as TEXT (ISO 8601)

SQLite has no native date type. Store dates as TEXT in ISO 8601 format (2024-01-15T10:30:00Z). Use SQLite's built-in datetime(), date(), and time() functions for date arithmetic.

Checklist

References

modernc.org/sqlite -- pure Go SQLite driver, no CGO required
mattn/go-sqlite3 -- CGO-based SQLite driver for Go
database/sql tutorial -- official Go database/sql documentation
SQLite WAL mode -- write-ahead logging for concurrent access
SQLite PRAGMA reference -- all available PRAGMA statements
SQLite foreign key support -- why foreign_keys must be enabled per-connection
database/sql connection pool -- SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime
Go SQL driver list -- all available database/sql drivers

Verifiers

go-sqlite-setup -- SQLite connection setup with WAL, foreign keys, pool settings, and context-aware queries

evals

skills

sqlite-go-best-practices

SKILL.md

verifiers

tile.json

tessl-labs/sqlite-go-best-practices

SKILL.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}skills/sqlite-go-best-practices/