SQL for anything

If you stick metadata on things they become a database and can thus be queried and mutated with SQL.

I had this realization when building the fm tool to migrate my Obsidian notes' outdated frontmatter and was grappling with its syntax. Turns out this idea holds for many other kinds of documents as well, like images with EXIF data, audio files with ID3 tags, Jira cards with fields and labels on them. Resources act as rows (local files or remote objects) and fields act as columns.

SQL is the natural choice for batch editing shallow data, which metadata typically is, because it is both powerful and widely known. I picked BigQuery as reference dialect, since it is nice and clean and I know it well, but obviously only implementing a relevant subset of it. For added convenience I also bolted on shorthand type casting and set operations commonly used on frontmatter.

On the flipside, deep and nested data are awkward to handle with SQL, but rarely occur in interfaces designed for humans (Markdown, Excel, Trello cards, etc.), so I'd be comfortable omitting such operations and instead handing off to yq or dasel.

In our current era of rapid AI adoption, shallow and human-friendly interfaces are the natural contact point in human + machine collaboration. fm may only operate on Markdown so far, but the code is rigged for extension and I'm excited to explore its utility on other formats as well.

Building fm with Claude Code was a somewhat different experience than hacking together a web app. The requirement on correctness is drastically higher since I hope that many people will use the software to manage their precious Obsidian Vaults. The code is also on public display on GitHub so I had to work hands-on with it quite a bit to ensure decent structure and remove slop.

Opus quickly got a working prototype in place, only supporting a few predefined query patterns. Widening the scope to a reasonably complete SQL dialect expressed in EBNF-like syntax took a lot of reasoning and up-front documentation before plunging into plans, testing strategy, and implementation. My previous experience and opinions on agentic coding best practice was confirmed yet again:

1. Think first and discuss with agent. Sketch out the target architecture and user interface in as great detail as you can.
2. Iterate implementation plan together with agent (don't blindly outsource!).
3. Generate the core data structures and interfaces then review manually.
4. Generate tests and review manually. Claude was way off in some areas forcing me to craft several tests by hand.
5. Generate implementation.
6. Acceptance test manually.

I don't try to eliminate the human-in-the-loop because I find that it would require too much effort in crafting instructions and tests and, more importantly, lose the creative process. If I want to make something generic or replicate someone else's software then it should be possible with enough token spend, but when making something new that I will put my name on and support going forward, then I need to explore the problem, learn and internalize the solution as the work unfolds.

Interestingly, despite defining the syntax tree data types up front, Sonnet failed miserably at implementing parsing on its own in one go. I solved it by ranking the different methods to-be-implemented by complexity and generating them iteratively from simplest to most complex. A human developer would have done such ranking intuitively from understanding the problem, but models of modest complexity do not.