MySQL database engine

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

Overview

The package supplies the engine half of a Piko database registration. db_engine_mysql.MySQL() returns one db.EngineConfig value that carries three concerns at once. The driver name (mysql), the code generation engine (a hand-written recursive-descent parser plus type resolver), and the MySQL migration dialect. You assemble no parser or resolver yourself.

The engine does not open the connection. It contributes the code generation parser and type resolver at build time, and the migration dialect at runtime. You supply the live *sql.DB. Pair the engine with github.com/go-sql-driver/mysql and pass the connection into db.DatabaseRegistration.

The same engine serves MariaDB through a dialect hook system. MariaDB-specific features attach as a flavour, so MySQL-compatible databases reuse one parser instead of a fork.

Reach for MySQL when your host only offers MySQL, when an ops team already runs MySQL replicas, or when a read-heavy workload relies on MySQL replication tooling. Reach for PostgreSQL when you need richer JSON, expression indexes, or a stricter type system. Reach for SQLite for embedded workloads. Reach for CockroachDB for distributed Postgres-compatible scale.

The engine is pure Go. It needs no build tags, no CGO, and no MySQL toolchain on the build host. It runs in interpreted dev mode (dev-i) and in compiled builds alike, and code generation runs without a database present.

Type resolution and code generation

The engine drives Piko's typed-query code generator through a real type resolver and a feature bitmap. The bitmap advertises window functions, JSON operators, scalar subqueries, and bitwise operators, so generated Go reflects MySQL's actual capabilities. The bitmap omits string concatenation through ||, because MySQL reads || as logical OR.

To generate typed query methods, set QueryFS alongside MigrationFS and EngineConfig, then run the scaffolded generator in SQL mode (go run ./cmd/generator/main.go sql), or pass all to generate everything. The generator lives in your project at cmd/generator/main.go, not in the piko CLI. It reads the SQL query files, resolves their result and parameter types against the engine, and writes Go methods.

The engine handles the MySQL JSON type and its path syntax (JSON_EXTRACT, ->, ->>). MySQL JSON values are less indexable than PostgreSQL JSONB. If you reach for generated columns to index JSON paths often, MySQL JSON may no longer fit the workload.

Configuration

EngineConfig is the bundle. The constructor takes no arguments.

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

engineConfig := db_engine_mysql.MySQL()

Bootstrap

import (
    "database/sql"
    "os"

    _ "github.com/go-sql-driver/mysql" // registers the "mysql" driver

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

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

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

The migration dialect ships advisory locking. It runs migrations under GET_LOCK() and RELEASE_LOCK() against a piko_migrations history table, so concurrent migrators serialise with no extra setup.

Multi-statement DSNs

When your DSN includes multiStatements=true, the driver runs multi-statement SQL natively. To stop the migration runner from also splitting statements, use the DSN-aware dialect.

The migration dialect inside EngineConfig always takes precedence over the top-level DatabaseRegistration.MigrationDialect field. Because MySQL() sets a non-empty dialect, setting the top-level field has no effect. Override the dialect on EngineConfig instead.

engineConfig := db_engine_mysql.MySQL()
engineConfig.MigrationDialect = db.MySQLDialectWithDSN(os.Getenv("MYSQL_DSN"))

registration := &db.DatabaseRegistration{
    DB:           connection,
    EngineConfig: engineConfig,
    MigrationFS:  migrationsFS,
}

db.MySQLDialectWithDSN checks the DSN for multiStatements=true and disables the runner's own statement splitting when it finds it. Without the match, it behaves like db.MySQLDialect().

See also

Other database engines:

  • PostgreSQL, richer SQL surface, stronger JSON, expression indexes.
  • MariaDB, MySQL fork with a divergent optimiser and JSON path syntax.
  • SQLite, embedded, single-file, zero-config.
  • DuckDB, embedded analytical engine for OLAP.
  • CockroachDB, Postgres-compatible distributed SQL.

Framework docs:

External: