Declare PostgreSQL types instead of scripting them

🧪

Hands-on lab

Run this module yourself — the `before/` source, the three-engine sandbox, and the worked solution to diff against.

Demos/Learn/course4-recipe-11 →

There’s a script in your repo that hasn’t done anything in eight months.

Not a broken one. It runs on every deploy, reports success, and touches nothing. Somebody edited it in March — tightened a CHECK, added a value to a list — and the edit has been sitting there ever since, in source control, reviewed and approved and completely inert. Nobody noticed, because nothing failed. There’s no error to grep for. The run was green every single time.

Here’s the shape it takes:

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'email_address') THEN
    CREATE DOMAIN public.email_address AS VARCHAR(256)
      CONSTRAINT email_address_has_at CHECK (VALUE LIKE '%@%');
  END IF;
END $$;

Look at what that guard actually promises. It says: create this if it doesn’t exist. Which means once it exists — once, on some laptop, eighteen months ago — the body never executes again. Every edit you make below that IF NOT EXISTS is decoration. The guard that makes the script safe to re-run is the same guard that makes it deaf.

Watch it lie

Run that script, then look at what landed:

email_address_has_at|CHECK (((VALUE)::text ~~ '%@%'::text))

Now edit it. Change VALUE LIKE '%@%' to VALUE LIKE '%@%.%' — require a dot after the at-sign, the sort of tightening a review would wave straight through. Run the same file again:

DO

That’s the whole output. DO. Success. Now look at the database:

email_address_has_at|CHECK (((VALUE)::text ~~ '%@%'::text))

Unchanged. Your edit is in git, in the deploy log, in the reviewer’s approval — and nowhere in the database. This is worse than a script that fails. A failure tells you something. This tells you it worked.

Declare it instead

SchemaSmith 2.6.0 lets you stop scripting three PostgreSQL object types and start declaring them: domain types, enum types, and standalone sequences. They get their own folders next to Tables/, and the same treatment your tables have had all along — compared on every deploy, converged where possible, removable by absence.

Templates/Main/
  Domain Types/email_address.json
  Enum Types/order_status.json
  Sequences/order_number.json
  Tables/public.customer_order.json

An enum type is exactly as boring as it should be:

{ "Schema": "public", "Name": "order_status",
  "Values": ["placed", "picked", "shipped", "delivered"] }

Now make the same kind of edit that vanished a minute ago. Append "returned" to Values, re-quench, and read the log:

Add Missing Enum Values
  Add enum value returned to public.order_status

And in the database:

1|placed   2|picked   3|shipped   4|delivered   5|returned

It landed — in the order you declared it, not wherever the engine felt like dropping it. Same edit, same class of object, opposite outcome. The difference is that nothing is guarding the declaration against being read. There’s no IF NOT EXISTS to go deaf behind. The file is compared to the database on every single deploy, so the file means something on every single deploy.

The domain converges the same way. Flip "NotNull" to false, give it a "Default", re-quench:

email_address|f|'unknown@example.com'::character varying

Both moved. Put them back, re-quench, and they move back. The declaration is the truth and the database follows it — in both directions, which is the part a migration script can never give you.

Spell the type the way the engine says it

One thing that will bite you on the first run. Declare the domain’s base type as PostgreSQL reports it, not as you’d type it in a CREATE:

{ "DataType": "character varying(256)" }

Not VARCHAR(256). PostgreSQL canonicalises its aliases when it stores them — varchar becomes character varying, int becomes integer, bool becomes boolean — and SchemaSmith compares your declared spelling against what the catalog reports back. An alias never matches, and the deploy stops rather than guessing at what you meant. Use the canonical name and it’s a non-event.

Then watch it refuse

A tool that converged everything would be a liability, not a feature. Two changes here are refused by name, and both refusals are the point.

Change the base type. Set "DataType" to "text" and re-quench:

P0001: Domain type public.email_address declares base type "text", but is currently deployed as
"character varying(256)". PostgreSQL has no ALTER DOMAIN ... TYPE -- changing it means dropping the
domain and every column that uses it. Migrate it with a script, or correct the declared type to match.

Exit 2. Nothing was attempted. PostgreSQL genuinely has no ALTER DOMAIN … TYPE — it isn’t an unsupported operation, it’s a syntax error — so the only route is to drop the domain, and dropping a domain drops every column typed by it. SchemaSmith names both types, names the reason, and stops.

Remove an enum value. Take "returned" back out and re-quench:

Enum type public.order_status has value 'returned' which the package no longer declares. PostgreSQL
cannot remove an enum value without recreating the type (and dropping every column that uses it), so
it is left in place - remove it by hand, or restore it to the package.

Exit 0 this time. That’s deliberate — it’s a report, not a failure. The value stays where it is, and you’re told precisely where the model and the database disagree, then left to make the call. A deploy that silently dropped an enum value would take a column’s worth of data with it.

Notice what just happened across those two beats. The scripted form was silent when it should have spoken. The declared form speaks when it can’t act — and speaks differently depending on whether the disagreement is fatal or merely worth knowing about. That’s the whole trade.

Check yourself: You move a guarded CREATE DOMAIN script into a Domain Types JSON file. On the first deploy against an existing database it fails with 'declares base type VARCHAR(256), but is currently deployed as character varying(256)'. What happened, and what would the same package have done on an empty database?

The declaration used a PostgreSQL type alias. The engine stores VARCHAR(256) as character varying(256) and reports the canonical name back, so the declared spelling can never match what the catalog says — and SchemaSmith stops rather than guess. Fix it by declaring character varying(256).

The second half is the useful part: an empty database fails too. Nothing about this is specific to an existing domain — the alias never matches the canonical name the catalog reports, so the mismatch is there the moment the object exists, including the moment the same run just created it. Reach for the canonical spelling from the start and neither case arises.

---

Every recipe in this course has been about moving a fact out of a script and onto the model, where something can read it. This one is the same move aimed at the scripts themselves. A guarded CREATE was never a declaration — it was an instruction that expired the first time it ran, and then spent years pretending otherwise. A declared object has no expiry, because nothing is protecting it from being compared.

So the question to carry out of here isn’t “which objects can I declare?” It’s the one you can ask of any script in your repo right now: if I edited this today, would anything happen? If the answer is no, that file isn’t configuration. It’s a fossil.

Found a guarded CREATE in your own repo that’s been inert for a year? Email me at forgebarrett@schemasmith.com — I collect these, and the record is longer than you’d think. More’s coming from the forge.

Until then, may every file you edit leave a mark, and may nothing in your repo be quietly asleep.

— Forge

Mark this lesson complete