8 ms·

> I rely a lot on SQL and in general advocate for it, but that’s too simplistic of a view IMHO. SQL makes it easy to write and read simple queries, and ridiculo

> I rely a lot on SQL and in general advocate for it, but that’s too simplistic of a view IMHO. SQL makes it easy to write and read simple queries, and ridiculously complicated and arcane to write slightly more complex logic. > > More than once I’ve been asked to help fix giant SQL queries written by a business or analytics team and that’s a terrible experience. > > Also, the tooling around SQL is often terrible and almost didn’t evolve during the past 15+ years. SQL queries from another programming language without an ORM means that you do everything by manipulating strings by hand, with zero type safety. But this is all beside the point. Yes, SQL kind of sucks. But what sucks more is having a leaky query language on top of SQL such that you have to learn the query language AND SQL. Because- let's face it- you almost always end up printing out the SQL that the damn thing generated to figure out why it's doing something weird. You almost always end up needing to drop to SQL anyway. And next year there will be a new awesome ORM/query-language that you're expected to learn all the pitfalls of.

dgellow 6y ago
It's not beside the point, it's the other face of the discussion. Building and calling SQL queries from a programming language without an ORM often sucks. SQL is not the simple language the author seems to talk about. ORMs have leaky abstractions. All of this is true.
- ragnese 6y ago
  But my view at this point in my life is that SQL is SO not a simple language, that any and all ORMs are going to be way too restrictive and leaky. There's all kinds of weird crap that SQL does when it comes to nulls, empty strings, falsey/truthy things, dates as numbers or strings, etc. And that's not even thinking about unions, stored procedures, weird types of joins, etc. My current comfort zone is if I can find a query builder that has enough static typing that it has all of the keywords of my preferred SQL flavor, has prepared statements with placeholders- mainly for safety/security, and basically returns a string when you're done. At least then I'm just dealing with SQL instead of figuring out Hibernate's arcane caching feature or why in the FUCKING FUCK JDBC will return a `0` if a column was an int value that was `null` in the result set. IT WAS NULL- GIVE ME NULL.
  - datavirtue 6y ago
    Which JDBC implementation? I can write a crappy JDBC driver on top of text files and have it mangle your data in a myriad of ways. I doubt most of the people using JDBC have to deal with what you describe because they are using a mature driver for their particular database--most often developed by the database OEM.
    - ragnese 6y ago
      Oracle MySQL's official one (Connector/J). Here's the code from the current version as of this message. It's in mysql-connector-java-8.0.23.jar. Class com.mysql.cj.jdbc.result.ResultSetImpl. Lines 814-818: @Override public int getInt(int columnIndex) throws SQLException { Integer res = getObject(columnIndex, Integer.TYPE); return res == null ? 0 : res; } Notice that the Java guys set the return type to `int` and not `Integer` in the `ResultSet` interface. So, if you call this method after retrieving a null value from a nullable column, you can either throw an exception or return something. I think it's absolutely insane to return a 0 here, but this is the way the MySQL connector has been forever. EDIT: Postgres does the same thing: https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSet.java#L2265 https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main...
  - lelanthran 6y ago
    > My current comfort zone is if I can find a query builder that has enough static typing that it has all of the keywords of my preferred SQL flavor, has prepared statements with placeholders- mainly for safety/security, and basically returns a string when you're done. Some self-promotion (shameless, I know): https://github.com/lelanthran/libsqldb/tree/v1.0.0-rc2 https://github.com/lelanthran/libsqldb/tree/v1.0.0-rc2 I'm intending to rewrite it ("the first one is always to throw away" - I put too much unnecessary functionality into it and not enough RDBMS server backends) but I've used it in a few projects (use the latest branch) and am happy with it for postgres or sqlite usage. See https://github.com/lelanthran/libsqldb/blob/v1.0.0-rc2/src/sqldb_test.c https://github.com/lelanthran/libsqldb/blob/v1.0.0-rc2/src/s... for example usage, but the basic premise is: 1. Send parameterised string to DB. 2. Get back records one at a time as an array of strings (NULLs are empty strings, as are empty strings) no matter the datatype of the column.
