SQLite in Go: Drivers, WAL, Locking and Production Patterns

SQLite rarely fails in Go because the database is “too small.” It fails because a *sql.DB looks like one connection while actually managing a pool, and that pool is allowed to open more writers than SQLite can execute.

The useful mental model is simple: the driver decides how SQLite enters your binary; the pool and transaction model decide whether it behaves in production.

This guide uses the current releases available on 23 September 2026 and SQLite 3.53.4. It covers embedded, single-host deployments with a real persistent disk. A database file on NFS, an ephemeral container filesystem, or a volume written by several application hosts is a different architecture, not a tuning exercise.

Which Go SQLite Driver Should You Choose?

Three drivers deserve a serious look. All expose database/sql; none changes SQLite’s one-writer rule.

mattn/go-sqlite3 is still the conservative default when CGO is acceptable. The trade-off is operational: cross-compiling now includes a C toolchain and target libc decisions.

modernc.org/sqlite removes CGO and makes static builds straightforward. Its own documentation is refreshingly explicit that CPU-bound SQLite work is slower than native C, while I/O-bound work narrows that gap. Pin the modernc.org/libc version selected by the driver’s go.mod; the project calls that dependency relationship fragile.

ncruces/go-sqlite3 is also CGO-free, but reaches SQLite through Wasm. It exposes online backup, WAL hooks, checkpoint controls, custom VFSes and typed SQLite errors. Each physical connection executes in its own Wasm sandbox, so memory per connection deserves measurement.

sqlc, sqlx, GORM and Ent are a separate layer. They generate queries, map rows or manage entities above database/sql. They are not SQLite drivers and cannot change WAL, locking or checkpoint semantics.

What Did the Drivers Cost Locally?

I ran the same database/sql harness against all three drivers on an Apple M4 Pro, macOS arm64, Go 1.26.0 and SQLite 3.53.4. Each benchmark used:

• a file-backed temporary database with 10,000 seeded rows,
• journal_mode=WAL, synchronous=NORMAL, foreign_keys=ON, busy_timeout=5000,
• SetMaxOpenConns(4) and SetMaxIdleConns(4),
• five runs at two seconds per benchmark,
• one hot primary-key QueryRow plus Scan,
• one transaction containing 100 prepared INSERTs,
• a stripped minimal binary built with -trimpath -ldflags='-s -w'.

The table reports the median of five runs. One batched-write operation is one 100-row transaction, not one row.

The native driver led this small CPU-heavy workload. ncruces came close on the batched transaction and allocated less there. modernc used the fewest bytes on the point read. Those are observations about this machine and query shape, not a universal ordering: storage latency, query plans, page-cache warmth, extension flags and concurrency can move the result.

The more durable numbers are the build properties and binary footprint. If a 15 µs difference per 100-row transaction is irrelevant beside network or disk work, a simpler release pipeline can be worth more than the benchmark lead.

Why database/sql Changes the Locking Problem

sql.Open normally does not open a connection. It returns a long-lived *sql.DB that creates and reuses physical connections as demand changes. The handle is safe for concurrent goroutines; an individual SQLite connection carries its own session state.

This distinction matters because SQLite settings have different scopes:

• journal_mode=WAL persists on the database file after it is enabled.
• foreign_keys, busy_timeout, synchronous, temporary state and many other settings are connection-local.
• a transaction and every statement executed through it stay on one physical connection.

This is unsafe:

db, err := sql.Open("sqlite", "app.db")
if err != nil {
    return err
}

_, err = db.Exec("PRAGMA foreign_keys = ON")

The Exec configures the connection leased for that call. A connection opened later by the pool may still have foreign-key enforcement disabled.

Put connection-local settings in the driver’s DSN or a connection hook. Here is a complete modernc.org/sqlite bootstrap; its validated shorthand keys are deliberately close to mattn/go-sqlite3:

package store

import (
    "context"
    "database/sql"
    "fmt"
    "net/url"
    "path/filepath"
    "time"

    _ "modernc.org/sqlite"
)

func openSQLite(ctx context.Context, path string, maxOpen int) (*sql.DB, error) {
    absolute, err := filepath.Abs(path)
    if err != nil {
        return nil, fmt.Errorf("resolve sqlite path: %w", err)
    }

    uri := &url.URL{Scheme: "file", Path: absolute}
    query := uri.Query()
    query.Set("_journal_mode", "WAL")
    query.Set("_synchronous", "NORMAL")
    query.Set("_foreign_keys", "1")
    query.Set("_busy_timeout", "5000")
    query.Set("_txlock", "immediate")
    uri.RawQuery = query.Encode()

    db, err := sql.Open("sqlite", uri.String())
    if err != nil {
        return nil, fmt.Errorf("open sqlite: %w", err)
    }

    db.SetMaxOpenConns(maxOpen)
    db.SetMaxIdleConns(maxOpen)

    pingCtx, cancel := context.WithTimeout(ctx, 3*time.Second)
    defer cancel()
    if err := db.PingContext(pingCtx); err != nil {
        db.Close()
        return nil, fmt.Errorf("ping sqlite: %w", err)
    }

    return db, nil
}

