September 27, 202613 min read

Ptah 0.10.0: A Dev Database Has to Be Empty

Ptah 0.10.0 refuses a dev database that holds anything its reset would drop. This post also covers Ptah 0.9.0, which had no post of its own.

Nathan BrooksTechnical Writer

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

Ptah 0.10.0 is published on GitHub. Ptah 0.9.0 was published a day earlier, on September 26, and had no post of its own. This post covers both releases: first 0.10.0, then what 0.9.0 added.

The main change in 0.10.0 is a check that runs before Ptah resets a dev database. Most of the other changes are about Ptah’s model of a database matching what the server builds: constraint names, deferrable keys, default privileges that apply in every schema, and the grant option on MySQL.

Every example ran with the official 0.10.0 linux/amd64 binaries, in disposable containers: PostgreSQL 18.6, MySQL 8.4.11, and, for one lint run, TimescaleDB 2.29.2 on PostgreSQL 17.11. The 0.9.0 features ran with the 0.10.0 binaries too. Where the post compares with Atlas, it uses the community binary, Atlas CE v1.3.0, built from its source archive. The example files hold the fixtures, the output of every command, and the checksums.

A dev database that holds something is refused

A dev database is a scratch database. ptah schema apply --dev-url rehearses its plan there before it touches the target, and ptah migrations lint and ptah migrations validate replay the migration directory there. Ptah resets the dev database before and after it uses one. If the URL names the wrong database, the reset drops what that database holds.

Ptah 0.10.0 reads the dev database before the first reset. In this example the billing database holds an invoices table with two rows, and --dev-url points at it by mistake:

ptah schema apply --db-url "$DATABASE_URL" --schema-file schema.sql --dev-url "$DEV_URL" --auto-approve

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

The command exited 2 after it printed the plan. Nothing ran: invoices still held both rows, and the target database had no tables.

The check refuses more than tables. It refuses anything the reset would drop, such as a view, a function, a sequence, a type or a default privilege. When the URL pins a schema with search_path, the check reads only that schema. Here the dev database holds one view in public, and the URL ends in ?search_path=public:

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

A pinned schema also limits what the reset touches. In another dev database we put the same table in a schema named ledger, left public empty, and pinned both URLs to public. The apply completed, ledger.invoices kept its two rows, and public was empty afterwards. The dev URL and the target URL have to pin the same schema. A run where they differ is refused before it starts (output).

ptah migrations generate --shadow-db makes the same check before it replays the directory. With the shadow URL pointing at billing, it wrote no files (output). The check covers every engine where Ptah resets a dev database, SQL Server, ClickHouse and Oracle included (#3839, #3855). We ran it on PostgreSQL only. A dev URL that reaches the target database under another name, for example through a connection pooler alias, is refused before any reset too (#3796). We did not run that case.

ptah-compat and Atlas CE

On the billing database, ptah-compat schema apply and Atlas CE v1.3.0 both exit 1, and both print this line first:

Error: sql/migrate: taking database snapshot: sql/migrate: connected database is not clean: found table "invoices" in schema "public"

The view in a pinned schema is where the two binaries differ. Atlas CE v1.3.0 accepted that dev database, applied the plan to its target, and left the view in place, because it does not model views (output). ptah-compat refuses the same database, because Ptah’s reset would drop the view. The difference is deliberate, and Compatibility differences records it.

Read a whole MySQL server

A MySQL URL without a database, such as mysql://root@127.0.0.1:3306/, names the whole server. ptah schema inspect reads it that way: every user database under its own name, and each foreign key with the database it references. In this example SERVER_URL is such a URL. The server holds crm.customers and shop.orders, and an order refers to a customer in the other database:

ptah schema inspect --db-url "$SERVER_URL" --format sql

CREATE SCHEMA IF NOT EXISTS `crm` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE SCHEMA IF NOT EXISTS `shop` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- MYSQL TABLE: crm.customers --
CREATE TABLE `crm`.`customers` (
  `id` bigint PRIMARY KEY,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL UNIQUE
) ENGINE=InnoDB;

-- MYSQL TABLE: shop.orders --
CREATE TABLE `shop`.`orders` (
  `id` bigint PRIMARY KEY,
  `customer_id` bigint NOT NULL
) ENGINE=InnoDB;

CREATE INDEX `orders_customer` ON `shop`.`orders` (`customer_id`);
-- ALTER statements: --
ALTER TABLE `shop`.`orders` ADD CONSTRAINT `orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `crm`.`customers`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--schemas shop narrows the read to the databases it names (output). ptah-compat schema inspect --url also reads the URL as the whole server, and so does Atlas CE v1.3.0 (ptah-compat, Atlas CE).

In 0.10.0, schema inspect is the only command that opens a whole server. The commands that plan or change a database refuse the URL and say what to write instead. This is ptah schema compare:

error: error connecting to database: the database URL names no database, and this command reads and changes one MySQL or MariaDB database, not a whole server. Name the database in the URL path (mysql://user@host:3306/app) or, for a socket URL, in the database parameter (mysql+unix://user@/run/mysqld/mysqld.sock?database=app), and run the command once for each database

schema plan, db read and ptah-compat schema diff refused with the same sentence. Planning across a whole server is #3789.

Global default privileges on PostgreSQL

ALTER DEFAULT PRIVILEGES without IN SCHEMA sets a default for every schema of the database, including the schemas a role creates later. A common use is hardening: a new function should not be executable by PUBLIC. Ptah 0.10.0 reads, compares and plans this form, and SQL, HCL, YAML and Go sources can declare it by leaving the schema out:

CREATE TABLE notes (
    id   bigint PRIMARY KEY,
    body text NOT NULL
);

-- No IN SCHEMA: these apply in every schema of the database, including the
-- ones app_owner creates later.
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner GRANT SELECT ON TABLES TO app_reader;

ptah schema apply wrote both statements as declared:

Planned schema changes:
-- POSTGRES TABLE: notes --
CREATE TABLE "notes" (
  "id" bigint PRIMARY KEY NOT NULL,
  "body" text NOT NULL
);
ALTER DEFAULT PRIVILEGES FOR ROLE "app_owner" REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE "app_owner" GRANT SELECT ON TABLES TO "app_reader";
Auto-approval enabled; applying schema changes.
Schema apply completed successfully.

ptah schema compare --exit-code then found no differences. To see the defaults take effect, app_owner created a schema named analytics with a function and a table in it (later.sql). app_reader could read the table and could not execute the function:

 reader_executes | reader_selects
-----------------+----------------
 f               | t
(1 row)

Global default privileges explains how Ptah compares the global default with PostgreSQL’s built-in one.

Deferrable keys

A PRIMARY KEY, UNIQUE or EXCLUDE constraint can defer its check to the end of the transaction. Ptah 0.10.0 keeps the clause in its model, compares it, and writes it for PostgreSQL and Oracle. A common case is a position column that must stay unique while two rows swap places:

CREATE TABLE playlist_items (
    playlist_id bigint NOT NULL,
    track       text NOT NULL,
    position    integer NOT NULL,
    PRIMARY KEY (playlist_id, track),
    CONSTRAINT playlist_items_position UNIQUE (playlist_id, position) DEFERRABLE INITIALLY DEFERRED
);

ptah migrations generate wrote the key with its clause:

-- POSTGRES TABLE: playlist_items --
CREATE TABLE "playlist_items" (
  "playlist_id" bigint NOT NULL,
  "track" text NOT NULL,
  "position" integer NOT NULL,
  PRIMARY KEY ("playlist_id", "track")
);
-- ALTER statements: --
ALTER TABLE "playlist_items" ADD CONSTRAINT "playlist_items_position" UNIQUE ("playlist_id", "position") DEFERRABLE INITIALLY DEFERRED;

After ptah migrations up, ptah schema compare --exit-code found no differences. Two rows then swapped positions in one transaction (swap.sql). Between the two updates both rows held position 2, and the check ran at commit:

BEGIN
UPDATE 1
UPDATE 1
COMMIT
 track | position
-------+----------
 outro |        1
 intro |        2
(2 rows)

The deferral is part of the key. Against the same schema without the clause, ptah schema compare plans a drop and an add of playlist_items_position (output). MySQL and MariaDB have no deferrable constraints, so Ptah refuses the clause when it reads a schema file for them (output).

Constraint names that match the server

When a SQL schema file leaves a constraint unnamed, the server picks the name. Ptah 0.10.0 derives the names that PostgreSQL and MySQL give. So a database built by running the file with psql or mysql compares equal to the file, and a database Ptah builds from the file carries the same names as one the server built. This PostgreSQL file names none of its constraints:

CREATE TABLE rooms (
    id bigint PRIMARY KEY
);

CREATE TABLE bookings (
    id      bigint PRIMARY KEY,
    room_id bigint NOT NULL REFERENCES rooms (id),
    guests  integer NOT NULL CHECK (guests > 0),
    during  tstzrange NOT NULL,
    lo      integer,
    hi      integer,
    CHECK (lo < hi),
    CHECK (hi > 0),
    EXCLUDE USING gist (during WITH &&)
);

We ran it with psql into one database and with ptah schema apply into another. Both list the same constraints:

        conname        | contype
-----------------------+---------
 bookings_check        | c
 bookings_during_excl  | x
 bookings_guests_check | c
 bookings_hi_check     | c
 bookings_pkey         | p
 bookings_room_id_fkey | f
(6 rows)

The CHECK over two columns is bookings_check, and the one over hi is bookings_hi_check, the way PostgreSQL names them. Ptah’s plan created each constraint under that name (output), and ptah schema compare --exit-code exited 0 against both databases.

The same test on MySQL 8.4.11, with an unnamed foreign key, two unnamed CHECK constraints and an unnamed UNIQUE, gave orders_ibfk_1, orders_chk_1, orders_chk_2 and code in both databases, and compare exited 0 against both (schema, names). MariaDB has rules of its own, which the reader follows as well (#3776, #3783). We did not run MariaDB.

The grant option on MySQL

On MySQL and MariaDB the grant option belongs to a grantee on an object, not to one privilege. REVOKE GRANT OPTION ON db.t FROM r is the statement that clears it, and it names no privilege. Ptah 0.10.0 plans with that rule (#3809, #3871).

Here the role reporter holds SELECT and INSERT on sales.orders with the grant option, and the declaration grants both without it. ptah schema compare plans one statement for the object:

Comparing schema from goschema with database mysql://root:***@127.0.0.1:3306/sales
=== SCHEMA COMPARISON ===

Differences detected (1 category):
  grant_options_revoked (2): reporter INSERT TABLE sales.orders, reporter SELECT TABLE sales.orders

Reconciling SQL:
REVOKE GRANT OPTION ON `sales`.`orders` FROM `reporter`;

After ptah schema apply, SHOW GRANTS listed both privileges without the option, and a second compare found no differences. A declaration that grants one privilege with the option and another without it can never hold on MySQL, so Ptah refuses it before it compares anything:

error: error comparing schemas: invalid schema diff: role "reporter" is granted INSERT on TABLE orders WITH GRANT OPTION and SELECT on the same object without it; MYSQL keeps the grant option at the whole object, not at one privilege, so the two declarations can never both hold -- declare WITH GRANT OPTION for every grant on this object, or for none of them

The declarations are Go annotations (sales.go). The role sets inherit="false", for the reason the first known issue gives. When a role loses its last privilege on an object and that privilege carried the option, MySQL keeps a grantable USAGE row for the role. Ptah clears it with the same statement (#3866). We did not run that case.

DS101 reports CREATE OR REPLACE TABLE

On MariaDB, CREATE OR REPLACE TABLE drops the table and creates an empty one. DS101, the rule for statements that delete a table and its rows, reports it:

CREATE OR REPLACE TABLE sessions (
    id    bigint PRIMARY KEY,
    token varchar(64) NOT NULL
);

ptah migrations lint --dir migrations --dialect mariadb

migrations/1790600000_sessions_token.up.sql:1 [error] DS101: CREATE OR REPLACE TABLE permanently deletes table sessions and every row in it; take a verified backup first and consider a rename-and-retire window instead (table dropped)

1 finding(s).

The lint exited 1. The statement classifier that schema plan --edit uses counts the statement as destructive too (#3807).

Also in 0.10.0

ptah schema plan --json and ptah schema apply --json print one versioned JSON document, on success and on failure. A dry run printed "contract_version": 1 and the statements it would run (output, #3777).
Every command that compares names the declared objects it could not decide, for example roles a MySQL account may not read. schema drift fails on them (#3850).
ptah migrations import --liquibase-dbms keeps the Liquibase changesets for one database, and rollback blocks are read the way Liquibase reads them (#3726, #3754).
ptah-compat accepts the maria:// scheme (#3758).

We did not run the last three items for this post.

What 0.9.0 added

Ptah 0.9.0 was published on September 26. The examples in this part ran with the 0.10.0 binaries.

Only the approved migrations run

A reviewer approves the migrations a run will apply. Between the approval and the run, the database can move: someone restores a backup, and more migrations are pending than were approved. ptah migrations up --expect-sequence names a JSON file with the approved list. up compares it with what it selects under the migration lock, and refuses any difference before the schema or the revision table changes.

In this example the database was at 1790200060, and a reviewer approved the one pending migration:

{"migrations":[{"version":1790200120}]}

Before the run, the database was restored from a backup taken at 1790200000, so two migrations were pending:

ptah migrations up --db-url "$DATABASE_URL" --migrations-dir migrations --verify-sum --expect-sequence approved.json

error: error running migrations: the migrations selected under the migration lock are [1790200060 1790200120], and the approved sequence is [1790200120]: the history moved after the sequence was approved, so nothing was run

The run exited 2, and accounts still had only id and email. With a new approval file that lists both migrations, up applied them.

Making a column NOT NULL

The contacts table has a row with NULL in email and score, and the schema file makes both columns NOT NULL. ptah migrations generate writes a plain SET NOT NULL, with a comment that says when it fails:

-- Add/modify columns for table: contacts --
-- Modify column contacts.email: nullable: true -> false --
-- ALTER statements: --
-- POSTGRES: SET NOT NULL fails if any row of "contacts" holds NULL in "email"; the column declares no default to fill it with.
ALTER TABLE "contacts" ALTER COLUMN "email" SET NOT NULL;
-- Modify column contacts.score: nullable: true -> false --
-- ALTER statements: --
-- POSTGRES: SET NOT NULL fails if any row of "contacts" holds NULL in "score"; the column declares no default to fill it with.
ALTER TABLE "contacts" ALTER COLUMN "score" SET NOT NULL;

ptah migrations up stopped on the first statement:

error: error running migrations: failed to apply migration 1790539321: failed to execute migration SQL: SQL execution failed: ERROR: column "email" of relation "contacts" contains null values (SQLSTATE 23502)
SQL: ALTER TABLE "contacts" ALTER COLUMN "email" SET NOT NULL

Both columns stayed nullable, and the row kept its NULL values. Ptah fills nothing on its own: the backfill is a decision the migration’s author makes. ptah migrations lint reports PG303 on both statements and describes the order that avoids the long lock: backfill, add a NOT VALID check, validate it, then set the column (output).

A psql schema file compares clean with the database it built

Many projects keep their schema as one SQL file for psql. The schema file in this example has an enum, a trigger and its function, row-level security with a restrictive policy, a view, grants on a function and a table, and comments. After psql ran it into an empty database, ptah schema compare --exit-code exited 0:

Comparing schema from schema.sql with database postgres://ptah:***@127.0.0.1:5432/tenancy?sslmode=disable
=== SCHEMA COMPARISON ===

No schema differences detected.

A second version of the file changes a column default, adds a grant and comments on the trigger. ptah migrations generate wrote those three changes and nothing else:

-- Add/modify columns for table: tenants --
-- Modify column tenants.plan: default_expr: 'trial'::text -> 'monthly' --
-- ALTER statements: --
ALTER TABLE "tenants" ALTER COLUMN "plan" SET DEFAULT 'monthly';
COMMENT ON TRIGGER "sites_touch" ON "sites" IS 'Skips the write when nothing changed.';
GRANT DELETE ON TABLE "sites" TO "app_user";

After ptah migrations up, a compare with the second file exited 0 as well.

Index advice for a TimescaleDB hypertable

TimescaleDB refuses CREATE INDEX CONCURRENTLY on a hypertable. With a TimescaleDB dev database, PG101 recognizes a hypertable and gives TimescaleDB’s per-chunk build as the advice:

migrations/1790300060_index_kind.down.sql:1 [warning] PG106: DROP INDEX without CONCURRENTLY blocks writes while PostgreSQL removes the index; use DROP INDEX CONCURRENTLY outside a transaction for populated tables (index dropped with a table lock)
migrations/1790300060_index_kind.up.sql:1 [warning] PG101: CREATE INDEX on the hypertable events blocks writes to it and to every chunk for the whole build, and TimescaleDB refuses CONCURRENTLY on a hypertable; WITH (timescaledb.transaction_per_chunk) in a no_transaction migration builds one chunk at a time, so only writes to the chunk being indexed wait. A per-chunk build that is interrupted leaves the index invalid, and IF NOT EXISTS then skips it, so drop the index before you retry (index built with a table lock)

2 finding(s).

Also in 0.9.0

The lint rule DS111P reports NO FORCE ROW LEVEL SECURITY (#3605).
Schema sources declare column privileges, and HCL and YAML declare privileges on functions (#3614, #3618).
ptah migrations refuses a revision row that spells its file’s version another way (#3557).
ptah migrations import converts Liquibase typed changes, such as createTable, for the dialect named with --dialect (#3640).

We did not run these items for this post.

Known issues

We found two defects in 0.10.0 while preparing this post, and filed both. Both also reproduce on master at the time of writing.

A declared MySQL role does not compare with the role on the server. A role declared with CREATE ROLE reporter; in a SQL schema file, or with //ptah:schema:role name="reporter" in Go, fails the comparison with a database that holds the role: role "reporter" declares an altered attribute, which a role does not carry here (output). A declared role inherits by default, and the MySQL reader reads the role as not inheriting. In Go, inherit="false" on the role is a workaround, which the grant example above uses. MySQL’s CREATE ROLE takes no such attribute, so a SQL schema file cannot use it (#3890).
A foreign key into another MySQL database breaks schema inspect output. With a URL that names shop, the read loses the database the key references. The SQL output stops with a template error that names customers (output), and the HCL output refers to table.customers as if it were in shop (output). With the whole-server URL and --schemas shop, the HCL output names table.crm.customers, and the SQL output stops with an error that names crm.customers (output). ptah db read and ptah schema compare fail on the same key (db read, compare, #3891).

Three defects in 0.10.0, filed before this post, bound the constraint-name section above. A database with two foreign keys over one column, compared with itself, drops one (#3873). An unnamed UNIQUE with INCLUDE never compares clean (#3863). A change to a UNIQUE column on MySQL or MariaDB adds a second unique key (#3857, fixed on master after the release). We did not run these cases.

Install 0.10.0

With Homebrew, on macOS or Linux. The tap installs a cask with ptah, ptah-compat and ptah-ls:

brew install stokaro/ptah/ptah

With the Go toolchain:

go install ptah.run/cmd/ptah@v0.10.0
go install ptah.run/cmd/ptah-compat@v0.10.0

As a container image, which holds ptah alone:

docker run --rm ghcr.io/stokaro/ptah:0.10.0 version

The release page has the archives for Linux, macOS and Windows on amd64 and arm64, with checksums.txt, an SBOM for each archive, and Sigstore bundles. We ran each of the three commands above once, in a disposable container, and each binary reported version 0.10.0. The example files say where.

Where to read more

The documentation links below go to the 0.10.0 version of each page.

Database URLs and dev databases says what a dev database and a shadow database must hold, and what Ptah resets.
PostgreSQL covers global default privileges and the names PostgreSQL gives unnamed constraints.
Apply migrations describes the approved sequence file.
Lint rules lists DS101, PG101, PG303 and the rest.
Compatibility differences lists where ptah-compat deliberately behaves differently from Atlas.
Install options covers the installer script, Homebrew, go install and building from source.

Put Ptah to work

Example files

← All articles