7 ms·

The argument against ORMs aren't that they are a leaky abstraction (although they most definitely are). The argument is although they appear to offer you value

by icxa 7y ago

The argument against ORMs aren't that they are a leaky abstraction (although they most definitely are). The argument is although they appear to offer you value up front, they cost you much more down the road. The moment you have a hot path query that needs optimizing, you are dropping down to your ORM's "raw sql" mode. Then you do it again. Then again. Then you are ripping out the ORM and spending cycles refactoring it out of your code and replacing it with simpler abstractions. I always find the people who don't believe writing raw SQL is preferable to using an ORM is usually down to a lack of experience or because they have been coerced to use ORMs by their "enterprise grade" language (usually your C# developers of the world, no offense to you all but if every time you look up examples of data operations it's dealing with EntityFramework you are probably going to wind up with a lot of devs using EntityFramework). The ORMers, as I call them, don't have confidence in their own SQL ability, and they haven't experienced the aforementioned situation enough times to realize you are better off starting with SQL to begin with. The realization is, if I could summarize it: yes all abstractions are at least somewhat leaky, so you are better to use simpler ones than complex ones (and if you need something more complex, compose it from simpler ones)

jtdev 7y ago
This is reflective of my experience with the ORM zealots as well... SQL is already an abstraction, and it happens to be one of the most well thought out, battle tested abstractions we have in the software world... it drives me nuts when junior devs and devs with poor SQL abilities want to pollute a codebase with EF, Hibernate, etc. without understanding if it’s necessary or even helpful beyond simply allowing the developer to continue onward without spending time becoming proficient in SQL. That said, there are scenarios where an ORM is helpful, but I personally find those occasions to be the exception.
- ajross 7y ago
  > SQL is already an abstraction Still a leaky one, though. Sure, the syntax likes to pretend that you "just describe what you want", but in practice getting queries on large data sets to perform well involves layers of optimization, all the way down to deciding on how data is partitioned between storage backends and what the on-disk format of it and its indexes needs to be. In fact I view SQL as a somewhat thin abstraction, with a bunch of unavoidable (or at least unavoided) holes. Maybe that's part of the reason it's been so successful.
asdkhadsj 7y ago
I'd not mind writing raw SQL if it was anything other than a big, non-typed blob of crap as far as my language is concerned. I want compile time safety in my SQL. Tbh, I'm surprised that raw SQL folks don't promote some type of non-ORM but also compile-time type safe SQL implementation. The runtime-ness of SQL strings have always blown me away. I imagine it would be pretty easy too. Move SQL out of the program language (ie, into files). Verify the syntax. Compare the SQL to the db to ensure validity. Bam, compile time verified SQL. Though, I've never used a setup like that, as that is what my ORM does, just in-language.
- deleted 7y ago
  [deleted]
- jmull 7y ago
  Your compiler doesn’t — and can’t — know what’s in your database. To the extent you create a system that requires this constraint, you’ve created a brittle (and soon to be broken) system. (I suppose there may be some case where all the data is all known up-front and will always be updated in lock-step with the consuming code/services but that’s rare in my experience.)
  - asdkhadsj 7y ago
    > Your compiler doesn’t — and can’t — know what’s in your database. At compile time, yes it can. Not inherently the compiler itself, but at compile time the process can fail to build if your models do not match the schema in the DB. Diesel, for example, keeps the schema in code and (optionally) compares it to that of the DB. Ensuring that at compile to your code matches the schema of the DB. That of course doesn't handle changes to the schema post-build, but I hope no one is ad-hoc modifying their DB :) edit: And this becomes even more of a local, isolated solution if your ORM is also managing your migrations. Which, in the case of Diesel, it also (optionally) does.
    - jmull 7y ago
      Maybe I should have added: it doesn't know what's in your database at runtime. It's not you'll be ad hoc modifying your DB. It's that your schema and your application-side entities will change over time (continuously, during development, and somewhere between continuously and periodically in production, depending on how you release). Generally you won't be able to guarantee that they are updated in lock-step, or you won't want to pay the price to ensure that they always are.
maehwasu 7y ago
Another way of saying it: database abstractions should be specific to your codebase, rather than use generalized patterns.
ken 7y ago
> The argument against ORMs aren't that they are a leaky abstraction (although they most definitely are). I'm not using an "OO" language these days, so I don't really have a horse in this race, but: isn't the problem you mention here completely explained by the leakiness? Replace "ORM" with "compiler" and "SQL" with "assembly language", and this is exactly the argument I heard back in the 1980's about why not to use a compiler, or in the 1990's about why not to use a GC. Nobody I know is writing assembly language at all any more. It sucks at composition, so even though it's better at many tactical micro-optimizations, we've improved compilers to the point that we can use them all the time, because it helps productivity massively. > and if you need something more complex, compose it from simpler ones Composition is the killer feature in all of software building, and the fatal flaw of assembly, and SQL. We use higher-level languages for their composition abilities, even when they are less efficient. When their inefficiencies make them unusable, we make them smarter, even when it means designing new languages with 100+ keywords (ugh). If the major complaint against ORMs is that they aren't good enough to make the value worth it in all cases, well, yeah, I've seen that phase of literally every other technology I'm using today. This, too, will pass.
- mntmoss 7y ago
  Comparing SQL - a 4GL - to assembly language seems disingenuous at best, to the point where the Blub paradox applies. (And lo and behold, just four days ago you compared SQL to Lisp on the basis of incompatible implementation!) Unlike assembly, which is famously redundant, SQL doesn't have much to abstract away, and the majority of counterexamples are covered by macros. The things that it really can't abstract are the implementation details that you want to care about when you optimize. Some parts could be a little less clunky, in the same way that the C language could be a little less clunky, but the successful track record of SQL over decades speaks volumes: it's not the stuff going on inside the database causing people the most grief, it's the programming goop that gets attached to it.
  - srean 7y ago
    He does have a point about composability though. Wonder how, things would have turned out had prolog become the default query language.
