5 ms·

What is your opinion on abstractions on top of SQL queries? On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a

What is your opinion on abstractions on top of SQL queries? On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use.

xarope 5y ago
I'd agree with this. Despite hating databases whilst studying for my undergrad, I've come to realise after almost 30 years in the industry that expertise in SQL and database optimizations are key, and all the various frameworks I've tried to use to "hide" the complexity of SQL for my team, end up causing more headaches. e.g. when I use sqlalchemy, I end up writing queries using core than ORM, because it becomes simple for me to optimize versus having to keep printing str(query_statement) to analyze sqlalchemy thinks it should do versus what it really should do. Do remember to find a good DB optimization expert (hint: not the type that answers "add an index" when asked how they would speed up a query) and you'd be surprised how well SQL continues to scale today. And for those of you about to say "but KV/NoSQL", by all means don't shoehorn SQL into every single thing; use the right tool for the right job; SQL will handle that 80+% of what you need, then implement the "other" stuff in memcached/redis/mongo/kafka/whatever you find is "easiest" to implement, deploy, manage and maintain.
- le-mark 5y ago
  > versus having to keep printing str(query_statement) to analyze sqlalchemy thinks it should do versus what it really should do. ORM generally aren’t meant to totally replace sql, it’s an anti pattern. There was a quote by Gavin king explicitly calling this out; ORM for crud operations on object hierarchies, sql for everything else (reporting, adhoc queries etc). I try to educate this whenever I can but so many teams resist it.
  - dorgo 5y ago
    ORMs assume that they are the only access point to the database and cache result sets based on that. Disable the cache or keep in mind that bugs can happen due to that behaviour.
    - le-mark 5y ago
      Caching is orthogonal to ORMs; https://old.reddit.com/r/programming/comments/2cnw8x/what_orms_have_taught_me_just_learn_sql/cjheyec/ https://old.reddit.com/r/programming/comments/2cnw8x/what_or...
      - ghusbands 5y ago
        No it's not, and that link isn't relevant. Some ORMs do have caching layers and when you use those ORMs you have to know how to invalidate the appropriate caches when you update without going through them.
tremon 5y ago
.Net's LINQ comes close, at least for querying. Update statements using LINQ usually translate to a select query followed by an in-language loop, but that's probably more an Entity Framework limitation than of LINQ itself. The problem with most abstractions on top of SQL is that the first thing they abstract away is the relational model; you end up with an opaque result set, a mapped object, or an untyped collection. In my opinion, in order for a higher-level SQL language to be useful it must retain all the low-level data type information. The abstraction should be on the operation level (DML), not the data level.
- ethbr0 5y ago
  SQL abstractions are stuck with a market problem, for reasons in this thread. Everyone who wants better-than-SQL is generally already adept at SQL relational gymnastics. And everyone who isn't adept at SQL wants to be able to ignore relational thinking. End result? Abstractions target the easier of the two markets: people who don't want to learn relational modeling.
  - treis 5y ago
    I think there's some low hanging fruit out there. Simple improvements to make queries easier to write and maintain. As an example, ActiveRecord allows you to define scopes. If you have an account things like: platinum: -> value >100000 churn_risk: -> churn>.95 You can then chain these together i.e Account.platinum.churn_risk to get all the platinum accounts at risk of churn. Afaik there's nothing similar in SQL. If your definition of a platinum customer changes you have to change a bunch of different queries instead of one definition.
    - no-s 5y ago
      While I agree it’s a PITA, most SQL-interfaced RDBMS support views, which is a way to abstract the Platinum customer issue you pose into something composable, albeit in SQL terms, not in the dotted property chaining style you propose. In the past I’ve created view generators to automatically populate a sweet syntactic palette for application semantics. Modern query planners are hardly bothered by the implied excess of joins any more, and will often use the embedded literals to optimize the query plan before execution… I’ve plucked that low hanging fruit before, and it can taste great even in SQL sauce.
