Four Ways to Seed a Postgres Database, and What Each Does When the Schema Moves

[Mikhail Shytsko]

Add a mandatory column to a table that already holds rows, give it no default, and Postgres declines the migration outright.

ALTER TABLE users ADD COLUMN role text NOT NULL;
ERROR:  column "role" of relation "users" contains null values

Failing there is the good outcome, because it lands in the migration rather than in your seed data, with whoever ran the DDL standing right next to it when it happens.

Trouble starts with the version of that change somebody makes ten seconds later, having read the error and done the sensible thing.

ALTER TABLE users ADD COLUMN role text NOT NULL DEFAULT 'member';

Since Postgres 11 that form does not even rewrite the table, the default living in the catalogue rather than being stamped onto every existing row, so the migration goes green and the seed script runs afterwards none the wiser. If the script lists its columns by name without mentioning role, every seeded user comes out a member and nothing anywhere reports a problem. I once spent most of a week explaining to a colleague that staging accounts could not possibly have no tier set, while a seed script written in March filled a column added in August with whatever the migration had defaulted it to.

Migrations get described as wearing a seed script down over time, which does not match anything I have watched happen. The script works exactly as written until one specific DDL statement lands, and after that it either stops or starts lying, depending on which statement it was.

Everything below was run on PostgreSQL 17.11 in a throwaway container, and the error text is copied out of psql rather than remembered.

The changes that actually break things​

A column that changes its name​

Renames are where the two styles of INSERT come apart. Write your column names out and the script breaks the moment the rename lands, with a caret pointing at the word that no longer exists.

ERROR:  column "email" of relation "recipients" does not exist
LINE 1: insert into recipients (id, email) values (1, '[email protected]');
                                    ^

Leave the names out and nothing happens at all.

INSERT INTO recipients VALUES (2, '[email protected]');   -- succeeds

Renames invert the usual advice about writing SQL, which is worth sitting with. Naming your columns is better practice by every other measure available, and here it is the version that breaks, while the positional script survives only because it was never checking anything to begin with.

Positional inserts have a second habit in the same family. Give it fewer values than the table has columns and it fills the remainder from their defaults without comment.

CREATE TABLE t (id int primary key, name text, note text);
INSERT INTO t VALUES (2, 'b');   -- legal, note is left at its default

So the script that survives the most migrations turns out to be the one least able to tell you when it should not have.

A column that changes its type​

Widening a column costs nothing at all, since changing an int to a bigint underneath a hardcoded literal needs no edit, Postgres having widened the literal on the way in. What that operation costs in locks and rewrite time at real table sizes is a different subject, and not one a one-row test has anything useful to say about.

Enums behave less gently, because converting a free-text column fails if any existing row holds a value outside the new set, and the comparison is case sensitive.

INSERT INTO orders VALUES (1,'pending'), (2,'Pending');
CREATE TYPE order_status AS ENUM ('pending','shipped','cancelled');
ALTER TABLE orders ALTER COLUMN status TYPE order_status USING status::order_status;
ERROR:  invalid input value for enum order_status: "Pending"

Clean the data up and the conversion goes through, after which a seed script's plain string literals keep working, because an untyped literal resolves against the enum's labels. The moment one of those literals is a label nobody defined, it fails and says which one.

ERROR:  invalid input value for enum order_status: "archived"

Immediacy like that is most of the argument for using enums at all.

A constraint that arrives after the data​

A foreign key added over rows that already point nowhere fails and names the row responsible.

ERROR:  insert or update on table "child" violates foreign key constraint "child_t_fk"
DETAIL:  Key (t_id)=(999) is not present in table "t".

For the case where the history cannot be fixed yet, a two-step path exists. Adding the constraint NOT VALID succeeds and begins enforcing against new rows without checking the old ones, which moves the bill rather than paying it.

