SQLite database engine

SQLite engine for Piko's database service. It bundles a full DDL/DML parser, type resolver, and migration dialect into a single EngineConfig.

Overview

The package provides the engine half of a Piko database registration. SQLite() returns a fully wired db.EngineConfig that bundles the engine adapter and the migration dialect, so registration is a single struct field. The engine is a hand-written parser for SQLite DDL and DML. It plugs into the querier through the EnginePort interface. That port supplies statement parsing, type resolution, RETURNING support, and window, JSON, and bitwise expression features. The code generator and querier reuse those features across every dialect.

The engine maps an ordinary table's INTEGER to a 32-bit Go int for parity with Postgres and MySQL, while honouring SQLite's 64-bit rowid and STRICT-table rules. Generated Go types stay consistent when you switch engines.

Piko applies no extra migration lock for SQLite. It relies on SQLite's own single-file locking, which suits the single-writer model.

SQLite's character is zero-server, single-file, embedded. The whole database lives in one file on disk, with no daemon, socket, or port. SQLite suits these shapes of work:

  • Read-heavy workloads. Concurrent reads scale to disk IOPS.
  • A low writer count. One process at a time, or processes coordinating through SQLite's own locking.
  • A config-free dev or test database that you can also ship to production for the right workload.

Choose a different engine when SQLite does not fit:

  • Postgres for network-attached writes from concurrent processes, expression indexes on JSON, or full-text search beyond FTS5.
  • DuckDB for analytical (OLAP) workloads. DuckDB and SQLite share the embedded model, and DuckDB's columnar storage handles aggregations over millions of rows.
  • Cloudflare D1 for SQLite at the edge.

This is the engine package only. It does not open a connection. Pair it with one of the SQLite drivers. Choose db_driver_sqlite_cgo for the mattn/go-sqlite3 driver (FTS5, JSON1, the full feature set, requires CGO). Choose db_driver_sqlite_nocgo for the modernc pure-Go build, which cross-compiles without CGO or system libraries.

Configuration

EngineConfig is the bundle. The constructor takes no arguments.

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

engineConfig := db_engine_sqlite.SQLite()

Bootstrap

The driver Open helper takes a leading context.Context. It opens the file with production PRAGMAs (WAL, busy_timeout, foreign_keys=ON, synchronous=NORMAL) and pins the pool to a single connection for the single-writer model.

import (
    "context"

    "piko.sh/piko/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 on the registration, Piko leaves the connection pool to you and does not apply its own pool settings. The driver Open helpers supply the tuned defaults, including SetMaxOpenConns(1). If you open a bare *sql.DB yourself, set the pool to one open connection. Otherwise concurrent writers raise database is locked errors.

Add a QueryFS to the registration alongside MigrationFS and EngineConfig to drive typed code generation. The engine is what lets the generate sql command produce typed Go query methods for SQLite.

The nocgo driver is pure Go, so it builds and runs in interpreted dev mode (dev-i) and cross-compiles without a C toolchain. The cgo driver requires CGO_ENABLED=1 and a C compiler, so it runs in compiled mode only.

See also

SQLite drivers:

Other database engines:

  • PostgreSQL, server-based, richer SQL surface.
  • MySQL, widely available managed offering.
  • DuckDB, embedded analytical engine; pairs well with SQLite for OLTP+OLAP.

Catalogue and codegen:

Framework docs:

External: