Using YAML With Databases for Seeds Exports and Queries

YAML and databases look like opposites. One is a text format people write by hand, with comments and indentation. The other is a typed store that enforces rules and answers questions at scale. They meet more often than you would expect: reference data kept in the repository, a table exported for a bug report, configuration stored in a column, fixtures for tests, a folder of manifests that someone wants to query. This guide covers each of those jobs, with code we ran against PostgreSQL 18, SQLite and DuckDB, the failures that happen at the boundary between a text format and a typed one, and the cases where the two should not be combined. For the language, begin with YAML syntax you need to stop guessing.

Five jobs, five approaches

The word "YAML" in a database context hides several different tasks. Name the job first, because the right tool and the risks differ.

A sixth task, turning a nested document into rows of path and value so that two configs can be compared with a join, is covered in flattening nested YAML into rows. The migration in the other direction, from a folder of YAML data files into a real table, is migrating YAML data files to a database.

What the five have in common is a boundary. On one side is a text parser that decides types by pattern. On the other is a database that has types by declaration. Every bug in this guide is a disagreement across that boundary, and every fix is the same move: state the type explicitly on one side so that it cannot be guessed.

Types across the boundary

The table below maps what a value becomes on its way from YAML to PostgreSQL through PyYAML and psycopg, based on the runs that follow.

Two rows deserve emphasis. The zip code row is the classic: 02134 without quotes is read as an octal number and becomes 1116, and nothing in PostgreSQL can recover it. The mapping row is the most common error message in practice. A psycopg insert of a dictionary raised ProgrammingError cannot adapt type 'dict' using placeholder '%s' (format: AUTO), because a dict could be json, jsonb, hstore or a composite type. Say which: wrap it in Jsonb(...).

In the other direction, yaml.safe_dump is just as strict. Dumping rows fetched with a dictionary row factory raised RepresenterError ('cannot represent an object', UUID('9f1c2f7e-...')), and a Decimal fails the same way. Strictness is a feature: the alternative, the unsafe dumper, writes a Python-specific tag that no other tool reads. You teach SafeDumper what to write, one representer per type.

Seeding: idempotent, transactional, constrained

A seed file is the data an application needs before it can run. The script that loads it is short, and three properties make it trustworthy.

It is safe to run twice. INSERT ... ON CONFLICT (id) DO UPDATE ran twice with two rows affected each time and left two rows. After you edit the YAML, rerunning the script brings the table in line. An upsert does not delete rows you removed from the file; decide whether the file is the whole truth, and if so delete the rest in the same transaction.

It is all or nothing. A row with plan: gold raised CheckViolation new row for relation "customers" violates check constraint "customers_plan_check", and because the load ran inside conn.transaction(), the table kept its two earlier rows. A script that commits row by row leaves a partial seed.

The database is the validator. YAML knows nothing about your allowed plans. A check constraint, a not null and a unique index in the table definition apply to every writer, including this script. Put the rules there, and use a Pydantic model or JSON Schema in the script only to produce better error messages before the database sees the data (validating YAML configuration with Pydantic).

The full script is in loading YAML seed data into PostgreSQL, including how a timestamp without an offset picks up the session time zone, which is a bug that waits for a daylight saving change.

Scale: where parsing becomes the bottleneck

Teams reach for YAML for data that starts small and grows. We generated a 2.2 MB file with 20,000 customer records and timed each stage on the same machine:

file bytes: 2257780
parse pure python: 6.31s, libyaml C loader: 1.17s, rows 20000
executemany: 0.35s, copy: 0.11s, rows 20000

The database is the fast part. Inserting 20,000 rows with executemany took 0.35 seconds, and COPY took 0.11. Parsing the YAML in pure Python took 6.31 seconds, about eighteen times the insert. Using PyYAML's libyaml-backed loader (yaml.load(text, Loader=yaml.CSafeLoader)) cut that to 1.17 seconds, and DuckDB's read_yaml read the same file and counted the rows in about 1.9 seconds of wall time, including starting the process. So the rule: for a seed file of a few hundred rows, nothing here matters. At tens of thousands, the parser is the cost, and the real fix is a different format. CSV or Parquet loads through COPY at the database's speed. Measurements of parse speed for the loaders are in large YAML files and parsing speed.

CSafeLoader is available when PyYAML was built against libyaml, which is true of most wheels; check yaml.__with_libyaml__. It is stricter in a few corner cases than the pure loader, so run your own files through both before switching.

Exporting: choose a text form per type

The reverse job, writing rows as YAML, is mostly a series of decisions about types. Exporting a table to YAML used a table with a uuid, a numeric(10,2), a timestamptz, a two-line text and a jsonb column. The choices that held up:

• Decimals as strings. '19.90' keeps its scale in any loader, while an unquoted 19.90 loads as the float 19.9.

• UUIDs as strings. There is no YAML type for them.

• Timestamps as ISO strings with an offset. PyYAML can write an aware datetime natively and read it back, but a loader that does not resolve timestamps returns text, so the string is the portable form.

• Multi-line text as a literal block. A small representer wrote note: |- followed by two lines, which people can read and edit. The default was a single-quoted scalar with a blank line standing for the newline.

• **jsonb as nested YAML**, which converts without loss.

A shell alternative is psql -At -c "select jsonb_agg(to_jsonb(o) order by id) from orders o" | yq -P, which produced block-style YAML with literal scalars in one line. It reorders keys, because jsonb orders them by length and then alphabetically, and it writes 19.90 as a bare number, so cast to text when exactness matters.

Know what an export is not. It is a snapshot of values, and it omits indexes, constraints, sequences, ownership and grants. It is not a backup, and pg_dump is.

Documents in a column: parse on write

The tempting design is a text column holding YAML, because the application already speaks YAML. We stored three configurations that meant the same server and compared them in a text column and a jsonb column:

same text: [(3,)]
distinct jsonb: [(2,)]
text LIKE: [(1,), (2,)]
jsonb port=5432: [(1,), (2,), (3,)]
jsonb types: [(1, 'number'), (2, 'number'), (3, 'string')]
garbage accepted as text; rows: 4
rows that do not parse: [(4, 'ParserError')]

