Loading YAML Seed Data Into PostgreSQL With Python

Seed data is the small set of rows an application needs before it can run: plans, roles, countries, a demo customer or two. Keeping it in a YAML file in the repository means it is reviewed with the code and loaded the same way in every environment. The load itself is a short script, and the details that make it trustworthy are the ones this post covers: how Python values become PostgreSQL types, how to make the load safe to run again, and what happens when a row is wrong. The wider picture of YAML next to databases is in using YAML with databases, and the reverse direction is exporting a table to YAML.

The data and the table

Two customers, with a date, a list, a nested mapping and a plan:

- id: 1
  email: [email protected]
  plan: pro
  joined: 2026-01-15
  tags: [early, beta]
  prefs: {theme: dark, digest: weekly}
- id: 2
  email: [email protected]
  plan: free
  joined: 2026-02-03
  tags: []
  prefs: {theme: light}

The table states the rules the seed must satisfy. Put the checks in the database, where they apply to every writer:

create table customers (
    id     int primary key,
    email  text not null unique,
    plan   text not null check (plan in ('free', 'pro')),
    joined date not null,
    tags   text[] not null,
    prefs  jsonb not null
);

The YAML parser knows nothing of these rules, so a typo such as plan: gold is valid YAML. The check constraint is what turns it into an error.

Python types become PostgreSQL types

yaml.safe_load returns a list of dictionaries with Python values: int, str, datetime.date, list and dict. psycopg adapts most of those to PostgreSQL types without help. The date becomes a date, and the list of strings becomes a text[] (an empty list too). The dictionary does not adapt, because it could be json, jsonb, an hstore or a composite. The first naive attempt failed with:

ProgrammingError cannot adapt type 'dict' using placeholder '%s' (format: AUTO)

Say what you mean by wrapping the value:

from psycopg.types.json import Jsonb

params = [{**r, "prefs": Jsonb(r["prefs"])} for r in rows]

Jsonb tells the driver to send the value as jsonb. Use Json for a json column; prefer jsonb for anything you will query. Dates deserve one more look. YAML 1.1 turns an unquoted 2026-01-15 into a date, and a quoted one into a string; PostgreSQL accepts both for a date column, so either works, but a string that is not a valid date fails at insert, not at load. If the YAML contains a timestamp without a time zone and the column is timestamptz, the server's session time zone fills the gap, which is a bug waiting for a daylight saving change. Write seed timestamps with an offset.

Make the load safe to run twice

A seed script runs in development, in CI and on deploy, so it will run on a database that already has the rows. Plain INSERT fails on the second run with a unique violation. The fix is an upsert keyed on the primary key:

sql = """insert into customers (id, email, plan, joined, tags, prefs)
         values (%(id)s, %(email)s, %(plan)s, %(joined)s, %(tags)s, %(prefs)s)
         on conflict (id) do update set email = excluded.email, plan = excluded.plan,
           joined = excluded.joined, tags = excluded.tags, prefs = excluded.prefs"""
with conn.transaction():
    conn.cursor().executemany(sql, params)

Running the load twice printed the same line both times:

run 1 rowcount 2 table rows 2
run 2 rowcount 2 table rows 2

Two rows affected, two rows in the table. The second run rewrote each row with identical values, which is the point: after an edit to the YAML, running the script brings the table in line with the file. Reading the rows back showed the types arriving as intended:

(1, '[email protected]', datetime.date(2026, 1, 15), ['early', 'beta'], {'theme': 'dark', 'digest': 'weekly'})
(2, '[email protected]', datetime.date(2026, 2, 3), [], {'theme': 'light'})

Know what an upsert does not do. It does not delete rows that you removed from the YAML. If the file is the full truth, delete the rows whose keys are not in it, in the same transaction (delete from customers where id <> all(%s)). If the table also holds rows created by users, do not delete, and keep seed rows distinguishable, for example with a reserved id range or a source column.

For the update list, excluded is the row you tried to insert. Listing every column is verbose and explicit; a generated list from the dictionary keys is shorter, and it must exclude the key column itself.

A bad row should fail the whole load

The transaction block matters. We added a row with plan: gold, inside conn.transaction():

CheckViolation new row for relation "customers" violates check constraint "customers_plan_check"
after failed seed, rows: 2

The load stopped with a clear error naming the table and the constraint, and the table still had its two earlier rows. Without the transaction block, a script that committed per row would leave a partial seed, which is the kind of state that takes an hour to understand. Load everything or nothing. The same property holds when the script fails for another reason, such as a network drop halfway.

Two cautions about the error. It names the constraint but not the row, so for a long file, catch the exception per row in a validation pass first, or validate the YAML with a schema before touching the database. Validating YAML configuration with Pydantic shows a model that rejects plan: gold with the row index.

Scale and speed

executemany with psycopg 3 uses pipeline mode and sends the statements efficiently, so a few thousand rows load in a moment. Past tens of thousands, use COPY, which streams rows and is the fastest path:

with cur.copy("copy customers (id, email, plan, joined, tags, prefs) from stdin") as copy:
    for r in rows:
        copy.write_row((r["id"], r["email"], r["plan"], r["joined"], r["tags"], Jsonb(r["prefs"])))

COPY does not support ON CONFLICT. The usual pattern loads into a temporary table with COPY and merges with one INSERT ... ON CONFLICT ... SELECT. At that size the data has outgrown YAML, which loads the whole file into memory and parses slowly; large YAML files and parsing speed has the numbers.

Where the script runs

Seeding belongs in the deployment path, after migrations and before the application starts. Two conventions help. Make the script read its connection from the environment, as in storing database connection config in YAML safely, so the same script serves every environment. And separate reference data from demo data: reference data (plans, roles) loads everywhere, and demo data loads only in development. Two YAML files and a flag are enough.

Tests can reuse the same seed files, loaded into a transaction that rolls back; that pattern is in fixtures in YAML for database tests.

When YAML is the wrong format for seed data

The data is large or tabular. Thousands of rows with the same columns are a CSV or a SQL dump, and COPY loads them faster than any YAML pipeline.

The data is relational. Rows that reference each other through generated ids need ordering and key lookups. YAML can name the references (a plan: pro that looks up the plan row), and the loader grows to handle them; at some point a framework seeder or a migration with SQL is simpler.

Non-developers edit the data. A spreadsheet or an admin screen suits them better. Convert on a schedule, as in converting an Excel sheet of settings into YAML.

Secrets or personal data are in it. Seed files live in the repository. Use synthetic demo data.

The driver behavior is documented in the psycopg 3 documentation, and the upsert syntax in the PostgreSQL INSERT reference. Tidy a seed file with the YAML formatter before committing it.