How to swap database engines

Piko supports SQLite, PostgreSQL, MySQL, MariaDB, CockroachDB, TimescaleDB, DuckDB, and ClickHouse through swappable engine configs. A project picks the target at bootstrap, and the generator, migrator, and queries adapt to the chosen dialect. This guide covers what changes between engines and how to migrate a project from one to another. See the querier reference for the engine-config API.

What stays the same

• db/queries/*.sql files with piko.query(...) headers (the query name and command are the first two positionals, for example -- piko.query(GetUser, one)).
• Generated Queries struct and method signatures (names and return types are dialect-neutral).
• Call sites in actions and partials: queries.ListTasks(ctx) looks identical regardless of engine.
• The MigrationService API.

What changes

Engine catalogue

Use db_engine_postgres.PostgresPgBouncer() when your application talks to PostgreSQL through PgBouncer in transaction-pooling mode. The pairing avoids advisory locks (which do not survive across pooled queries) and switches the migration runner to a table-based lock instead. db_engine_mariadb.MariaDB() ships as a MySQL variant - it returns the same *MySQLEngine underneath, with extra MariaDB-only function definitions registered.

Example: swap SQLite to PostgreSQL

Start from a SQLite setup:

import (
    _ "modernc.org/sqlite"

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

database, _ := sql.Open("sqlite", "file:./data/app.db")

executor := db.NewMigrationExecutor(database, db.SQLiteDialect())

ssr := piko.New(
    piko.WithDatabase("primary", &db.DatabaseRegistration{
        DB:           database,
        EngineConfig: db_engine_sqlite.SQLite(),
    }),
)

Switch to PostgreSQL by changing four lines:

import (
    _ "github.com/jackc/pgx/v5/stdlib"

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

database, _ := sql.Open("pgx", os.Getenv("DATABASE_URL"))

executor := db.NewMigrationExecutor(database, db.PostgresDialect())

ssr := piko.New(
    piko.WithDatabase("primary", &db.DatabaseRegistration{
        DB:           database,
        EngineConfig: db_engine_postgres.Postgres(),
    }),
)

Everything else in the codebase (action handlers, generated queries, partials) stays untouched.

Adjust the SQL

The generator reads your db/migrations/*.sql and db/queries/*.sql files as-is (these are the scaffolded project's chosen directories, configurable through MigrationDirectory and QueryDirectory). If you wrote them for SQLite and now target Postgres, six syntax differences matter:

MariaDB, unlike standard MySQL, supports RETURNING. The db_engine_mariadb engine inherits the MySQL dialect but enables WithReturningSupport(true), so a MariaDB target can use RETURNING where the MySQL column above says it cannot.

When queries or migrations diverge, keep per-dialect variants in side-by-side files (for example, users.postgres.sql and users.sqlite.sql) or keep a single dialect-neutral subset where possible.

Run migrations against the new engine fresh

Migrations tracked against SQLite do not translate automatically to Postgres. Two paths:

1. Fresh database: apply every migration from the start on the new engine. Appropriate during development or for projects that carry no production data yet.
2. Data migration: export data from the source, recreate the schema on the target, then import the data. Mark the equivalent migrations as applied on the target without rerunning them. Use a one-off script that writes the appropriate rows to the migration-tracking table.

For production migrations, option 2 is the realistic path. Write it as a one-off tool outside the main binary and run it under a maintenance window.

Test against the target engine

Dialect differences surface as runtime errors, not compile errors. Exercise the target engine in integration tests:

• Use testcontainers-go to spin up a real PostgreSQL/MySQL/MariaDB container per test run. Scenarios 023 MySQL and 024 PostgreSQL demonstrate the pattern.
• For SQLite, point sql.Open at an in-memory database (file::memory:?cache=shared) for fast tests.

Never let a query reach production that has not run against the target engine at least once.

Multiple databases in one project

Call piko.WithDatabase(name, ...) multiple times with different names:

piko.WithDatabase("primary", &db.DatabaseRegistration{
    DB:           primaryDB,
    EngineConfig: db_engine_postgres.Postgres(),
}),
piko.WithDatabase("analytics", &db.DatabaseRegistration{
    DB:           duckDB,
    EngineConfig: db_engine_duckdb.DuckDB(),
}),

By default every database in a project shares the single db/generated/ output directory. The generator produces one Queries struct per project, and each call site resolves the right *sql.DB by name via db.GetDatabaseConnection("analytics") (from piko.sh/piko/wdk/db). Override GeneratedOutputDirectory on a DatabaseRegistration if you want a database to emit into its own subdirectory instead.

Back Piko's internal services with SQL

Reserved names let Piko's registry and orchestrator persist to SQL instead of the default in-memory backends:

piko.WithDatabase(db.DatabaseNameRegistry, &db.DatabaseRegistration{
    DB:           postgresDB,
    EngineConfig: db_engine_postgres.Postgres(),
}),
piko.WithDatabase(db.DatabaseNameOrchestrator, &db.DatabaseRegistration{
    DB:           postgresDB,
    EngineConfig: db_engine_postgres.Postgres(),
}),

Use this for production deployments where a process restart should not lose in-flight orchestrator tasks.

See also

• Querier reference.
• How to migrations.
• How to writing queries.
• Scenarios: 022 SQLite, 023 MySQL, 024 PostgreSQL, 025 DuckDB.