Reading down: as text, the three documents are three distinct values, while as jsonb two of them are equal, because comments, flow style and key order are formatting. A LIKE '%port: 5432%' found two of the three and missed the document that wrote the port as a quoted string. A path query on jsonb found all three and jsonb_typeof exposed which stored a string. And the text column accepted server: [unclosed without complaint, which only a later parse revealed.

The design that avoids all of it: parse at the application boundary, reject invalid input with an error a user can read, and store jsonb. If comments must survive, keep the original text in a second column and write both in one transaction. The details, including converting an existing column, are in storing YAML text in a database column and its pitfalls. Indexing the result is the subject of indexing JSONB with GIN.

Fixtures: small files, one transaction per test

For tests, the pattern is a YAML file per scenario, a loader, and a fixture that rolls back. With pytest:

...                                                                      [100%]
3 passed in 0.13s

The fixture creates a temporary table, yields the connection and rolls back afterward, so nothing is committed and every test starts empty. One of the three tests loads nothing and asserts that the table is empty, which is the test that proves the isolation. Rollback is nearly free, so suites with hundreds of database tests stay fast. Two rules keep the fixtures useful: each test loads the smallest file that makes its assertion meaningful, and the constraint tests insert bad rows and expect errors, since fixtures built from valid rows say nothing about the rules. A CheckViolation test passed once the constraint was part of the temporary table. The walkthrough is fixtures in YAML for database tests; frameworks that ship their own fixture format (Rails, Django) handle foreign keys and ordering and are the better choice inside an ORM.

Querying files in place

Sometimes the YAML never goes into a database at all, and you want to ask it questions. Two approaches worked.

SQLite with JSON. SQLite has no YAML reader, so parse in Python and store each record as JSON text with a few identifying fields as real columns. A check (json_valid(doc)) rejected '{oops' with CHECK constraint failed. json_extract(doc, '$.limits.cpu') returned 2 for one service and None for the one without a memory limit, and json_each(doc, '$.tags') unnested the tags into rows. The trap is that a misspelled path returns NULL silently. See reading YAML into SQLite for quick queries.

DuckDB with the YAML extension. After INSTALL yaml FROM community; LOAD yaml;, read_yaml('svc.yaml') returned one row per service with inferred types: port as SMALLINT and limits as STRUCT(cpu TINYINT, memory SMALLINT), so limits.cpu worked in SQL. A glob across two files with different keys returned the union of columns with NULL for missing ones. A column mixing "3.10", 3.10 and no stayed type yaml with each value as written, avoiding the 1.1 conversions. A broken file raised an IO Error from yaml-cpp with the line and column. The extension is third-party code, so pin versions. See using DuckDB to query data stored as YAML.

For comparisons across files, flatten each document into path, type and value rows and use SQL. A 12-line generator produced service.ports[1] int 8443-style rows; a set difference on paths found a key present in production and absent in staging, and a join on path found replicas set to 3 and 1. That technique is the basis of the analysis cluster, starting with analyzing configuration and metadata held in YAML.

Seeds that reference each other

Reference data rarely stands alone. Plans have customers, countries have cities, roles have permissions. The question is how a YAML file expresses a relationship when the target row's id is generated by the database. Writing the numeric id in the YAML works until someone adds a row in the middle, and then every reference is wrong. The pattern that holds up is to reference the natural key, a code or a slug that is stable and unique, and let SQL resolve it.

We ran this with two tables, plans (an identity id and a unique code) and customers (an email and a plan_id that references plans). The customer file says plan: pro, not plan_id: 2, and the insert looks the id up:

insert into customers (email, plan_id)
values (%(email)s, (select id from plans where code = %(plan)s))
on conflict (email) do update set plan_id = excluded.plan_id

Three behaviors came out of the run. Inserting a customer before any plan existed failed with insert or update on table "customers" violates foreign key constraint "customers_plan_id_fkey", so load order matters: parents first, children after, and the script should load files in a fixed order, with a list of tables rather than a directory listing. A customer with plan: gold made the subquery return no row, and the insert failed with null value in column "plan_id" of relation "customers" violates not-null constraint. The message names the column and not the code that caused it, so the script wrapped each row in a savepoint and printed the offending value itself. And after the valid rows loaded, a join returned ('[email protected]', 'pro') and ('[email protected]', 'free'), which is the check to run after any seed.

The lesson generalizes. Use natural keys in YAML, resolve them in SQL, and treat a lookup that returns nothing as an error the script reports with the key. For many-to-many relations, a list of codes under each parent (roles: [admin, editor]) is easy to read, and the loader expands it into rows of the join table inside the same transaction. Deleting is harder: removing a code from the file means removing the row, and every row that references it must go or move first. Decide whether the seed is allowed to delete, and if so, run it in a transaction with a dry-run mode that prints what it would remove.

Reading the error messages

The failures in this guide have recognizable messages, and knowing them saves time. This table lists what we saw and what it points to.

Read the table as a map from the layer where the error appears to the layer where the fix belongs. A CheckViolation appears at the database and is fixed in the file. A RepresenterError appears in your script and is fixed in the script. A ParserError that appears months later means the validation was missing at the write.

Operating the load

A seed script is code that runs in your deployment, and a few operational details matter more than the SQL.

Order in the pipeline. Migrations first, then seeds, then the application. Seeds depend on the schema, and the application depends on the seeds. Run seeds as a separate step with its own log line, so a failure there does not look like a failure of the migration.

Environments. Reference data (plans, roles, countries) loads everywhere. Demo data (sample customers) loads in development only. Two folders and a flag are enough, and the production deploy never has the flag. A third category, per-environment settings that really differ, belongs in configuration and not in seed files; see environment specific YAML config without copy paste.

Permissions. The seed script needs insert and update rights on specific tables and nothing else. Give it a role of its own instead of the migration owner, so a bug in a seed file cannot drop a table. A transaction does not protect against truncate in a script someone adds later.

Connections. The script reads its connection settings from the environment or a file that names the environment variable holding the password, as in storing database connection config in YAML safely. Never put a password in the seed YAML, and never print a connection string in a log.

Logging. Print the number of rows inserted, updated and unchanged for each file. A seed that says "2 inserted, 0 updated, 5 unchanged" tells the on-call engineer what happened. INSERT ... ON CONFLICT reports affected rows without saying which were inserts and which were updates, so select the existing keys first and compare when the split matters.

Review. Seed files change production behavior as much as code. They go through the same pull request process, and a CI job loads them into a scratch database and runs the constraint checks, so a typo fails the build and not the deploy.

A worked path: from a settings file to a table

Suppose an application keeps its feature settings in settings.yaml, and operations now want to query and audit them. A sensible migration has five steps, each of which we exercised above in some form.

1. Validate the file. Run yamllint and a schema check, so the data is well-formed before it moves. Linting YAML with yamllint covers the first and validating YAML with JSON Schema the second.

2. Design the table. Put the fields you filter on in real columns (key, scope, enabled) and the rest in a jsonb column. Add constraints for every rule you know.

3. Load with an upsert in a transaction, keyed on the natural key, with the file's path stored in a source column. Report counts.

4. Switch readers. The application reads from the table, and the file stays as the reviewed source that seeds it. Or the table becomes the source and the file becomes an export. Choose one; do not edit both.

5. Add the drift check. A CI job exports the table and diffs it against the committed file, failing when they differ. This catches the manual edit someone makes in production.

The fourth step is the hard decision. Treat the table as a cache of the file, and you keep code review, history and rollbacks in git, at the cost of a deploy for every change. Treat the file as an export of the table, and changes are immediate, at the cost of building your own audit trail. For small, rarely changed reference data, the first is usually right.

What a query tool changes

Once data can be queried, the tooling changes what questions are cheap. Before DuckDB read YAML, "how many services in these sixty files set a CPU limit" meant a script. With read_yaml('svcs/*.yaml') it is a count(limits.cpu). Before SQLite's JSON functions, "which documents list the tag web" was a loop; now it is an exists over json_each. The effect is that you ask more questions, and some of those questions turn out to be recurring, which is the signal to move the data into a real table. Use the query tools as a way to learn what the table should look like.

The same applies to quality. A SQL query for keys that appear in production and not in staging, or for values stored as strings where the rest are numbers, finds errors that a linter cannot, because they are valid YAML. The rest of this topic builds on that: analyzing configuration and metadata held in YAML and finding inconsistent versions across YAML configs use the queries from this guide at the scale of a repository.

Choosing between the approaches

Which database for which YAML job

The three databases in this guide are not interchangeable, and picking by job avoids a migration later.

PostgreSQL is the home for data that outlives a script: seeds, stored documents and anything several services read. It has jsonb with indexes, constraints, transactions that cover DDL, and roles. Use it when the YAML data becomes part of the product.

SQLite is the scratch pad that needs no server. A script builds an in-memory or file database from a folder of YAML, asks five questions with the JSON functions, and throws it away or hands the file to a colleague. Its constraints still apply, so json_valid catches a bad document on the way in. Its limits are concurrency and the smaller function set; Python's module in our container linked SQLite 3.40.1, while the command-line shell was 3.54.1, a reminder to check sqlite3.sqlite_version before using a newer function.

DuckDB is the analysis tool. With the community extension it reads YAML as tables, infers types and handles globs, and it is columnar, so aggregates over many records are quick. Treat it as a reader and not a system of record: nothing in this guide used it to hold data that other programs write to.

A fourth option sits outside the three. For one file and one question, yq needs no database at all, and for config comparison between two files a text diff after normalizing may be enough. Reach for a database when the question repeats, when the data spans many files, or when the answer must join against something else.

A checklist for the boundary

Run through this when a script moves data between YAML and a database.

1. **Use safe_load.** Anything else can build arbitrary objects from a tag.

2. Quote identifiers in the YAML. Zip codes, versions, codes and anything with a leading zero.

3. State types at the boundary. Jsonb(...) on the way in, representers on the way out.

4. Keep decimals exact. Strings or Decimal, never floats.

5. Put rules in the database. Constraints and unique indexes, so every writer meets them.

6. Load in a transaction. All or nothing.

7. Make loads idempotent. Upsert on a natural key.

8. Name time zones. Timestamps with an offset.

9. Test with a hostile row. A bad plan, a string no, an empty list, an emoji, a very long line.

10. Measure before you optimize. Parsing, not the database, was the cost at 20,000 rows.

When YAML and a database should not be combined

The data is large and tabular. A 2.2 MB YAML file held 20,000 small records and took 6.31 seconds to parse with the default loader. Millions of rows should never be YAML.

Many writers update the same data. A YAML file in a repository is edited through pull requests. Live, concurrent writes belong in the database, with the file demoted to an export.

The structure is irregular and deep. A schema-less tree stored as jsonb is queryable, but a model that fits relational tables is better when the shape is regular.

Secrets are in play. A file in a repository is the wrong home for credentials, and so is a text column that anyone with select can read.

You need transactional history. A repository gives you file history, and a database with audit tables gives you row history. Pick the one that matches the question you will be asked.

References: the psycopg adaptation documentation, the PostgreSQL JSON types, the SQLite JSON functions, the DuckDB community extensions catalog and the PyYAML documentation. Format a file for reading with the YAML formatter.