Change the database schema#

This example creates audit_events, then adds a non-null actor field while preserving existing rows. Start in an initialized application. If the table already exists, skip its creation and adapt the additive migration to its current schema.

1. Create the table#

zyn g table audit_events

The generated SQL includes a UUID primary key, timestamps, and an update trigger. Add message text NOT NULL inside its CREATE TABLE declaration, retaining the generated fields and trigger. The resulting table declaration is:

CREATE TABLE public.audit_events (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    message text NOT NULL
);

Apply it before creating the next migration:

zyn db migrate

2. Add and backfill a field#

zyn g migration add_actor_to_audit_events

Put this SQL in the newly generated file:

ALTER TABLE audit_events ADD COLUMN actor text;
UPDATE audit_events SET actor = 'system' WHERE actor IS NULL;
ALTER TABLE audit_events ALTER COLUMN actor SET DEFAULT 'system';
ALTER TABLE audit_events ALTER COLUMN actor SET NOT NULL;

Existing rows receive system. The default keeps old application code that omits actor compatible during a rolling deployment. Update new writes to supply the actual actor; remove the compatibility default only after old writers have stopped. Plan separate bounded backfills for tables whose size or traffic makes this single-transaction migration unsuitable.

zyn db migrate

Commit both migrations with the refreshed db/schema.sql, db/zyn_schema.sql, and db/types.zig. Do not edit those outputs by hand. db.AuditEvent now has a non-optional actor field.

3. Verify the new write and read contract#

Create this test module and export it from src/app.zig:

// src/schema_tests.zig
const std = @import("std");
const zyn = @import("zyn");

test "audit events accept old and new writers" {
    var app = try zyn.testing.start(std.testing.io, std.testing.allocator, .{});
    defer app.deinit();
    try app.db.exec("INSERT INTO audit_events (message) VALUES ($1)", .{"Old writer"});
    try app.db.exec(
        "INSERT INTO audit_events (message, actor) VALUES ($1, $2)",
        .{ "New writer", "operator" },
    );
    const rows = try app.db.many(
        struct { message: []const u8, actor: []const u8 },
        "SELECT message, actor FROM audit_events ORDER BY message LIMIT 2",
        .{},
    );
    try std.testing.expectEqual(@as(usize, 2), rows.len);
    try std.testing.expectEqualStrings("operator", rows[0].actor);
    try std.testing.expectEqualStrings("system", rows[1].actor);
}

// src/app.zig: exports
pub const schema_tests = @import("schema_tests.zig");

zyn check

Managed tests restore db/schema.sql; they do not replay migration history. Also rehearse migrations on disposable databases: one empty database and one at the preceding schema with existing rows. Verify that the latter's rows are preserved with actor = 'system'. zyn check alone does not prove a backfill or upgrade path.

For deployed applications, preserve compatibility while releases overlap. Use zyn deploy --maintenance when overlap is impossible, with a backup and recovery plan; it does not reverse failed database changes. See framework/deployment for recovery behavior.

Do not alter the reserved zyn schema directly. Encrypted-column migrations must preserve the table, column, and primary-key identity, or rewrite the ciphertext under its new identity; see framework/database.