JeremyNT 6y ago
> And next year there will be a new awesome ORM/query-language that you're expected to learn all the pitfalls of. This may be true in some lines of work, but as a Rails developer I find it amusing. I've been happily using ActiveRecord for over a decade. I get that chasing the new shiny can be fun and look good on the resume, but the ORM shouldn't be a fad you chase that you need to swap out every couple of years. You're not wrong that sometimes the ORM gets in the way and slows you down. There are certainly times I've been frustrated by having to figure out the right incantations to satisfy the APIs of ActiveRecord or Arel. However, this tradeoff is well worth it, because these tools protect us from so many footguns present with raw query string manipulation.
- codeduck 6y ago
  > these tools protect us from so many footguns present with raw query string manipulation. cough Parler springs to mind.
- oblio 6y ago
  I just checked and Hibernate, the granddaddy of Java ORMs, is 19 years old.
- ragnese 6y ago
  There's a lot of air between "I need a query string sanitizer and/or a PreparedStatement construct" vs. "I need a full blown ORM."
eitland 6y ago
> And next year there will be a new awesome ORM/query-language that you're expected to learn all the pitfalls of. Or you can just stick with a JPA implementation (like Hibernate) which is highly performant and has been around for over a decade. Someone will probably mention poor performance and yes, if you routinely query multiple millions of rows you'll probably want to hand code som SQL (something you can easily do without ditching JPA). Otherwise, if you just happen to do the famous n+1 query, just get over it and actually learn how to use your ORM. (Sibling comment also mentions ActiveRecord. I think the canonical .Net ORM has been stable for a few years already. Not everything is a Javascript/NoSQL :-)
- ajuc 6y ago
  I've used Hibernate and JPE for several projects. My first job was moving PL/SQL + C++ software to J2EE. Recently I had a chance to work on a simple 6-months 4-developers Java project from scratch. We had to interface with legacy software through an Oracle db and I was getting ready to use Hibernate again but our team lead asked if we really want to use it or just do it cause it's the "best practice". The project was pretty simple from data POV and we were mostly fetching lots of records to be processed and bulk updating a few "status" fields, but I've seen simpler projects using Hibernate for no good reason. Using SQL directly was a breath of fresh air and I estimate it cut at least a month from our schedule.
  - eitland 6y ago
    > The project was pretty simple from data POV and we were mostly fetching lots of records to be processed and bulk updating a few "status" fields, Sounds like a perfect example for when you just want to whip up a few SQL queries, yes. I'm not against raw sql. I'm just against people presenting it as either/or and claiming that there is no gopd use case for ORMs.
