---
title: "SQLite in Go: Drivers, WAL, Locking and Production Patterns"
description: "SQLite works remarkably well inside a Go service when the connection pool respects its locking model. This guide compares current drivers, measures their actual cost, and builds a production setup around WAL, bounded pools, short transactions, checkpoints, backups and observable failure modes."
author: Aleksei Aleinikov
date: 2026-09-23
lang: en
tags: [golang sqlite, go sqlite3, sqlite wal, database/sql, sqlite locking, modernc sqlite, go-sqlite3]
canonical: https://www.alekseialeinikov.com/en/blog/topics/programming/sqlite-in-go-drivers-wal-locking-production-patterns
source: alekseialeinikov.com
---
# 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.
| Driver | Implementation | CGO | License | Best fit |
|---|---|---:|---|---|
| [`github.com/mattn/go-sqlite3`](https://pkg.go.dev/github.com/mattn/go-sqlite3) v1.14.52 | Bundled SQLite C amalgamation | Yes | MIT | Mature ecosystem, native C execution, extensions through build tags |
| [`modernc.org/sqlite`](https://pkg.go.dev/modernc.org/sqlite) v1.59.0 | SQLite C translated to Go | No | BSD-3-Clause | Static cross-builds, Go tooling, no C compiler in CI |
| [`github.com/ncruces/go-sqlite3`](https://pkg.go.dev/github.com/ncruces/go-sqlite3) v0.35.5 | SQLite compiled through Wasm and translated with wasm2go | No | MIT | Portability, broad low-level SQLite API, custom VFS and backup features |
`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.
Choose the build and runtime contract first. A microbenchmark is evidence, not an architecture.
`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 `INSERT`s,
- 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.
| Driver | Point read | Read allocs | 100-row transaction | Write allocs | Binary |
|---|---:|---:|---:|---:|---:|
| mattn | **2.479 µs/op** | 623 B / 19 | **74.431 µs/op** | 18,404 B / 720 | **3.72 MB** |
| modernc | 3.385 µs/op | **543 B / 18** | 89.102 µs/op | 18,236 B / 812 | 6.38 MB |
| ncruces | 3.466 µs/op | 647 B / 18 | 84.563 µs/op | **15,932 B / 513** | 7.59 MB |
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.
`*sql.DB` is the pool boundary. Configure every connection, not whichever connection happened to execute the first PRAGMA.
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:
```go
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`:
```go
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.
WAL separates readers from the writer. It does not remove writer serialization or checkpoint pressure.
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()`:
```go
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.
```go
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:
```go
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:
```text
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](https://www.alekseialeinikov.com/en/blog/topics/data/sql-query-optimization-2026-faster-database-performance).
## 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](https://www.alekseialeinikov.com/en/blog/topics/data/postgres-vs-mysql-2026) separates their operational and SQL trade-offs. If you are tuning Go process density around an embedded database, the [Go runtime thread investigation](https://www.alekseialeinikov.com/en/blog/topics/programming/how-many-threads-go-really-uses-runtime-investigation-2026) 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.
