I’m happy to announce a project I’ve been working on for the last month: pql. pql is a pipelined query language inspired by the syntax of Microsoft’s KQL and Splunk’s Search Processing Language that I developed in collaboration with the fine folks at RunReveal. pql takes queries like:

logs
	| where eventName != ''
	| summarize AllEventNames=groupUniqArray(eventName) by sourceType

and translates them into SQL like:

WITH "__subquery0" AS (SELECT * FROM "logs" WHERE coalesce("eventName" <> '', FALSE))
SELECT "sourceType" AS "sourceType", groupUniqArray("eventName") AS "AllEventNames" FROM "__subquery0" GROUP BY "sourceType";

I personally find the pql query easier to read and modify, while still retaining the power of what a relational database has to offer. pql supports all the basics of filtering, sorting, and aggregating, as well as complex joins.

Under the hood, pql builds a full Abstract Syntax Tree (AST) of its input and transforms it into a sequence of chained subqueries (similar to Static Single-Assignment form). While the generated SQL is sometimes more verbose than hand-written SQL, the queries are straightforward for database query planners to optimize out. This means that the generated queries are just as efficient as the equivalent, more condensed queries.

pql can be used with nearly any SQL-based database. If you’re interested in learning more, check out pql.dev and the announcement on RunReveal’s blog. pql is packaged as a Go library and small CLI that’s available on GitHub under an Apache 2.0 license.

If you're looking for someone to help with a complex software problem like this for your business, I'm available for consulting and contract work! Read about how I can help you and contact me at consulting@zombiezen.com.