October 26, 20268 min read

Generate OpenMeter's View Migrations Instead of Copying Them

OpenMeter copies generated view SQL into its migrations by hand. Ptah Compat writes those statements and keeps Ent, golang-migrate and schema_om.

Simon HartSolutions Engineer

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

OpenMeter defines its tables and one view, charges_search_v1s, in Ent. Atlas generates the table migrations. The view is different: the project’s migration instructions tell the developer to copy the generated CREATE VIEW from tools/migrate/views.sql into the new migration, and to add a DROP VIEW when the definition changes. A parity test catches a wrong copy afterwards.

We ran the same workflow with ptah-compat as the generator. Ent stays the source of truth. The committed files keep the golang-migrate layout, and golang-migrate still applies them and records the version in schema_om. The copy step goes away: ptah-compat migrate diff writes the view statements itself, in the order PostgreSQL needs.

The study uses OpenMeter commit f04b561f8, Ptah 0.16.1 and PostgreSQL 15.19. The integration patch, scenario changes and records are beside the article. This is an independent case study. OpenMeter has not adopted it.

Who writes which SQL

Each part of OpenMeter’s schema pipeline writes its own SQL:

Part	Writes
Ent schema and `tools/migrate/cmd/entschema`	the table SQL Atlas compares with
`viewgen`, run by `go generate`	`tools/migrate/views.sql`
`tools/migrate/deferred_constraints.sql`	foreign keys Ent cannot make deferrable
`atlas migrate --env local diff`	timestamped `.up.sql` and `.down.sql` files and `atlas.sum`

golang-migrate applies the committed files. OpenMeter’s own instructions say Atlas “does not auto-generate view DDL”, so step 4 of every view change is a manual edit of a generated file.

The configuration also needs more than the open-source Atlas build. The Community edition refuses the exporter data source on this atlas.hcl:

Error: data.external_schema is not supported by the community version of Atlas.

Its upgrade notice also lists views among the objects the edition does not support. OpenMeter’s CI job installs the non-Community Atlas binary, version 0.36.0. ptah-compat reads the same atlas.hcl, runs the exporter as Atlas does, and needs no account.

OpenMeter’s Ent tables, generated views.sql and deferred constraints feed the migration diff. With Atlas, the developer copies view SQL into the generated file. With ptah-compat, the diff writes the view statements. golang-migrate applies the committed files in both cases.

What the integration changes

The integration patch touches the exporter, atlas.hcl and one new SQL file. The migration history stays as it is.

The exporter reads the views from views.sql. Ent’s own view DDL lists the columns of charges_search_v1s in a different order from the one its SELECT produces them in. PostgreSQL names a view’s columns by position, so that DDL would label the id column type. views.sql is the definition OpenMeter already tests, so the exporter passes it on beside the deferred constraints:

      ddl, err := entschema.GenerateSQL(cmd.Context(), entschema.GenerateSQLInput{
        SchemaPath:      "./openmeter/ent/schema",
        PostgresVersion: "15",
        SupplementalDDL: string(views) + "\n" + string(unvalidated) + "\n" + string(constraints),
        OmitViews:       true,
      })

Objects the history owns are excluded from the comparison. Functions, a trigger, the pgcrypto extension and two kinds of operational tables come from hand-written migrations, not from Ent. Without an exclusion, the diff would plan to drop them:

    schema_exclude = [
      "distributed_locks[type=table]",
      "om_migration_backup_*[type=table]",
      "om_func_*[type=function]",
      "delete_entitlement_on_subscription_item_delete[type=function]",
      "*.trigger_delete_entitlement_on_subscription_item_delete[type=trigger]",
      "pgcrypto[type=extension]",
    ]

Ten ledger foreign keys may stay NOT VALID. Migration 20260709134422_add_ledger_internal_fks adds them that way on purpose and says they are validated “separately after orphan preflight”. Ptah compares validation state, and Ent has no way to declare it. Without a declaration, the first diff would validate all ten at once, and a production run with an orphaned row would fail on it. The new unvalidated_constraints.sql allows each key to stay NOT VALID, one line per key, the way deferred_constraints.sql records deferrability:

-- ptah:allow-not-valid CONSTRAINT "ledger_customer_accounts_ledger_accounts_customer_accounts" ON "ledger_customer_accounts"

The definition stays Ent’s. A change to the key’s columns or actions is still planned, and the key comes back NOT VALID. A key validated later satisfies the line too; removing the line makes Ptah require the key validated again.

The first diff on the unchanged history is empty

With the patch applied, we run OpenMeter’s documented command with ptah-compat in place of atlas:

ptah-compat migrate --env local diff --dev-url "$DEV_URL" baseline_check

Ptah replays the 304 committed migrations into the dev database and compares the result with the exporter’s output:

The migration directory is synced with the desired state, no changes to be made

No file is written. The history, the views it created and the ten unvalidated keys match the desired schema as declared.

The error stream reports each kind of warning in one line:

warning: 3 atlas.hcl settings are ignored for Atlas compatibility and have no effect; PTAH_LOG_LEVEL=debug lists them
warning: 47 names are longer than 63 bytes; the server keeps their first 63 bytes, and they are read that way; PTAH_LOG_LEVEL=debug lists them

The three settings are exclude and non_linear entries the community binary accepts and ignores too.

Ent writes 40 foreign keys and 7 indexes with names longer than PostgreSQL’s 63-byte limit. The server keeps the first 63 bytes, and so do the history files Atlas generated. Ptah reads each name the way the server stores it and refuses two names that would be cut to the same one. No dev database is needed for that check. PTAH_LOG_LEVEL=debug lists each name as written and as stored.

$DEV_URL points at a dedicated PostgreSQL 15.19 server. The history runs DO blocks and its own BEGIN, so the run also sets PTAH_DEV_SERVER_DISPOSABLE=1, which declares that server disposable. It holds no application data.

Change the view

The view change adds status_detailed to charges_search_v1s. The developer edits the Ent view schema and runs go generate, which rewrites views.sql. The scenario patch holds the Ent change. Then:

ptah-compat migrate --env local diff --dev-url "$DEV_URL" add_status_detailed_to_charges_search

Ptah writes an up file with one statement, CREATE OR REPLACE VIEW, which appends status_detailed to each branch of the UNION ALL. PostgreSQL accepts a replace that only adds columns at the end, so the view keeps its grants and nothing that reads it is dropped. The down file cannot use a replace, because it removes a column, so it drops the view and recreates the previous definition:

-- reverse: modify "charges_search_v1s" view
DROP VIEW "charges_search_v1s";
-- reverse: modify "charges_search_v1s" view
CREATE VIEW "charges_search_v1s" AS
SELECT charge_credit_purchases.id,
    charge_credit_purchases.namespace,

Nobody copied views.sql. golang-migrate applies the new file:

migrate -database "$OPENMETER_URL" -path ./tools/migrate/migrations up

The rows already in the three charge tables appear in the view with the new column. A second migrate diff reports that the directory is synced.

Change a column the view reads

The dependency case changes charge_flat_fees.feature_key from varchar to varchar(128) in Ent. PostgreSQL refuses to change the type of a column that a view reads:

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view charges_search_v1s depends on column "feature_key"

Ptah plans around it. The up file drops the view, changes the column, and creates the view again from views.sql:

-- drop "charges_search_v1s" view
DROP VIEW "charges_search_v1s";
-- modify "charge_flat_fees" table
ALTER TABLE "charge_flat_fees" ALTER COLUMN "feature_key" TYPE character varying(128);

The down file does the same in reverse. Under the manual workflow, this is the change where a developer has to know about the dependency and order three statements by hand.

The third scenario, a nullable external_reference on tax_codes, is an ordinary ALTER TABLE ... ADD COLUMN.

Down files, a fresh database and OpenMeter’s tests

The records compare each state of the reference database with the catalog after the matching step:

down 1 = after scenario 2: same
down 2 = after scenario 1: same
up again = after scenario 3: same
data after up again = after scenario 3: same

A fresh database built from the whole directory, history and new files, has the same catalog as the incrementally upgraded one, and ptah-compat schema diff reports them synced. The ten ledger keys are still NOT VALID at the end.

OpenMeter’s own TestViewDefinitionsMatchGeneratedSchemaSQL, TestSourceWrapper and TestLegacyEntAdoptionSchemaParity pass on the extended directory. The view parity test now checks statements Ptah wrote.

OpenMeter’s CI checks pass

The “Migration Checks” job in OpenMeter’s CI installs Atlas and runs one target. We ran it in the golang:1.27.0 image, the Go version OpenMeter’s .go-version names, with ptah-compat 0.16.1 installed as atlas:

make migrate-check

The target exits 0. go generate leaves Ent’s code unchanged. atlas migrate --env local diff starts its own dev database from docker://postgres/15/dev and writes no file. migrate validate accepts the atlas.sum checksums.

migrate lint --latest 10 covers the three new files. Two have no findings. The column change gets a warning, not an error:

  -- analyzing version 20261026150332
    -- diagnostics detected:
      -- L4 [PG301]: ALTER COLUMN "charge_flat_fees"."feature_key" TYPE character varying(128)
         changes the type from character varying to character varying(128), which checks every
         value against the new limit; PostgreSQL rewrites the whole table and rebuilds every
         index on it under the ACCESS EXCLUSIVE lock the statement holds, blocking reads and
         writes until the rewrite finishes, and a stored value the new type cannot hold aborts
         the statement (value too long for type). A widening PostgreSQL can prove safe is a
         catalog edit instead: a longer or unlimited varchar, a higher numeric precision at the
         same scale, a longer fractional-seconds precision. Where the change cannot be expressed
         that way, add a new column, backfill it in batches, and swap the two
  -- ok (2.259297ms)

The scenario adds a length limit to an unlimited column, so PostgreSQL checks every value and rewrites charge_flat_fees. On a large production table, that change needs the rollout the warning describes. The older files in the window get their own warnings, for example for BEGIN blocks in hand-written migrations. None of them is an error.

The pull request job also checks that atlas.sum only gains lines. The three new files append three records, and the check passes against the base commit:

python3 .github/tools/migrate/check_atlas_sum_append_only.py f04b561f8

What changes for the next schema change

Step	With Atlas	With `ptah-compat`
Edit Ent, run `go generate`	yes	yes
Generate the migration	`atlas migrate --env local diff`	`ptah-compat migrate --env local diff`
Copy view SQL into `.up.sql`, add `DROP VIEW`	by hand	not needed
Order view drops around column changes	by hand	written by the diff
Apply the files	golang-migrate	golang-migrate
Run `make migrate-check` in CI	Atlas 0.36.0	`ptah-compat` installed as `atlas`

SQL execution stays where it was. golang-migrate runs the committed files and knows nothing about views. The view becomes declared state at generation time: Ptah compares the definition in views.sql with the one the replayed history produced and writes the statements that close the gap.

The new files use comments for each planned change and leave the Atlas-written history intact. Each file applies, and the repeat diff is empty.

Known issues

Some migration shapes still differ from Atlas’s generated SQL: stokaro/ptah#4921.
A view whose CREATE VIEW has a column list and aliased select items, as Ent’s own view DDL does, is rendered with two aliases on an item: stokaro/ptah#4933. The integration reads views.sql, which has no column list.

Start with the examples README to reproduce the run.

Put Ptah to work

Example files

← All articles