deleted 7y ago
[deleted]
0xDEFC0DE 7y ago
By now, we should be able to transition between ORM and raw SQL code without anyone batting an eye. It's not hard at all. You can't convince me people are actually worried about an extra ORM dependency these days when we have node_modules. I would much, much rather junior devs use an ORM to start simply to prevent SQL injection while they skill up on SQL itself. But there's not many companies who will hire you and give you a month to learn SQL from the ground-up and do nothing else (learning SQL is NOT like learning another compiled/interpreted language -- there's new concepts plus the DBMS's own language idiosyncrasies which you're definitely using in prod).
Smithalicious 7y ago
Dropping down to a lower level of abstraction from the get go because you might need that level of control for performance reasons in the future sounds like a typical case of premature optimization to me. Of course any sufficiently complex system is eventually going to need specifically optimized code at a lower level of abstraction in some places (usually then wrapped in an abstraction of your own)but that should always be done on a case by case basis as necessary.
- icxa 7y ago
  > Dropping down to a lower level of abstraction from the get go because you might need that level of control for performance reasons in the future sounds like a typical case of premature optimization to me. On the flip side, I can make use of the code generation tools i have compiled (some myself, some others) and write code just as fast, if not faster, using raw sql and simple data structures than I did when I used ORMs. There are infinitely many possibilities in between: - A developer who is experienced in modeling and sql will deliver working software faster than a developer who might be experienced, but not with modeling and not familiar with a particular ORM or a particular version of that ORM. We could continue ad nauseam here. The main point: if all things are equal, meaning you have a two developers, one comfortable and experienced in their tools of choice, the "raw sql and simple data modeler" developer and the developer very comfortable in their ORM of choice, the raw sql developer will deliver working software at the same time as the ORM developer, but will have a smaller surface area for buggy software due to the increased inadvertent complexity of using an ORM, and poorer database designs, since naturally, developers that rely on ORMs tend to have poorer or suboptimal database design (see this classic talk https://www.youtube.com/watch?v=uFLRc6y_O3s https://www.youtube.com/watch?v=uFLRc6y_O3s) Again if the rebuttal to the database design point is "well you should still know SQL" then what point does the ORM serve?! To write less code? Anyone can write a code generator. If in order to be an effective ORM user I have to know the ORM and understand the SQL it generates to tweak the ORM flags or features to make it generate better SQL, then not only is it a leaky abstraction, it s a bad abstraction. I rely on my compiler to write assembly for me because I can't write assembly better than my compiler and I don't know x86_64 better than compiler developers. If I can't rely on my ORM to write good enough SQL, and I have to know the underlying things going on with the ORM, then why use the ORM? Again, "less code" by itself is not a sufficient answer. As your database fills, the performance penalty for using the unoptimized mapped queries and the unoptimized data schema will start to increase by whole factors.
ww520 7y ago
This argument against ORM is like just because I need to drop down to assembly to get maximum speed in a few cases I should throw out all the C code and redo the whole application in assembly.
- jtdev 7y ago
  No, it’s actually not like that at all...
- mywittyname 7y ago
  It's more like writing a Python framework, which dynamically produces Ruby code which is executed, then the results are returned back to Python. Sub out Python and Ruby for any other pair of languages. At some point, your generator framework must be at least as complex as the underlying language in order to produce the same functionality. ORMs seems fine for SQL, because on the surface, SQL appears to be a simple language. But that image falls apart the minute you try and use some (relatively) esoteric, but important language feature.
  - ww520 7y ago
    Yes, just because I need to drop down to SQL to use the few esoteric feature in the 1% case doesn't mean I cannot use ORM to make my life easier for 99% of the case. I just don't see the point of the argument against ORM to have 100% SQL coverage or else. Most ORM's allow dropping down to SQL when needed. It's easy to have the best of both worlds.
    - deleted 7y ago
      [deleted]
  - deleted 7y ago
    [deleted]
  - tomnipotent 7y ago
    > But that image falls apart the minute you try and use some (relatively) esoteric, but important language feature. No, it doesn't. The only people I hear claim this are people that don't have decent experience with ORMs. Mature ORMs also act as data mappers when you need more fine-tuned control.
tabtab 7y ago
When ORM's work as expected, they are wonderful, but when they don't, you can waste a lot of time figuring out why because they are complex contraptions with lots of parts and rules. They are black boxes, or at least dark gray boxes such that if they don't produce the expected answer, your hair will turn light-gray trying to figure them out. I would suggest using "sub-helpers", utilities that make using SQL and/or stored procedures easier, but don't hide all of the SQL when you don't want to. They automate the common grunt-work, but you are not forced to use them if they don't apply to a situation. Come up with shop conventions for how and where to join, how to handle reference tables, etc. Then automate/abstract around these conventions. Tune based on lessons from the last application and they will improve over time. It's difficult to get abstractions right the first time. In other words, wrap and automate the repetitious parts primarily. Use abstraction where it works best and don't use it where it doesn't.
- mbrameld 7y ago
  > They are black boxes, or at least dark gray boxes such that if they don't produce the expected answer, your hair will turn light-gray trying to figure them out. I'd love to hear specifics about some of your personal experiences like this if you're willing to share.
ianamartin 7y ago
My bones to pick with ORMs are slathering them all over the place like too much mayo on a sandwich. They are very useful tools for some situations and more trouble than they are worth in others. Where they really shine is writing/editing data, managing transactions, and migrations. Even for situations where I know I'm going to use the ORM very little, I'll model the database in SQLAlchemy, for example, so I can version the database with Alembic. Reading data, I generally want more flexibility and don't want to have to push a code change to include another column here or there. For datagrid stuff, you develop a dynamic grid once for your app that just displays whatever the SQL spits out and then you can push changes by updating a stored proc. Reading data is also where a lot of complexity adds up quickly if it's directly from a normalized transactional database. And if you need to manipulate data that's already in your database, obviously, the best tool for that job is SQL, not an ORM. Where a lot of the friction comes in is where people start off using an ORM and don't make any allowance for what's going to happen when you need to use SQL. Then using a little requires a ton of effort. Conversely, you have the same problem with projects that are designed to only use raw SQL, and then it's a big overhaul to use the ORM where it's genuinely useful, adds clarity and safety. At this point, I just assume that every project is going to be a hybrid of the two, set up my boilerplate wiring and don't worry about it after that.
bch 7y ago
> The argument is although they appear to offer you value up front, they cost you much more down the road. I always enjoyed this[0][1] take on that sentiment. “Although it may seem trite to say it, Object/Relational Mapping is the Vietnam of Computer Science. It represents a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy.” [0] https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t... [1] https://web.archive.org/web/20180118171352/http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ https://web.archive.org/web/20180118171352/http://blogs.tedn...
crimsonalucard 7y ago
SQL itself is a leaky abstraction. You're essentially hacking a sql expression and permuting it in different ways so it executes a specific imperative search algorithm. AN ORM is like a backwards abstraction. SQL abstracts imperative instructions into an expression. An ORM abstracts a SQL expression into imperative object oriented instructions. I get the reasoning though. It's to keep the developer from having to write foreign code within code. Sometimes, the ORM abstraction just isn't expressive enough. So I have this idea for an ERM. Expression relational mapper. Basically it compiles an expression based language into ORM methods. So like "SELECT * FROM TABLE" becomes table.all().
- goto11 7y ago
  This is how Linq in .net works.
zzzeek 7y ago
so your generalization is, "people who understand SQL really well would never use an ORM unless forced by their job". That is, you are claiming those who choose to use ORMs must lack experience. This is what you have observed, in your experience. Can I therefore claim, since I know tons of SQL experts who use ORMs and even create new ones, that you "lack experience" as well? because your claim is wildly untrue. edit: i apologize for the snark but you are literally claiming a whole segment of the programming userbase is less experienced than you are, based on their choice of tools, while you aren't considering that perhaps you haven't worked with many of these kinds of tools to know what's really out there and how different ORMs approach the problem.
- mywittyname 7y ago
  Unless ORMs are 1:1 compatible with SQL features, some developers will inevitably be required to write SQL by hand. Lots of developers are happy to leverage stored procedures and let the database do heavy lifting to reduce network traffic (or whatever reason). Others prefer the database to simply be persistent storage for data structures and leave the logic to their Java/C++/PHP code. One of those camps will never be able to use ORMs, and the other may live by them. It stands to reason that more advanced SQL developers will leverage more advanced SQL features, some of which aren't available in ORM implementations.
  - zzzeek 7y ago
    But that doesn't matter, you still can use tools to help compose the 90% of SQL that is quite boring Truth be told I think it is the "i write 100% of my SQL by hand" crowd that is still quite junior in their SQL experience. How many times would they like to write a rote INSERT statement over and over against their data records before realizing an abstraction would save them lots of typing and redundancy? Edit: also note that ORMs most certainly can be used with stored procedure architectures in theory. I'm not sure if such tools exist but if I had to use SPs, I'd be writing tools to both generate the SPs from a given set of models as well as to do the runtime work of marshaling my objects to and from these SPs from cursor.callproc() in the same way an ORM does it to plain SELECT / INSERT / UPDATE / DELETE statements from cursor.execute(). Additionally, systems like PostgreSQL's pluggable SP languages, which includes Python, might even allow SQLAlchemy models to work inside a stored procedure, something I've always wanted to try. Both concepts are things I'd gladly pursue if someone wanted to pay me a few years' salary.
goto11 7y ago
I prefer ORMs to writing raw SQL in application logic - and that is not due to lack of experience with either. (I notice your criticism is mostly ad-hominems directed against people using ORM's rather than actual criticism of the technology.) Note that SQL is also a leaky abstraction over the relational operations - it is just a question of which abstraction is most useful for a particular purpose. Linq express the relational operations just as directly as SQL does, and with a less clunky and more composable syntax. If you are optimizing raw SQL for performance then you are breaking the SQL abstraction anyway. SQL is supposed to work on a logical level, and the query optimizer do the rest. I know it is sometimes necessary anyway, but that does not show a weakness in the ORM abstraction, it shows a weakness in the query optimizer.
dcosson 7y ago
This is a pretty condescending take, and doesn't really seem rooted in anything substantial. The core of your argument revolves around the inevitability of needing to drop down to raw sql for performance -- care to elaborate on that? It seems overblown, I've worked on some apps at decent scale and optimized quite a few queries and I haven't encountered this pattern of needing to throw out the ORM altogether in more and more places. For any simple to moderately complex queries, the ORM is generating the exact SQL you would write by hand... There are a few pitfalls -- by default you may be fetching more fields than you absolutely need, but this is fairly cheap as long as your rows aren't super wide, and any good ORM offers an easy way to pluck only specific fields that you can use in hot paths. It's often easy to end up with N+1 queries in an ORM, but that's also a well-known access pattern that you learn to avoid with a little bit of experience and doesn't require raw sql. This latter is one area that I think a lot of ORMs could really improve upon, in terms of making it harder to do the wrong thing for inexperienced folks. But it's hardly a reason to throw out the whole tool, you can write n+1 queries in raw sql too. I would agree that very complex queries can be more tedious to express in an ORM syntax. A good rule of thumb for when it's worth dropping down to raw SQL from the ORM, is if the shape of the query results don't match an object shape anyway -- i.e. for complex analytics type queries that are doing aggregations. But again I don't see much of an argument for throwing out the whole ORM just because you want to write a small percentage of very complex queries in raw sql. Use the best tool for the job. I don't think there's really al performance argument for these types of queries either, a really complex query can still be just as slow written in raw SQL. The solution to scaling these kinds of queries in a hot path tends to be denormalizing or precomputing or caching parts of the data.
- deleted 7y ago
  [deleted]
- fjp 7y ago
  Agreed - For example in SQLAlchemy, you have: 1) Super high level - my_record_object.filter(whatever) 2) Common query api: my_table_object.select.where(whatever).limit(whatever) etc etc 3) Straight up SQL execution: db_engine_object.connection.execute(Sql query as string) There are tools to safely interpolate arguments into SQL query templates for use in #3 too. What else could you want in the vast majority of applications?