- ragnese 6y ago
  For me, the issue isn't performance or even n+1 (though I do think it's easily to accidentally do stuff like that, even if you "know what you're doing"). You're probably a Hibernate expert. I'm not. I used it once, several years ago. I'm a polyglot, Jack of all trades, developer- I know lots of languages reasonably well, I know several high profile frameworks well enough that I at least know what to look up when I need to, etc. Hibernate has a steeper learning curve than you might remember if you've been using it extensively or for a long time. I can't give specific examples, because it's been some time, but I just vaguely remember it having some of the same rough edges as other annotation-heavy Java frameworks- some of the annotations didn't actually work together the way I expected, it's mostly only checkable at runtime, remembering to use `@Column(nullable = false)` instead of just `@NotNull` (I just looked that one up because I remembered it), etc. I also vaguely remember some issue where I had accidentally defined a class field as `int` when the column in the database was nullable. Then, instead of exploding when the ORM pulled a null out, I think it just magically converted the null to 0 and set my field to 0. Took a while to figure out why we had such strange results for some data. I'm not totally positive if I'm remembering that right, though. You can accuse me of laziness for not wanting to learn the intricacies, shortcomings, opinions, and foot-guns of an ORM like Hibernate (and keep up to date with it even when I'm not doing Java this month/year). But I don't see it that way. I see it as being defensive. There is no way I'm going to be able to remember Hibernate's weird parts, AND Symfony's weird parts, AND ActiveRecord's weird parts, AND whoever else's weird parts. If I can just put the thinnest possible layer over SQL, map the result columns to specific types, and then build my object(s) from those "by hand", I'm perfectly happy. And I don't believe that my dev speed is slowed down by a meaningful amount. Will that part of the code take me longer to write compared to an expert in Hibernate? Probably. But that's probably not a significant amount of time relative to the whole project. And it saves me a lot of ramp up and debugging time. This is what I've convinced myself of, anyway. Plus there's basically always an inefficiency in modeling an entity class per table. What if one of my domain types is constructed by taking a subset of multiple tables' columns joined together? Do I have to define an Entity class for every subset of columns I plan to use? That would be tedious, and POSSIBLY even more so than just writing some SQL and getting a typed tuple out per result row. Or can Hibernate et al understand pulling partial entities somehow? Is this easy and simple or does it require me to spend a couple of hours reading documentation and figuring out tricky annotations? Everyone talks about these 80% rules: ORMs make 80% of stuff trivial and then you just break out the SQL when you need it. To me, that's a losing proposition. That 80% was already trivial to me. Writing a basic JOIN is trivial. It's the more complex stuff that I'm worried about and it just doesn't seem like ORMs do much for us there.
geocar 6y ago
> But what sucks more is having a leaky query language on top of SQL such that you have to learn the query language AND SQL. I think this speaks to the sad state of ORMs, but not the value of SQL. I think SQL is rubbish: The two biggest things I hate about SQL are (1) that it's unstructured, so getting data into or out of SQL involves strings, (2) the syntax of those strings. Seriously. I really don't want to program in COBOL either. ORMs are better on both these points, but as you point out, they are rife with pitfalls and I'll add I find their APIs absolutely reek of the SQL implementation they hide. I certainly want for better. What we really need are languages to be better at dealing with data (especially data that is backed by permanent storage or distributed across multiple machines), but it's difficult to do this without (basically) creating a whole new language, and I think the decision to bring a new language into the world isn't one to be taken lightly. Especially when you're asking people to trust their data to it. Bad software just gets turned off and on, and it resets, but people don't put up with a bad database for very long.
- ragnese 6y ago
  Right. Agreed. I'm definitely not trying to say anything positive about SQL. ORMs are a doomed proposition. There is simply no way to write a database-agnostic ORM API while also taking full advantage of the underlying tech. Very simple examples include whether to return rows that have been inserted. In Postgres, you can do that in a single query. In MySQL, it's two queries because INSERT does not return the inserted row. So if I'm using MySQL and a popular "agnostic" ORM I have to realize that I'm (probably) doing an extra query on every insert, even if I don't use the result of the second query. Then, of course, some of these ORMs don't even implement any of the database-specific features, like MySQL's fulltext search, because they're catering to the least common denominator.
  - hodgesrm 6y ago
    > ORMs are a doomed proposition. There is simply no way to write a database-agnostic ORM API while also taking full advantage of the underlying tech. Not every application needs to take full advantage of the underlying DBMS. Sometimes it's just a place to put data and not lose it, e.g. a key-value store with transactions. ORMs handle that use case perfectly well. (I'm not a fan of ORMs personally, but they seem to work fine for logs of applications.)

dgellow 6y ago

It's not beside the point, it's the other face of the discussion. Building and calling SQL queries from a programming language without an ORM often sucks. SQL is not the simple language the author seems to talk about. ORMs have leaky abstractions. All of this is true.

ragnese 6y ago
But my view at this point in my life is that SQL is SO not a simple language, that any and all ORMs are going to be way too restrictive and leaky. There's all kinds of weird crap that SQL does when it comes to nulls, empty strings, falsey/truthy things, dates as numbers or strings, etc. And that's not even thinking about unions, stored procedures, weird types of joins, etc. My current comfort zone is if I can find a query builder that has enough static typing that it has all of the keywords of my preferred SQL flavor, has prepared statements with placeholders- mainly for safety/security, and basically returns a string when you're done. At least then I'm just dealing with SQL instead of figuring out Hibernate's arcane caching feature or why in the FUCKING FUCK JDBC will return a `0` if a column was an int value that was `null` in the result set. IT WAS NULL- GIVE ME NULL.
- datavirtue 6y ago
  Which JDBC implementation? I can write a crappy JDBC driver on top of text files and have it mangle your data in a myriad of ways. I doubt most of the people using JDBC have to deal with what you describe because they are using a mature driver for their particular database--most often developed by the database OEM.
  - ragnese 6y ago
    Oracle MySQL's official one (Connector/J). Here's the code from the current version as of this message. It's in mysql-connector-java-8.0.23.jar. Class com.mysql.cj.jdbc.result.ResultSetImpl. Lines 814-818: @Override public int getInt(int columnIndex) throws SQLException { Integer res = getObject(columnIndex, Integer.TYPE); return res == null ? 0 : res; } Notice that the Java guys set the return type to `int` and not `Integer` in the `ResultSet` interface. So, if you call this method after retrieving a null value from a nullable column, you can either throw an exception or return something. I think it's absolutely insane to return a 0 here, but this is the way the MySQL connector has been forever. EDIT: Postgres does the same thing: https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSet.java#L2265 https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main...
