SQLite in Go: The cgo Decision You Make Before Anything Else

Q: Which SQLite driver should I use in Go?

Use modernc.org/sqlite unless you have measured that driver throughput is your bottleneck. It needs no cgo, so cross-compilation and static binaries keep working. Take mattn/go-sqlite3 when you need maximum write performance, SQLite extensions or connection hooks.

Q: Why does my SQLite Go binary fail with CGO_ENABLED=0?

Because mattn/go-sqlite3 requires cgo. With it disabled the package compiles to a stub and the driver is never registered, so sql.Open fails at runtime with an unknown driver. Either enable cgo or switch to the pure-Go driver.

There are two real choices for SQLite in Go: the cgo-based driver that is fastest and complete, or the pure-Go one that keeps static builds and easy cross-compilation. Pick before you write the data layer, because switching later means retesting everything.

SQLite in Go is a solved problem with one fork in the road. The long-standing driver wraps the C library through cgo. The newer one is a machine translation of SQLite’s C source into Go, with no C dependency at all.

Both implement database/sql and both work. The difference shows up in your Dockerfile, your CI, and how quickly you can cross-compile — which is to say, everywhere except the code.

The two drivers
What cgo actually costs you
Pragmas: the settings that stop the errors
The single-writer rule, and connection pools
When SQLite is the wrong answer
Deploying a Go service that uses SQLite
How this fits the rest of the stack
FAQ

The two drivers

mattn/go-sqlite3 is the established option. It binds the real SQLite C library through cgo, so you get the genuine implementation, full extension support, connection hooks and the best raw throughput.

modernc.org/sqlite is SQLite’s C source automatically transpiled to Go. No cgo, no C toolchain, no linker configuration. Slower on write-heavy benchmarks, and it lags upstream SQLite releases slightly.

Both satisfy database/sql, so the query code is identical either way. Only the import and the driver name change:

// cgo
import _ "github.com/mattn/go-sqlite3"
db, err := sql.Open("sqlite3", "file:app.db")

// pure Go
import _ "modernc.org/sqlite"
db, err := sql.Open("sqlite", "file:app.db")

Note the driver names differ by one character — sqlite3 versus sqlite. That mismatch is the first error most people hit when switching.

What cgo actually costs you

The benchmark difference between the drivers is a smaller factor in most projects than the build consequences, which people discover on deployment day.

With cgo enabled:

Cross-compilation stops being free. GOOS=linux go build from a Mac needs a Linux C cross-toolchain, not just an environment variable.
The binary is no longer static by default. It links against the system libc, so a FROM scratch image will not run it and Alpine’s musl needs its own build.
Builds are slower, and CI needs a C compiler present.
CGO_ENABLED=0 silently breaks it. Many Dockerfiles set that for smaller images, and the resulting binary compiles fine and fails at runtime with an unknown driver.

The pure-Go driver removes all four. CGO_ENABLED=0 GOOS=linux GOARCH=arm64 go build just works, the output is a static binary, and the container image can be FROM scratch.

So the decision rule is simple. If the application is write-heavy enough that driver throughput is a measured bottleneck, take cgo and accept the build complexity. Otherwise take the pure-Go driver — for the overwhelming majority of applications, the SQLite driver is not the constraint.

Pragmas: the settings that stop the errors

Default SQLite settings are conservative and will produce database is locked under any concurrency. Set these in the DSN:

dsn := "file:app.db?" +
	"_journal_mode=WAL&" +
	"_busy_timeout=5000&" +
	"_foreign_keys=on&" +
	"_synchronous=NORMAL"

db, err := sql.Open("sqlite", dsn)

What each one does:

_journal_mode=WAL — write-ahead logging, so readers do not block the writer. The single highest-value setting for a concurrent application.
_busy_timeout=5000 — wait up to 5s for a lock instead of failing instantly. Without it, brief contention surfaces as an error rather than a short pause.
_foreign_keys=on — SQLite does not enforce foreign keys unless asked, per connection.
_synchronous=NORMAL — safe with WAL and considerably faster than FULL.

