3 ms·

The problem with SQL is that it is not a (very) composable language. The documentation for EdgeDb goes into some detail about that and shows an alternative bet

The problem with SQL is that it is not a (very) composable language. The documentation for EdgeDb goes into some detail about that and shows an alternative better language for data-queries. https://www.edgedb.com/showcase/edgeql https://www.edgedb.com/showcase/edgeql To understand why SQL is bad, you must first be shown something better, and EdgeDb seems to be such better more composable language.

IanCal 3y ago
Malloy is another thing to check out in this space https://www.malloydata.dev/ https://www.malloydata.dev/
nalgeon 3y ago
SQL is infinitely composable. Each SELECT returns a relation that another SELECT can query (or combine with another relation using set operators like UNION etc).
- default-kramer 3y ago
  I suppose it's infinitely composable in that very limited dimension, but SQL's critics are asking for composability in other dimensions. For example, say I have a pretty long query of some Order table. Now I want the exact same query, but it should start from the OrderArchive table instead. How do you do this? Dynamic SQL? The world has lambasted Javascript for much less, but somehow the fact that so many tasks require dynamic SQL (or copy-paste) is considered acceptable. For the record, I make heavy use of SQL because relational databases are awesome and SQL is the least bad option I've found so far. But the many shortcomings of SQL still annoy me.
  - camgunz 3y ago
    The answers are stored procedures and templates. People argue against them, but they're basically the data versions of software affordances we already have.
fbdab103 3y ago
I like PRQL [0]. It fixes a lot of the SQL warts and compiles down to regular SQL (think Typescript to JS). The syntax example on the PRQL homepage from invoices filter invoice_date >= @1970-01-16 derive [ transaction_fees = 0.8, income = total - transaction_fees ] filter income > 1 group customer_id ( aggregate [ average total, sum_income = sum income, ct = count, ] ) sort [-sum_income] take 10 join c=customers [==customer_id] derive name = f"{c.last_name}, {c.first_name}" select [ c.customer_id, name, sum_income ] Trailing commas, select at the bottom, filter is both a WHERE and HAVING replacement, easy filtering of created columns, etc [0] https://prql-lang.org/ https://prql-lang.org/
- ttfkam 3y ago
  I'd much rather debug an SQL query with CTEs, which is what that is approximating. The notion that SQL is not composable is either a lie or simply repeated by folks with only a cursory knowledge of SQL from 20 years ago. Views, set-returning functions, CTEs, and more: all examples of composability in SQL. Then of course there's the issue of security where folks tend to put all of their access constraints into their middleware AFTER the data has already been returned over the wire in bulk. Take a moment to consider role-based access control and row-level security policies. Instead of trying to track down every possible spot where a JOIN could have crept in past the code reviews (you code review your DDL and DML, right?), you set your GRANTs, REVOKEs, and POLICYs at the points in your data model that need them; restricted data never makes it into intermediate result sets let alone the final result set and the wire. Don't misunderstand me. GRANT, REVOKE, and POLICY can be a real PITA, but that's because *security* is a PITA, not the SQL syntax to enforce it. Anyone who tells you their app solves your data security problems in the app tier with a point and click is a lying salesman.
interleave 3y ago
> The problem with SQL is that it is not a (very) composable language. I thought the same until a few weeks ago. Then we used the WITH operator for pre-processing and giving things human-readable names. That helped us manage complexity. The final SELECT statement was very easy to reason about. Not sure if this is a best (or worst) practice but it helped us ship it.
cratermoon 3y ago
Interestingly, EdgeDB is exactly the unnamed tool that the article criticizes.
scottcodie 3y ago
SQL in the context of a single query struggles with a composable features. But looking slightly outside the scope of queries with assignment statements and view/materialization dags then you start getting some of that composition back. I think SQRL is an good example of SQL with a bunch of composable feature bolt-ons. https://www.datasqrl.com/blog/sqrl-high-level-data-language-sql/#sqrl-features-overview https://www.datasqrl.com/blog/sqrl-high-level-data-language-...

IanCal 3y ago

Malloy is another thing to check out in this space https://www.malloydata.dev/ https://www.malloydata.dev/

nalgeon 3y ago

SQL is infinitely composable. Each SELECT returns a relation that another SELECT can query (or combine with another relation using set operators like UNION etc).

default-kramer 3y ago
I suppose it's infinitely composable in that very limited dimension, but SQL's critics are asking for composability in other dimensions. For example, say I have a pretty long query of some Order table. Now I want the exact same query, but it should start from the OrderArchive table instead. How do you do this? Dynamic SQL? The world has lambasted Javascript for much less, but somehow the fact that so many tasks require dynamic SQL (or copy-paste) is considered acceptable. For the record, I make heavy use of SQL because relational databases are awesome and SQL is the least bad option I've found so far. But the many shortcomings of SQL still annoy me.
- camgunz 3y ago
  The answers are stored procedures and templates. People argue against them, but they're basically the data versions of software affordances we already have.

fbdab103 3y ago

I like PRQL [0]. It fixes a lot of the SQL warts and compiles down to regular SQL (think Typescript to JS). The syntax example on the PRQL homepage from invoices filter invoice_date >= @1970-01-16 derive [ transaction_fees = 0.8, income = total - transaction_fees ] filter income > 1 group customer_id ( aggregate [ average total, sum_income = sum income, ct = count, ] ) sort [-sum_income] take 10 join c=customers [==customer_id] derive name = f"{c.last_name}, {c.first_name}" select [ c.customer_id, name, sum_income ] Trailing commas, select at the bottom, filter is both a WHERE and HAVING replacement, easy filtering of created columns, etc [0] https://prql-lang.org/ https://prql-lang.org/

ttfkam 3y ago
I'd much rather debug an SQL query with CTEs, which is what that is approximating. The notion that SQL is not composable is either a lie or simply repeated by folks with only a cursory knowledge of SQL from 20 years ago. Views, set-returning functions, CTEs, and more: all examples of composability in SQL. Then of course there's the issue of security where folks tend to put all of their access constraints into their middleware AFTER the data has already been returned over the wire in bulk. Take a moment to consider role-based access control and row-level security policies. Instead of trying to track down every possible spot where a JOIN could have crept in past the code reviews (you code review your DDL and DML, right?), you set your GRANTs, REVOKEs, and POLICYs at the points in your data model that need them; restricted data never makes it into intermediate result sets let alone the final result set and the wire. Don't misunderstand me. GRANT, REVOKE, and POLICY can be a real PITA, but that's because *security* is a PITA, not the SQL syntax to enforce it. Anyone who tells you their app solves your data security problems in the app tier with a point and click is a lying salesman.

interleave 3y ago

> The problem with SQL is that it is not a (very) composable language. I thought the same until a few weeks ago. Then we used the WITH operator for pre-processing and giving things human-readable names. That helped us manage complexity. The final SELECT statement was very easy to reason about. Not sure if this is a best (or worst) practice but it helped us ship it.

cratermoon 3y ago

Interestingly, EdgeDB is exactly the unnamed tool that the article criticizes.

scottcodie 3y ago

SQL in the context of a single query struggles with a composable features. But looking slightly outside the scope of queries with assignment statements and view/materialization dags then you start getting some of that composition back. I think SQRL is an good example of SQL with a bunch of composable feature bolt-ons. https://www.datasqrl.com/blog/sqrl-high-level-data-language-sql/#sqrl-features-overview https://www.datasqrl.com/blog/sqrl-high-level-data-language-...