- lelanthran 6y ago
  > My current comfort zone is if I can find a query builder that has enough static typing that it has all of the keywords of my preferred SQL flavor, has prepared statements with placeholders- mainly for safety/security, and basically returns a string when you're done. Some self-promotion (shameless, I know): https://github.com/lelanthran/libsqldb/tree/v1.0.0-rc2 https://github.com/lelanthran/libsqldb/tree/v1.0.0-rc2 I'm intending to rewrite it ("the first one is always to throw away" - I put too much unnecessary functionality into it and not enough RDBMS server backends) but I've used it in a few projects (use the latest branch) and am happy with it for postgres or sqlite usage. See https://github.com/lelanthran/libsqldb/blob/v1.0.0-rc2/src/sqldb_test.c https://github.com/lelanthran/libsqldb/blob/v1.0.0-rc2/src/s... for example usage, but the basic premise is: 1. Send parameterised string to DB. 2. Get back records one at a time as an array of strings (NULLs are empty strings, as are empty strings) no matter the datatype of the column.

JeremyNT 6y ago

> And next year there will be a new awesome ORM/query-language that you're expected to learn all the pitfalls of. This may be true in some lines of work, but as a Rails developer I find it amusing. I've been happily using ActiveRecord for over a decade. I get that chasing the new shiny can be fun and look good on the resume, but the ORM shouldn't be a fad you chase that you need to swap out every couple of years. You're not wrong that sometimes the ORM gets in the way and slows you down. There are certainly times I've been frustrated by having to figure out the right incantations to satisfy the APIs of ActiveRecord or Arel. However, this tradeoff is well worth it, because these tools protect us from so many footguns present with raw query string manipulation.

codeduck 6y ago
> these tools protect us from so many footguns present with raw query string manipulation. cough Parler springs to mind.
oblio 6y ago
I just checked and Hibernate, the granddaddy of Java ORMs, is 19 years old.
ragnese 6y ago
There's a lot of air between "I need a query string sanitizer and/or a PreparedStatement construct" vs. "I need a full blown ORM."

eitland 6y ago

> And next year there will be a new awesome ORM/query-language that you're expected to learn all the pitfalls of. Or you can just stick with a JPA implementation (like Hibernate) which is highly performant and has been around for over a decade. Someone will probably mention poor performance and yes, if you routinely query multiple millions of rows you'll probably want to hand code som SQL (something you can easily do without ditching JPA). Otherwise, if you just happen to do the famous n+1 query, just get over it and actually learn how to use your ORM. (Sibling comment also mentions ActiveRecord. I think the canonical .Net ORM has been stable for a few years already. Not everything is a Javascript/NoSQL :-)

ajuc 6y ago
I've used Hibernate and JPE for several projects. My first job was moving PL/SQL + C++ software to J2EE. Recently I had a chance to work on a simple 6-months 4-developers Java project from scratch. We had to interface with legacy software through an Oracle db and I was getting ready to use Hibernate again but our team lead asked if we really want to use it or just do it cause it's the "best practice". The project was pretty simple from data POV and we were mostly fetching lots of records to be processed and bulk updating a few "status" fields, but I've seen simpler projects using Hibernate for no good reason. Using SQL directly was a breath of fresh air and I estimate it cut at least a month from our schedule.
- eitland 6y ago
  > The project was pretty simple from data POV and we were mostly fetching lots of records to be processed and bulk updating a few "status" fields, Sounds like a perfect example for when you just want to whip up a few SQL queries, yes. I'm not against raw sql. I'm just against people presenting it as either/or and claiming that there is no gopd use case for ORMs.
