Reading YAML Into SQLite for Quick Queries With json_extract

Sometimes the question is small and the data is YAML: which services have a CPU limit, how many tags does each have, which of these sixty files mention port 8080. You could write a loop in Python. A few lines of SQL are easier to change, and SQLite is already there. It has no YAML reader, but it has solid JSON functions, and YAML converts to JSON with one call. This post builds the pipeline, runs it and says where it stops being the right tool. It belongs to the cluster on using YAML with databases. The same idea in DuckDB, which has a YAML extension, is using DuckDB to query data stored as YAML.

The data

Two services, one with nested limits and two tags, one with fewer fields:

- name: api
  port: 8080
  tags: [web, public]
  limits: {cpu: 2, memory: 512}
- name: worker
  port: 9000
  tags: [queue]
  limits: {cpu: 1}

Load it

Each record becomes a row with its identifying fields as columns and the whole record as JSON text:

import json
import sqlite3
import yaml

services = yaml.safe_load(open("svc.yaml"))
db = sqlite3.connect(":memory:")
db.execute("create table svc (name text primary key, port integer, doc text check (json_valid(doc)))")
db.executemany("insert into svc values (?,?,?)",
               [(s["name"], s["port"], json.dumps(s)) for s in services])

Three things in that code are deliberate.

**json.dumps is the bridge.** YAML and JSON share a data model for these types, so a loaded YAML structure serializes straight to JSON text. If the YAML holds dates, sets or other types JSON lacks, json.dumps raises, and the fixes are in converting YAML to JSON in Python.

**name and port are real columns.** They are what you will filter, sort and join on. A real column can carry a primary key, a unique constraint and an index; a value inside JSON can only be indexed through an expression index. The rest of the record stays in doc, which keeps the table stable when new settings appear in the YAML.

**check (json_valid(doc))** makes the database refuse anything that is not valid JSON. Inserting '{oops':

IntegrityError: CHECK constraint failed: json_valid(doc)

The check cannot make the JSON mean the right thing. It is a floor, and because it is in the table, every writer meets it, not only this script.

Query it

json_extract(doc, path) pulls a value out by a JSON path. Combined with real columns:

select name,
       json_extract(doc, '$.limits.cpu')    as cpu,
       json_extract(doc, '$.limits.memory') as mem,
       json_array_length(doc, '$.tags')     as ntags
from svc order by port;

('api', 2, 512, 2)
('worker', 1, None, 1)

The worker has no memory in its YAML, and json_extract returned NULL (Python's None) instead of raising. That is convenient and also the source of a classic silent error: a misspelled path such as $.limts.cpu returns NULL for every row and nothing complains. When a result is all NULL, check the path first.

json_each is the other function to learn. It turns an array (or an object) into rows, one per element:

select s.name, t.value
from svc s, json_each(s.doc, '$.tags') t
order by 1, 2;

[('api', 'public'), ('api', 'web'), ('worker', 'queue')]

The array became three rows, so tags can be counted, grouped, joined to another table, or tested with exists:

select name from svc
where exists (select 1 from json_each(doc, '$.tags') where value = 'web');

[('api',)]

That is the full toolkit for most questions: json_extract for a scalar at a path, json_each to unnest, and aggregate functions as usual. SQLite 3.38 and later also supports the -> and ->> operators for the same job, which read more like the PostgreSQL syntax; check your library's SQLite version before relying on them.

Many files in one table

The same table holds records from many files. Add a column for the source path and loop:

import pathlib

for path in pathlib.Path("services").glob("**/*.yaml"):
    doc = yaml.safe_load(path.read_text())
    db.execute("insert into svc values (?,?,?,?)", (str(path), doc["name"], doc["port"], json.dumps(doc)))

A query across sixty files then runs in one statement, and group by over json_extract(doc, '$.image') answers "which images do we use and how many times" without writing the counting code. This is the technique behind finding inconsistent versions across YAML configs and counting services across many Compose files. A file with several documents needs safe_load_all and a row per document; give each a sequence number.

Types and gotchas

JSON in SQLite has fewer types than YAML, and the loader's choices carry through.

• Booleans become JSON true and false, which json_extract returns as the integers 1 and 0. A filter = 'true' matches nothing; use = 1.

• Dates cannot be JSON, so they were strings by the time they got into the table (if default=str converted them). SQLite's date functions then work on those strings in ISO format.

• Large integers and floats follow SQLite's rules (64-bit integers, doubles), which cover most settings.

• **The version 3.10** is a float to the YAML loader and 3.1 in the table. Quote it in the YAML if it is a string, as in YAML version numbers turning into floats.

• Key order is not meaningful in json_extract; a path picks by name.

Indexing what you filter on

With a few records nothing needs an index, and with a few thousand rows the habit of putting identifying fields in columns pays. If you filter on a value inside the JSON often, SQLite can index the expression itself:

create index svc_cpu on svc (json_extract(doc, '$.limits.cpu'));

The planner uses the index when a query repeats the same expression, json_extract(doc, '$.limits.cpu'), exactly. A query that writes the path differently misses it, so wrap the expression in a view and filter through the view. Check with explain query plan that the index is used; that habit is the same as in any other database, and for larger JSON workloads the comparison with PostgreSQL's jsonb is in indexing JSONB with GIN. For the files you will search once, skip the index: building it costs more than the scan it saves.

Persisting the database

":memory:" is for a one-off question. For repeated use, write to a file (sqlite3.connect("configs.db")), rebuild it from the YAML on demand and never edit it by hand. The database is a cache of the files: delete it and run the loader. For a shared analysis, hand over the .db file, and everyone sees the same rows without parsing anything. SQLite's JSON functions documentation lists every function, and Python's sqlite3 module the connection API.

When SQLite is more than a YAML question needs

It is a single file you can read. Open it and look. For a ten-line file the quickest answer is yq '.[] | select(.port > 8000)' svc.yaml, which querying YAML with yq covers, with no database.

The data is nested deeply and irregular. Paths into varying structure get fiddly. A tool that understands the structure, such as yq or DuckDB's read_yaml, is closer to the shape.

You need a long-lived store with many writers. SQLite is a good file-based database and not a server. Use PostgreSQL, with jsonb, when several processes write.

You will run the question once. Writing the loader costs more than a Python loop. The SQL pays when you will ask five different questions of the same data.

Try the paths in your own data with the JSON path tool before writing the SQL, and format a YAML file for reading with the YAML formatter.