PostgreSQL database engine

PostgreSQL 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 supplies the engine half of a Piko database registration. It bundles a parser that understands PostgreSQL syntax, including JSONB, arrays, partial indexes, generated columns, range types, and composite types. It also ships a type normaliser for the querier code generator and the migration dialect that drives advisory-lock-based migration runs. It does not open the connection. Pair it with a driver such as pgx or lib/pq and pass the resulting *sql.DB into db.DatabaseRegistration.

The engine is pure Go. It carries no build tags and no CGO, so it compiles on any platform and runs identically in compiled mode and in interpreted dev (dev-i) mode.

Reach for PostgreSQL when the workload depends on rich JSON manipulation, expression and partial indexes, range types, schema-scoped catalogues, or extension-driven features such as PostGIS, pgvector, and TimescaleDB. Reach for MySQL when hosting availability or read-heavy operational simplicity dominates. Pick MariaDB when you want the MySQL wire protocol with a divergent optimiser, SQLite for embedded single-node workloads, or CockroachDB when you need horizontal scale on the Postgres dialect.

This same engine kernel backs the TimescaleDB and CockroachDB engines. Both call NewPostgresEngine and layer extra types, functions, and statement hooks on top. Neither forks the parser. A fix to the Postgres parser reaches every variant.

The default Postgres() constructor uses session-level advisory locks (pg_advisory_lock) for migration coordination, which is the right choice for direct connections. If you sit behind PgBouncer in transaction pooling mode, advisory locks do not survive across statements. Use PostgresPgBouncer() instead. It is a one-line swap that changes only the lock strategy, from pg_advisory_lock to a table-based SELECT ... FOR UPDATE lock, and leaves everything else identical.

Configuration

EngineConfig is the bundle. The constructor takes no arguments. One call returns a fully populated db.EngineConfig that carries the driver name, the codegen engine, and the migration dialect, with no further configuration.

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

engineConfig := db_engine_postgres.Postgres()
// or, for PgBouncer transaction-mode pools:
// engineConfig := db_engine_postgres.PostgresPgBouncer()

Bootstrap

Plug the engine into a db.DatabaseRegistration alongside an open *sql.DB. The example uses the database/sql adapter from pgx. Any Postgres driver works.

import (
    "database/sql"
    "os"

    _ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" driver

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

connection, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
    return err
}

ssr := piko.New(
    piko.WithDatabase("primary", &db.DatabaseRegistration{
        DB:           connection,
        EngineConfig: db_engine_postgres.Postgres(),
        MigrationFS:  migrationsFS, // embed.FS of *.up.sql / *.down.sql
    }),
)
if err := ssr.Run(piko.RunModeDev); err != nil {
    return err
}

A registration that supplies DB owns the connection lifecycle. Piko does not open or close it, and it ignores the registration's own DriverName and DSN fields. The driver name comes from the engine config. Supply DriverName on the registration only when you register by DSN instead of a pre-opened DB, or when you add read replicas by DSN. Set MigrationFS to a filesystem of migration files to auto-wire a migration service for the database.

db.GetDatabaseConnection("primary"), db.GetDatabaseReader("primary"), and db.GetMigrationService("primary") retrieve the runtime services after bootstrap.

Generating query code

Typed query code comes from a separate generate phase, not from ssr.Run. Register the engine with EngineConfig and omit DB. Set MigrationFS for the schema and QueryFS for the queries, then call ssr.Generate to emit the code that the runtime consumes.

ssr := piko.New(
    piko.WithDatabase("primary", &db.DatabaseRegistration{
        EngineConfig: db_engine_postgres.Postgres(),
        MigrationFS:  migrationsFS, // schema source
        QueryFS:      queriesFS,    // *.sql query files
    }),
)
if err := ssr.Generate(context.Background(), piko.GenerateModeManifest); err != nil {
    return err
}

The generate phase parses the schema and queries against the engine catalogue. Ambiguous identifiers, missing schema qualifiers, and type-incompatible expressions surface here, at build time, before deploy.

Health diagnostics

Registering the engine adds runtime health probes for free. The engine satisfies the DatabaseHealthChecker interface, and the bootstrap discovers it by type assertion, so the probes plug into Piko monitoring with no extra wiring.

CheckHealth runs four probes against the connection. Each probe handles its own errors, so one failure does not block the others.

Database size. Reports the current database size in bytes, rendered with IEC binary suffixes.
Active connections. Counts sessions whose state is active.
Recovery state. Reports whether the server is in recovery mode.
Replication lag. Reports lag in seconds when the server is in recovery. Lag at or above 10 seconds reports DEGRADED, and lag at or above 60 seconds reports UNHEALTHY.

Tradeoffs

The Postgres parser is the largest of the engine implementations and supports nearly everything the dialect ships. It is also the most strict about correctness. Ambiguous identifiers, missing schema qualifiers, and type-incompatible expressions become generate-time errors instead of runtime failures. That strictness is the payoff. It does mean a hand-written query that works against a live Postgres can still fail catalogue analysis when it relies on implicit type coercions. The cost lands at build time, where it is cheap to fix, not in production.