YAML to SQL Converter

Q: Are anchors and merge keys resolved before the INSERTs?

Yes. A fixture that merges a defaults block into six entries produces six complete rows. Without resolution you would get six partial rows and one row of defaults, which is not the data the fixture describes.

Q: Does a YAML null become NULL?

Yes, and an empty string stays an empty string. Those are different facts and keeping them apart is what a nullable column is for.

Q: How are nested mappings turned into columns?

With dot notation, so resources.cpu becomes one column. Those names are quoted in the output, which is why the dialect choice matters: unquoted, that name would be read as a table qualifier and the statement would fail.

Q: Which dialects are supported?

Postgres, MySQL, SQLite and SQL Server. The identifier quote, the boolean literal, the text type and the exact-number type all change with the choice, and any one of them being wrong fails the first statement.

Q: What if some entries have keys others do not?

The columns are the union of every key across every entry, in first-seen order, so nothing is dropped for being absent from the first one. Entries lacking a field get NULL in that column.

Q: Why is a boolean-looking column text?

Because at least one value in it is not a real YAML boolean, usually a quoted "true" or a Y. The column stays text so it is consistent, and quoting the rest in the source or casting after the load are both better than a half-typed column.

Load a fixture file into a real database. Anchors are resolved so every row is complete, nesting flattens into dotted columns, and the types come from reading whole columns.

To convert YAML to SQL, paste your file above and choose a dialect. Anchors and merge keys are resolved, nested mappings flatten into dotted columns, and you get a CREATE TABLE whose types are inferred from all the values in each column, followed by batched INSERT statements. A YAML null becomes a SQL NULL.

Want to clean the entries first? Open the app

Fixtures that need to become tables

Test fixtures live in YAML because they are edited by hand and reviewed in pull requests. At some point somebody needs the same rows in an actual database: to reproduce a bug against real query plans, to seed a demo environment, or to check that the fixture and the production data agree.

The other case is reference data that was never in a database in the first place. A list of plan tiers or feature limits maintained in a config file, which a new reporting query now needs to join against.

Both want a CREATE TABLE that is correct rather than everything as TEXT, because a fixture loaded as strings makes every comparison in the reproduction a cast.

Worked example, in SQLite

A fixture with an anchor and a null:

- service: billing-api
  replicas: 6
  autoscale: true
  cost_centre: "01730"
  owner: null
- service: web-frontend
  replicas: 4
  autoscale: false
  cost_centre: "02139"
  owner: platform

And the script:

CREATE TABLE "services" (
  "service" TEXT,
  "replicas" NUMERIC,
  "autoscale" BOOLEAN,
  "cost_centre" TEXT,
  "owner" TEXT
);

INSERT INTO "services" ("service", "replicas", "autoscale", "cost_centre", "owner") VALUES
  ('billing-api', 6, TRUE, '01730', NULL),
  ('web-frontend', 4, FALSE, '02139', 'platform');

The YAML null became a SQL NULL, not an empty string. autoscale is a real BOOLEAN because the source held real booleans. cost_centre is text because it was quoted in the YAML and therefore arrived as a string with its zero intact.

The YAML shapes it reads

A sequence of mappings is the ordinary case: one entry, one row.
Several documents separated by --- become one row each when they are all mappings, which is the Kubernetes manifest shape and the useful reading of it.
A single mapping becomes a one-row table.
Nested mappings flatten into dotted columns, so resources.limits.cpu is one column.
A sequence of scalars becomes a single column called value, rather than an error.
Anchors, aliases and merge keys are resolved by the parser before the table is built, so a manifest that reuses a block through <<: *defaults comes out with the merged values in place.

Types, and where they come from

Numeric when every value in the column is a real YAML number. One quoted value in the column makes the whole column text, which is what keeps a padded code joinable.
Integer versus decimal follows the values, so a column with no fractional part gets the exact type and money keeps its precision.
Boolean when every value is a real YAML boolean. Note that this means an unquoted no in the source was already false before this saw it.
Dates stay TEXT in the CREATE TABLE. A DATE column that rejects one row fails the whole load, and changing the word is a one-second edit if you know the data.
No VARCHAR sized from your rows. A width fitted to a fixture is a trap that springs when the real data arrives.

Nesting, lists and the row count

Nested mappings flatten into dotted columns, and those names are quoted in the output, which is why picking the right dialect matters: unquoted, resources.cpu would be read as a table qualifier and the statement would fail.

A list inside an entry is written as text into a text column rather than being spread into extra rows. That keeps the row count equal to the entry count, and most databases can query JSON or delimited text in a column when you need to go further.

Columns are the union of every key across every entry, so a field that only some entries carry still gets a column with NULLs elsewhere. Nothing is dropped because the first entry did not have it.

Frequently Asked Questions

Are anchors and merge keys resolved before the INSERTs?

Yes. A fixture that merges a defaults block into six entries produces six complete rows. Without resolution you would get six partial rows and one row of defaults, which is not the data the fixture describes.

Does a YAML null become NULL?