mavelikara 7y ago
> I always find the people who don't believe writing raw SQL is preferable to using an ORM is usually down to a lack of experience > The ORMers, as I call them, don't have confidence in their own SQL ability SQL is leaky abstraction too. Every time you look at query plans and try to tweak the right amount and kind of indices, you are having to deal with the drawbacks of the SQL abstraction. That does not make people who do that "don't have confidence in their own file-system ability".
stcredzero 7y ago
The argument against ORMs aren't that they are a leaky abstraction From what you say, they are indeed a leaky abstraction. It's just that the conditions in which they leak are not easily accessible from the "Hello World" level demo. This is why the leaky analogy is good. It's not hard to make a boat that floats, period. It's hard to make a boat that will remain reliably watertight on the open ocean on rough seas. yes all abstractions are at least somewhat leaky, so you are better to use simpler ones than complex ones This is like the Russian philosophy towards military equipment. It's better that it's simple, robust, and can take a lot of dirt and abuse, because this will better in actual combat (or "production") conditions.
diminoten 7y ago
Lack of experience in this case being, "Haven't blamed problems on the same things as I have." Stances like yours sit in defiance of so many thousands of people who've been able to extract value from an ORM. Maybe the issue you're having is specific to you, not the problem. Are they perfect? No. Are your incredibly limited set of experiences universal? Also no.

