DuckDB database engine

DuckDB engine for Piko's database service. It bundles a DDL/DML parser and type resolver into an EngineConfig, targeting DuckDB's analytical (OLAP) dialect.

Overview

DuckDB is an embedded analytical database, think SQLite for OLAP. It runs in-process, stores data in a columnar format, and uses a vectorised execution engine over large scans. The Piko engine builds on the same PostgreSQL recursive-descent parser the framework already maintains, then layers DuckDB-specific deltas on top. It inherits broad SQL coverage instead of a thin bespoke dialect. The deltas cover DuckDB-specific types (STRUCT, LIST, MAP), table-valued functions (read_csv_auto, read_parquet, read_json), and DuckDB's expression set.

The engine implements the full querier EnginePort. It models DuckDB's type system, table-valued functions, RETURNING, window functions, scalar subqueries, struct field access, and lambda expressions. Generated query methods type-check against real DuckDB semantics, not a lowest-common-denominator dialect.

Reach for DuckDB when the workload is reporting, dashboards, ad-hoc analytics over local files (Parquet, CSV, JSON), or exploratory data work. Reach for PostgreSQL when the workload is transactional, with high concurrent writers, row-level updates, and point lookups by primary key. DuckDB does not target OLTP. Pair the two when you have both shapes of workload. Run Postgres for the transactional path and DuckDB for the analytics path, populated from a change-data-capture pipeline or scheduled extract.

DuckDB is a codegen-only engine in Piko today. The EngineConfig ships without a MigrationDialect, so the migration runner does not run. Use the engine for typed query generation against an existing DuckDB schema. Apply that schema yourself, with DuckDB's own tooling or a SQL script in your seed pipeline.

Requirements

A DuckDB driver. Go bindings come from github.com/duckdb/duckdb-go/v2, which links the DuckDB C library and so requires CGO_ENABLED=1 and a C toolchain at build time. There is no pure-Go DuckDB driver, DuckDB itself is a C++ project.
Compiled run mode. The driver is a CGO dependency, so DuckDB runs under the compiled dev and prod modes, not the interpreted dev-i mode.
Disk space for DuckDB's columnar files, or :memory: for in-process, ephemeral analytics.

Configuration

DuckDB() returns the shared db.EngineConfig carrying the duckdb driver name and a fresh engine. The constructor takes no arguments.

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

engineConfig := db_engine_duckdb.DuckDB()

DuckDB drops into the same DatabaseRegistration and EngineConfig surface as every other engine. Swapping db_engine_postgres.Postgres() for db_engine_duckdb.DuckDB() is the only change at the wiring level.

Bootstrap

The runtime registration supplies the open *sql.DB and the engine. The blank import registers the duckdb SQL driver name. Because the engine is codegen-only, apply the schema yourself before serving, then call ssr.Run.

import (
    "database/sql"

    _ "github.com/duckdb/duckdb-go/v2" // registers the "duckdb" driver

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

connection, err := sql.Open("duckdb", "data/analytics.duckdb")
if err != nil {
    return err
}

// The DuckDB engine ships without a MigrationDialect, so the migration
// runner does not run. Apply the schema directly.
if _, execErr := connection.Exec(schemaSQL); execErr != nil {
    return execErr
}

ssr := piko.New(
    piko.WithDatabase("analytics", &db.DatabaseRegistration{
        DB:           connection,
        EngineConfig: db_engine_duckdb.DuckDB(),
    }),
)
if err := ssr.Run(command); err != nil {
    return err
}

Code generation

Typed query generation is a separate build-time step from the runtime registration. The generator binary points the engine at the schema and the query files, then calls ssr.Generate. Codegen reads the DDL through MigrationDirectory to resolve column types, and the queries through QueryFS plus QueryDirectory. The runtime path needs no migration files, but codegen still consumes the DDL for type inference.

import (
    "context"
    "os"

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

ssr := piko.New(
    piko.WithDatabase("analytics", &db.DatabaseRegistration{
        EngineConfig:       db_engine_duckdb.DuckDB(),
        QueryFS:            os.DirFS("db"),
        QueryDirectory:     "queries",
        MigrationDirectory: "migrations",
    }),
)
if err := ssr.Generate(context.Background(), command); err != nil {
    return err
}

Tradeoffs

DuckDB is less mature than Postgres or SQLite as a long-running production service. Schema evolution, replication, and high availability are less developed. The right shape is an analytical sidecar to a transactional store, or fast local analytics over Parquet files already on disk.