Database commands#

Usage:
  zyn db [command]

Commands:
  connect    connect using the bundled psql
  migrate    run pending migrations and refresh generated schema
  query SQL  query the development database
  reset      recreate the database from the schema snapshot
  schema     manage generated database artifacts
  seed       run db/seeds.zig against the development database
  verify     replay migrations in a disposable database and compare artifacts

Manage application data and schema. Use pg to manage the local PostgreSQL server.

Example:
  zyn db migrate

Documentation: zyn docs cli/db

Commands#

Run zyn db or zyn db --help to print this command list.

Common workflows#

Create and apply a schema change:

zyn generate migration add_status_to_orders
# Edit db/migrations/{VERSION}_add_status_to_orders.sql
zyn db migrate

Open PostgreSQL directly:

zyn db connect

db connect starts psql with public as its search path. Migration, schema-dump, and restore connections use the same initial path; SQL in a migration or schema snapshot may change it for its session.

Refresh generated files after changing the live schema manually:

zyn db schema dump

Recreate development data from committed schema files, then seed it:

zyn db reset
zyn db seed

SQL queries#

zyn db query 'SELECT id, title FROM articles ORDER BY id LIMIT 10'

query takes exactly one non-empty UTF-8 SQL argument, at most 65,536 bytes without NUL. Like zyn prod db query, it accepts one PostgreSQL preparable SELECT, VALUES, TABLE, INSERT, UPDATE, DELETE, or MERGE statement, including WITH. Batches, transaction control, schema commands, and psql meta-commands are rejected. Successful statements commit; failures roll back.

Results are UTF-8 CSV with a header, even for empty result sets. Non-null values are quoted PostgreSQL text; unquoted \N means SQL NULL. Statements without result columns print their PostgreSQL command tag. Output is capped at 64 MiB; exceeding the cap rolls back. Streamed output does not prove commit succeeded. Connections, statements, and locks have 10-second, 25-second, and 2-second limits respectively. The command uses the development database and local PostgreSQL ownership/cleanup rules of db connect; it does not run migrations. Queries load the bundled native libpq library. Source-built musl CLIs reject this operation; use the published CLI.

Generated files#

Successful migrate refreshes:

• db/schema.sql: application schema and applied migration versions;
• db/zyn_schema.sql: framework-owned zyn objects;
• db/types.zig: generated enums, table types, and typed-operation metadata.

Commit these files with the migration and do not edit them manually. Schema snapshots contain no application rows, queued jobs, notifications, recurring schedule state, or blob rows. Migration backfills affect the live database but are not copied into db/schema.sql.

Each pending migration and its recorded version run in one transaction. migrate stops on failure; earlier migrations from the same invocation remain committed, while generated files remain unchanged.

Zyn records the version of each applied migration. It does not compare applied files to their original contents. Keep applied migrations unchanged so fresh databases replay the same history; put subsequent changes in a new migration.

Use zyn db verify when validating a change to migration history or generated artifacts. It creates and drops a uniquely named disposable database in the managed development PostgreSQL cluster, replays all migration files (including data backfills), generates the schema and types, and compares both to db/schema.sql and db/types.zig. The configured development database and committed files are not changed. The command fails with MigrationSchemaMismatch or MigrationTypesMismatch when they differ. It requires database-creation privileges; do not run it on production. Normal zyn check and application tests do not replay migrations. Snapshots retaining the former checksum column do not match a fresh replay: run db migrate to regenerate the snapshot before db verify. If a verification error is followed by scratch-database cleanup failure, zyn reports both failures and the disposable database name. A process interruption can also leave a zyn_verify_ database behind. Inspect any such database and confirm no verification is using it before dropping it manually.

Seeds#

A seed module exports:

const zyn = @import("zyn");

pub fn run(db: *zyn.Database) !void {
    try db.exec(
        "INSERT INTO tags (name) VALUES ($1) ON CONFLICT DO NOTHING",
        .{"example"},
    );
}

zyn db seed is development-only and runs only when invoked. Zyn does not wrap the complete seed in a transaction, so make seeds repeatable and create an explicit transaction when partial success is unacceptable.

Database commands temporarily start bundled PostgreSQL when needed. Managed Unix development uses project-local peer authentication; Windows uses loopback SCRAM. PostgreSQL diagnostics are reported when available.

Usage:
  zyn db schema [command]

Commands:
  dump  refresh the schema snapshot and generated database types

Manage application data and schema. Use pg to manage the local PostgreSQL server.

Example:
  zyn db schema dump

Documentation: zyn docs cli/db