by icxa 7y ago

jtdev 7y ago

This is reflective of my experience with the ORM zealots as well... SQL is already an abstraction, and it happens to be one of the most well thought out, battle tested abstractions we have in the software world... it drives me nuts when junior devs and devs with poor SQL abilities want to pollute a codebase with EF, Hibernate, etc. without understanding if it’s necessary or even helpful beyond simply allowing the developer to continue onward without spending time becoming proficient in SQL. That said, there are scenarios where an ORM is helpful, but I personally find those occasions to be the exception.

ajross 7y ago
> SQL is already an abstraction Still a leaky one, though. Sure, the syntax likes to pretend that you "just describe what you want", but in practice getting queries on large data sets to perform well involves layers of optimization, all the way down to deciding on how data is partitioned between storage backends and what the on-disk format of it and its indexes needs to be. In fact I view SQL as a somewhat thin abstraction, with a bunch of unavoidable (or at least unavoided) holes. Maybe that's part of the reason it's been so successful.

asdkhadsj 7y ago

I'd not mind writing raw SQL if it was anything other than a big, non-typed blob of crap as far as my language is concerned. I want compile time safety in my SQL. Tbh, I'm surprised that raw SQL folks don't promote some type of non-ORM but also compile-time type safe SQL implementation. The runtime-ness of SQL strings have always blown me away. I imagine it would be pretty easy too. Move SQL out of the program language (ie, into files). Verify the syntax. Compare the SQL to the db to ensure validity. Bam, compile time verified SQL. Though, I've never used a setup like that, as that is what my ORM does, just in-language.

deleted 7y ago
[deleted]
jmull 7y ago
Your compiler doesn’t — and can’t — know what’s in your database. To the extent you create a system that requires this constraint, you’ve created a brittle (and soon to be broken) system. (I suppose there may be some case where all the data is all known up-front and will always be updated in lock-step with the consuming code/services but that’s rare in my experience.)
- asdkhadsj 7y ago
  > Your compiler doesn’t — and can’t — know what’s in your database. At compile time, yes it can. Not inherently the compiler itself, but at compile time the process can fail to build if your models do not match the schema in the DB. Diesel, for example, keeps the schema in code and (optionally) compares it to that of the DB. Ensuring that at compile to your code matches the schema of the DB. That of course doesn't handle changes to the schema post-build, but I hope no one is ad-hoc modifying their DB :) edit: And this becomes even more of a local, isolated solution if your ORM is also managing your migrations. Which, in the case of Diesel, it also (optionally) does.
  - jmull 7y ago
    Maybe I should have added: it doesn't know what's in your database at runtime. It's not you'll be ad hoc modifying your DB. It's that your schema and your application-side entities will change over time (continuously, during development, and somewhere between continuously and periodically in production, depending on how you release). Generally you won't be able to guarantee that they are updated in lock-step, or you won't want to pay the price to ensure that they always are.

maehwasu 7y ago

Another way of saying it: database abstractions should be specific to your codebase, rather than use generalized patterns.

ken 7y ago

> The argument against ORMs aren't that they are a leaky abstraction (although they most definitely are). I'm not using an "OO" language these days, so I don't really have a horse in this race, but: isn't the problem you mention here completely explained by the leakiness? Replace "ORM" with "compiler" and "SQL" with "assembly language", and this is exactly the argument I heard back in the 1980's about why not to use a compiler, or in the 1990's about why not to use a GC. Nobody I know is writing assembly language at all any more. It sucks at composition, so even though it's better at many tactical micro-optimizations, we've improved compilers to the point that we can use them all the time, because it helps productivity massively. > and if you need something more complex, compose it from simpler ones Composition is the killer feature in all of software building, and the fatal flaw of assembly, and SQL. We use higher-level languages for their composition abilities, even when they are less efficient. When their inefficiencies make them unusable, we make them smarter, even when it means designing new languages with 100+ keywords (ugh). If the major complaint against ORMs is that they aren't good enough to make the value worth it in all cases, well, yeah, I've seen that phase of literally every other technology I'm using today. This, too, will pass.