Parameter names differ slightly between the two drivers, so check the driver docs when switching. The concepts are identical; the spelling is not.

The single-writer rule, and connection pools

SQLite allows one writer at a time. database/sql opens a pool of connections. Left alone, those two facts collide and produce lock errors under load that look random.

The reliable configuration is two pools — one writer, many readers:

write, _ := sql.Open("sqlite", dsn)
write.SetMaxOpenConns(1) // serialise writes in Go, not in SQLite

read, _ := sql.Open("sqlite", dsn+"&mode=ro")
read.SetMaxOpenConns(max(4, runtime.NumCPU()))

Serialising writes in the application means goroutines queue on a Go mutex instead of racing for a database lock. With WAL enabled, reads proceed concurrently and are unaffected.

Also call db.SetConnMaxLifetime(0) — SQLite connections are local file handles with no reason to expire, and recycling them costs more than it saves.

When SQLite is the wrong answer

SQLite is excellent and it has one architectural constraint that decides most cases: the database is a file on a local disk, so it belongs to exactly one machine.

That is fine, often ideal, for a CLI’s local state, a desktop application, an embedded device, a test suite, or a single-instance service with modest write volume. Read-heavy workloads on one box are genuinely faster with SQLite than with a network database, because there is no network.

It breaks the moment you want a second instance. Two containers cannot share a SQLite file safely — and network filesystems make it worse rather than better, since SQLite’s locking depends on POSIX semantics that NFS does not reliably provide. Horizontal scaling, rolling deploys and multi-region are all off the table.

The signal to move is wanting more than one running copy of the application. At that point you want a networked database, and the migration is best done before the deadline that forces it. Postgres and MySQL are managed on RunxBuild with connection limits, backups and private networking, so the Go service that was reading a local file becomes a service reading a connection string, with the deploy path unchanged.

Deploying a Go service that uses SQLite

If you stay on SQLite, the database file needs to survive deploys. A container filesystem does not — redeploy and the file is gone, which is a bad way to learn this.

The requirement is persistent storage attached to the service, with the database file on the mounted path rather than in the image. On RunxBuild, persistent storage attaches to a service and the deploy replaces the code without touching the volume.

Two more habits worth having. Back up with SQLite’s VACUUM INTO rather than copying the file, which is not safe while writes are in flight. And keep the service to a single instance — if autoscaling can start a second replica, that replica will open the same file and the corruption is on you rather than on SQLite.

How this fits the rest of the stack

The driver choice is really a build choice: cgo buys throughput and costs you static binaries and easy cross-compilation, and for most applications that is a bad trade. Set WAL and a busy timeout, serialise writes through a single-connection pool, and be honest about the single-machine constraint — the day you want two instances is the day you want Postgres. The RunxBuild hosting calculator shows a Go service with persistent storage next to the managed-database option so the two paths are comparable before you pick one.

Useful related references:

FAQ

Which SQLite driver should I use in Go?

Use modernc.org/sqlite unless you have measured that driver throughput is your bottleneck. It needs no cgo, so cross-compilation and static binaries keep working. Take mattn/go-sqlite3 when you need maximum write performance, SQLite extensions or connection hooks.

Why does my SQLite Go binary fail with CGO_ENABLED=0?

Because mattn/go-sqlite3 requires cgo. With it disabled the package compiles to a stub and the driver is never registered, so sql.Open fails at runtime with an unknown driver. Either enable cgo or switch to the pure-Go driver.

How do I fix database is locked errors?

Enable WAL journal mode and set a busy timeout in the DSN, then limit the writing pool to a single connection with SetMaxOpenConns(1) and use a separate read-only pool for queries. That serialises writes in Go rather than letting connections contend for the file lock.

Can I run SQLite with multiple application instances?

No. The database is a local file with a single-writer lock, and network filesystems do not provide the locking semantics SQLite relies on. Multiple instances mean a networked database such as Postgres or MySQL.

Does the SQLite file survive a container redeploy?

Only if it lives on persistent storage mounted into the service. A file written to the container filesystem disappears with the container, so attach a volume and point the DSN at the mounted path rather than a path inside the image.