nesarkvechnep 5y ago
Elixir's Ecto.
jiggawatts 5y ago
> On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use. That's precisely because of some the flaws of SQL outlined in the article. Generating SQL is complex. Generating portable SQL is impossible. Take a look at the queries sent by something like Power BI to MS SQL Server when running in direct-query mode. It's just obscene how complex the queries can get! I've tried to write some SQL generators before, of various types. I always got bogged down in the ludicrous complexity. The author of the article makes so many good points that it's easy to gloss over entire categories of mistakes in the SQL spec. For example, he briefly mentions that getting a column schema back in query is weird and non-standard. The real problem here is that SQL is not homoiconic: tables, columns, and their types could have been represented as tables, including when defining them. E.g.: instead of "CREATE TABLE" the syntax should have been more like "INSERT INTO sys.tables ... ". (Similarly for columns, constraints, foreign keys, etc...) Instead, we have a language designed for data that until recently had implementation-defined read-only system views for the schema data! Writing into that data uses an entirely different set of key words and syntax, which is "not really SQL" in the sense that it isn't relational and cannot use tables as input unless coded in an external language like Java, or built up using string manipulation an called via some sort of non-standard "exec string". Of course, then you have to worry about things like not having a built-in standard escape/unescape function! (QUOTENAME is the MS SQL version of this, surely non-standard).
- barrkel 5y ago
  There's a lot more wrong with DDL than the inability of expressing it as DML. Some SQL doesn't scale well, but DDL fundamentally scales really badly. It needs either an async or resumable execution model, for one thing, which doesn't map well to connection-oriented transactions. Big migrations take days to complete. That's too long to be very reliable in a distributed system when operating on a synchronous basis.
  - jiggawatts 5y ago
    Big migrations take days to complete because -- like the author alluded to -- SQL merges very low level data layout concerns with very high level abstract data representations. So to make a change to one you have to make a change to the other. Decoupling the materialisation of a schema from its theoretical representation would make all migrations "instant". Many column-oriented databases can do this, or nearly so, but most row-oriented databases can't or won't. I.e.: ideally database engines should be able to keep track of multiple "versioned" physical schemas and transparently map in-flight queries to them. Big data migrations often implement this kind of thing manually with "writeable views" or similar techniques. This shouldn't be manual, the database engine should be able to do it behind the scenes with minimal human involvement.
barrkel 5y ago
Something as fully expressive as SQL is hard to design well. I think jOOQ is one of the best, but it relies on code generation to get good typing. The context for my generators was querying over data whose schema was customer defined. I wrote two generators in Ruby and one in Java. They were designed to take advantage of quirks and performance characteristics of MySQL when doing arbitrary ad-hoc sorts and filters, and the optimization was mostly about delaying joins if they weren't necessary for the predicates, and creating an inside out nest of subtable queries to control the execution plan. Doing this without a generator of some kind is impossible - the columns are dynamic - and then you leave a lot of performance on the table if you don't tune.
koheripbal 5y ago
I never understood the point of this. SQL is easy to learn and usually easy to write, and usually easy to performance tune. I find that if my query is getting overly complex, that some of the logic is often better suited in the app layer. At best, you are spending time learning something niche with little upside. At worst, you are introducing buggy bad performing complexity.
- spicybright 5y ago
  Hense, so far they all seem to suck. While I agree with you, it's too bad you can't create macros over common patterns. Not sure about you but I copy and paste a ton for this. Really, I'd just want a coffee script, or moon script (https://moonscript.org/ https://moonscript.org/) where you have the idea for a query in your head, and know what the constructs will ultimately generate.
ako 5y ago
Agree, probably because it's not what people are looking for. Most devs complain they don't have enough control over how a query is executed, they want to be closer to the actual storage, as they think they can do a better job deciding how data should be retrieved than the database engine. Adding another abstraction layer on top would only increase this frustration, as they'd have even less control.
smackeyacky 5y ago
EnntityFramework in .NET core is actually very good for an ORM that allows you to get right into the weeds of your queries if you need to. LINQ was ok, but entityframework is a lot better, no rubbish gui layout and the relationships are explicit and can be specified through object annotations right down to the level of controlling the table column collation. If you have bounced disappointingly off ORMs like I have since the 1990s, EntityFramework is a great step forward.

