From Seed Scripts to Desired-State Reference Data in PostgreSQL

From Seed Scripts to Desired-State Reference Data in PostgreSQL • pgmi

From Seed Scripts to Desired-State Reference Data in PostgreSQL# Version the catalog that should exist — not the procedure that inserts it. By Alexey Evlampiev A seed script records how to construct a catalog. What you need to version is the catalog that should exist. The difference barely matters when seed.sql is twelve lookup rows. It starts to matter once roles reference permissions, workflow states reference their legal transitions, and plan tiers reference feature flags — once every edit has to preserve foreign-key order by hand. At that point the script has quietly become a hand-maintained topological sort of your data model: an acyclic insert-dependency graph, revised on every new relationship and enforced by nothing but SQLSTATE 23503 at deploy time. This article is about that kind of data — the catalogs a schema cannot function without. Application roles and their permission grants. Status catalogs and which transitions are legal. Tax rate tables and ISO currency codes. Call it desired-state reference data : what you want to version is the catalog that should exist, not the procedure that builds it. (It is not immutable — tax rates and permissions change constantly — which is exactly why the maintenance problem below bites.) Teams routinely carry tens of such tables; the worked example here is three, and it already exhibits the core failure modes. One file doing two jobs# Here is the seed script for a three-table role/permission catalog, the way these scripts actually look in the wild: INSERT INTO permission (permission_id, key, description) VALUES (1, 'invoice.read', 'View invoices'), (2, 'invoice.approve', 'Approve invoices for payment'), (3, 'report.run', 'Run financial reports'), (4, 'catalog.edit', 'Maintain product catalog entries');

INSERT INTO role (role_id, key, description) VALUES (1, 'auditor', 'Read-only access for audits'), (2, 'controller', 'Approves spending'), (3, 'catalog_manager', 'Owns the product catalog');

-- must run last, and every pair is a number puzzle INSERT INTO role_permission (role_id, permission_id) VALUES (1, 1), (1, 3), (2, 1), (2, 2), (2, 3), (3, 4); The ordering ceremony. role_permission must come last; swap the statements and the deploy fails with violates foreign key constraint (SQLSTATE 23503). With three tables that is easy to keep straight. Each new relationship adds an edge to the dependency graph you are sorting by hand. (PostgreSQL does offer an escape hatch — constraints declared DEFERRABLE INITIALLY DEFERRED are checked at commit rather than per statement — but deferring the check means violations surface only at COMMIT, far from the statement that caused them, and it does nothing for the surrogate keys or the validation gaps below.) The surrogate-key puzzle. What does (2, 3) mean? You have to cross- reference two other statements to find out it grants report.run to controller. The diff of a permission change is a diff of integer pairs. Django’s fixture system has surfaced this exact failure mode repeatedly — hardcoded primary keys that collide and re-insert instead of updating (ticket #31531 , and downstream reports like NetBox #10940 where re-running loaddata hits duplicate keys). Non-idempotent re-runs. Run the script twice and the second run dies on a unique violation — unless every statement grows its own ON CONFLICT clause, at which point the script is no longer data anyone can read. The schema-evolution tax. Add a NOT NULL column without a default to permission and every seed INSERT that omits it breaks. The script has to be revised in lockstep with migrations. (This tax never fully disappears — it moves, as we’ll see, from a hundred INSERTs into one loader contract.) Constraints validate structure, not intent. A foreign key catches a genuinely nonexistent id: (2, 99) where permission 99 does not exist fails immediately with SQLSTATE 23503. What a foreign key cannot catch is an opaque (2, 3) that satisfies the constraint while pointing at the wrong permission — the mapping is valid SQL and wrong data. Nothing checks that the catalog is internally coherent. None of these is a fringe observation. Microsoft’s Entity Framework team renamed EF Core’s “data seeding” feature to “model managed data” because, in their own words, the old name “sets incorrect expectations, as the feature has a number of limitations and is only appropriate for specific types of data” — the documented limits: hardcoded primary keys even when the database would generate them; data removal when a key changes; and explicit foreign-key values for every child row (EF Core docs this describes the HasData model-managed path, not EF’s runtime UseSeeding hook). When a first-party vendor renames the category to lower expectations, the category has a problem. The common thread is that one file is doing two jobs: it encodes both the desired catalog...

From Seed Scripts to Desired-State Reference Data in PostgreSQL

Related Articles

(no title)

Scientists reverse brain aging, with a nasal spray

AI has torched the market for junior programmers

Al Vigier: Canada's AI strategy shouldn't include secret Palantir bills

The Anatomy of an AI-Native Org