3 ms·

I love SQL. I'd love someone to make it better for complex queries. Have you seen the enterprise SQL monstrosities. Why do we have ORMs if SQL is perfect?

tsimionescu 6y ago
We have ORMs because our programming languages object models are not relational, they are hierarchical - from C to Haskell, everyone goes for highly non-relational data representations. So, when we interact with a relational DB we need some kind of layer to map between the world of relations in the DB and the world of objects in our program.
- quickthrower2 6y ago
  Correct but I didn’t explain well. I mean the SQL generation is left to the ORM and I think that’s no accident.
  - mattmanser 6y ago
    Not for the reason you're implying, actually for the opposite reason. I'm from the days of yore, just before ORMs became popular and it basically replaced a lot of boilerplate code, but it wasn't the SQL that was the bulk of it. It was mainly to save time in writing code to map columns to object properties really, the sql statements themselves were trivial even if you weren't lazy and just used select *. Also, here's a now mainly historical pain most devs never encounter any more: Before ORMs and the various migrators, your object properties might not have the same name as your SQL columns. Yes, it was dumb when you did it, yes, it caused loads of bugs, yes, it actually happened quite a lot.
    - Multicomp 6y ago
      > It was mainly to save time in writing code to map columns to object properties really, the sql statements themselves were trivial even if you weren't lazy and just used select *. I am running a side project and yes, writing the native SQL statement to take your object and put it in the database is not a problem, put in the parameterised values and off you go. But getting the data back from the database? Oh the horror. So much boilerplate in order to see if there are any records returned it all, if there are enough columns with the correct name for the kind of object you are making, if there is data or not in each column as appropriate for that specific column, if a given field can be coerced into being a string or an integer or a date or similar, then they're all marshaled into a dto object which is passed to the create new object validator. 800 lines of code later, and you may have an object back! Dapper appears to be the sweet spot for me, I am still writing SQL queries and still designing the SQL tables myself, no orm magic here, but it handles the actual marshalling to and from an in-memory dto object versus data in the table for me, and that is very valuable time savings.
      - mattmanser 6y ago
        Most SQL libraries handle this all for you? You really don't have to do any of that. It's perfectly fine to just 'know' that the data is going to be an int and just do `var id = rs.getInt(id")`. I'm on my phone so it's too fiddly to write my own brief example, but if you get rid of the silly comments in this you'll see you can do it all in a few lines, just very boilerplate lines: https://thedeveloperblog.com/sqlconnection https://thedeveloperblog.com/sqlconnection
- jimbokun 6y ago
  I would say they're not even hierarchical in many cases, but an unconstrained graph.
nicoburns 6y ago
Completely agree. For example, why isn't there functionality to define aliases for complex expressions and then reuse those through a query. Simple query-local SQL functions would be nice too. I'd love to see a "SQL-like" language that compiles down to SQL itself, much like Babel or TypeScript in the JavaScript world. I think the tricky thing is that there is no single SQL target.
- pkalinowski 6y ago
  > Completely agree. For example, why isn't there functionality to define aliases for complex expressions and then reuse those through a query. Simple query-local SQL functions would be nice too. What about common table expressions? Or custom defined functions? with X as ( select... ) It's widely used for analysis
  - jeltz 6y ago
    CTEs can do that, but it is quite clunky for when you just want to reuse a partial per row computation.
- quickthrower2 6y ago
  Multi sql targets means it’s even more of an advantage to compile
- jimbokun 6y ago
  > I think the tricky thing is that there is no single SQL target. So the jquery of SQL, then.
- scarface74 6y ago
  Linq
- parliament32 6y ago
  >functionality to define aliases for complex expressions and then reuse those through a query What's an example of what you're trying to do? I think views, user-defined functions, routines, and select aliases would cover all the bases...

tsimionescu 6y ago

We have ORMs because our programming languages object models are not relational, they are hierarchical - from C to Haskell, everyone goes for highly non-relational data representations. So, when we interact with a relational DB we need some kind of layer to map between the world of relations in the DB and the world of objects in our program.

quickthrower2 6y ago
Correct but I didn’t explain well. I mean the SQL generation is left to the ORM and I think that’s no accident.
- mattmanser 6y ago
  Not for the reason you're implying, actually for the opposite reason. I'm from the days of yore, just before ORMs became popular and it basically replaced a lot of boilerplate code, but it wasn't the SQL that was the bulk of it. It was mainly to save time in writing code to map columns to object properties really, the sql statements themselves were trivial even if you weren't lazy and just used select *. Also, here's a now mainly historical pain most devs never encounter any more: Before ORMs and the various migrators, your object properties might not have the same name as your SQL columns. Yes, it was dumb when you did it, yes, it caused loads of bugs, yes, it actually happened quite a lot.
  - Multicomp 6y ago
    > It was mainly to save time in writing code to map columns to object properties really, the sql statements themselves were trivial even if you weren't lazy and just used select *. I am running a side project and yes, writing the native SQL statement to take your object and put it in the database is not a problem, put in the parameterised values and off you go. But getting the data back from the database? Oh the horror. So much boilerplate in order to see if there are any records returned it all, if there are enough columns with the correct name for the kind of object you are making, if there is data or not in each column as appropriate for that specific column, if a given field can be coerced into being a string or an integer or a date or similar, then they're all marshaled into a dto object which is passed to the create new object validator. 800 lines of code later, and you may have an object back! Dapper appears to be the sweet spot for me, I am still writing SQL queries and still designing the SQL tables myself, no orm magic here, but it handles the actual marshalling to and from an in-memory dto object versus data in the table for me, and that is very valuable time savings.
    - mattmanser 6y ago
      Most SQL libraries handle this all for you? You really don't have to do any of that. It's perfectly fine to just 'know' that the data is going to be an int and just do `var id = rs.getInt(id")`. I'm on my phone so it's too fiddly to write my own brief example, but if you get rid of the silly comments in this you'll see you can do it all in a few lines, just very boilerplate lines: https://thedeveloperblog.com/sqlconnection https://thedeveloperblog.com/sqlconnection
jimbokun 6y ago
I would say they're not even hierarchical in many cases, but an unconstrained graph.

nicoburns 6y ago

Completely agree. For example, why isn't there functionality to define aliases for complex expressions and then reuse those through a query. Simple query-local SQL functions would be nice too. I'd love to see a "SQL-like" language that compiles down to SQL itself, much like Babel or TypeScript in the JavaScript world. I think the tricky thing is that there is no single SQL target.

pkalinowski 6y ago
> Completely agree. For example, why isn't there functionality to define aliases for complex expressions and then reuse those through a query. Simple query-local SQL functions would be nice too. What about common table expressions? Or custom defined functions? with X as ( select... ) It's widely used for analysis
- jeltz 6y ago
  CTEs can do that, but it is quite clunky for when you just want to reuse a partial per row computation.
quickthrower2 6y ago
Multi sql targets means it’s even more of an advantage to compile
jimbokun 6y ago
> I think the tricky thing is that there is no single SQL target. So the jquery of SQL, then.
scarface74 6y ago
Linq
parliament32 6y ago
>functionality to define aliases for complex expressions and then reuse those through a query What's an example of what you're trying to do? I think views, user-defined functions, routines, and select aliases would cover all the bases...