4 ms·
> learn SQL properly. It's not that hard. Focus on it for a few weeks intensely and you've mastered it for life. Exactly the same is true of ORMs. I find most
by lmm 4y ago
> learn SQL properly. It's not that hard. Focus on it for a few weeks intensely and you've mastered it for life.
Exactly the same is true of ORMs. I find most of the people who advocate "just use SQL" have a bizarre aversion to applying the same learning effort to their ORM.
> I've had weekends ruined troubleshooting my "highly productive ORM layer" that nuked a production database. Whilst functionally speaking my ORM code was in no way incorrect. I'm talking differences of a thousand fold in query load depending on how one expresses the ORM calls.
That kind of problem happens with regular SQL all the time, and the tools to test/investigate it are less available.
- smallnamespace 4y ago> have a bizarre aversion to applying the same learning effort to their ORM. SQL is a lingua franca while an ORM is specific to a stack. If you know that you will always, say, be using SQLAlchemy on Python, then learning it well might be a good investment, but I don't find learning multiple ORMs to be a good time investment over just learning SQL. Most every stack has good, lightweight query builders.
- deleted 4y ago[deleted]
- jjoonathan 4y ago> 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 agoHow 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 agoCan 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 agoDepends. 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
- webnrrd2k 4y agoYes, both SQL and ORMs can give you problems, but I have seen far more serious, hard to diagnose trouble from ORMs than straight SQL. Learning straight SQL is far more transferable across databases than learning an ORM. There are reasons to have ORMs, for e.g. to keep a large code base consistent. It's useful for keeping relatively simple queries consistent across an ERP system, stuff like that. However, learning SQL is going to have a far higher payoff across your career.
- epgui 4y agoI’m not sure I understand the consistency argument… Do you have an example, even if it’s just a silly one?
- webnrrd2k 4y agoThe example is from a python ERP that I modified a few years ago. All the db interaction was done through an ORM and I thought that it was an OK strategy for keeping the SQL consistent and manageable. There were other problems with the ORM approach -- mostly that it ran tens or hundreds of extra queries for relatively straightforward SQL queries. But the software was only used by a few people at a time, so the performance wasn't that big of a deal.
- lmm 4y ago> Learning straight SQL is far more transferable across databases than learning an ORM. Not my experience. For example a recursive/tree query usually has one way to do it in an ORM, but three or four different ways to express it in SQL depending on the database, some of which seem like they're wilfully screwing you over (one popular database requires you to only write "WITH" and won't recognise "WITH RECURSIVE"; another requires you to write "WITH RECURSIVE", even though the error message it gives you when you only write "WITH" tells you that it knew exactly what you meant).
- dahwolf 4y ago"That kind of problem happens with regular SQL all the time" Well, no, it doesn't. I told you to learn SQL properly. When you do, you know what a query plan is. Which is something you'd rarely need if you know how to design a database and its indices. All of this would be covered in every beginner's book.
- lmm 4y agoI'm aware of what a query plan is. I can see that the query plan is wrong. There's nothing I can do about it, since I took the standard advice on HN and used PostgreSQL as my database. Meanwhile if you'd applied the same "learning it properly" standard to your ORM you would never have had the problem you described.