SQLite cgo driver

CGO-backed SQLite driver based on github.com/mattn/go-sqlite3, opened with production-grade PRAGMAs (WAL, foreign keys on, sensible cache size) and a single-connection pool that matches SQLite's writer model.

Overview

This is the canonical SQLite driver for Go. mattn/go-sqlite3 links against the upstream SQLite C library and exposes the full feature surface. That covers FTS5, JSON1, R-Tree, custom collations, and the latest SQLite point releases as the binding tracks them. Open opens the connection, pings it, and applies a curated set of production PRAGMAs. WAL journaling, foreign keys on, and a sensible page cache mean you do not relearn SQLite's tuning on every project. It also fixes the pool to a single connection (MaxOpenConns=1) to match SQLite's single-writer model. You hand the result straight to piko and write no PRAGMA or pool glue.

Reach for the CGO driver when you need SQLite features that the pure-Go driver lags on (FTS5 specifics, R-Tree, custom collations and aggregate functions, the latest SQLite version). It also fits when write throughput matters. The C library is the upstream reference implementation, and it targets higher throughput than the transpiled pure-Go variant on write-heavy workloads. It also fits when you ship to environments that already accept a CGO toolchain. Reach for the No-CGO driver when you need cross-compilation, scratch-image Docker builds, or to drop the C toolchain entirely. Reach for Cloudflare D1 for SQLite at the edge.

Requirements

CGO_ENABLED=1 at build time. The default go build honours this when a C toolchain is present, but you must set it explicitly for cross-compilations (for example GOOS=linux CGO_ENABLED=1).
A C toolchain (gcc or clang) on the build host. mattn/go-sqlite3 bundles SQLite itself, so the build does not need a system SQLite.
Write access to the directory containing the database file (the driver creates it with 0o750 permissions if missing).
Compiled run modes only. This driver links a C library, so the interpreted dev mode (dev-i) cannot load it from component code. Open the connection in your compiled host or bootstrap. The No-CGO driver is pure Go and runs under dev-i.

Configuration

import (
    "context"

    "piko.sh/piko/wdk/db/db_driver_sqlite_cgo"
)

connection, err := db_driver_sqlite_cgo.Open(context.Background(), "data/app.db", db_driver_sqlite_cgo.Config{
    BusyTimeoutMs:    10_000,            // optional, default 10000 ms
    CachePages:       -20_000,           // optional, default -20000 (about 20 MB)
    MmapSize:         64 * 1024 * 1024,  // optional, default 64 MB
    JournalSizeLimit: 32 * 1024 * 1024,  // optional, default 32 MB
})
if err != nil {
    return err
}

Open takes a context.Context first, then the file path, then the config. It pings the connection before returning, so a missing path or locked file surfaces as an error here at startup, not later at the first query.

Every Config field is optional. Passing Config{} uses the production defaults baked into the package, the right choice for most projects.

Bootstrap

import (
    "piko.sh/piko"
    "piko.sh/piko/wdk/db"
    "piko.sh/piko/wdk/db/db_engine_sqlite"
)

ssr := piko.New(
    piko.WithDatabase("primary", &db.DatabaseRegistration{
        DB:           connection,
        EngineConfig: db_engine_sqlite.SQLite(),
        MigrationFS:  migrationsFS,
    }),
)

Pass the pre-opened connection as DatabaseRegistration.DB. When you set DB, piko ignores DSN and DriverName and does not apply its own pool settings, because the driver already owns the connection. Do not set those fields alongside DB. The EngineConfig from db_engine_sqlite.SQLite() drives codegen, the migration dialect, and the SQL parser. It is the same engine config for both SQLite drivers, so swapping the CGO driver for the No-CGO driver needs no query changes.

Tradeoffs

The CGO build constrains where the binary goes. Cross-compiling to other platforms requires a cross-toolchain (zig cc, xx, osxcross). Docker scratch images cannot run the resulting binary without bundling a glibc or musl userspace, and CI pipelines that lack a C toolchain need adjusting. For most projects CGO is fine, and the full SQLite feature surface pays for it. Reach for the pure-Go driver when build-time portability matters more than runtime throughput.