Our great sponsors
-
prql
PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement
-
trustfall
A query engine for any combination of data sources. Query your files and APIs as if they were databases!
-
InfluxDB
Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.
-
-
ArangoDB
🥑 ArangoDB is a native multi-model database with flexible data models for documents, graphs, and key-values. Build high performance applications using a convenient SQL-like query language or JavaScript extensions.
-
-
-
WorkOS
The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.
-
-
zillion
Make sense of it all. Semantic data modeling and analytics with a sprinkle of AI. https://totalhack.github.io/zillion/
PRQL actually goes through two intermediary ASTs (PL and RQ) before being compiled into SQL. This is not that well documented yet but have a look at the comments at the top of the lib.rs [0]. Both representations are accessible as json in the Rust and Python APIs.
On the roadmap are plans to support DataFrame backends such as Polars, Pandas, Ibis, ... and Substrait.io [1]. The aim of Substrait is similar to what you describe, i.e. to create a universal relational algebra representation that can be executed directly by backend systems without having to go through SQL.
[0]: https://github.com/PRQL/prql/blob/main/prql-compiler/src/lib...
[1]: https://substrait.io/
Also all languages has an query-builder / ORM so the benefit of something like PRQL is possibly not big enough to merit it as an additional dependency.
My suggestion:
Make PRQL a cli tool that can be used by allowing users to connect to a database in a similar fashion as something like usql (https://github.com/xo/usql),
Some databases like ArangoDB (https://www.arangodb.com/) allow you to use Javascript instead of SQL.
However, using a type-unsafe, turing-complete language introduces type unsafety and turing-complete problems to the query layer; the usual problems we know and love, such as infinite loops, runtime type errors, exceptions, and the like.
Personally, I'm looking forward to a WASM runtime for databases -- so we can run webassembly on the database. This COULD be carefully designed to be statically checked and, possibly, make it really hard to write runaway loops.
Thanks for the suggestion. I don't think I knew about usql. I completely agree with you and have been working on a cli tool called `prql-query` or `pq` at the command line:
https://github.com/prql/prql-query/
Unfortunately I haven't had much time to spend on it of late but hope to push some updates soon.
You might enjoy the FunSQL library; SQL verbs are represented using regular Julia objects and you pipeline them together to build bigger queries. A key difference to other SQL builders is that you assemble a tree of "tabular operations" instead of a SQL syntax tree.
https://github.com/MechanicalRabbit/FunSQL.jl
Re: VCS, I’ve found that with a small amount of setup, tools like Liquibase[0] allow you to maintain functions and views (with “CREATE OR REPLACE” or equivalent) as SQL files in a file system with full VCS, diffing, etc. just like any other code.
[0] https://www.liquibase.org/
The last example in this notebook [0] shows how similar PRQL is to dplyr. The rest of the notebook shows how you can use PRQL from R, Python and the command line.
[0]: https://github.com/snth/normconf2022/blob/main/notebooks/nor...
At first glance this seems more confusing, particularly the grouping/aggregation syntax, though I suppose that's something I'd just get used to. Some of the syntactic sugar is nice, but some things are also unlike SQL for no apparent reason which just makes adoption harder than necessary (join syntax for example).
IMO the main selling point would be the "database agnostic" part, but I already achieve that through SQLAlchemy Core and/or a warehouse layer like https://github.com/totalhack/zillion (disclaimer: I'm the author and this is alpha-level stuff, though I use it regularly). It seems like many newer DB technologies/services I'd want to use either speak PostgreSQL or MySQL wire protocol anyway.
The roadmap is worth a read, as it notes some limitations and expected challenges supporting the wide variety of DBMS features and syntax. That said, I can see where this might be useful in the cases where I do have to jump into direct SQL, but want the flexibility to easily switch the back end DB for that code -- that's assuming it can cover the use cases that forced me to write direct SQL in the first place though.
So, after digging about in the docs, I found the Best Practices[0] page which lays out what you describe. I was disappointed to find it is just using idempotent creation statements for procedures and views and putting them all in files that are re-run on each migration step-along with each new roll forward migration-so that new versions of these logic objects replace any old ones. This is not exactly something that liquidbase provides, should be easily replicatable, and I was hoping it'd do diffs to provide similar experience on tables as well.
After some web searching, I came across a project[1] that's trying to do what I describe but it appears to be dead. I'm surprised that migration tools (that I looked through) don't already support such a basic approach that I suspect I'm missing something obvious. Some fundamental block in how SQL works or maybe it doesn't work that well in practice for, in concept, it sounds easy to script using what they call a shadow db and `pg_diff`.
[0]: https://docs.liquibase.com/concepts/bestpractices.html
[1]: https://github.com/cbowdon/ddl-diff