For mattn, import github.com/mattn/go-sqlite3, use driver name sqlite3, and keep the same shorthand options. For ncruces, import github.com/ncruces/go-sqlite3/driver, use driver name sqlite3, and express PRAGMAs with repeated _pragma=... parameters; _txlock=immediate is supported directly. Do not assume DSNs are portable just because the SQL API is.

After opening, query the values you depend on and fail startup if they differ. In particular, the journal_mode pragma returns the mode SQLite actually selected; asking for WAL does not prove the VFS accepted it.

synchronous=NORMAL is a deliberate latency-versus-durability choice. In WAL mode the database can remain consistent after power loss, but the latest acknowledged transactions may roll back. If that recovery-point objective is unacceptable, use FULL and measure commit latency on the real storage device.

What WAL Changes — and What It Does Not

In rollback-journal mode, a writer eventually needs exclusive access to update the main database. WAL reverses the write path: committed pages are appended to app.db-wal, while readers keep an end mark representing their snapshot.

That gives SQLite its useful production property: readers do not block a writer, and a writer does not block readers. It does not give SQLite multiple writers. The official WAL documentation is unambiguous: there is one WAL file, therefore only one writer at a time.

SQLite automatically attempts a checkpoint when the WAL reaches 1,000 pages by default. A checkpoint copies committed frames back to the main file. It can run beside readers, but it must stop before overwriting a page needed by the oldest active reader.

That is checkpoint starvation: overlapping or forgotten reads prevent a complete checkpoint, the WAL keeps growing, and reads become more expensive because more state must be considered.

Close Rows promptly and always inspect Rows.Err():

rows, err := db.QueryContext(ctx, query, args...)
if err != nil {
    return err
}
defer rows.Close()

for rows.Next() {
    if err := rows.Scan(&item.ID, &item.Name); err != nil {
        return err
    }
}
return rows.Err()

The -wal and -shm files are part of the live database state. Do not delete, move or back up the main file without them while connections are open.

One current operational requirement matters here: SQLite fixed a rare WAL-reset corruption race in 3.51.3. It affected WAL databases through 3.51.2 when multiple connections wrote or checkpointed at the same instant. The three driver versions tested here all reported SQLite 3.53.4. If you use the system SQLite or an older pinned driver, verify SELECT sqlite_version() rather than assuming the fix is present.

How Should You Shape the Pool?

database/sql defaults to an unlimited number of open connections. That is a poor default for an embedded database where every connection owns a page cache and all writers ultimately queue for one slot.

SetMaxOpenConns(1) avoids write races but also serializes reads. With WAL, a practical starting point is:

• one *sql.DB for reads with a small bounded pool, often four connections,
• one *sql.DB for writes with exactly one open connection,
• the same database path and per-connection configuration on both,
• one API boundary that prevents ad hoc writes through the read handle.

type Store struct {
    Read  *sql.DB
    Write *sql.DB
}

func Open(ctx context.Context, path string) (*Store, error) {
    readDB, err := openSQLite(ctx, path, 4)
    if err != nil {
        return nil, err
    }

    writeDB, err := openSQLite(ctx, path, 1)
    if err != nil {
        readDB.Close()
        return nil, err
    }

    return &Store{Read: readDB, Write: writeDB}, nil
}

This does not make writes faster. It makes contention explicit and bounded inside the process while preserving concurrent reads.

If writes arrive in bursts from many goroutines, put commands through one application-level writer queue. The queue can batch adjacent work into a transaction, expose queue depth, reject work when full, and apply backpressure before SQLite becomes the queue.

Why BEGIN IMMEDIATE Beats a Deferred Upgrade

SQLite transactions are deferred by default. BEGIN itself takes no write lock. A transaction can read a snapshot, do application work, then attempt its first UPDATE and discover another connection already owns the write slot. The upgrade fails with SQLITE_BUSY.

BEGIN IMMEDIATE tries to claim the write transaction at the beginning. It may still return busy, but it does so before the transaction has performed work based on a snapshot it cannot update.

Configure _txlock=immediate on the write handle when the driver supports it. Then keep the transaction brutally short:

func (store *Store) Rename(ctx context.Context, id int64, name string) error {
    tx, err := store.Write.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin rename: %w", err)
    }
    defer tx.Rollback()

    if _, err := tx.ExecContext(
        ctx,
        `UPDATE items SET name = ?, updated_at = unixepoch() WHERE id = ?`,
        name,
        id,
    ); err != nil {
        return fmt.Errorf("rename item: %w", err)
    }

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

Do validation, HTTP calls, JSON encoding and CPU-heavy transformations before BeginTx. A transaction is not a convenient scope for a request handler; it is time spent owning scarce database state.

