4 ms·

> 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 sta

by 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.)
  - ragnese 6y ago
    > 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. Okay, yes... But an ORM is a TON of baggage for such a trivial use case, is it not? And these data have no interesting relationships to each other at all? Not to mention that transactions work differently across DBMSs, too. So what is your ORM's default? Is it the same default as your DBMS? Does it know that MySQL doesn't support nested transactions, but Postgres does? Do you (metaphorical "you")? Do you fully understand what happens if you try to nest transactions in your ORM? Also, do you understand how your ORM is going to convert data types and which ones are supported? Remember that JavaScript numbers don't even have the full range of a MySQL/Postgres BIGINT? Probably your programming language doesn't even support DECIMAL types natively- is your ORM going to just cast a DECIMAL to a float for you, or give you an error? Which is worse (I have my opinion)? What about Dates and Times? How will your ORM handle timezones? How does your DB handle timezones? ORMs don't seem to help with any of this. In fact, it seems like it only complicates things even more because you have to figure out how your DBMS works AND what your ORM is going to do about it.
    - hodgesrm 6y ago
      Sometimes it's the lesser of two evils. I'm working on ClickHouse SQLAlchemy driver support to enable better integration with Superset. Superset talks to a bunch of backends and chose SQLAlchemy as the API. It has many of the problems you mention, though since it's read-only there are perhaps fewer of them. Superset does workarounds on top, but they don't have to worry about basic stuff like listing table metadata, distinguishing between tables and views, etc. Using an ORM seems like a reasonable choice for multi-platform use cases or at least some of them. The alternative would be to implement something more or less from scratch.
folmar 6y ago
> (1) that it's unstructured, so getting data into or out of SQL involves strings Structured types are in SQL since 1999.
- alexvoda 6y ago
  Also, all languages I used support parametrized queries so you do not have to mix data and query strings. Not converting data to strings is not an argument for ORMs, it's an argument for parametrized queries. People should really look into JOOQ to see what a SQL friendly ORM looks like.
  - gregopet 6y ago
    Another vote for jOOQ, it's just so good. You can really see it's made by a true SQL fan.
  - geocar 6y ago
    > Also, all languages I used support parametrized queries so you do not have to mix data and query strings. Not converting data to strings is not an argument for ORMs, it's an argument for parametrized queries. Your "parameterized" queries are still serialized strings. Many language bindings for SQL interfaces don't have good support for serializing data types, so you will often see things like ?::datetime in the template string. Some don't have the ability to parameterize column names (or have subtle bugs) either. > People should really look into JOOQ to see what a SQL friendly ORM looks like. I've never heard of JOOQ. Do you know of a good introduction?
    - lukaseder 6y ago
      > Your "parameterized" queries are still serialized strings. Many language bindings for SQL interfaces don't have good support for serializing data types, so you will often see things like ?::datetime in the template string. How else would you do it, short of a binary query format?
im3w1l 6y ago
I think the first step to better languages is to go back to basics. SQL queries are turned into imperative query plans [0]. Expose that interface. And polish it as nice as possible. When that is done, that's when we can start building alternate abstractions on top of it. [0] https://en.wikipedia.org/wiki/Query_plan https://en.wikipedia.org/wiki/Query_plan

by geocar 6y ago

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.)
- ragnese 6y ago
  > 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. Okay, yes... But an ORM is a TON of baggage for such a trivial use case, is it not? And these data have no interesting relationships to each other at all? Not to mention that transactions work differently across DBMSs, too. So what is your ORM's default? Is it the same default as your DBMS? Does it know that MySQL doesn't support nested transactions, but Postgres does? Do you (metaphorical "you")? Do you fully understand what happens if you try to nest transactions in your ORM? Also, do you understand how your ORM is going to convert data types and which ones are supported? Remember that JavaScript numbers don't even have the full range of a MySQL/Postgres BIGINT? Probably your programming language doesn't even support DECIMAL types natively- is your ORM going to just cast a DECIMAL to a float for you, or give you an error? Which is worse (I have my opinion)? What about Dates and Times? How will your ORM handle timezones? How does your DB handle timezones? ORMs don't seem to help with any of this. In fact, it seems like it only complicates things even more because you have to figure out how your DBMS works AND what your ORM is going to do about it.
  - hodgesrm 6y ago
    Sometimes it's the lesser of two evils. I'm working on ClickHouse SQLAlchemy driver support to enable better integration with Superset. Superset talks to a bunch of backends and chose SQLAlchemy as the API. It has many of the problems you mention, though since it's read-only there are perhaps fewer of them. Superset does workarounds on top, but they don't have to worry about basic stuff like listing table metadata, distinguishing between tables and views, etc. Using an ORM seems like a reasonable choice for multi-platform use cases or at least some of them. The alternative would be to implement something more or less from scratch.

folmar 6y ago

> (1) that it's unstructured, so getting data into or out of SQL involves strings Structured types are in SQL since 1999.

alexvoda 6y ago
Also, all languages I used support parametrized queries so you do not have to mix data and query strings. Not converting data to strings is not an argument for ORMs, it's an argument for parametrized queries. People should really look into JOOQ to see what a SQL friendly ORM looks like.
- gregopet 6y ago
  Another vote for jOOQ, it's just so good. You can really see it's made by a true SQL fan.
- geocar 6y ago
  > Also, all languages I used support parametrized queries so you do not have to mix data and query strings. Not converting data to strings is not an argument for ORMs, it's an argument for parametrized queries. Your "parameterized" queries are still serialized strings. Many language bindings for SQL interfaces don't have good support for serializing data types, so you will often see things like ?::datetime in the template string. Some don't have the ability to parameterize column names (or have subtle bugs) either. > People should really look into JOOQ to see what a SQL friendly ORM looks like. I've never heard of JOOQ. Do you know of a good introduction?
  - lukaseder 6y ago
    > Your "parameterized" queries are still serialized strings. Many language bindings for SQL interfaces don't have good support for serializing data types, so you will often see things like ?::datetime in the template string. How else would you do it, short of a binary query format?

im3w1l 6y ago

I think the first step to better languages is to go back to basics. SQL queries are turned into imperative query plans [0]. Expose that interface. And polish it as nice as possible. When that is done, that's when we can start building alternate abstractions on top of it. [0] https://en.wikipedia.org/wiki/Query_plan https://en.wikipedia.org/wiki/Query_plan