Pql, a pipelined query language that compiles to SQL
pql.dev
pql.dev
Question: it looks like you wrote the parser by hand. How did you decide that that was the right approach? I myself am new to parsers and am working on implementing the PostgREST syntax in go using PEG to translate to Clickhouse, which is to say, a similar mission as this project. Would love to learn how you approached this problem!
Turned out in webdev there are a lot of instances where you actually want a parser - legacy places where they used to save things in plain text for example, and I started seeing the pattern everywhere.
Where I would have reached for some monstrosity of a regex to solve this, now I just whip out a recursive decent parser and call it a day, takes surprisingly small amount of code! (https://github.com/dmaevsky/rd-parse)
StormEvents
| where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31))
and DamageCrops > 0
| summarize EventCount = count() by bin(StartTime, 7d)
I would guess they didn't wrap the main PRQL library (which is written in Rust) because Go code is a lot easier to deal with when it's pure Go. And they probably didn't just write a Go version of PRQL because that would be a mountain of work.
Still I think that's a mistake. PRQL is a far more mature project and has things like IDE support and an online playground which they are never going to do...
Better just to bite the bullet and wrap the Rust library.
There's some C bindings and the example in the README shows integration with Go:
https://github.com/PRQL/prql/tree/main/prqlc/bindings/prqlc-...
This article has some details: https://dave.cheney.net/2016/01/18/cgo-is-not-go
Note that this isn't true of languages like Python (CPython) or Rust which are much more closely tied to C than Go is. Go is a "from scratch as if C never existed" language which is great because it doesn't have any C baggage but it does make integrating with C more awkward.
Couldn't find your own words? The details don't say much. Sure, you have to bend to C to some degree, but that's true of every language that wants to integrate with C. Go integrates no less well than any other language on that front.
And it's not even really all that accurate. Consider "Performance will always be an issue" – gccgo and tinygo have shown that you don't have to have any call overhead. They can call C functions as fast as C can. That is in no way a limitation of Go.
That is only a limitation of gc, and even then the overhead is only a few nanoseconds these days. You're never going to notice. These may have been concerns in 2016 – indeed, overhead was a lot higher back then – but time marches forward. Things change. The link is fast approaching being a decade old by this point. At least give us something from 2024, about the current release that is 17 versions newer than the one referred to in the link, if you really don't know how to formulate your own thoughts.
But you should be formulating your own thoughts if you want to participate in a discussion. Outsourcing thoughts to other people is nonsensical. If those other people want to participate in the discussion, they can write their own comments, but that is for them to do.
> or Rust
PRQL is written in Rust, so it would be quite strange if it wasn't true of Rust. Interestingly, the Javascript bindings use a WASM target. Go could also use the WASM target if there was some reason to avoid more traditional linking. It is curious that you didn't mention that. Again, reason to use your own brain. If you have to outsource discussion, why bother participating at all?
Most of the languages I'm aware of integrate with the C runtime much more naturally than go is capable of. Go has its own type of stack, which means it's a pain in the ass to embed into the C runtime and it's a pain in the ass to embed the C runtime into it.
> gccgo and tinygo
Does anyone use these implementations? I honestly had no idea either project existed.
In what way? Is it because you call `C.function_name` instead of `function_name`, the latter of which some other languages will allow? I don't see how that is a meaningful difference. Especially when Go developers are already accustomed to referencing pure Go functions in the same way.
> Go has its own type of stack
gc brings its own type of stack, but that's not a feature of Go. Let's not confuse an implementation with a language. Go says nothing about stack layout.
> which means it's a pain in the ass to embed into the C runtime and it's a pain in the ass to embed the C runtime into it.
There might be a pain in the ass for the gc maintainers, but that's not you. You will never notice or care. It is fully abstracted away.
There is some overhead cost to that, but it has shrunk so dramatically over the years, you're not going to notice it anymore either. Protip: Don't use version 1.5 like the previous link is talking about. That was 17 major versions ago.
> I honestly had no idea either project existed.
How'd you miss gccgo, at least? It's maintained by the official Go team. It is the second compiler they always talk about – the one they use to ensure that the standard isn't defined by an implementation.
No, it's because they use completely different stacks. It has nothing to do with the aesthetics of the language.
> You will never notice or care. It is fully abstracted away.
I mean... aesthetically, sure. How the runtime works still matters a lot.
1. Again, that's implementation dependent, not a constraint of Go. The language says absolutely nothing about stacks. tinygo, for instance, uses a C stack.
2. Even in the case of gc, where the stack is unusual, it doesn't really matter. Once upon a time there was some meaningful latency introduced because of it, but that's not true anymore.
What is still something to think about, albeit unrelated to the stack, is if you want to statically link a cross-compiled C library. Cross-compiling C programs a really hard problem. Of course, that is a hard problem no matter what. It is no less easy to cross-compile a C library for a Python program, but the static linking adds an additional complication.
But, at the same time, you don't have to statically link libraries. You can opt to dynamically load libraries, thereby having no cross-compilation issues (assuming the shared library is already compiled for your target platform). This is how most of those other languages we've talked about are doing it to get around the linking challenges where cross-platform execution is pertinent. You can do the same in Go. Go binaries normally being statically linked end-to-end is generally considered a nice feature, but if you're willing to accept dynamic linking in another language...
Naturally, if you never build for systems outside of the system you are using, that's moot. Compiling and linking a C target that matches the platform it is being performed on is easy.
> How the runtime works still matters a lot.
Not really. Anything that might matter is abstracted away – at least to the same extent as other languages. Not your problem.
Interfacing with C is a first-class feature of Go. I don't know where you got the idea that it isn't. There are a lot of good reasons to keep all of your code in the same language (true of Go and every other language in existence), but it is hardly the end of the world if you have a solid reason to turn elsewhere.
We're not anti-PRQL, but our users (folks in security) coming from Kusto, Splunk, SumoLogic, LogScale, and others have expressed that they have a preference for this syntax over PRQL syntax.
I wouldn't be surprised if we end up supporting both and letting folks choose the one they're most happy with using.
With prql, if they don’t support your favorite operator then you’re out of luck.
Neither PRQL nor Pql seem to be able to do anything outside of SELECT like Preql[1] can.
I propose we call all attempts at transpiling to SQL "quels".
[0] https://prql-lang.org/ [1] https://github.com/erezsh/Preql
Also a pipeline language, PRQL-inspired, but differing in that (i) TQL supports multiple data types between operators, both unstructured blocks of bytes and structured data frames as Arrow record batches, (ii) TQL is multi-schema, i.e., a single pipeline can have different "tables", as if you're processing semi-structured JSON, and (iii) TQL has support for batch and stream processing, with a light-weight indexed storage layer on top of Parquet/Feather files for historical workloads and a streaming executor. We're in the middle of getting TQL v2 [@] out of the door with support for expressions and more advanced control flow, e.g., match-case statements. There's a blog post [#] about the core design of the engine as well.
While it's a general-purpose ETL tool, we're targeting primary operational security use case where people today use Splunk, Sentinel/ADX, Elastic, etc. So some operators are very security'ish, like Sigma, YARA, or Velociraptor.
Comparison:
users
| where eventTime > minus(now(), toIntervalDay(1))
| project user_id, user_email
export
where eventTime > now() - 1d
select user_id, user_email
[#] https://docs.tenzir.com/blog/five-design-principles-for-buil...
If you squint, this query language is very similar to Polars, which is state-of-the-art for performance. I expect Pql could be as performant with sufficient investment.
The real problem is that creating a new query language is a ton of work. You need to create tooling, language servers, integrate with notebooks, etc… If you use SQL you get all of this for free.
SELECT * FROM "users" WHERE like ("email", 'gmail')
Should “like” here be a user-defined function? Because that’s not the syntax for SQL-like. To which SQL version will Pql translate its queries?
From the release blog [1] they mention that unknown functions are passed through to the underlying SQL engine -- this let's them target anything from mysql, Postgres, ClickHouse or proprietary engines like Snowflake.
edit: guess I can't
My hunch is tuning english prompts on less verbose, more "left to right" flowing languages might yield better results.
For some reason, a lot of these SQL alternatives seem to be syntactic preference and not much simpler or clearer than the original.
We're still fairly bullish on the LLM-to-SQL front though, but in the meantime PQL is a good bridge.
In short, it has a longer spec than famously-complex C++ while making a much less expressive language out of it.
When properly styled with good indentation and syntax habits SQL is extremely readable.
But this is only for initial generation. After that, you should be using pure SQL.
I think what people really want is business rules and data cleaning and schema discovery.
If you had to use English against multiple source systems to and tons of joins, the sentence would be paragraphs.
Where I think there’s value is in using something like a data catalog to label business rules against a data warehouse, tied to dashboard queries and other common ones.
But that’s a hard problem and a unique model to every customer. And always changing.
Rather than an LLM, you can send your request for an SQL query directly to Donald D. Chamberlin, one of the original designers of SQL. Furthermore, he gets an ERD for your database.
What odds you get back a query that gives you correct answers?
You have to know SQL to use them. They produce a lot of code that looks correct and produces correct-looking results with subtle errors. So you can't just hand it to someone who doesn't know SQL and let them query the database, but that's the use case where something like this would be valuable. You have to be experienced with SQL and know all the peccadillos of the DB you're working with to check the query and output for correctness.
For someone like me who is experienced with SQL, I can write simple queries just as fast as I can figure out how to prompt the LLM to get what I want. Where a tool like this would be really helpful is if it could help me write more complex queries more quickly. However, it is non-trivial to get the LLM to generate complex queries that take into account all the idiosyncrasies of your specific data model. So again it ends up being much faster for me to just write the query myself and not involve the LLM.
Where I think LLMs go wrong with SQL is that to write good SQL you have to have a deep knowledge of the underlying data model, and the LLMs aren't good at that yet.
The SQL generation works well out of the box and works better as you update the semantic layer. The semantic layer includes things like joins and measures (e.g. aggregate functions) that you'd want standard definitions for. For example, you don't want an LLM creating a definition for MRR on the fly. All the semantic definitions are plain SQL.
quick demo: https://www.loom.com/share/a0d3c0e273004d7982b2aed24628ef40?...
> users > | where like(email, 'gmail') > | count
becomes
> WITH > "__subquery0" AS ( > SELECT > * > FROM > "users" > WHERE > like ("email", 'gmail') > ) > SELECT > COUNT(*) AS "count()" > FROM > "__subquery0";
Fetching everything from the users table can be a ton slower than just running a count on that table, if the table is indexed on email. I had to deal with that very problem this week.
(Just like any C compiler will produce the same output for `x += 2` and `x += 1 + 1`.)
---
A notable exception was PostgreSQL prior to version 12, which treated CTEs as an optimization fences.
It's just worth recognizing that SQL itself is a tool that doesn't do a great deal promote understanding what goes on under the hood. (I've witnessed that firsthand many times.)
If nothing else, having to write SQL tends to lead engineers to the engine manual, where they have at least a chance to become aware of the existence of query planners.
And the worst part: nothing better exist; single’ish bad language is better than dozens of new shortlived ones that have quirks in various other places.
But somebody needs to be idealist and keep trying.
- CTEs are very close if not the same across Oracle, PostgreSQL, DB2, Hive, Snowflake, and MS SQL Server - I believe even Sybase too but it's been a while. - Joins work all largely the same even though a couple of those support additional join types, especially when you want to join on functions that return data sets. - Window functions are supported by every major DB with similar or the same syntax too. Any differences take 5sec to lookup in documentation.
My only complaint is loading data is highly vendor specific.
The difference in enjoyability is stark: I truly hate SQL now.
More robust criticism is provided here (https://carlineng.com/?postid=sql-critique#blog). The quote I usually drag out is from Chris Date, who helped pioneer relational DBs:
"At the same time, I have to say too that we didn’t realize how truly awful SQL was or would turn out to be (note that it’s much worse now than it was then, though it was pretty bad right from the outset)."
https://www.red-gate.com/simple-talk/opinion/opinion-pieces/...
For example, why not allow the following expression as a legal statement:
tablename;
TABLE tablename;I'd be more sympathetic to concerns about switching engines if I had, at any point in a career now heading for its 25th year, ever seen that occur.
Given the frequency with which ORMs are used in greenfield to defend against this notional problem, and the many problems that using an ORM always inflicts, this may well be the costliest form of premature optimization I've ever seen. It certainly can't be outside the top three.
like ("email", 'gmail')
minus (now (), toIntervalDay (1))
Edit: my comment acted like ORM type libraries than execute within databases like Ibis don't exist. My bad!
For that I use squirrel which uses the builder pattern to compose sql strings. Keeping it as strings and interfaces allow it to be very easily extended, for example I was able to customize it to speak SOQL (salesforce). plenty of downide though.
NULL is always an awkward thing to deal with - how you want to handle it depends on the specific thing you're trying to accomplish. I'd probably prefer it if NULL equaled NULL when dealing with where conditions but it actually makes join evaluations a lot cleaner - if NULL equaled NULL then joining on columns with nulls would get really weird.
At the end of the day IS NULL and IS DISTINCT FROM/IS NOT DISTINCT FROM exist so you can handle cases where it'd be weird.
unfortunately they were not invented at the time sql was created
The problem is that id = id is fundamentally incorrect for a nullable column. You should have done id is not null and id = id. And you shouldn’t have been allowed to do the first anyways, because nothing good can come of it (there is no sane semantics to stuffing a trinary logic into a boolean algebra, and SQL chooses one of the many insane options, leading to both false positive and false negative matches depending.) the only correct answer is not to do that.
It's true that you have to adopt a completely different language, but when that language saves you from potentially expensive bugs, it becomes appealing.
I think it handles all the 3VL problems I've encountered in SQL, but that doesn't mean it handles all possible 3VL problems. It also might not make any sense to anyone except me.
Also, this is an aside, but is your thing named after Snake Plissken?
So you do agree that the rest of SQL is broken. That’s why there is a value in creating (and learning) such new languages.
Using the psychosis of ORMs to defend the psychosis of SQL is itself a form of psychosis
In your codebase, do you stick raw SQL all over the place and iterate over rows exclusively? Or instead, as a convenience, do you write helpers that map objects into SQL statements and map result rows into objects? If so, congratulations, you’re using an ORM. The concept of ORMs is not bad. It’s a logical thing to do. Some ORM _implementations_ have some very serious issues, but that does not make ORMs as a whole bad.
"designed to be small and efficient" – adding a layer on top of SQL is necessarily less efficient, adding a layer of indirection on underlying optimisations means it is likely (but not guaranteed) to also generate less efficient queries.
"make developing queries simple" – this seems to be just syntactic preference. The examples are certainly shorter, but in part that's the SQL style used.
I think it either needs more evidence that the syntax is actually better or cases it simplifies, the ways in which it can optimise that are hard in SQL, or it perhaps needs to be more honest about the intent of the project being to just be a different interface for those who prefer it that way.
It's an interesting exercise, and I'm glad it exists in that respect, and hope the author enjoyed making it and learnt something from it. That can be enough of a why!
I tend to think this is a little more user friendly, personally, and it's nice to give some open-source competition to the major languages that are used in security (SPL, Sumologic, KQL, and ES|QL).
We were surprised that there weren't syntactic competitiors (i.e. -- while prql has some similar goals, the syntax and audience in mind were very different)
Whether or not this is true for PQL/SQL, I don't know enough to say. But I do know that I don't write SQL at a high-enough level to be sure that a wrapper couldn't compile to something more efficient than what I produce, especially for complicated queries.
They are suitable for Reads on tables that are tuned specifically for the desired use cases. The amount of optimization required in the conversion should be limited intrinsicly by this assumption and the higher level language should be strict enough that an optimization related to deciding A/B SQL Approach in the conversion to SQL is not required (because results will be unpredictable due to table sizes etc)
Pipe-driven SQLes have been interesting in the past but I much prefer when you start with a big-blob of joins to define the data source before getting into the applied operations - the SQL PQL produces looks like it would have really large issues with performance due to forcing certain join orders and limiting how much the query planner can rearrange operations.
That's essentially the model we've chosen for XTQL, with the addition of a logic var unification scope for even more concise joins: https://docs.xtdb.com/intro/what-is-xtql#unify
Also, anyone interested in this post-SQL space would probably enjoy this recent paper: https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf
It's not if it's compile time
That cost may be offset by other benefits, but that isn't obviously true in this case.