mntmoss 7y ago
Comparing SQL - a 4GL - to assembly language seems disingenuous at best, to the point where the Blub paradox applies. (And lo and behold, just four days ago you compared SQL to Lisp on the basis of incompatible implementation!) Unlike assembly, which is famously redundant, SQL doesn't have much to abstract away, and the majority of counterexamples are covered by macros. The things that it really can't abstract are the implementation details that you want to care about when you optimize. Some parts could be a little less clunky, in the same way that the C language could be a little less clunky, but the successful track record of SQL over decades speaks volumes: it's not the stuff going on inside the database causing people the most grief, it's the programming goop that gets attached to it.
- srean 7y ago
  He does have a point about composability though. Wonder how, things would have turned out had prolog become the default query language.

deleted 7y ago

[deleted]

0xDEFC0DE 7y ago

By now, we should be able to transition between ORM and raw SQL code without anyone batting an eye. It's not hard at all. You can't convince me people are actually worried about an extra ORM dependency these days when we have node_modules. I would much, much rather junior devs use an ORM to start simply to prevent SQL injection while they skill up on SQL itself. But there's not many companies who will hire you and give you a month to learn SQL from the ground-up and do nothing else (learning SQL is NOT like learning another compiled/interpreted language -- there's new concepts plus the DBMS's own language idiosyncrasies which you're definitely using in prod).

Smithalicious 7y ago

Dropping down to a lower level of abstraction from the get go because you might need that level of control for performance reasons in the future sounds like a typical case of premature optimization to me. Of course any sufficiently complex system is eventually going to need specifically optimized code at a lower level of abstraction in some places (usually then wrapped in an abstraction of your own)but that should always be done on a case by case basis as necessary.