xarope 5y ago

I'd agree with this. Despite hating databases whilst studying for my undergrad, I've come to realise after almost 30 years in the industry that expertise in SQL and database optimizations are key, and all the various frameworks I've tried to use to "hide" the complexity of SQL for my team, end up causing more headaches. e.g. when I use sqlalchemy, I end up writing queries using core than ORM, because it becomes simple for me to optimize versus having to keep printing str(query_statement) to analyze sqlalchemy thinks it should do versus what it really should do. Do remember to find a good DB optimization expert (hint: not the type that answers "add an index" when asked how they would speed up a query) and you'd be surprised how well SQL continues to scale today. And for those of you about to say "but KV/NoSQL", by all means don't shoehorn SQL into every single thing; use the right tool for the right job; SQL will handle that 80+% of what you need, then implement the "other" stuff in memcached/redis/mongo/kafka/whatever you find is "easiest" to implement, deploy, manage and maintain.

le-mark 5y ago
> versus having to keep printing str(query_statement) to analyze sqlalchemy thinks it should do versus what it really should do. ORM generally aren’t meant to totally replace sql, it’s an anti pattern. There was a quote by Gavin king explicitly calling this out; ORM for crud operations on object hierarchies, sql for everything else (reporting, adhoc queries etc). I try to educate this whenever I can but so many teams resist it.
- dorgo 5y ago
  ORMs assume that they are the only access point to the database and cache result sets based on that. Disable the cache or keep in mind that bugs can happen due to that behaviour.
  - le-mark 5y ago
    Caching is orthogonal to ORMs; https://old.reddit.com/r/programming/comments/2cnw8x/what_orms_have_taught_me_just_learn_sql/cjheyec/ https://old.reddit.com/r/programming/comments/2cnw8x/what_or...
    - ghusbands 5y ago
      No it's not, and that link isn't relevant. Some ORMs do have caching layers and when you use those ORMs you have to know how to invalidate the appropriate caches when you update without going through them.

tremon 5y ago

.Net's LINQ comes close, at least for querying. Update statements using LINQ usually translate to a select query followed by an in-language loop, but that's probably more an Entity Framework limitation than of LINQ itself. The problem with most abstractions on top of SQL is that the first thing they abstract away is the relational model; you end up with an opaque result set, a mapped object, or an untyped collection. In my opinion, in order for a higher-level SQL language to be useful it must retain all the low-level data type information. The abstraction should be on the operation level (DML), not the data level.

ethbr0 5y ago
SQL abstractions are stuck with a market problem, for reasons in this thread. Everyone who wants better-than-SQL is generally already adept at SQL relational gymnastics. And everyone who isn't adept at SQL wants to be able to ignore relational thinking. End result? Abstractions target the easier of the two markets: people who don't want to learn relational modeling.
- treis 5y ago
  I think there's some low hanging fruit out there. Simple improvements to make queries easier to write and maintain. As an example, ActiveRecord allows you to define scopes. If you have an account things like: platinum: -> value >100000 churn_risk: -> churn>.95 You can then chain these together i.e Account.platinum.churn_risk to get all the platinum accounts at risk of churn. Afaik there's nothing similar in SQL. If your definition of a platinum customer changes you have to change a bunch of different queries instead of one definition.
  - no-s 5y ago
    While I agree it’s a PITA, most SQL-interfaced RDBMS support views, which is a way to abstract the Platinum customer issue you pose into something composable, albeit in SQL terms, not in the dotted property chaining style you propose. In the past I’ve created view generators to automatically populate a sweet syntactic palette for application semantics. Modern query planners are hardly bothered by the implied excess of joins any more, and will often use the embedded literals to optimize the query plan before execution… I’ve plucked that low hanging fruit before, and it can taste great even in SQL sauce.

nesarkvechnep 5y ago

Elixir's Ecto.

jiggawatts 5y ago

