CockroachDB database engine

CockroachDB engine for Piko's database service. It bundles a Postgres-compatible parser, type resolver, and migration dialect into a single EngineConfig.

Overview

CockroachDB speaks the PostgreSQL wire protocol and accepts most Postgres SQL. Piko leans into that. The engine is the PostgreSQL engine configured with four CockroachDB dialect options. Those options set the cockroachdb dialect name, add CockroachDB type aliases, add a type normaliser hook, and register CockroachDB built-in functions. The whole engine package is configuration over the Postgres engine, so codegen, migrations, and the querier behave as they do for Postgres. CockroachDB is a distributed SQL system that scales horizontally by adding nodes, with strong consistency (Raft-replicated ranges) and survivability across zone or region failures.

The engine is pure Go with no CGO and no build tags, so it runs identically in compiled builds and in interpreted (dev-i) mode.

Reach for CockroachDB when horizontal scale and high availability dominate the requirements. Examples include multi-region writes with bounded staleness, automatic failover without operator intervention, or a dataset that has already outgrown a single Postgres primary. Reach for PostgreSQL when a single-primary deployment is enough. Postgres wins on single-node throughput, breadth of extension ecosystem (PostGIS, pgvector, TimescaleDB), and operational familiarity. Reach for MySQL when operations standardise on MySQL replication, or SQLite for embedded.

CockroachDB inherits Postgres conventions with caveats. Advisory locks exist but behave differently. Sequences exist, but unique_rowid() is the standard replacement. CockroachDB does not support every Postgres extension. The migration dialect Piko ships uses the standard Postgres advisory-lock pattern, which works against current CockroachDB. To override it, register a custom MigrationDialect on the DatabaseRegistration.

CockroachDB-aware codegen

The engine teaches the shared Postgres catalogue CockroachDB-idiomatic SQL, so hand-written queries against CockroachDB resolve to concrete Go types instead of any.

  • Type aliases. STRING and BYTES normalise to text and bytea. The engine ignores length modifiers such as STRING(50), which do not change the Go type.
  • Integer width. INT, INTEGER, and SERIAL resolve to a 64-bit integer (int8), not the 32-bit int4 Postgres uses. A column migrated from Postgres can produce a wider Go field, so check generated integer types after a switch.
  • Built-in functions. Calls such as unique_rowid(), gateway_region(), cluster_logical_timestamp(), and the crdb_internal.* builtins resolve in the catalogue, so queries that use them pass analysis with typed results.

Configuration

EngineConfig is the bundle. The constructor takes no arguments.

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

engineConfig := db_engine_cockroachdb.CockroachDB()

CockroachDB() returns the same db.EngineConfig shape that db_engine_postgres.Postgres() returns. It sets the driver name to postgres, wires the CockroachDB engine, and ships PostgresDialect() as the migration dialect. The DatabaseRegistration treats it exactly as it treats the Postgres engine config, so the integration needs no extra glue.

Bootstrap

CockroachDB speaks the Postgres wire protocol, so use a Postgres driver such as pgx.

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_cockroachdb"
)

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

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

See also

Other database engines:

  • PostgreSQL, single-primary Postgres with the same parser and the same type system.
  • MySQL, wider hosting availability for non-distributed workloads.
  • MariaDB, MySQL fork with a divergent optimiser.
  • SQLite, embedded, single-file.
  • DuckDB, embedded analytical engine.

Framework docs:

External: