Skip to content
PtahDatabase MigrationsTry in browser

7 min read

Go Models and HCL Database Rules in One Ptah Schema

Declare tables with Go annotations, keep a shared PostgreSQL trigger function in HCL, and let Ptah merge both into one schema it diffs and migrates.

Simon HartSolutions Engineer

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

A Go service stores account emails in a unique column. Ada@Example.COM and ada@example.com must be the same account, whether the row comes from the application, a backfill script, or someone at a psql prompt. The rule that guarantees this belongs in the database: a trigger that normalizes the email before the uniqueness check sees it.

The tables already live in Go structs with Ptah annotations. The trigger function is PL/pgSQL, and two tables need it. We’ll keep the application model in Go, put the database rule in an HCL file, and let Ptah treat both as one desired schema: one diff, one migration, one comparison.

This run used Ptah 0.14.0 and PostgreSQL 18.6. The supporting files contain the models, the HCL file, the generated migrations, and every recorded result.

Where a Go annotation ends

Section titled “Where a Go annotation ends”

An annotation is a //ptah: comment beside a struct or a field. Ptah reads the source files with Go’s own parser and never builds or runs the package, so the Go toolchain does not have to be installed. Our render below ran with no go on the PATH.

examples/models/invitation.go
package models


// Invitation is an address an account has invited but that has not signed up
// yet, so it is matched against Account.Email when the invitee arrives.
//
//ptah:schema:table name="invitations"
type Invitation struct {
  //ptah:schema:field name="id" type="BIGINT" primary="true" identity_generation="BY DEFAULT"
  ID int64


  //ptah:schema:field name="account_id" type="BIGINT" not_null="true" foreign="accounts(id)" foreign_key_name="invitations_account_fk"
  AccountID int64


  //ptah:schema:field name="email" type="TEXT" not_null="true"
  Email string
}

Annotations can declare functions and triggers too, but every annotation is a single comment line. A trigger annotation takes its body as one quoted attribute, and it has no attribute for running a function declared somewhere else. We tried to point a trigger at normalize_email:

examples/expected/annotation-shared-function.txt
error: error parsing packages: unknown annotation attribute "function" on //ptah:schema:trigger at Account

With the body written inline instead, Ptah generates a private function for every trigger. Two triggers on one table become two copies of the same body:

examples/expected/annotation-inline-body.txt
CREATE FUNCTION "ptah_trigger_accounts_accounts__normalize__email__on__insert"()
CREATE TRIGGER "accounts_normalize_email_on_insert" BEFORE INSERT ON "accounts" FOR EACH ROW EXECUTE FUNCTION "ptah_trigger_accounts_accounts__normalize__email__on__insert"();
CREATE FUNCTION "ptah_trigger_accounts_accounts__normalize__email__on__update"()
CREATE TRIGGER "accounts_normalize_email_on_update" BEFORE UPDATE ON "accounts" FOR EACH ROW EXECUTE FUNCTION "ptah_trigger_accounts_accounts__normalize__email__on__update"();

Four triggers across two tables would mean four copies to keep in step. The tables are a good fit for annotations; the rule is not.

Put the database rule in HCL

Section titled “Put the database rule in HCL”

HCL gives the function a multi-line body and lets a trigger name the function it runs. The file declares normalize_email once and four triggers that execute it, one per table and event. This is the function and the first trigger:

examples/expected/email-function-and-trigger.hcl
function "normalize_email" {
  schema = schema.public
  lang   = PLpgSQL
  return = trigger
  as     = <<-SQL
    BEGIN
      NEW.email := lower(btrim(NEW.email));
      RETURN NEW;
    END;
  SQL
}


trigger "accounts_normalize_email_on_insert" {
  on = table.accounts
  before {
    insert = true
  }
  for = ROW
  execute {
    function = function.normalize_email
  }
}

table.accounts refers to a table the HCL file never declares. It comes from the Go models. The reference crosses the source boundary without an import or a stub declaration:

The Go models own the accounts and invitations tables and the foreign key between them. The HCL file owns the normalize_email function and four triggers, two on each table. Every trigger is attached to a Go table and executes the one HCL function.The Go models own the accounts and invitations tables and the foreign key between them. The HCL file owns the normalize_email function and four triggers, two on each table. Every trigger is attached to a Go table and executes the one HCL function.
Each object has one owner. References cross between the sources.

How two sources become one schema

Section titled “How two sources become one schema”

Both flags repeat and mix: --root-dir for Go packages, --schema-file for HCL, YAML, SQL or DBML files. Every reader returns the same Go value, a schemamodel.Database, and nothing after the readers can tell which format an object came from.

Go files are parsed by goschema.ParseDirRaw and HCL files by the HCL reader. Each produces a schemamodel.Database. schemamodel.Merge joins them, checks identities, drops identical duplicates, expands embedded fields and orders by dependency. schema render, schema compare and migrations generate read the one merged schema.Go files are parsed by goschema.ParseDirRaw and HCL files by the HCL reader. Each produces a schemamodel.Database. schemamodel.Merge joins them, checks identities, drops identical duplicates, expands embedded fields and orders by dependency. schema render, schema compare and migrations generate read the one merged schema.
Readers produce the same model; one merge step finalizes it for every command.

The model is not a tree of Go types or HCL blocks. It is a set of flat lists: tables, fields, indexes, constraints, functions, triggers and the rest. A Go struct becomes one entry in Tables, and each annotated field becomes one entry in Fields that records its struct in StructName. An HCL function becomes one entry in Functions. A trigger becomes one entry in Triggers that names its table and the function it executes as plain strings.

The Account struct becomes a Tables entry and one Fields entry per annotated field, each field recording StructName Account. The HCL function becomes a Functions entry, and the trigger a Triggers entry that names table accounts and ExecuteFunction public.normalize_email.The Account struct becomes a Tables entry and one Fields entry per annotated field, each field recording StructName Account. The HCL function becomes a Functions entry, and the trigger a Triggers entry that names table accounts and ExecuteFunction public.normalize_email.
Flattening: each declaration becomes an entry in a list, and entries refer to each other by name.

Because references are names, a cross-source reference is not a special case. The merge concatenates every source’s lists. Then it runs the same finalization a single Go root gets:

  1. Compare every object by its database identity: the schema-qualified name for a table, the table-qualified name for a field, index, constraint or trigger, and the global name for a function or extension. Identical definitions collapse to one. Different definitions with the same identity stop the command.
  2. Expand embedded Go structs into the fields of the tables that embed them.
  3. Build the dependency graph from foreign keys, then order tables and functions by their dependencies.

The code is short enough to read in full: internal/schemaload parses each Go root without finalizing it (goschema.ParseDirRaw), reads each schema file, and hands everything to schemamodel.Merge.

Rendering the merged schema shows the result. The summary counts two tables and five fields, all from Go. The statement order interleaves the two sources by dependency:

Terminal window
ptah schema render --root-dir ./models --schema-file ./db/email.hcl \
  --dialect postgres
examples/expected/render-summary.txt
Found 2 tables, 5 fields, 0 indexes, 0 enums, 0 embedded fields


Table Dependencies:
==================
accounts: (no dependencies)
invitations: depends on [accounts]


Table Creation Order:
====================
1. accounts
2. invitations

The first line of each rendered statement, in order:

examples/expected/render-order.txt
CREATE FUNCTION "public"."normalize_email"() RETURNS trigger AS $$
CREATE TABLE "accounts"
CREATE TABLE "invitations"
ALTER TABLE "invitations" ADD CONSTRAINT "invitations_account_fk" FOREIGN KEY ("account_id") REFERENCES "accounts"("id");
CREATE TRIGGER "accounts_normalize_email_on_insert" BEFORE INSERT ON "accounts" FOR EACH ROW EXECUTE FUNCTION "public"."normalize_email"();
CREATE TRIGGER "accounts_normalize_email_on_update" BEFORE UPDATE ON "accounts" FOR EACH ROW EXECUTE FUNCTION "public"."normalize_email"();
CREATE TRIGGER "invitations_normalize_email_on_insert" BEFORE INSERT ON "invitations" FOR EACH ROW EXECUTE FUNCTION "public"."normalize_email"();
CREATE TRIGGER "invitations_normalize_email_on_update" BEFORE UPDATE ON "invitations" FOR EACH ROW EXECUTE FUNCTION "public"."normalize_email"();

The function comes first, because it needs nothing else. The foreign key follows the two tables it connects, and the triggers come last, after both the tables they attach to and the function they run. The tables came from Go and the rest from HCL; the order does not show it.

Generate, apply, and test the rule

Section titled “Generate, apply, and test the rule”

DB_URL points to an empty PostgreSQL database and SHADOW_URL to a separate, disposable one. The same two flags drive migration generation:

Terminal window
ptah migrations generate --db-url "$DB_URL" \
  --root-dir ./models --schema-file ./db/email.hcl \
  --migrations-dir migrations --name accounts_and_email_rules \
  --shadow-db "$SHADOW_URL"
ptah migrations lint --dir migrations --dialect postgres --fail-on error
ptah migrations hash --dir migrations
ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum

Generation passed its shadow apply, rollback and reapply check. Lint exited 0 with one note and five warnings, all worth reading. The note says the function body is not analyzed: Ptah does not parse PL/pgSQL, so reviewing the rule is still a person’s job. The warnings are about locks that CREATE TRIGGER and a new foreign key take. In this migration the tables are created by the same file, so no other session is writing to them yet.

The application check inserts two accounts and an invitation, then updates the invitation’s email:

examples/measured/application.txt
INSERT 0 2
INSERT 0 1
UPDATE 1
accounts|1|ada@example.com
accounts|2|grace@example.com
invitations|1|1|linus.t@example.com

The inserted ' Ada@Example.COM ' and the updated 'LINUS.T@EXAMPLE.COM ' were stored normalized. A second account for ADA@example.com is now a duplicate of the first, and the unique constraint rejects it:

examples/measured/duplicate.txt
ERROR:  duplicate key value violates unique constraint "accounts_email_key"
DETAIL:  Key (email)=(ada@example.com) already exists.

Comparing the live database with the merged desired schema found no differences. The function and all four triggers read back from the catalog exactly as the HCL file declares them.

Terminal window
ptah schema compare --db-url "$DB_URL" \
  --root-dir ./models --schema-file ./db/email.hcl \
  --schemas public --exit-code

Change the Go side only

Section titled “Change the Go side only”

The service needs a display name. The change touches one Go struct:

examples/models/account.go
package models


// Account is a customer login. Its email is unique, so every spelling of one
// address has to reach the database in a single canonical form.
//
//ptah:schema:table name="accounts"
type Account struct {
  //ptah:schema:field name="id" type="BIGINT" primary="true" identity_generation="BY DEFAULT"
  ID int64


  //ptah:schema:field name="email" type="TEXT" not_null="true" unique="true"
  Email string


  //ptah:schema:field name="display_name" type="TEXT"
  DisplayName *string
}

Generating against the updated database, with the same two sources, produced a migration with a single statement:

examples/expected/display-name.sql
ALTER TABLE "accounts" ADD COLUMN "display_name" TEXT;

Nothing from the HCL file was recreated. The desired function and triggers already matched the database, so they were not part of the diff. After applying it, the comparison found no differences again, and an update that set the display name still stored the email normalized. All four triggers still executed the one normalize_email function.

Give every object one owner

Section titled “Give every object one owner”

Composition works when each object has exactly one owner. We added a second HCL file that also declared accounts, with email as varchar(320) instead of TEXT, and rendered all three sources. No database is involved:

Terminal window
ptah schema render --root-dir ./models --schema-file ./db/email.hcl \
  --schema-file ./conflict/accounts.hcl --dialect postgres
examples/expected/conflict.txt
error: error merging composite schema: conflicting table "accounts" definitions

The command exited 2 and rendered nothing. Ptah does not choose a winner between two definitions of one object, and it does not treat one source as an overlay on another. Split ownership by object, not by property: accounts belongs to Go in full, and normalize_email belongs to HCL in full.

The result is a split that follows the objects. Go annotations describe the tables the application reads and writes, next to the code that uses them. The HCL file holds the database rules that need a real body and more than one table. Ptah merges both into one desired schema, and every command that reads the schema reads the merged one.

Put Ptah to work

Example files

← All articles