SQLite driver (pure Go)

Pure-Go SQLite driver based on modernc.org/sqlite. It opens the database with production PRAGMAs (WAL, foreign keys on, tuned cache) and a single-connection pool that matches SQLite's single-writer model.

Overview

modernc.org/sqlite is a pure-Go transpilation of the upstream SQLite C source. There is no CGO, no C toolchain, and no shared library. The Piko wrapper calls sql.Open("sqlite", ...) and applies the same curated PRAGMA set as the CGO driver. That set covers journal_mode=WAL, synchronous=NORMAL, foreign_keys=ON, plus cache and mmap tuning. The operational characteristics line up regardless of which driver you pick. The pool fits SQLite's single-writer model with MaxOpenConns=1.

This driver is a true drop-in for the CGO driver. Both packages share a byte-for-byte identical Config struct and Open(ctx, path, Config) signature. Swap drivers by changing one import, with no other code edits. Driver choice is by import alone. Neither package carries build tags.

Reach for this driver when build-time portability matters. Examples include cross-compiling Go binaries from a Mac to a Linux ARM host. It also fits scratch-image Docker container builds, or a CI pipeline that has no C toolchain. This is also the SQLite driver that runs under Piko's interpreted development mode (dev-i), because the interpreter cannot load a CGO driver. That gives no-rebuild iteration the CGO driver cannot offer. Reach for the CGO driver when you need raw throughput or the latest SQLite features, because the transpiled tree lags upstream by a release or two. Reach for Cloudflare D1 for managed SQLite at the edge.

The wrapper seeds the busy timeout, journal mode, and foreign-key enforcement through the DSN, so they take effect from the first connection. It applies the remaining tuning PRAGMAs through explicit PRAGMA statements after opening. The CGO driver does the same for parity.

Requirements

Go only. No C toolchain, no CGO, no system SQLite, and no build tags.
Write access to the database directory. The driver creates a missing directory through safedisk with 0o750 permissions.

Configuration

import "piko.sh/piko/wdk/db/db_driver_sqlite_nocgo"

connection, err := db_driver_sqlite_nocgo.Open(ctx, "data/app.db", db_driver_sqlite_nocgo.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 path and Config. It returns a configured *sql.DB ready to register. Every Config field is optional. Config{} uses the production defaults, the right choice for most projects.

Bootstrap

Open the connection with a context, then thread the returned *sql.DB into db.DatabaseRegistration.DB.

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

connection, err := db_driver_sqlite_nocgo.Open(ctx, "data/app.db", db_driver_sqlite_nocgo.Config{})
if err != nil {
    return err
}

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

When you set DB to a pre-opened connection, the framework skips its own pool settings (MaxOpenConns, ConnMaxLifetime, and the rest). The driver's single-writer pool and PRAGMAs are authoritative. The querier system takes over from there, so you write no pool, PRAGMA, or migration glue. The same db_engine_sqlite.SQLite() EngineConfig drives both the generate sql command and runtime queries.

Tradeoffs

The pure-Go driver runs slower than the CGO driver on most workloads. The transpiled code cannot match what cc -O2 produces from the original C. For low-write workloads, the typical SQLite use case, the difference is hard to see. For write-heavy or high-concurrency reads, you may notice it. Edge cases where the modernc port and the C library diverge are uncommon but real. If a query behaves differently on the two drivers, treat the C library as the reference implementation.