September 30, 20267 min read

Ptah 0.11.0: A Schema Plan Can Span a MySQL Server

Plan several MySQL databases together, defer PostgreSQL constraint validation, and keep index options with Ptah 0.11.0.

Nathan BrooksTechnical Writer

Ptah helps you plan, review, and apply database migrations. Try in your browser

Ptah 0.11.0 is published on GitHub. A MySQL schema plan can include several databases and the foreign keys between them. The URL determines the scope: a URL with no database names the whole server. A schema filter narrows that scope when the server is shared.

The release also represents constraint validation and enforcement separately, and keeps more index options through a plan. Those options affect what the database checks, which indexes its optimizer uses, and whether an alteration needs to copy a table.

The examples ran with the official 0.11.0 darwin/arm64 binaries against MySQL 8.4.11 and PostgreSQL 18.6 in disposable containers. The release’s linux/amd64 container also passed a version check. The example files hold the SQL, recorded output and checksums.

Plan the databases and their tables together

Suppose app.accounts holds accounts and audit.events refers to them. One SQL file declares both databases. USE selects the database for the statements that follow it:

CREATE DATABASE app;
USE app;
CREATE TABLE accounts (id bigint PRIMARY KEY);

CREATE DATABASE audit;
USE audit;
CREATE TABLE events (
    id bigint PRIMARY KEY,
    account_id bigint NOT NULL,
    CONSTRAINT events_account FOREIGN KEY (account_id) REFERENCES app.accounts (id)
);

Here SERVER_URL names no database, such as mysql://root:...@127.0.0.1:3306/. The server also holds a database named keep_me, which belongs to another application. Limit the plan to app and audit:

ptah schema apply --db-url "$SERVER_URL" --schema-file schema.sql --schemas app,audit --dry-run

Planned schema changes:
CREATE SCHEMA IF NOT EXISTS `app`;
CREATE SCHEMA IF NOT EXISTS `audit`;
-- MYSQL TABLE: app.accounts --
CREATE TABLE `app`.`accounts` (
  `id` bigint PRIMARY KEY
);
-- MYSQL TABLE: audit.events --
CREATE TABLE `audit`.`events` (
  `id` bigint PRIMARY KEY,
  `account_id` bigint NOT NULL
);
-- ALTER statements: --
ALTER TABLE `audit`.`events` ADD CONSTRAINT `events_account` FOREIGN KEY (`account_id`) REFERENCES `app`.`accounts`(`id`);

Ptah creates the databases before the tables. It adds the foreign key after both tables exist. There is no statement about keep_me in this plan.

Without --schemas app,audit, the same file describes the desired state of every user database on the server. The unfiltered dry run ends with this excerpt:

-- WARNING: This will delete all data!
DROP TABLE IF EXISTS `keep_me`.`notes`;
DROP DATABASE `keep_me`;

We did not apply that plan. An undeclared database is a drop request at this scope. System databases are excluded. A URL naming one database keeps its scope and refuses declarations for another database. The whole-server reference describes the source combinations and filters.

Rehearse on an empty, separate server

For this plan, DEV_SERVER_URL points to a second MySQL server with no user databases. It also names no database:

ptah schema apply --db-url "$SERVER_URL" --schema-file schema.sql --schemas app,audit --dev-url "$DEV_SERVER_URL" --auto-approve

The command rehearsed the plan on the dev server and applied it to the target (output). The dev server had zero user databases afterwards. Cleanup removed the foreign key between audit and app before dropping the databases used for the rehearsal.

The target held app.accounts and audit.events. The row in keep_me.notes was still there. Repeating the scoped dry run gave:

Schema is synced, no changes to be made.

Native schema compare still refuses a URL with no database in this release (output). The repeated schema apply --dry-run above is the convergence check for this example.

A dev server holding a user database is refused. We put a table with one row in reserved_scratch, then asked Ptah to rehearse an added name column:

ptah schema apply --db-url "$SERVER_URL" --schema-file schema-next.sql --schemas app,audit --dev-url "$DEV_SERVER_URL" --auto-approve

error: connected database is not clean: found schema "reserved_scratch"; Ptah resets a dev database before and after it uses one, so point --dev-url at an empty database

The command exited 2. The scratch row remained, and the target’s accounts table still had only id. The dev-server rules also require a different server identity. Changing the account or the address spelling does not make the target a separate dev server.

Keep migration history in its own database

