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/public.email_address.json
  Enum Types/public.order_status.json
  Sequences/public.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"] }

The domain is declared the way you’d write it in a CREATE — "DataType": "VARCHAR(256)". PostgreSQL stores that as character varying(256), and SchemaSmith compares the two as the same type, so there’s no canonical spelling to memorise.

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.

Now make the edit that vanished at the top of this recipe. In the domain’s declaration, change the CHECK’s Expression from VALUE LIKE '%@%' to VALUE LIKE '%@%.%' — same name, new rule — and re-quench:

Altering constraint email_address_has_at on domain type public.email_address

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

The exact edit the guarded script swallowed lands here, because nothing guards a declaration from being compared. The rest of 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.

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: Two refusals in this recipe exit differently: changing the domain's base type exits 2, removing an enum value exits 0. Why aren't they the same, and what does each one leave in the database?

Both are changes PostgreSQL can’t make in place without dropping every column that uses the type, so SchemaSmith attempts neither. The difference is what the mismatch means. A different base type is a declaration the database can never satisfy — the model is simply wrong about the object — so the run stops at exit 2 and names both types. An extra enum value is harmless to leave: every declared value is still there, so the run reports the leftover at exit 0 and moves on.

In both cases the database is untouched. The domain keeps its original type; the enum keeps returned. What you get is a precise statement of where the file and the database disagree, and the decision stays yours.

---

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