ragnese 6y ago
For me, the issue isn't performance or even n+1 (though I do think it's easily to accidentally do stuff like that, even if you "know what you're doing"). You're probably a Hibernate expert. I'm not. I used it once, several years ago. I'm a polyglot, Jack of all trades, developer- I know lots of languages reasonably well, I know several high profile frameworks well enough that I at least know what to look up when I need to, etc. Hibernate has a steeper learning curve than you might remember if you've been using it extensively or for a long time. I can't give specific examples, because it's been some time, but I just vaguely remember it having some of the same rough edges as other annotation-heavy Java frameworks- some of the annotations didn't actually work together the way I expected, it's mostly only checkable at runtime, remembering to use `@Column(nullable = false)` instead of just `@NotNull` (I just looked that one up because I remembered it), etc. I also vaguely remember some issue where I had accidentally defined a class field as `int` when the column in the database was nullable. Then, instead of exploding when the ORM pulled a null out, I think it just magically converted the null to 0 and set my field to 0. Took a while to figure out why we had such strange results for some data. I'm not totally positive if I'm remembering that right, though. You can accuse me of laziness for not wanting to learn the intricacies, shortcomings, opinions, and foot-guns of an ORM like Hibernate (and keep up to date with it even when I'm not doing Java this month/year). But I don't see it that way. I see it as being defensive. There is no way I'm going to be able to remember Hibernate's weird parts, AND Symfony's weird parts, AND ActiveRecord's weird parts, AND whoever else's weird parts. If I can just put the thinnest possible layer over SQL, map the result columns to specific types, and then build my object(s) from those "by hand", I'm perfectly happy. And I don't believe that my dev speed is slowed down by a meaningful amount. Will that part of the code take me longer to write compared to an expert in Hibernate? Probably. But that's probably not a significant amount of time relative to the whole project. And it saves me a lot of ramp up and debugging time. This is what I've convinced myself of, anyway. Plus there's basically always an inefficiency in modeling an entity class per table. What if one of my domain types is constructed by taking a subset of multiple tables' columns joined together? Do I have to define an Entity class for every subset of columns I plan to use? That would be tedious, and POSSIBLY even more so than just writing some SQL and getting a typed tuple out per result row. Or can Hibernate et al understand pulling partial entities somehow? Is this easy and simple or does it require me to spend a couple of hours reading documentation and figuring out tricky annotations? Everyone talks about these 80% rules: ORMs make 80% of stuff trivial and then you just break out the SQL when you need it. To me, that's a losing proposition. That 80% was already trivial to me. Writing a basic JOIN is trivial. It's the more complex stuff that I'm worried about and it just doesn't seem like ORMs do much for us there.

geocar 6y ago

> But what sucks more is having a leaky query language on top of SQL such that you have to learn the query language AND SQL. I think this speaks to the sad state of ORMs, but not the value of SQL. I think SQL is rubbish: The two biggest things I hate about SQL are (1) that it's unstructured, so getting data into or out of SQL involves strings, (2) the syntax of those strings. Seriously. I really don't want to program in COBOL either. ORMs are better on both these points, but as you point out, they are rife with pitfalls and I'll add I find their APIs absolutely reek of the SQL implementation they hide. I certainly want for better. What we really need are languages to be better at dealing with data (especially data that is backed by permanent storage or distributed across multiple machines), but it's difficult to do this without (basically) creating a whole new language, and I think the decision to bring a new language into the world isn't one to be taken lightly. Especially when you're asking people to trust their data to it. Bad software just gets turned off and on, and it resets, but people don't put up with a bad database for very long.

ragnese 6y ago
Right. Agreed. I'm definitely not trying to say anything positive about SQL. ORMs are a doomed proposition. There is simply no way to write a database-agnostic ORM API while also taking full advantage of the underlying tech. Very simple examples include whether to return rows that have been inserted. In Postgres, you can do that in a single query. In MySQL, it's two queries because INSERT does not return the inserted row. So if I'm using MySQL and a popular "agnostic" ORM I have to realize that I'm (probably) doing an extra query on every insert, even if I don't use the result of the second query. Then, of course, some of these ORMs don't even implement any of the database-specific features, like MySQL's fulltext search, because they're catering to the least common denominator.
- hodgesrm 6y ago
  > ORMs are a doomed proposition. There is simply no way to write a database-agnostic ORM API while also taking full advantage of the underlying tech. Not every application needs to take full advantage of the underlying DBMS. Sometimes it's just a place to put data and not lose it, e.g. a key-value store with transactions. ORMs handle that use case perfectly well. (I'm not a fan of ORMs personally, but they seem to work fine for logs of applications.)