4 ms·

> bizarre aversion to applying the same learning effort to their ORM Oh, sure, instead of just learning SQL I'll just learn SQL, the ORM, the ORM's weird edge

> bizarre aversion to applying the same learning effort to their ORM Oh, sure, instead of just learning SQL I'll just learn SQL, the ORM, the ORM's weird edge case features that actually support the SQL I want, the undocumented ORM internals that prevent the good query from actually being generated, and then I'll commit a patch to the open source project to fix the undocumented internals and shepherd a custom dependency for 6 months while it gets into a numbered release. Then I'll do it all over again when I switch stacks and have to learn a completely new ORM. Or I could just use SQL.

ttymck 4y ago
How do I map my complex join into a list of structs with embedded associations? I'd probably need to write a good bit of code just to unmarshal the data, right? How are you doing this every time you write a new query?
- epgui 4y ago
  Can you give an example?
lmm 4y ago
> the ORM, the ORM's weird edge case features that actually support the SQL I want, the undocumented ORM internals that prevent the good query from actually being generated ORMs are much less bad than SQL at that, IME. If the ORM generated a particular query there is usually documentation for why, often an option you can change. If the SQL engine decided not to use the right index for this query... tough, there's literally nothing you can do.
- jjoonathan 4y ago
  "SQL is worse" is a nonsensical comparison. The problems of ORMs add to the problems of SQL and compound the problems of SQL, they do not substitute the problems of SQL. I'm glad you found an ORM you like, but it seems we have very different experiences with ORMs.
  - lmm 4y ago
    Depends. Sometimes the ORM knows about the pitfalls and works around them automatically. Same as how using a compile-to-JS language can often end up being much nicer than writing javascript yourself.
- KronisLV 4y ago
  > If the SQL engine decided not to use the right index for this query... tough, there's literally nothing you can do. MySQL Index Hints: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html https://dev.mysql.com/doc/refman/8.0/en/index-hints.html MariaDB Index Hints: https://mariadb.com/kb/en/use-index/ https://mariadb.com/kb/en/use-index/ PostgreSQL: No. More information: https://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index https://stackoverflow.com/questions/309786/how-do-i-force-po... SQL Server INDEX hint: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16#g-use-index https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr... Oracle INDEX Hint: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50405 https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_e... Of course, knowing exactly what is wrong and what hint you need to use (whether the issue is the index in particular or something else) is also a bit of work to figure out. I recall working on a project that had Oracle as the RDBMS of choice and there was this one query that took approx. 45 minutes to execute, which wasn't acceptable. Merely a SELECT that JOINed a few tables and checked the data with some EXISTS constructions and such. If memory serves me right, looking through the AWR reports and adding a single NO_EXPAND hint made the query execute in not 45 minutes but around 3 seconds. Of course, ORMs don't necessarily solve the aforementioned types of problems either, since it's still SQL statements being executed under the hood and figuring out how to add hints to those might be a bit more challenging. I've had cases where you need to drop down to native query level when using ORMs for particular queries, because there were no elegant ways of doing it otherwise (apart from creating a DB view and mapping against that in some cases, for read only access). I think both using ORMs and using SQL directly suck, just in their own unique ways. That said, I wouldn't outright dismiss either: depending on what you're doing, one or the other is going to be a good enough tool for the job. You occasionally also see some pretty interesting projects in the ORM space, which is nice. For example, jOOQ allows you to use a type safe fluent API for constructing your queries: https://www.jooq.org/ https://www.jooq.org/ Oh and MyBatis let's you generate your own SQL dynamically, which is an interesting approach: https://mybatis.org/mybatis-3/dynamic-sql.html https://mybatis.org/mybatis-3/dynamic-sql.html
- skeeter2020 4y ago
  I would always use an ORM over raw SQL but this doesn't make any sense, your ORM is still going to run a query on the same DB engine. It is much harder to optimize an ORM query, but if you're only writing very simple operations you don't typically need to. The power of ORMs (at least light weight ones) is compile time types and parameters by default.

ttymck 4y ago

How do I map my complex join into a list of structs with embedded associations? I'd probably need to write a good bit of code just to unmarshal the data, right? How are you doing this every time you write a new query?

epgui 4y ago
Can you give an example?

lmm 4y ago

> the ORM, the ORM's weird edge case features that actually support the SQL I want, the undocumented ORM internals that prevent the good query from actually being generated ORMs are much less bad than SQL at that, IME. If the ORM generated a particular query there is usually documentation for why, often an option you can change. If the SQL engine decided not to use the right index for this query... tough, there's literally nothing you can do.

jjoonathan 4y ago
"SQL is worse" is a nonsensical comparison. The problems of ORMs add to the problems of SQL and compound the problems of SQL, they do not substitute the problems of SQL. I'm glad you found an ORM you like, but it seems we have very different experiences with ORMs.
- lmm 4y ago
  Depends. Sometimes the ORM knows about the pitfalls and works around them automatically. Same as how using a compile-to-JS language can often end up being much nicer than writing javascript yourself.
KronisLV 4y ago
> If the SQL engine decided not to use the right index for this query... tough, there's literally nothing you can do. MySQL Index Hints: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html https://dev.mysql.com/doc/refman/8.0/en/index-hints.html MariaDB Index Hints: https://mariadb.com/kb/en/use-index/ https://mariadb.com/kb/en/use-index/ PostgreSQL: No. More information: https://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index https://stackoverflow.com/questions/309786/how-do-i-force-po... SQL Server INDEX hint: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16#g-use-index https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr... Oracle INDEX Hint: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50405 https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_e... Of course, knowing exactly what is wrong and what hint you need to use (whether the issue is the index in particular or something else) is also a bit of work to figure out. I recall working on a project that had Oracle as the RDBMS of choice and there was this one query that took approx. 45 minutes to execute, which wasn't acceptable. Merely a SELECT that JOINed a few tables and checked the data with some EXISTS constructions and such. If memory serves me right, looking through the AWR reports and adding a single NO_EXPAND hint made the query execute in not 45 minutes but around 3 seconds. Of course, ORMs don't necessarily solve the aforementioned types of problems either, since it's still SQL statements being executed under the hood and figuring out how to add hints to those might be a bit more challenging. I've had cases where you need to drop down to native query level when using ORMs for particular queries, because there were no elegant ways of doing it otherwise (apart from creating a DB view and mapping against that in some cases, for read only access). I think both using ORMs and using SQL directly suck, just in their own unique ways. That said, I wouldn't outright dismiss either: depending on what you're doing, one or the other is going to be a good enough tool for the job. You occasionally also see some pretty interesting projects in the ORM space, which is nice. For example, jOOQ allows you to use a type safe fluent API for constructing your queries: https://www.jooq.org/ https://www.jooq.org/ Oh and MyBatis let's you generate your own SQL dynamically, which is an interesting approach: https://mybatis.org/mybatis-3/dynamic-sql.html https://mybatis.org/mybatis-3/dynamic-sql.html
skeeter2020 4y ago
I would always use an ORM over raw SQL but this doesn't make any sense, your ORM is still going to run a query on the same DB engine. It is much harder to optimize an ORM query, but if you're only writing very simple operations you don't typically need to. The power of ORMs (at least light weight ones) is compile time types and parameters by default.