Comment by haspok
2 years ago
One notable reason is being database-agnostic. Like ORM's, if you can generate SQL you can generate database-specific SQL as well.
SQL is also quite verbose in places (JOINs are the most trivial example), and lack a decent amount of abstraction (CTEs are relatively low level).
Updating a large set of FK'd tables can be a nightmare (this is what ORMs shine at).
Finally, some modern additions are quite unreadable, Postgres' JSON syntax, for example.
I'm not saying that PRQL solves any of the above, but these are all legit problems with "plain" SQL.
Database agnosticism is so 2010. There's very little reason to choose a DB other than postgres, and if you have a reason to choose a specific niche db you're not probably not going to be migrating away from it any time soon.
CTEs are a first step in structuring queries to make them decomposable. You can extract CTEs to functions and mark them stable and it's logically equivalent to the original query.
> There's very little reason to choose a DB other than postgres
Sure, if you are a startup, or write your own code. But for most people the choice of database(s) is a given, and they are not in a position to challenge that. At the end of the day, Oracle has to make a living, too...
From parent:
> you're not probably not going to be migrating away from it any time soon.
Oracle has its own optimizations and foot-guns that extend well beyond what you can represent in a database-agnostic API. And once you're on that DB, you can write DB-agnostic and have performance be relatively horrible or require a careful rewrite of your schema and stored procedures when you migrate. There is not door number three.
Writing a common layer for any and all relational databases is like using a Java UI library for all operating systems. Sure, it will work, but it will have obvious shortfalls, be immediately recognizable as such to anyone familiar with the underlying platform, be inconsistent with other apps on that platform, and leave any opportunities for efficiency and performance on the floor.
Say you want a pivot table. In Oracle and MS SQL, it's built in. In Postgres, it's possible but noticeably more annoying. In MySQL, it's simply not possible. How would you represent this in a database-agnostic way? And yet performing in the app layer is very much slower/less efficient.
Did you know Oracle supports parallel DML for enhanced performance and lower multi-query latency? You have to intentionally use though, and neither Postgres nor MySQL support it at all.
What about global temporary tables? Those especially aren't found in Postgres or MySQL and are not easily swapped into the app layer without a massive performance penalty.
Per-user namespaces are yet another Oracle-ism that just doesn't translate to other DB engines, but you definitely should know about.
If you're making a living from Oracle, earn your pay. Make the most of what you've got.
I like PostgreSQL quite a bit and pushed for using it by default at my company, but SQL pops up in a surprising number of places. You can use it in PowerBI, Snowflake, and pandas just to name a few. You don't always control which DB you're talking to, and it would be nice if the SQL interface that gets presented in those scenarios got an upgrade. PRQL looks like a really promising option for that.
> There's very little reason to choose a DB other than postgres
Postgres is single server OLTP DB with complicated failover story, it is strong enough reason to consider some other contenders e.g. CocroachDB, SpannerDB for distributed OLTP or OLAP specialized ClickHouse, BigQuery, DuckDB.
Aren't most of those wire-compatible with Postgres?
3 replies →
How are joins verbose? It’s pretty straight to the point: combine these 2 tables on these columns… what do you want to remove to make it less verbose?