> On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use. That's precisely because of some the flaws of SQL outlined in the article. Generating SQL is complex. Generating portable SQL is impossible. Take a look at the queries sent by something like Power BI to MS SQL Server when running in direct-query mode. It's just obscene how complex the queries can get! I've tried to write some SQL generators before, of various types. I always got bogged down in the ludicrous complexity. The author of the article makes so many good points that it's easy to gloss over entire categories of mistakes in the SQL spec. For example, he briefly mentions that getting a column schema back in query is weird and non-standard. The real problem here is that SQL is not homoiconic: tables, columns, and their types could have been represented as tables, including when defining them. E.g.: instead of "CREATE TABLE" the syntax should have been more like "INSERT INTO sys.tables ... ". (Similarly for columns, constraints, foreign keys, etc...) Instead, we have a language designed for data that until recently had implementation-defined read-only system views for the schema data! Writing into that data uses an entirely different set of key words and syntax, which is "not really SQL" in the sense that it isn't relational and cannot use tables as input unless coded in an external language like Java, or built up using string manipulation an called via some sort of non-standard "exec string". Of course, then you have to worry about things like not having a built-in standard escape/unescape function! (QUOTENAME is the MS SQL version of this, surely non-standard).

barrkel 5y ago
There's a lot more wrong with DDL than the inability of expressing it as DML. Some SQL doesn't scale well, but DDL fundamentally scales really badly. It needs either an async or resumable execution model, for one thing, which doesn't map well to connection-oriented transactions. Big migrations take days to complete. That's too long to be very reliable in a distributed system when operating on a synchronous basis.
- jiggawatts 5y ago
  Big migrations take days to complete because -- like the author alluded to -- SQL merges very low level data layout concerns with very high level abstract data representations. So to make a change to one you have to make a change to the other. Decoupling the materialisation of a schema from its theoretical representation would make all migrations "instant". Many column-oriented databases can do this, or nearly so, but most row-oriented databases can't or won't. I.e.: ideally database engines should be able to keep track of multiple "versioned" physical schemas and transparently map in-flight queries to them. Big data migrations often implement this kind of thing manually with "writeable views" or similar techniques. This shouldn't be manual, the database engine should be able to do it behind the scenes with minimal human involvement.

barrkel 5y ago

Something as fully expressive as SQL is hard to design well. I think jOOQ is one of the best, but it relies on code generation to get good typing. The context for my generators was querying over data whose schema was customer defined. I wrote two generators in Ruby and one in Java. They were designed to take advantage of quirks and performance characteristics of MySQL when doing arbitrary ad-hoc sorts and filters, and the optimization was mostly about delaying joins if they weren't necessary for the predicates, and creating an inside out nest of subtable queries to control the execution plan. Doing this without a generator of some kind is impossible - the columns are dynamic - and then you leave a lot of performance on the table if you don't tune.

koheripbal 5y ago

I never understood the point of this. SQL is easy to learn and usually easy to write, and usually easy to performance tune. I find that if my query is getting overly complex, that some of the logic is often better suited in the app layer. At best, you are spending time learning something niche with little upside. At worst, you are introducing buggy bad performing complexity.

spicybright 5y ago
Hense, so far they all seem to suck. While I agree with you, it's too bad you can't create macros over common patterns. Not sure about you but I copy and paste a ton for this. Really, I'd just want a coffee script, or moon script (https://moonscript.org/ https://moonscript.org/) where you have the idea for a query in your head, and know what the constructs will ultimately generate.

ako 5y ago

Agree, probably because it's not what people are looking for. Most devs complain they don't have enough control over how a query is executed, they want to be closer to the actual storage, as they think they can do a better job deciding how data should be retrieved than the database engine. Adding another abstraction layer on top would only increase this frustration, as they'd have even less control.

smackeyacky 5y ago

EnntityFramework in .NET core is actually very good for an ORM that allows you to get right into the weeds of your queries if you need to. LINQ was ok, but entityframework is a lot better, no rubbish gui layout and the relationships are explicit and can be specified through object annotations right down to the level of controlling the table column collation. If you have bounced disappointingly off ORMs like I have since the 1990s, EntityFramework is a great step forward.