What Does busy_timeout Actually Solve?

busy_timeout=5000 installs a per-connection busy handler. When a lock cannot be acquired, SQLite sleeps and retries until the accumulated wait reaches five seconds, then returns SQLITE_BUSY.

It is useful for millisecond-scale overlap. It is not a concurrency strategy.

Three rules keep it honest:

1. Set it on every connection through the DSN or hook.
2. Keep the timeout below the shortest request deadline; do not assume context cancellation preempts the driver’s busy handler.
3. Count busy failures and latency. A timeout that hides a permanently saturated writer only turns fast errors into slow errors.

That second point is measurable. With modernc.org/sqlite v1.59.0, a writer held the lock, busy_timeout was 5 seconds, and the competing operation had a 100 ms context deadline. It returned after about 5.06 seconds, not 100 ms, then reported context deadline exceeded. Test this interaction for your selected driver and set both limits deliberately.

Retry whole idempotent transactions, not arbitrary statements in the middle of a transaction. Use capped exponential backoff with jitter and a small attempt limit. Never retry constraints, syntax errors, corruption or disk-full failures as if they were contention.

Why :memory: Breaks Tests with a Pool

Every connection opened with the literal :memory: receives a different private database. A test creates a table, the pool opens another connection, and the next query reports no such table.

Use a named shared-memory URI when several pooled connections must see the same test database:

file:testdb?mode=memory&cache=shared

The database disappears when the last connection closes. Keep at least one idle connection alive, or use a temporary file and test the real locking path. File-backed tests catch WAL and permission mistakes that an in-memory database cannot reproduce.

How Do You Back Up a Live WAL Database?

Copying app.db alone is not a backup. Committed transactions may still exist only in app.db-wal. SQLite explicitly treats the WAL as part of the persistent state.

Use one of two database-aware paths:

• Online Backup API: copies a consistent snapshot incrementally and releases the source read lock between batches. All three drivers expose a route to the underlying backup API, though their Go APIs differ.
• VACUUM INTO: writes a compact snapshot to a new file. It is simple for scheduled backups but performs the work as one operation.

A production backup job should also:

1. write to a new path on the same reliable local filesystem,
2. move the completed file to independent storage,
3. open the backup separately and run PRAGMA integrity_check,
4. record duration, bytes and SQLite version,
5. restore a backup in automation.

An untested backup is only an optimistic file copy.

What Should You Observe?

Start with db.Stats():

• OpenConnections, InUse and Idle,
• WaitCount and WaitDuration,
• query and transaction latency split by read and write,
• SQLITE_BUSY, SQLITE_LOCKED, SQLITE_FULL, SQLITE_CORRUPT and I/O errors.

Add SQLite-specific probes:

• bytes in app.db, app.db-wal and app.db-shm,
• PRAGMA wal_checkpoint(PASSIVE) results: busy, log pages and checkpointed pages,
• periodic PRAGMA quick_check, with integrity_check in backup validation,
• writer queue depth and age if writes are serialized in the application,
• disk free space and filesystem latency.

A growing WAL with a flat checkpointed-page count points toward long readers. Rising WaitDuration on the one-connection write handle points toward write saturation. Those are different incidents and should page differently.

Use EXPLAIN QUERY PLAN before changing pool sizes. An unindexed scan held inside a read transaction can starve checkpoints; four faster connections do not repair the query. The same discipline appears in the SQL query optimization guide.

When Should You Move to PostgreSQL or MySQL?

Database size alone is a weak migration signal. SQLite can hold large databases when access remains local and the workload fits one writer.

Move to a client-server database when one of these becomes a product requirement:

• several application instances must write the same logical database,
• the database must live on a network filesystem or shared writable volume,
• sustained write demand keeps the single writer or its queue saturated,
• independent failure domains, managed failover or cross-region writes are required,
• database-level users, grants, auditing or operational tooling outweigh embedded simplicity,
• backups and maintenance cannot fit the service’s local-disk lifecycle.

Do not wait for lock timeouts to make the architecture decision for you. Define thresholds on writer queue age, busy rate, restore objectives and availability, then migrate while both systems are healthy.

If the question becomes PostgreSQL versus MySQL, the 2026 comparison separates their operational and SQL trade-offs. If you are tuning Go process density around an embedded database, the Go runtime thread investigation explains why connection count is not the only hidden concurrency cost.

The Bottom Line

SQLite is a strong production database for a Go service that owns one host, one durable filesystem and one serialized write path. WAL gives it excellent read concurrency. database/sql gives it a robust Go API. Neither removes the need to design around one writer.

Pick the driver that fits your release pipeline. Configure every physical connection. Bound the pool. Claim write intent early, leave transactions quickly, watch checkpoints, and back up through SQLite itself.

That is the difference between “a file that happened to work in development” and a deliberately operated embedded database.