icxa 7y ago
> Dropping down to a lower level of abstraction from the get go because you might need that level of control for performance reasons in the future sounds like a typical case of premature optimization to me. On the flip side, I can make use of the code generation tools i have compiled (some myself, some others) and write code just as fast, if not faster, using raw sql and simple data structures than I did when I used ORMs. There are infinitely many possibilities in between: - A developer who is experienced in modeling and sql will deliver working software faster than a developer who might be experienced, but not with modeling and not familiar with a particular ORM or a particular version of that ORM. We could continue ad nauseam here. The main point: if all things are equal, meaning you have a two developers, one comfortable and experienced in their tools of choice, the "raw sql and simple data modeler" developer and the developer very comfortable in their ORM of choice, the raw sql developer will deliver working software at the same time as the ORM developer, but will have a smaller surface area for buggy software due to the increased inadvertent complexity of using an ORM, and poorer database designs, since naturally, developers that rely on ORMs tend to have poorer or suboptimal database design (see this classic talk https://www.youtube.com/watch?v=uFLRc6y_O3s https://www.youtube.com/watch?v=uFLRc6y_O3s) Again if the rebuttal to the database design point is "well you should still know SQL" then what point does the ORM serve?! To write less code? Anyone can write a code generator. If in order to be an effective ORM user I have to know the ORM and understand the SQL it generates to tweak the ORM flags or features to make it generate better SQL, then not only is it a leaky abstraction, it s a bad abstraction. I rely on my compiler to write assembly for me because I can't write assembly better than my compiler and I don't know x86_64 better than compiler developers. If I can't rely on my ORM to write good enough SQL, and I have to know the underlying things going on with the ORM, then why use the ORM? Again, "less code" by itself is not a sufficient answer. As your database fills, the performance penalty for using the unoptimized mapped queries and the unoptimized data schema will start to increase by whole factors.

ww520 7y ago

This argument against ORM is like just because I need to drop down to assembly to get maximum speed in a few cases I should throw out all the C code and redo the whole application in assembly.

jtdev 7y ago
No, it’s actually not like that at all...
mywittyname 7y ago
It's more like writing a Python framework, which dynamically produces Ruby code which is executed, then the results are returned back to Python. Sub out Python and Ruby for any other pair of languages. At some point, your generator framework must be at least as complex as the underlying language in order to produce the same functionality. ORMs seems fine for SQL, because on the surface, SQL appears to be a simple language. But that image falls apart the minute you try and use some (relatively) esoteric, but important language feature.
- ww520 7y ago
  Yes, just because I need to drop down to SQL to use the few esoteric feature in the 1% case doesn't mean I cannot use ORM to make my life easier for 99% of the case. I just don't see the point of the argument against ORM to have 100% SQL coverage or else. Most ORM's allow dropping down to SQL when needed. It's easy to have the best of both worlds.
  - deleted 7y ago
    [deleted]
- deleted 7y ago
  [deleted]
- tomnipotent 7y ago
  > But that image falls apart the minute you try and use some (relatively) esoteric, but important language feature. No, it doesn't. The only people I hear claim this are people that don't have decent experience with ORMs. Mature ORMs also act as data mappers when you need more fine-tuned control.

tabtab 7y ago

When ORM's work as expected, they are wonderful, but when they don't, you can waste a lot of time figuring out why because they are complex contraptions with lots of parts and rules. They are black boxes, or at least dark gray boxes such that if they don't produce the expected answer, your hair will turn light-gray trying to figure them out. I would suggest using "sub-helpers", utilities that make using SQL and/or stored procedures easier, but don't hide all of the SQL when you don't want to. They automate the common grunt-work, but you are not forced to use them if they don't apply to a situation. Come up with shop conventions for how and where to join, how to handle reference tables, etc. Then automate/abstract around these conventions. Tune based on lessons from the last application and they will improve over time. It's difficult to get abstractions right the first time. In other words, wrap and automate the repetitious parts primarily. Use abstraction where it works best and don't use it where it doesn't.

mbrameld 7y ago
> They are black boxes, or at least dark gray boxes such that if they don't produce the expected answer, your hair will turn light-gray trying to figure them out. I'd love to hear specifics about some of your personal experiences like this if you're willing to share.

ianamartin 7y ago

My bones to pick with ORMs are slathering them all over the place like too much mayo on a sandwich. They are very useful tools for some situations and more trouble than they are worth in others. Where they really shine is writing/editing data, managing transactions, and migrations. Even for situations where I know I'm going to use the ORM very little, I'll model the database in SQLAlchemy, for example, so I can version the database with Alembic. Reading data, I generally want more flexibility and don't want to have to push a code change to include another column here or there. For datagrid stuff, you develop a dynamic grid once for your app that just displays whatever the SQL spits out and then you can push changes by updating a stored proc. Reading data is also where a lot of complexity adds up quickly if it's directly from a normalized transactional database. And if you need to manipulate data that's already in your database, obviously, the best tool for that job is SQL, not an ORM. Where a lot of the friction comes in is where people start off using an ORM and don't make any allowance for what's going to happen when you need to use SQL. Then using a little requires a ton of effort. Conversely, you have the same problem with projects that are designed to only use raw SQL, and then it's a big overhaul to use the ORM where it's genuinely useful, adds clarity and safety. At this point, I just assume that every project is going to be a hybrid of the two, set up my boilerplate wiring and don't worry about it after that.

bch 7y ago

> The argument is although they appear to offer you value up front, they cost you much more down the road. I always enjoyed this[0][1] take on that sentiment. “Although it may seem trite to say it, Object/Relational Mapping is the Vietnam of Computer Science. It represents a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy.” [0] https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/ https://blog.codinghorror.com/object-relational-mapping-is-t... [1] https://web.archive.org/web/20180118171352/http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ https://web.archive.org/web/20180118171352/http://blogs.tedn...

crimsonalucard 7y ago

SQL itself is a leaky abstraction. You're essentially hacking a sql expression and permuting it in different ways so it executes a specific imperative search algorithm. AN ORM is like a backwards abstraction. SQL abstracts imperative instructions into an expression. An ORM abstracts a SQL expression into imperative object oriented instructions. I get the reasoning though. It's to keep the developer from having to write foreign code within code. Sometimes, the ORM abstraction just isn't expressive enough. So I have this idea for an ERM. Expression relational mapper. Basically it compiles an expression based language into ORM methods. So like "SELECT * FROM TABLE" becomes table.all().

goto11 7y ago
This is how Linq in .net works.

zzzeek 7y ago

so your generalization is, "people who understand SQL really well would never use an ORM unless forced by their job". That is, you are claiming those who choose to use ORMs must lack experience. This is what you have observed, in your experience. Can I therefore claim, since I know tons of SQL experts who use ORMs and even create new ones, that you "lack experience" as well? because your claim is wildly untrue. edit: i apologize for the snark but you are literally claiming a whole segment of the programming userbase is less experienced than you are, based on their choice of tools, while you aren't considering that perhaps you haven't worked with many of these kinds of tools to know what's really out there and how different ORMs approach the problem.

mywittyname 7y ago
Unless ORMs are 1:1 compatible with SQL features, some developers will inevitably be required to write SQL by hand. Lots of developers are happy to leverage stored procedures and let the database do heavy lifting to reduce network traffic (or whatever reason). Others prefer the database to simply be persistent storage for data structures and leave the logic to their Java/C++/PHP code. One of those camps will never be able to use ORMs, and the other may live by them. It stands to reason that more advanced SQL developers will leverage more advanced SQL features, some of which aren't available in ORM implementations.
- zzzeek 7y ago
  But that doesn't matter, you still can use tools to help compose the 90% of SQL that is quite boring Truth be told I think it is the "i write 100% of my SQL by hand" crowd that is still quite junior in their SQL experience. How many times would they like to write a rote INSERT statement over and over against their data records before realizing an abstraction would save them lots of typing and redundancy? Edit: also note that ORMs most certainly can be used with stored procedure architectures in theory. I'm not sure if such tools exist but if I had to use SPs, I'd be writing tools to both generate the SPs from a given set of models as well as to do the runtime work of marshaling my objects to and from these SPs from cursor.callproc() in the same way an ORM does it to plain SELECT / INSERT / UPDATE / DELETE statements from cursor.execute(). Additionally, systems like PostgreSQL's pluggable SP languages, which includes Python, might even allow SQLAlchemy models to work inside a stored procedure, something I've always wanted to try. Both concepts are things I'd gladly pursue if someone wanted to pay me a few years' salary.

goto11 7y ago

I prefer ORMs to writing raw SQL in application logic - and that is not due to lack of experience with either. (I notice your criticism is mostly ad-hominems directed against people using ORM's rather than actual criticism of the technology.) Note that SQL is also a leaky abstraction over the relational operations - it is just a question of which abstraction is most useful for a particular purpose. Linq express the relational operations just as directly as SQL does, and with a less clunky and more composable syntax. If you are optimizing raw SQL for performance then you are breaking the SQL abstraction anyway. SQL is supposed to work on a logical level, and the query optimizer do the rest. I know it is sometimes necessary anyway, but that does not show a weakness in the ORM abstraction, it shows a weakness in the query optimizer.

dcosson 7y ago

This is a pretty condescending take, and doesn't really seem rooted in anything substantial. The core of your argument revolves around the inevitability of needing to drop down to raw sql for performance -- care to elaborate on that? It seems overblown, I've worked on some apps at decent scale and optimized quite a few queries and I haven't encountered this pattern of needing to throw out the ORM altogether in more and more places. For any simple to moderately complex queries, the ORM is generating the exact SQL you would write by hand... There are a few pitfalls -- by default you may be fetching more fields than you absolutely need, but this is fairly cheap as long as your rows aren't super wide, and any good ORM offers an easy way to pluck only specific fields that you can use in hot paths. It's often easy to end up with N+1 queries in an ORM, but that's also a well-known access pattern that you learn to avoid with a little bit of experience and doesn't require raw sql. This latter is one area that I think a lot of ORMs could really improve upon, in terms of making it harder to do the wrong thing for inexperienced folks. But it's hardly a reason to throw out the whole tool, you can write n+1 queries in raw sql too. I would agree that very complex queries can be more tedious to express in an ORM syntax. A good rule of thumb for when it's worth dropping down to raw SQL from the ORM, is if the shape of the query results don't match an object shape anyway -- i.e. for complex analytics type queries that are doing aggregations. But again I don't see much of an argument for throwing out the whole ORM just because you want to write a small percentage of very complex queries in raw sql. Use the best tool for the job. I don't think there's really al performance argument for these types of queries either, a really complex query can still be just as slow written in raw SQL. The solution to scaling these kinds of queries in a hot path tends to be denormalizing or precomputing or caching parts of the data.

deleted 7y ago
[deleted]
fjp 7y ago
Agreed - For example in SQLAlchemy, you have: 1) Super high level - my_record_object.filter(whatever) 2) Common query api: my_table_object.select.where(whatever).limit(whatever) etc etc 3) Straight up SQL execution: db_engine_object.connection.execute(Sql query as string) There are tools to safely interpolate arguments into SQL query templates for use in #3 too. What else could you want in the vast majority of applications?