ptah-compat migrate apply accepts the same URL scope. On a clean server, this migration creates a database and a table:

-- atlas:txmode none
CREATE DATABASE shipments;
CREATE TABLE shipments.deliveries (id bigint PRIMARY KEY);

The atlas:txmode none directive runs the migration without a transaction. A migration that creates a database requires it at this scope. From examples/server-migrations/, with MIGRATION_SERVER_URL pointing at an empty server:

ptah-compat migrate hash --dir file://migrations
ptah-compat migrate apply --url "$MIGRATION_SERVER_URL" --dir file://migrations
ptah-compat migrate status --url "$MIGRATION_SERVER_URL" --dir file://migrations

Migration Status: OK
  -- Current Version: 1790700100
  -- Next Version:    Already at latest version
  -- Executed Files:  1
  -- Pending Files:   0

The server held shipments.deliveries and a revision table in the separate atlas_schema_revisions database. --revisions-schema selects another history database. Status before the apply had created neither the table nor the history database.

A first apply against the populated server from the earlier example was refused as dirty (output). We then ran the migration on the clean server. Whole-server scope does not remove the migration system’s check for an existing, untracked schema. ptah-compat migrate down still refuses this URL scope (output).

Defer checking old rows, while checking new ones

On PostgreSQL, NOT VALID adds a constraint without scanning existing rows. New rows must still satisfy it. Our orders table starts with amounts -10 and 20. The desired schema declares a positive amount:

CREATE TABLE orders (id bigint PRIMARY KEY, amount numeric);
ALTER TABLE orders
    ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID;

ptah schema apply --db-url "$DATABASE_URL" --schema-file not-valid.sql --schemas public --auto-approve
ptah schema compare --db-url "$DATABASE_URL" --schema-file not-valid.sql --schemas public --exit-code

Both commands exited 0. PostgreSQL kept the old negative row, and its catalog reported convalidated = false and conenforced = true. Inserting a new row with amount -5 failed:

ERROR:  new row for relation "orders" violates check constraint "orders_amount_positive"
DETAIL:  Failing row contains (3, -5).

Remove NOT VALID from the desired file when the old rows are ready to be checked. Ptah plans validation of the existing constraint:

ptah schema apply --db-url "$DATABASE_URL" --schema-file validated.sql --schemas public --dry-run

Planned schema changes:
-- ALTER statements: --
ALTER TABLE "orders" VALIDATE CONSTRAINT "orders_amount_positive";

With the negative row still present, applying that plan exited 2:

error: apply schema changes: failed to execute SQL statement: SQL execution failed: ERROR: check constraint "orders_amount_positive" of relation "orders" is violated by some row (SQLSTATE 23514)
SQL: ALTER TABLE "orders" VALIDATE CONSTRAINT "orders_amount_positive"

The constraint remained unvalidated. After correcting that row’s amount to 10, the same apply completed and a comparison with validated.sql exited 0. A comparison with not-valid.sql also exited 0: NOT VALID permits an unvalidated constraint, but does not require one. An already validated constraint satisfies it. A rollback does not make a validated constraint unvalidated. See unvalidated constraints.

NOT ENFORCED has a different meaning

PostgreSQL 18 can keep a check without enforcing it. On a separate database, we applied:

CREATE TABLE orders (id bigint PRIMARY KEY, amount numeric);
ALTER TABLE orders
    ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT ENFORCED;

ptah schema apply --db-url "$UNENFORCED_DATABASE_URL" --schema-file not-enforced.sql --schemas public --auto-approve
ptah schema compare --db-url "$UNENFORCED_DATABASE_URL" --schema-file not-enforced.sql --schemas public --exit-code

Both commands exited 0. Inserting amount -10 succeeded. The catalog reported conenforced = false. This is different from deferring validation of old rows: the database permits new rows that violate the check too.

An offline render needs the target version:

ptah schema render --schema-file not-enforced.sql --dialect postgres --server-version 18

Rendering for version 18 succeeded. Rendering the same file with --server-version 17 exited 2, naming the missing not_enforced_checks capability (output). The constraint reference covers checks, foreign keys and the supported engines.

Change index visibility without rebuilding it

This MySQL table declares comments, block-size hints and an invisible secondary index:

CREATE TABLE accounts (
    id bigint NOT NULL,
    email varchar(255) NOT NULL,
    PRIMARY KEY (id) COMMENT 'account identity' KEY_BLOCK_SIZE=8,
    KEY accounts_email (email) COMMENT 'lookup by email' KEY_BLOCK_SIZE=8 INVISIBLE
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

ptah schema apply --db-url "$INDEX_DATABASE_URL" --schema-file hidden.sql --auto-approve
ptah schema compare --db-url "$INDEX_DATABASE_URL" --schema-file hidden.sql --exit-code

Both commands exited 0. MySQL stored the comments on both indexes and kept accounts_email invisible. An invisible index is maintained by the server, but is not available to the optimizer by default.

Change INVISIBLE to VISIBLE in visible.sql and inspect the plan:

ptah schema apply --db-url "$INDEX_DATABASE_URL" --schema-file visible.sql --dry-run

Planned schema changes:
-- ALTER statements: --
ALTER TABLE `accounts` ALTER INDEX `accounts_email` VISIBLE;

Applying this plan completed, and the subsequent compare exited 0. The visibility change uses ALTER INDEX, with no drop and add.

A block-size change can require copying the table

block-size.sql changes the secondary index’s KEY_BLOCK_SIZE from 8 to 4. On this compressed MySQL table, the plan is:

ptah schema apply --db-url "$INDEX_DATABASE_URL" --schema-file block-size.sql --dry-run

Planned schema changes:
-- ALTER statements: --
ALTER TABLE `accounts` DROP INDEX `accounts_email`, ADD INDEX `accounts_email` (`email`) KEY_BLOCK_SIZE=4 COMMENT 'lookup by email', ALGORITHM=COPY;

The drop and add share one statement. ALGORITHM=COPY asks MySQL to copy the table. It is not an online operation. With this policy in online.yaml:

diff:
  online_alter: true

ptah schema apply --db-url "$INDEX_DATABASE_URL" --schema-file block-size.sql --config online.yaml --auto-approve

error: apply schema changes: failed to execute SQL statement: SQL execution failed: Error 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
SQL: ALTER TABLE `accounts` DROP INDEX `accounts_email`, ADD INDEX `accounts_email` (`email`) KEY_BLOCK_SIZE=4 COMMENT 'lookup by email', ALGORITHM=COPY, LOCK=NONE

Ptah kept LOCK=NONE; MySQL refused the copy rather than taking the lock it needed. The command exited 2, and the table still matched visible.sql. The MySQL reference explains block-size limits and the differences on MariaDB.

Read migration status without creating history

A status check does not need to create a revision table. In a fresh PostgreSQL database, status_reader had CONNECT, USAGE and SELECT, but no CREATE privilege. One migration was pending:

ptah migrations status --db-url "$READ_ONLY_DATABASE_URL" --migrations-dir migrations --verify-sum --exit-code

=== MIGRATION STATUS ===
Database: ***
Dialect: postgres
Schema: public

Current Version: 0
Total Migrations: 1
Applied Migrations: 0
Pending Migrations: 1
Out-of-order Migrations: 0
Status: ⚠️  Pending migrations available

Run 'ptah migrations up' to apply pending migrations.

The command exited 1 because --exit-code reports pending migrations. ptah-compat migrate status on the same account also reported one pending file and exited 0. Afterwards, the database still had no user tables or revision schema. Neither status check had created migration history.

Also in this release

Ptah 0.11.0 also includes these changes:

SQL schema files follow ordered drops and recreations, and preserve more constraint names and references between databases.
Foreign keys retain their MATCH type according to the engine. MariaDB and SQLite declarations that would lose MATCH FULL are refused.
DBML export includes composite primary and foreign keys and checks. It reports constraint options that DBML cannot represent.
Connected planning uses the dialect and capabilities of the server that answered. Comparisons between schema documents leave a SQLite dev file alone.

The release notes cover the full change list and the limits for each engine.

Known issues

MySQL SQL inspection in 0.11.0 omits primary-key comments; reapplying the export can clear them. Keep the original declaration. #3976.

Get Ptah 0.11.0

The release page has archives and checksums for each platform. Check the container version:

docker --context remote-dev-container run --rm ghcr.io/stokaro/ptah:0.11.0 version

Output excerpt:

Version: 0.11.0

Review the scope of a MySQL URL before applying a schema. Keep an empty, separate dev server for a whole-server rehearsal. For PostgreSQL constraints, choose whether to defer validation or disable enforcement; they permit different data. For MySQL block-size changes, review the requested copy and the lock policy before applying the plan.

Put Ptah to work

Example files

← All articles