Schema Migrations

How NeNe handles schema evolution after the initial setupDatabase.php / initSQLite.php run, the operator-applied review-before-apply workflow that ADR-0009 adopted, and the explicit list of changes that the framework intentionally does not automate.

Audience: anyone running a NeNe deployment past its first release, or anyone evaluating whether NeNe's migration story fits their production cadence. Trial source: FT17 (docs/field-trials/2026-05-field-trial-17.md). Boundary ADR: ADR-0009 (docs/adr/0009-schema-migration-story.md).

TL;DR

# 1. Edit class/xion/SchemaDefinition.php (add column, add table, etc.)
# 2. Regenerate the docker entrypoint snapshot (ADR-0005)
composer schema:generate

# 3. Generate the migration SQL for the running DB (ADR-0009)
composer schema:diff -- \
    --dsn='mysql:host=127.0.0.1;port=3306;dbname=nene' \
    --user=nene --pass=nene \
    > migrations/2026-05-23-add-archive-flag.sql

# 4. Review the file (it's tiny — one ALTER TABLE per change)
$EDITOR migrations/2026-05-23-add-archive-flag.sql

# 5. Apply it
mysql -h 127.0.0.1 -u nene -pnene nene < migrations/2026-05-23-add-archive-flag.sql

# 6. Re-run the diff to confirm the live DB is back in sync
composer schema:diff -- --dsn='mysql:host=...' --user=nene --pass=nene
# -- schema is in sync with SchemaDefinition (driver=mysql)

That's the whole workflow. No Phinx, no Liquibase, no auto-apply.

Why no auto-apply

ADR-0009 picked operator-applied as the deliberate choice. The framework will never silently mutate your production schema. Three reasons:

1. Destructive operations need data semantics. Dropping a column means deciding whether to back up its data first. Renaming a column means deciding whether to copy-then-drop or RENAME COLUMN. Those decisions live in operator brains, not in a tool.
2. Rollback fantasy. Even Phinx ships with caveats around rollback. NeNe does not pretend it can revert. The operator owns the consequence.
3. Review-before-apply is the right pace. A small framework with quarterly schema changes does not need pipeline integration. A bigger system that does can layer Phinx on top — NeNe does not preclude it.

What composer schema:diff emits

Change	CLI behavior
Add column	ALTER TABLE <t> ADD COLUMN <c> <type> NOT NULL DEFAULT <d>;
Add table	CREATE TABLE IF NOT EXISTS <t> (…) ENGINE=InnoDB …; (MySQL) or CREATE TABLE IF NOT EXISTS <t> (…); (SQLite)
Add index	(not yet — see #421 follow-up; works as part of "add table" but standalone index addition is queued)

Change	CLI behavior
Drop column	Warning to stderr, no SQL. Operator hand-writes the SQL.
Drop table	Same — warning only.
Drop index	Same.
Column type change	Same. (Data semantics decide whether a CAST is safe.)
Column rename	Same. (The tool can't disambiguate "rename" from "drop old + add new".)
Constraint changes (UNIQUE / FOREIGN KEY / CHECK)	Same.
Default-value-only change	Same.

stdout is the SQL (clean for redirect). stderr is annotations and warnings (review-only).

Driver support

Driver	Status	DSN format	Notes
MySQL	Full	mysql:host=…;port=…;dbname=…	Introspects via INFORMATION_SCHEMA.COLUMNS
SQLite	Full	sqlite:/path/to/file.sqlite	Introspects via PRAGMA table_info()

Other PDO drivers (PostgreSQL, etc.) are explicitly not supported. The CLI rejects unknown drivers with exit code 1.

Reading the output

composer schema:diff prints clearly-delimited sections:

-- generated by cli/schemaDiff.php (ADR-0009)
-- driver: mysql
-- review every statement before applying; this tool only emits add-only changes.

-- new table: audit_log
CREATE TABLE IF NOT EXISTS audit_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    message TEXT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- new column: todos.archived_at
ALTER TABLE todos ADD COLUMN archived_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Annotations on stderr (not part of redirected output):

-- schema diff for driver=mysql (review before applying)
-- warning: column `users.legacy_phone` exists in the live database but not in SchemaDefinition — drop SQL must be hand-written (ADR-0009 destructive-op rule).

Workflow recommendations

Recommended deploy hook

#!/usr/bin/env bash
# scripts/deploy-schema-step.sh — example hook (not bundled)
set -euo pipefail

# 1. Make sure SchemaDefinition matches the docker entrypoint snapshot.
composer schema:check

# 2. Diff against the live DB.
diff_out=$(composer schema:diff -- --dsn="$NENE_DB_DSN" --user="$NENE_DB_USER" --pass="$NENE_DB_PASS")

if [[ -z "$diff_out" ]]; then
    echo "✓ schema is in sync"
    exit 0
fi

# 3. Surface for human review (CI gate / Slack notification / etc.)
echo "$diff_out" | tee "migrations/$(date +%F)-pending.sql"
echo "Review and apply manually."
exit 1

The script intentionally exits with 1 if a diff exists — release pipelines treat that as "needs operator action" instead of silently mutating.

Versioning the migration files

Treat each migrations/<date>-<slug>.sql as a first-class artifact:

• Commit it to the deploy repo (or the operator's runbook).
• Include the output of composer schema:check as a sibling artifact so reviewers can confirm SchemaDefinition and docker/mysql/init/001_schema.sql match.
• After applying, add a one-liner note in your release log: "applied 2026-05-23-add-archive-flag.sql to prod".

NeNe does not provide a migration history table. Operators who need multi-version tracking (Phinx-style "this migration ran on 2026-02-01") layer Phinx or a similar tool on top — SchemaDefinition stays the source of truth either way.

Test coverage

The SchemaDiffer engine is covered by tests/Unit/Xion/SchemaDifferTest.php (10 cases). Live verify against MySQL is part of the FT17 trial report; SQLite is unit-test-only because the dev compose stack doesn't include a SQLite DSN.

Related

• ADR-0005 (docs/adr/0005-schema-php-single-source.md) — the source of truth (SchemaDefinition).
• ADR-0009 (docs/adr/0009-schema-migration-story.md) — the migration design decision.
• composer schema:generate — regenerate docker/mysql/init/001_schema.sql.
• composer schema:check — drift check between SchemaDefinition and the snapshot.
• composer schema:diff — this doc's main subject.
• cli/schemaDiff.php — implementation.
• class/xion/SchemaDiffer.php — diff engine (testable in isolation).
• docs/field-trials/2026-05-field-trial-17.md — the implementation trial.
• REPORT_commercial_feasibility.md — the external evaluation that triggered ADR-0009.
• Issue #421 — follow-up for the add-index path (currently not auto-detected when added to an existing table).