ALTER TABLE child ADD CONSTRAINT child_t_fk FOREIGN KEY (t_id) REFERENCES t(id) NOT VALID;  -- ALTER TABLE
ALTER TABLE child VALIDATE CONSTRAINT child_t_fk;
ERROR:  insert or update on table "child" violates foreign key constraint "child_t_fk"
DETAIL:  Key (t_id)=(999) is not present in table "t".

Later the same error turns up, sitting in whichever migration ran the VALIDATE. Teams reach for NOT VALID because it takes a weaker lock than validating up front, which is a real operational reason and a poor way of pretending the rows are fine.

One further failure needs no migration at all. Insert explicit ids and the sequence behind the column never hears about it, so the first insert that lets Postgres choose a value collides with a row your seed script already put there.

Four ways to seed, and what each does when the schema moves​

Below, four common approaches are sorted by one property alone, which is how each behaves once the schema moves underneath it.

Hand-rolled SQL​

Raw SQL survives more migrations than anything else here and reports fewer of them. Positional inserts sail through renames and added columns without comment, which reads as robustness from a distance and as blindness up close. Named-column inserts break loudly instead, which is what you want even on the mornings when it does not feel that way. Either way a human opens the file afterwards, and the file only ever covers the cases that human thought about.

ORM factories​

FactoryBot, Prisma's seed script and TypeORM seeders differ from raw SQL less in syntax than in where the failure lands. Regenerate the client or the model from the migrated schema and a renamed field or a new required field can surface as a type error at build time, well before anything touches a database. That is a real improvement and worth having.

Somebody still opens the factory afterwards and decides what the new column ought to contain, and that answer has to make sense for a domain the type system knows nothing about, since a required tier column typed as text is perfectly satisfied by the string "x".

Faker chains​

Faker holds no opinion about your schema, which leaves it nothing to warn you about when the schema changes. Every value comes out one column at a time with nothing carried between calls, and that leaves a rename, a new constraint and a new required column equally invisible to it. Whatever breaks is code you wrote around Faker, and it breaks on exactly the same terms as hand-rolled SQL.

Schema-aware generators​

Schema-aware generators read the database's current structure and produce rows from it, turning a schema change into an input rather than a surprise. Neosync works this way, and so does Seedfast, which I work on.

That trade is worth stating plainly. You stop maintaining a file that describes your data, and accept in exchange that a generator's idea of a sensible value comes from the schema plus whatever you tell it about your domain. A generator reads that tier is text and not null. Knowing that your business has exactly three tiers requires either the database to say so or you to say so.

Underneath all four sits one honest rule. The more of your intent lives in the schema as real constraints, the more any of these approaches can do for you.

What survives what​

• When a new mandatory column arrives with no default, the migration itself stops, so the whole team hears about it before seed data enters the picture.
• Give that same column a default and it becomes the expensive case, because nothing fails anywhere and named-column scripts and ORM factories simply stop filling it.
• A renamed column breaks named-column inserts and regenerated ORM clients loudly, while positional inserts carry on running and quietly stop being correct.
• Widening a type costs nothing at all, since hardcoded literals carry over untouched.
• Converting text to an enum fails on any out-of-set value, then fails again by name on any hardcoded literal outside the labels.
• Adding a foreign key over existing rows prints the offending key, and NOT VALID defers that failure rather than removing it.

When none of this is worth solving​

With ten tables, a migration every few months and one person who owns the seed file, write the SQL by hand and stop reading here. Every failure above is real at that size and cheap at that size, and a hand-written file somebody understands completely beats any amount of tooling.

The arithmetic changes when a schema moves faster than one person can track, when several teams migrate independently, or when a lower environment has to look convincing to somebody who is not a developer. Somewhere around there, the fact that the seed script still runs stops being evidence of very much.

Whichever way you go, most of the value sits in finding out quickly, because none of these failures is expensive on the morning it happens. They get expensive by sitting there, which makes this a property of your discovery process more than of your database.

Mikhail Shytsko builds Seedfast, a schema-aware test-data generator for Postgres, and writes about test environments, migrations and data integrity. Disclosure: the author works on Seedfast.