8 ms·
> A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. What's the difference? To me, an ORM is largel
by avmich 9y ago
> A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM.
What's the difference? To me, an ORM is largely a query builder.
- int_19h 9y agoIs it a good one, though?
- JamesBarney 9y agoProbably better than the one I could build by hand, with far better documentation, and greater ease in googling problems :)
- failedxyz 9y agowhile ORMs can do quite a bit of optimization, they're still general query builders and can't construct the optimal queries for your use case. if you don't know SQL, or you don't know what's going on behind the scenes, your ORM could be performing much larger queries than it really needs to, costing performance and time
- stouset 9y agoBut that's a completely orthogonal problem — the point made (way) above is that you need to understand SQL whether or not you use an ORM.
- gjjrfcbugxbhf 9y agoMost ORMs have way too write raw SQL. Furthermore someone using an ORM well will understand how their queries are mapping to SQL. Your scenario happens with people that either don't know or don't care. They will write crappy queries with any tool.
- collyw 9y agoSounds about right. I built a moderately complex application in Django at a previous workplace, using the ORM for most things, until the queries were too complex for the ORM. Another guy connected to the same database and built some graphs using PHP and SQL. Guess who had to help him write the SQL when the queries got too complex for him? The ORM user.
- jbergens 9y agoBascially all in-house built query builders I've seen has been much worse.
- emilsedgh 9y agoORM: You ask for something and you don't care _how_ its fetched. Query Builder: You build a query, just not in SQL. So you can get around SQL's limitations (like composition)
- andybak 9y ago> ORM: You ask for something and you don't care _how_ its fetched. Try this: > ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query. or possibly: > ORM: You ask for something and you accept the tradeoffs vs hand-written queries but you're content that it's the correct tradeoff for your use-case. There's plenty of places for bottlenecks to hide and premature optimization is the root of at least some evils.
- jcadam 9y ago> ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query. Ha. More like: ORM: You've just joined a project already using an ORM selected by an 'architect' that no longer works here. Everything is fine until you start testing your system with a database sufficiently populated with real-world data. You and the DBA spend the next next 6 months trying to get the damn ORM to generate performant SQL (you even call Oracle support, which promptly dispatches an "engineer" who tries to sell you on another $500k of crap you don't need that won't really address the problem). You eventually just start writing queries by hand wherever you find a bottleneck caused by the naive ORM, which you could have done at the outset, but no one who actually knew SQL well enough was on the team back then.
- paulryanrogers 9y agoIME, it should never take more than a few hours to run down why the ORM made such a query. At a minimum most RDBMS's have query logging and can explain queries. Ironically the last time I had a big ORM performance problem was Hibernate eager loading all joins. Diagnosing and fixing it didn't take more than an hour. (Though we did have a very experienced DBA at the time.) YMMV
- Animats 9y agoORMs are more useful as insert builders. Putting data from an object into the database is something of a boilerplate process. Queries vary with what you want to ask. Most of the time, you don't need all the fields, so filling up some object just because it has slots for everything is a waste of effort. Especially if it means references to multiple tables.
- egeozcan 9y agoGood ORMs have Partial<T> and lazy loading of complex properties through proxies, which can be overridden with something like .With(x => x.ComplexProperty). But of course, as the queries get more and more complex, the flexibility of the ORM syntax approaches the flexibility SQL. In the end, there are many situations one would rather just use SQL.
- majewsky 9y agoI think the best ORMs are those that just leave out the "Relational" part entirely. So... "OM"? For example, in Go, I use Gorp, which has a Select() function where you pass in the SELECT query string (plus bound values) and the target type, and it loads every result row into an object of that type. So you can have an arbitrarily complex SQL query as long as it starts with `SELECT one_table.* FROM`. That's a marvelous design. And when you have to do a query that returns results from multiple tables? Guess what, you just use the normal SQL module from the standard library.
- scarface74 9y agoWhy is it an either or? EF can do that but then you loose the benefits of a type safe language.
- oceanswave 9y agoIf type safety is so great, why isn’t sql statically type checked
- 9y ago
- petre 9y agoA query builder aids you at constructing queries, while an ORM builds queries for you, runs them and maps the output to objects. It's more sophisticated than a query builder.
- zimpenfish 9y ago> A query builder aids you at constructing queries ... that you understand and can be sure are sensible. > while an ORM builds queries for you ... that you have to hope are sensible. That's one of the biggest flaws of the ORM for me - you have limited visibility of what it's doing to your DB.
- scarface74 9y agoHow so? I can log the the sql being generated and look at the logs. Something I should be doing either way.
- zimpenfish 9y agoIf you have to log the generated SQL to understand what's happening, you're already behind the curve. And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place?
- mgkimsal 9y ago> And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place? The point may be that 98% of the queries are just fine, and you've saved time vs writing by hand, and it may be easier to read/understand for the next people to have to touch the code.
- zaphar 9y ago"saved time" at the least optional time to save time. ORM's are a maintenance burden. They obfuscate DB performance behind usually an enormous API surface. Most software work is maintenance work. Optimizing for initial deployment is shortsighted.
- akvadrako 9y agoSqlAlchemy Core vs SqlAlchemy ORM
- cosarara97 9y agoAnd doctrine DBAL vs doctrine ORM