Generating Table Schemas From YAML Definitions Safely

A table definition in YAML is attractive for a plain reason: it is shorter than the SQL it produces and easier to put through a review. The same file can feed documentation, a validator and the create table statement, so the three never disagree. The risk is in the generator. A string that glues names and types together works for customers and email, and fails the first time a table is called order. This post builds a small generator, shows the failure and the fix, and runs the result on three engines. It belongs to the cluster on YAML in data engineering workflows. If you meant to load rows and not define tables, see loading YAML seed data into PostgreSQL.

The definition

One table, four columns, each exercising something a generator must handle:

table: order
columns:
  - {name: id, type: integer, primary_key: true}
  - {name: status, type: text, nullable: false, default: "'new'"}
  - {name: amount, type: "numeric(10,2)", nullable: false}
  - {name: group, type: text}

The names are deliberate. order and group are both reserved words in SQL, and the realistic way to meet them is a domain that has orders and customer groups. Two details in the YAML matter before any SQL is generated. The default is "'new'", a string that contains its own single quotes, because the generator pastes it into the statement as an expression; writing default: 'new' would hand the generator the bare word new, and the YAML parser would have eaten the quotes (YAML strings and quoting). And numeric(10,2) is quoted because the comma inside a flow mapping would otherwise split it, which turned decimal(10,2) into decimal(10 when I forgot the quotes in a related test. A type with a comma, in braces, always needs quotes.

The generator

MAP = {"integer": "integer", "text": "text"}

def ident(n):
    return '"' + n.replace('"', '""') + '"'

def ddl(spec):
    cols = []
    for c in spec["columns"]:
        parts = [ident(c["name"]), MAP.get(c["type"], c["type"])]
        if c.get("primary_key"):
            parts.append("primary key")
        elif c.get("nullable") is False:
            parts.append("not null")
        if "default" in c:
            parts.append("default " + c["default"])
        cols.append(" ".join(parts))
    return f"create table {ident(spec['table'])} (\n  " + ",\n  ".join(cols) + "\n)"

Three small choices carry the safety. ident() wraps a name in double quotes and doubles any double quote inside it, the standard SQL rule for delimited identifiers, so a name can contain a space, a keyword or a quote and still be one identifier. The MAP translates your vocabulary into engine types and passes unknown types through, which keeps numeric(10,2) working and makes adding a mapping a one-line change. And nullable is read with is False, not by truthiness, so a missing key means nullable and only an explicit false adds the constraint. A primary key already implies not null, so the elif avoids writing both.

The output for the definition above:

create table "order" (
  "id" integer primary key,
  "status" text not null default 'new',
  "amount" numeric(10,2) not null,
  "group" text
)

The failure it prevents

The naive version joins the names without quoting:

create table order (id integer, status text, amount numeric(10,2), group text)

PostgreSQL rejects it:

naive on PostgreSQL: SyntaxError syntax error at or near "order"

The error points at the first reserved word, and fixing that one reveals group next. The deeper problem is when it appears. A generator tested on customers and email passes every test, ships, and breaks on the day someone adds a table that happens to be a keyword. Quoting everything, always, removes that class of failure, because a quoted identifier is never parsed as a keyword. The cost is that the names become case-sensitive in PostgreSQL: "Order" and order are different. Generating names in lowercase and quoting them consistently avoids the surprise, and so does validating names against a pattern before they reach the generator.

Three engines, one statement

The statement ran unchanged on all three:

generated DDL ran on PostgreSQL
[('id', 'integer', 'NO'), ('status', 'text', 'NO'), ('amount', 'numeric', 'NO'), ('group', 'text', 'YES')]
generated DDL ran on SQLite: ['id', 'status', 'amount', 'group']
generated DDL ran on DuckDB: ['id', 'status', 'amount', 'group']

PostgreSQL's information_schema confirms the intent: id, status and amount are not nullable, and group is. That the same text runs on SQLite and DuckDB is a convenience of keeping to the common subset, double-quoted identifiers, plain types and not null. It is not a promise. SQLite treats types loosely, so numeric(10,2) is accepted and does not enforce the precision. MySQL quotes identifiers with backticks unless ANSI_QUOTES is on, so a generator for MySQL needs its own ident(). If you target several engines, make the quoting function and the type map part of the engine, not of the file.

Beyond the single table

A flat column list is the easy case. The next features each add something the generator must decide:

• Foreign keys. A references: customers.id key on a column becomes a references "customers" ("id") clause. Generate the tables in dependency order, or emit the constraints in a second pass with alter table, so a reference never points at a table that does not exist yet. Loading YAML seed data into PostgreSQL has the same ordering problem on the data side.

• Indexes. A top-level indexes: list becomes separate create index statements, and the generator should name them deterministically so a rerun is recognizable.

• Defaults that are functions. default: now() is an expression and default: 'new' is a string, and the generator cannot tell which from the YAML alone. Keep defaults as written SQL, as here, and review them as SQL.

• Idempotence. create table if not exists is safe to rerun and silently ignores a changed definition. The plain form fails loudly on a second run, which is often what you want from a script that should only run once. Neither updates an existing table. Changing a table is a migration, covered in writing Flyway and Liquibase settings in YAML and in the migrations cluster.

Keep the YAML honest

Validate the file before generating. A JSON Schema that requires table, a non-empty columns list and a name and type on every column turns a malformed definition into a clear message instead of a half-built statement (validating YAML with JSON Schema). Reject duplicate column names, which a mapping would hide but a list does not. And print the generated SQL in review: a pull request that changes schema.yaml should show the diff of the SQL it produces, since that is the thing that runs (reviewing YAML config changes in pull requests).

When generating schemas from YAML is the wrong approach

You already have migrations. Two sources of truth for a schema, a YAML file and a migration directory, drift apart. Pick one. If migrations are authoritative, generate documentation from the database and not the other way round.

You need engine features. Partitioning, generated columns, check constraints with subqueries, exclusion constraints and storage parameters are specific to engines. Encoding them in a neutral YAML vocabulary produces either a leaky abstraction or a second SQL dialect in disguise.

The team knows SQL. A create table statement is not hard to read. A generator earns its place when the definition feeds several outputs, or when many similar tables share one template. For three tables, write the SQL.

Names come from users. Quoting makes a hostile name safe as an identifier, and it does not make it acceptable. Restrict names to a pattern, and read generating SQL from YAML templates safely before accepting a definition from anyone outside the team.

The identifier rules are in the PostgreSQL lexical structure documentation, and the list of reserved words in its key words appendix. Check the file in the YAML formatter.