mavelikara 7y ago

> I always find the people who don't believe writing raw SQL is preferable to using an ORM is usually down to a lack of experience > The ORMers, as I call them, don't have confidence in their own SQL ability SQL is leaky abstraction too. Every time you look at query plans and try to tweak the right amount and kind of indices, you are having to deal with the drawbacks of the SQL abstraction. That does not make people who do that "don't have confidence in their own file-system ability".

stcredzero 7y ago

The argument against ORMs aren't that they are a leaky abstraction From what you say, they are indeed a leaky abstraction. It's just that the conditions in which they leak are not easily accessible from the "Hello World" level demo. This is why the leaky analogy is good. It's not hard to make a boat that floats, period. It's hard to make a boat that will remain reliably watertight on the open ocean on rough seas. yes all abstractions are at least somewhat leaky, so you are better to use simpler ones than complex ones This is like the Russian philosophy towards military equipment. It's better that it's simple, robust, and can take a lot of dirt and abuse, because this will better in actual combat (or "production") conditions.

diminoten 7y ago

Lack of experience in this case being, "Haven't blamed problems on the same things as I have." Stances like yours sit in defiance of so many thousands of people who've been able to extract value from an ORM. Maybe the issue you're having is specific to you, not the problem. Are they perfect? No. Are your incredibly limited set of experiences universal? Also no.