The post specifically complains about ActiveRecord, and claims that the abstraction of ORM breaks down not for 20% of projects, but close to 100% of them.
This certainly doesn't match with my experience, and I've worked on dozens of projects using ActiveRecord. Some of these were hairy production projects with strict performance requirements and complicated schemas.
About 90% of the time, the ORM does the right thing. It creates, updates and deletes individual records, and it generates entirely reasonable queries. And it does this without requiring dozens or hundreds of lines of SQL.
You'll get much better performance out of ActiveRecord if you know a few tricks, however:
- Read the SQL queries in development.log to make sure they're sane, and use New Relic to find slow queries in production.
- Use 'include' to load a model and its associated objects in 2 queries instead of 1+N queries.
- Use 'select' to load only the necessary columns from your tables.
- Don't try to force it: If you hit something weird, never hesitate to fall back to SQL. ActiveRecord provides a dozen different places where you can inject small SQL fragments into your queries, or you can just call 'find_by_sql' and do everything by hand.
If you start out with basic ORM models, benchmark your database, and add some raw SQL as you scale, you can get reasonable rcesults with ActiveRecord. If it breaks down for 100% of projects, you either have a weird definition of "breaks down", or you're doing it wrong.
What you just describe is exactly what he defines as breaking down. You may not consider this a big deal but he is right that instead of just learning SQL or an ORM you have to learn/use both. The abstraction does break down somewhere for close to 100% of projects in my own experience, usually for exactly the reasons he is describing. Saying it's no big deal doesn't change the fact that each of the points he makes in his post do indeed happen.
Now I'll grant you that this may not personally bug you that much. It bugs me quite a bit though.
If you use a relational database and believe you don't need to know sql, you're delusional.
If you are using a relational database, know sql, and want to eliminate a TON of boilerplate, use an ORM.
I don't know of many ORM's that claim "Use us and you won't have to know SQL". That's just a straw man argument.
If you define breaking down in the terms mentioned by the comment you are replying to, you are playing a semantics game to frame the debate to be: this ORM sucks because I can't use it without knowing sql.
no. I define it thus. "If I already know SQL why should I have to learn this ORM?" SQL already works and does what I need. What does an orm give me that makes the headache of figuring out when I need to shift to naked SQL worth it? Why try to hide the SQL when its just going to leak out to me in non obvious ways later anyway. That is the authors point as well I think.
I'm really not trying to flame here, so bear with me. :)
It's just that in software, the argument "If I already know A, so why should I have to learn B" isn't really useful. Given that software is often about pushing down complexity, one of our main tools is building a "B" so that we don't have to do "A".
I know how to write socket code in C, but I'm glad for libraries that don't force me to think about that level--I can pop up a level or two. Examples continue ad infinitum.
On the other hand, I can buy the argument "B isn't really very good at abstracting A; I've tried to use B, and every time I use B, I end up just doing A anyway. So B isn't useful."
But with ORMs, my experience isn't so black-and-white. I find them very useful a lot of the time. When I run into problems, I can switch to doing SQL directly. That's an okay tradeoff for me. I can certainly understand if others don't find that tradeoff as useful.
My point was more B doesn't in my experience abstract A well enough to justify its use in anything other than toy or really simple apps that won't live very long or won't have much maintenance. I don't mind an upfront cost if it means I won't be pulling my hair out at 2am trying to make some database call take less then 10 minutes to complete. (Exaggerating there but you get the idea.)
If B doesn't do a great job abstracting A then the time spent learning B feels wasted to me.
The answer is: because you often want ONE PLACE through which queries are sent, and you often want this place to understand the STRUCTURE of the query, in order to do things like sharding, for example.
Sometimes, this one place is actual a MySQL proxy. Check out ScaleDB, which they just launched - it looks promising.
But other times, it will be in your own app. That is the ORM :) But in that case I wonder how often you really need relational data stores.
You will actually arrive at some sort of ORM if you start abstracting your CRUD operations into ONE PLACE. Basically ORM is the product of DRY.
No one is forcing you to do anything, you can write your own database access layer if you like. Often developers who do this end up using parts of their code across different projects, basically they write their own ORM.
Or you can use a pre-built ORM, as far as I am concerned for me this is the saner choice. I really doubt I could write something on my lonesome that is as secure, performant and has the features of ActiveRecord in a non trivial amount of time.
But the important part is that whichever path you go down, if your project is growing, complex or needs to scale, you need to know your stack. You need to be able to modify or reconfigure each part. You need to understand at a high level what each part of the stack does, and be able to learn exactly what it does at a low level quickly should the need arise.
"What does an orm give me that makes the headache of figuring out when I need to shift to naked SQL worth it?"
ORM has lots of benefits. but having to learn less is not one of them. Biggest one is that you don't need to write/maintain a separate code base (sql) for CRUD operations. that's BIG benefit even if you get to use it on 40% of your database interactions. Something as stupid as not worrying about creating the right data conversions and logical types to store data in, will avoid a ton of bugs from intermediate programmers.
You're not just eliminating a ton of boilerplate, you're also eliminating set oriented thinking, which is key to working with relational databases effectively and efficiently.
I find ORMs useful for simple CRUD but that's so little of what I do that I mostly don't bother adding a huge framework to my code just to do some CRUD.
A set oriented DB API that cuts down on boilerplate is the way to go in my view. And that is simple enough to create on a weekend.
That's just not true, though. Most ORM's I've used use the set as the primitive element. You declare your schema in terms of sets with relationships. Then you join on relationships. So if you want to find all artists whose name contains 'foo' that have released an album that starts with bar, you would write $albums->search({ name => { -like => 'bar%' } })->search_related('artists', { name => { -like => '%foo%' } }). This looks a lot like the SQL that you would write for the query, except that you don't have to write it and marshal the results back to some format that your application understands. And, the object you get back isn't a set of rows from the database, it's a description of what query you want to run. So you can pass this object around and further restrict or expand the set of results you'll get back when you actually start asking for row data.
This works out very nicely for things like web applications where the currently-logged-in-user can only manipulate his own data. You can write your model so that it takes the current user and restricts all data to his rows so that your application doesn't even see data that the user is not allowed to see. It's really quite wonderful and something that raw SQL queries simply don't provide enough abstraction to handle flexibly.
Ultimately, people dislike ORMs for the same reason they dislike things like CoffeeScript. They're not mentally comfortable delegating code generation to a computer program -- that's their job. And so, they will complain whenever they are looking at code that's not exactly what the lower level of the system will see. But in general, that's due to inexperience rather than actual dislike.
I used ORMs for a long time and I have written my own many years ago, so I understand them well and I have learned to dislike them for most tasks. But I should have been more specific in what I mean by set oriented.
The point of working with sets of tuples is that you can apply any set operation to a set of tuples and what you get is a set of tuples. You can use selection, projection or union and you still get a set of tuples. The recursive nature of this principle makes it so powerful and simple. Kind of like Lisp.
ORMs introduce a conceptual barrier between objects defined by domain classes created at design time and generic containers used to hold the result of projections and aggregate functions. If you join over two classes and project over some of their attributes you lose all the properties of the original classes, including any attribute accessor methods. Same thing with aggregate functions and grouping.
Even if the result of a query involving projections or aggregate functions happens to have the exact same attributes as another existing class, you don't get objects of that class and hence none of its methods. The tuples don't compare equal to objects of that class either because they don't share their type.
Different ORMs have various features to partially paper over these issues. I don't want to get caught up in a debate about what ORMs can or cannot do because powerful ORMs like hibernate can do almost anything and everything is optional.
The point is that they discourage me from working in a set oriented fashion because they make it hugely more complex than it needs to be. They create a conceptual mess and then they want me to learn a lot of tuning techniques to fix what is broken as a result of that mess. It makes no sense to me.
The current Ruby ActiveRecord is based on Arel, which is a relational algebra. Everything in Arel is necessarily set-oriented and the results of expressions in the ActiveRecord ORM language are the exact objects that went into the set, as long as they are truly set operations.
Same thing with aggregate functions and grouping.
Why do you expect the resulting object of an aggregate over source objects to have the same kinds of methods? That makes no sense at all. What should the 'name' of a record return when you've aggregated over the salary of many employees? That makes no sense in the original relational algebra, whether expressed in SQL or ORM, either. Aggregating is not a set operation and there part of the false dichotomy comes in: SQL isn't quite as pure and easy to understand as ORM opponents would have you believe.
Exactly, encapsulation as a combination of data and methods makes no sense if data is transformed, joined in different ways, grouped, etc. Some methods have to become invalid. Others, like accessors, may still be logically valid but are lost because they are linked inextricably to a class type even though they may depend only on the attribute value itself.
The goal of a query is not to process sets of objects. The goal is to answer a question. The ORM gives you two choices. Either limit yourself to answers that can be expressed as objects of existing classes or get something that is fundamentally different from all other domain objects - a generic tuple of values.
You lose the homogenous, recursive transformation capability that the relational model provides.
In terms of simplicity, I suggest you take a few queries involving joins, group by, having and aggregates and translate them to procedural code. I've done that a lot. It's very eye opening. Functional languages using homogenous data structures like lists or generalized sequences can be similarily productive for data that fits into memory. That is a more and more viable alternative to SQL in my view, but OO systsms and querying/transformation just clash badly.
There's more to database programming than just concocting queries. Once you've built a query you want, you have to run it and handle the results somehow. Most object-oriented programs are going to use an object for this. So it makes sense to just write your whole program in terms of set operations on top of predefined objects which map pretty nicely to the database's idea of objects.
You are confirming what I said originally. What you call "handle the result" is quite frequently something that can be done in a set oriented fashion using orders of magnitude less code.
But what ORMs encourage you to do is to load a bunch of objects and then use procedural code to do the real thing.
I fully understand that not all types of algorithms and datastructures lend themselves to set oriented thinking. I work mostly with those nowadays. But for those cases it makes no sense to use RDBMS at all.
But what ORMs encourage you to do is to load a bunch of
objects and then use procedural code to do the real thing.
The exact same thing happens when you directly express the query in SQL. You load a bunch of objects and post-process them, to do what SQL can't do. Unless you count the various languages you can use in stored procedures in RDBMS's, but those aren't set-oriented SQL either.
If I want to do something that SQL can't do I don't use SQL. Using SQL is not an end in itself.
My rule of thumb is pretty simple. I use whatever takes fewer and/or simpler lines of code unless it's a lot slower.
The raging scalability debate is a different matter. I'm sure if you're Google there are good reasons to write more lines of code in order to scale better. I'm not Google so I can prioritize productivity over scalability.
> Ultimately, people dislike ORMs for the same reason they dislike things like CoffeeScript
This is not a good comparison. With ORM, you started with Python/Java/C#/etc + SQL, and ended with Python/Java/C#/etc + SQL (a lot less). There is nothing new added.
With CoffeeScript, you started with JavaScript, and ended with JavaScript + a perlish language.
There is the problem for me. ORMs (well maybe Entity Framework with LINQ) don't attempt to be an abstraction. They are more like sprintf. Knowing sprintf doesn't free you from knowing what numbers look like as strings. ORMs don't free you from knowing SQL. They free you from having to write some tedious shelping rows to objects.
Although he did specifically call out Ruby on Rails I rather think he meant ActiveRecord the pattern [0] rather than ActiveRecord the implementation [1]. Ruby on Rails' ActiveRecord has come a long way and actually implements proper relational algebra through Arel [2] now.
The alternative and more flexible approach is to use a software package with the Data Mapper pattern. It differs in that one is no longer tightly coupled to the actual database tables. Instead, you can create whatever views of the data make sense to your application. Even though it seems like a higher level of abstraction, you actually have greater access to all the features SQL provides, almost in the form of an SQL builder.
One example of the Data Mapper pattern is in python's SQLAlchemy. Another is in perl's DBIx::ObjectMapper.
"Don't try to force it: If you hit something weird, never hesitate to fall back to SQL. ActiveRecord provides a dozen different places where you can inject small SQL fragments into your queries, or you can just call 'find_by_sql' and do everything by hand."
I'm comfortable working like that, but it is exactly what the article is criticizing. Sure, it works - but the whole idea of an ORM was supposed to be avoding that.
"But the whole idea of an ORM was supposed to be avoding that."
Maybe some ORM zealots said that in the past, but that certainly isn't the common conception now. ORMs as they're actually used properly today allow devs who know SQL really well to eliminate code dup. If you can't drop into SQL quickly, stay away from ORMs, you're going to hurt yourself and others.
Here we go again. I'm pretty sure I've seen this post here (and the famous Vietnam of Computer Science one) several times before.
No database access abstraction is going to be great for all situations. Whether it's object-oriented, functional, or DSL-based, there are going to be situations where it's frustrating to use. However, each of these abstractions have a purpose and a clear benefit for a certain subset of situations, or else they likely wouldn't have been created.
Since people find some abstractions useful for some subset of situations, why waste so much time and energy fighting against its use entirely--labeling it an "anti-pattern"? On the other hand, why waste time and energy arguing for its ubiquitous use? Like with all other technology choices, the right prescription is to use an abstraction when it makes sense, and to abandon it when it does not.
Maybe it would be more constructive to frame the discussion in this way: what cases are ORMs useful for, and what cases are they bad at? How can we identify the good use cases from the bad? Which use cases seem to be good uses but turn out to be bad, and why? What are alternative solutions for those bad use cases? Are there different philosophies of ORMs with different tradeoffs? What are those philosophies and tradeoffs and how can we choose which one most suits our problem?
It feels like people keep getting burned by investing all their stock into an ORM and then overcompensate by never wanting to use any ORM again.
ORM's are awesome to get things done quickly, but as stated in the article, and stated by those very same ORM's, you do sacrifice some performance.
As usual with these articles, there is a false dichotomy that one should choose between ORM or no ORM. It's a perfectly sound decision to use an ORM and hand-optimize whenever the need arises.
Also, ORM's usually _are_ optimized for the 90% use case, so going of on your own to write those in plain sql for your entire domain model is passing up on a huge amount of leverage.
ORM is like satnav in your car. Sometimes it will get you from A to B without you having to think too much. But sometimes it will send you off around the houses.
Now a normal person would say "stupid satnav" but an ORM fan would say "stupid car" (blame the database for being slow).
I don't see anyone arguing that the database is stupid for being slow. I see a bunch of anti-ORM people arguing that an ORM is never the right solution to any problem ever. I also see a bunch of people arguing that ORM mixed with SQL is perfectly fine and there is nothing 'broken' about it (which I think is the more moderate view). I don't see anyone here arguing the opposite extreme of "ORM is always the right approach to every problem." I view the "ORM fan" as a strawman for the most part in this whole discussion.
My experience as a DBA is that someone will develop using Hibernate, it'll go into production and run like a dog, and they'll go straight to their manager and say "It's the database". Because noooooo it couldn't be his perfect code, could it?
ORMs are useful for one thing for sure: for making an illusion that OO way of thinking and relational way of thinking fits.
But they don't fit really!
I like the way original author explained it: the problem was that people were forced or convinced to use wrong abstractions. Due to the massive marketing of really big players since 80s, relational databases are everywhere around; most of developers during the years took for granted that they MUST use SQL, so they stopped thinking if they really need relational model.
Luckily we have other setting these days. There are plenty of production-ready non-relational database engines. To simplify your application layer and really map your OO data, please do use document database - they fit. That's it. No amount of pretending will dissolve the differences between relational model and object-oriented model.
If in doubt, please go back to the theory, to the computer science. You don't have to dig through tons of papers from 70s when relational model has had its peak of scientific evolution - you just buy yourself the CTM book and go through all the computational models with examples of the real code. Please do the lesson. Otherwise any discussion will be about habits and beliefs again, not about science and engineering.
And - I hate to say it - most of the comments like "here we go again" are really about habits and beliefs. How sad. The foundation of our occupation is hard science and engineering. We strive to build better things based on such concrete foundation. By sticking to the thinking "OO + relations fit" we abandon this foundation and we are turning into believers of some cult created decades ago by the marketing people of the few companies everybody knows in order to massively increase sales of RDBMSs. This is not engineering.
Please, put yourself in the distance from the habits of using specific tools you're accustomed to; please think about it.
So, say I do use a document database and object serialization is indeed easy and fun again. Now I need to do reporting, only it's really slow until I heavily denormalize (i.e. reinvent half of a SQL database and update every time my reports change). Now I need to share data across objects so it turns out I did need joins after all. Maybe I could invent an Object-Non-Relational-Mapper to make some of that complexity easier to maintain…
None of this is saying a particular technology is wrong or bad but it's a reminder that Eric was right to point out that they all have drawbacks which developers should consider when picking a good fit for their application. Blindly asserting that a particular one is good or bad is no better than telling carpenters they only need hammers.
Even Google, for all of their massive resources and BigTable's deserved respect, uses a ton of SQL databases - and they didn't add support to AppEngine simply because they were unwilling to tell developers to try something new.
The answer is to use both. Do you really need realtime reporting? If you don't, store everything in a document database and write a small job to copy the data to a relational store for querying and reporting.
If you're going to store in a relational database anyway, why have the expense of having to develop and support both? It'd make more sense to try to either denormalize - with some support framework - or just use the SQL database from the beginning, the deciding factor being how important aggregate operations are to your app and simple questions like how much your NoSQL database costs to operate vs. your SQL database.
tl;dr: "Know your data access patterns and pick a good fit with your resources"
I also know that they use at least one big financial reporting packages but I'm not sure whether the use of Oracle Hyperion is evidence for or against wisdom.
Google has contributed quite a few large patches to MySQL. I doubt they would be spending engineering time on improving MySQL if they did not use it internally for at least _some_ projects, and probably many.
Do a Google search for mysql google contributions and plenty of stuff pops up.
Yes, there is an impedance mismatch in mapping data from relational data stores to object instances.
This does not mean ORM's are an anti-pattern, or bad. ORM's provide a lot of value for the 90% use case that people often need to bang out under various time constraints (mvp, project for a customer, etc).
There is a trade-off when using an ORM that you, or your team, should be aware of. Once you start hitting the limits of what an ORM can reasonably do for you, it's fine to either:
- drop down to using handoptimized sql for specific queries / submodules of your project (e.g. a set of reporting pages)
- figure out if you are trying to punch a cube through a circle by using a relational database over a nosql type storage, and consider moving part of your data to a different storage.
I haven't had a lot of experience with nosql databases yet, but I think it's also interesting to consider that relational databases have been around for a long time, and alot of corner cases where they are unwieldy are known. Nosql databases are not as vetted against reality yet, so abolishing one for the other may amount to nothing else than trading one set of problems for another.
All abstractions leak when you're debugging or trying to understand operational behavior of a whole system.
But there are plenty of abstractions code virtually never has to plumb around (Many kernel APIs/memory management, network protocols, various programming language abstractions, etc).
If an abstraction requires "plumbing around" in almost every project, that is indeed a problem with the abstraction.
Wow, kunley, I just had a flashback to 15 years ago when Object DB zealots were making these same kind of dogmatic go-back-to-set-theory arguments.
Yes! We get it! We got it years ago: ORMs are a compromise over an irreducible set-theoretical problem. I got it in CS class and I get it now. You can't square the circle -- no one is claiming to have squared the circle.
But for 90% of the cases, it turns out that really experienced developers use and leverage ORMs to boost productivity and eliminate code duplication. It happens every day. It's not habits and tools, it's from careful consideration of the results of what happens in practice. Good Baconian Science should teach us to go out into the world and observe and report. And what we've learned is that neither side of the Object/Relational tension really ever fully wins out, and what works is experienced devs using the right tool at the right time.
We code in the real world, get burned by bad practice and adjust. That's why ORMs have survived, because good devs have found ways to used them as effective bandaids on difficult problems. We don't code in a classroom and I couldn't care less whether my code violates anybody's CS or Set Theory dogma. I have features to ship.
Well, just assume that I'm also speaking from experience of delivering complicated applications to production and I'm tired of how ORMs become a pain in the long term:
- they create a dangerous illusion of an leaky abstraction which leaks very quickly
- they require programmers to learn 2 things anyway
- they initially speed up the coding, but later slow it down, esp. when you have to tune stuff (how many times you peeked generated SQLs only in order to feed them to EXPLAIN ANALYZE... ?)
- they can promote bad habits, code which takes your engine to a crawl
- they hinder debugging, esp. tracking performance problems, often effectively putting a problem under the carpet to be discovered by admins
There's also a funny thing: most of the world seems to happily use active record (as a pattern), although when you go deep into it it occurs far inferior to data mapper (as a pattern). That also demonstrates that many people are using habits, not engineering, and stick to what's used around instead of making some investigation on the topic. This is not bad per se; but when it comes to discussing problems, such people are not in the position to argue, precisely because they haven't done their homework.
So, the problems above are caused by the mismatch which one can expect from the theory. You've got what you paid for.
I guess we all could learn most from the history of relational databases or just try to remember what relational databases really are and what other examples of this technology are around. For example Prolog is relational (apart from logic engine). If one does something in it then it becomes more clear how much it "fits" objects or not.
I'm not in love with relational databases or ORM by any means, but your post sounds like document database marketing.
Document databases are usually not a perfect fit either in my experience (I have worked on a few MongoDB projects). The fact remains that you are responsible for storing the state and retrieving that state in an optimal way (this is where you usually end up writing some custom document db queries, or creating large multi-indexed collections specifically for reports).
What would be ideal is if state was managed transparently for you using a lot of shared / clustered memory and a transaction log. I.e. something like Terracotta in the JVM (No I don't work for them, and have never even used it. I just love the theory behind it). You'd still want to archive data somewhere (Relational or Document), so you would still have that complexity for large datasets.
Forget NoSQL, how about NoFetch? (oversimplified perhaps).
Erm, sir, philosophies? I'm talking about computer science as a foundation of hacker's work. I haven't merely stated my opinions, but mentioned some materials recognized as state of the art in this science. Do you propose that we abandon it and put it into a bag of relativism? Because if you do, we can just ask PG to shut down this forum and go for a beer.
I think the "right tool for the job" argument is weaker than it appears. It implies that the job is fully defined before we start to model or even before we start to think about the problem.
Application requirements may sometimes provide broad limits for technology choices but the link isn't very strong as there is a huge gap between the problem space and the solution space (evidenced by the army of software developers needed to translate between the two)
Software is not hardware and it's not even engineering in my view. The extent to which we in the software space are able to define and redefine jobs as well as tools makes this kind of thinking useless.
Good point. Maybe you have to choose a tool that allows you to discard it if you find a better tool once the problem becomes clearer. So, the question is: are ORMs easy to "unplug" if you need to use a better tool?
The problem with unplugging the ORM or inter operating is that the 2 biggest ORM frameworks are implemented such that they don't support composite primary keys and have stupid requirements like having an auto increment column called "id" on every table (including tables used as part of a many to many relationship!!).
As far as I know Doctrine 2.0 absolves a lot of these problems but I've not used it yet.
what cases are ORMs useful for, and what cases are they bad at?
That is covered in the article. ORMs are an advantage in the very early stages of a project, but a disadvantage in the later stages. Since a project by definition spends the least amount of its lifetime in the early stages, don't paint yourself into a corner!
You (and the article) would be correct if the ORM/no ORM was a black and white affair. However, as others already pointed out in this thread, it is not. It is perfectly viable to use an ORM as the basis and gradually switch to 'raw' sql if the need arises.
For most of my projects I use Django nowadays. Its ORM is usually sufficient. In cases where it is not I add a query method to a model class that just executes the most efficient sql query possible and returns the results in the most efficient format possible. For mature applications this can result in quite a lot of those query methods and the ORM than plays a lesser role.
However, even in those case the ORM continues to be a convenience to me as a developer, e.g. because it powers the Django admin interface and because together with South (a database schema migration tool) it makes schema management a breeze.
An ORM is a tool. And each tool has its own place and time. So, yes - there we go again ...
"together with South (a database schema migration tool) it makes schema management a breeze."
Thanks for mentioning this. I'm just learning Django, mainly for using its admin interface to avoid a lot of CRUD, and South seems just what I need to evolve my schema while I go along.
Early vs. later isn't the problem. You can write n+1 queries in your first model or your 1000th. The problem is developers who don't know how to watch logs, don't know how to write efficient SQL, and projects that don't use adequate monitoring in general. If your ORM use is getting worse as you go, you probably need code reviews or NewRelic or better developers.
Yes that's the whole point - ORM is a crutch developers use to avoid learning how databases work. Yet weirdly they're happy to learn the ORM! Hell in Hibernate you have a query language called HQL, why not just use SQL!
Said developers will never take the next step of swapping out bad ORM-generated SQL for good hand-tuned SQL. The app is therefore doomed.
You seem to have worked with a lot of lazy hack developers, and I can only sympathize. I hope you find a better group of colleagues soon, and maybe you'll find less use for words like 'never' and 'doomed'.
The logic behind articles like this can be summarized as "trains are an anti-pattern because you almost always eventually have to go somewhere where there are no tracks or stations".
Getting tired of this. The fact that a tool or method has limitations and risks doesn't make it an anti-pattern.
The article makes a completely different point. It says, if you're travelling from Moscow to Irkutsk don't take the train just because it departs an hour earlier than the plane.
You are correct in that that appears to be the main point of the article. However, the analogy is unfair.
Airports are in relative short supply, so once you've taken the train, your stuck on it for quite a bit.
Migrating away from an ORM or using raw sql queries in addition to an ORM is possible at all times (with more or less work, depending on the ORM you've chosen). So you can start with the train and hop on the plane anytime you want or need.
Hi! Author of the post here. Much as I'm happy to have this go around again, this is actually a dupe, and has been very extensively discussed on HN before:
The reason it has a different URL now is a long story: my hosting provider had a meltdown and I was less than religious about backups for my personal blog (oh, the shame!) so I lost timestamp information on this and a few other posts, as well as all the original comments. So it looks like I posted this a month ago, but it's actually more than 3 months old.
Anyway, feel free to argue about this all over again -- it's new to you. I just felt I'd post so nobody who remembers this the first time around thinks I'm intentionally trying to game HN.
And yet, DBIx::Class (and Class::DBI before that) daily make my life easier over large, complex applications. The reason I use it is not because I was told to by someone, but because it was a better way of doing what I'd find myself doing in every bit of DB code I wrote anyway - abstracting the data layer. DBIC does this in a predictable way that I know most other Perl programmers will also understand.
So I'm sorry your ORM failed you. But there's no way in hell I'd ever go near your codebase if you're shying off them in favour of raw SQL, but also because talking about things as being 'anti-patterns' is ... urgh.
She makes a bunch of great points, but as someone who has worked on several projects with a properly implemented ORM, I find that I have to disagree with the post.
It sounds like a lot of people who are Anti-ORM in the comments (here, and on her blog), are focusing on the saturation of relational databases, and touting that NoSQL style data stores are a better fit for certain problems.
No argument here
But in a relational DB, assuming you are using it for its intended purpose (storing relational models, and not "objects"), I don't see why anyone would want to eschew the use of a proper ORM, with the exception of incredibly small projects with minimal database access.
For medium to large projects, I've never once regretted using an ORM. Not having to write my CRUD statements? Having all of an objects relational data lazy-loadable and at my finger tips? Sign me up! Just my experience.
I feel as though this author has worked in environments with other developers who didn't "get" the benefits of ORMs, and thus the implementations of those ORMs suffered. This is just my opinion from what I've read. I could be wrong here.
Death by a thousand queries, however, is a completely valid complaint. In fact, it's my one of my only major gripes with ORMs. But maybe I've just never run into these cases that the author has that makes ORMs so unbearable to use. Maybe i'm just lucky?
ORM is bad for reporting and complex queries, but it makes CRUD bearable. The amount of code required to write CRUD subroutines for every kind of data present in database tends to bore absolutely to death, with endless realms of near-copypaste code. Any good programmer would throw together a half-baked ORM just to DRY database access code.
I don't particularly disagree with anything in that post, but the fact is that the code time saved with an ORM is so massive, that NOT using one is effectively gross premature optimization.
Sure, as your app grows, you write SQL to speed up the bottlenecks. And that's O.K.
It depends on the ORM you use, but I'm afraid it's not as simple as "you can later hand write SQL queries to speed things up". This will interact badly with the ORM's cache/transaction handling: your select queries won't see uncommitted changes held by the ORM, and even worse, your ORM won't see the modifications you made with insert/update/delete SQL queries.
Also, one of the pro ORM arguments is that it generates database-specific SQL, making your code theoretically portable between different RDBMS. Hand writing SQL will break this.
Another point: your ORM would automatically pick up changes to your model and cope with it, not your hand-written SQL, making your code harder to maintain.
>> ... one of the pro ORM arguments is that it generates database-specific SQL ... portable between different RDBMS
Yes, hand writing SQL will break this but:
- it is rare in my experience to completely migrate to a different RDBMS
- migrating to a different RDBMS should easily be viewed by everyone from management to development as a very tricky process that will take time to perform correctly. Part of this time will be tuning your custom queries.
- the ORM promise that it's queries are RDBMS independent is not broken
>> Another point: your ORM would automatically pick up changes to your model and cope with it, not your hand-written SQL, making your code harder to maintain.
Writing everything by hand (known alternative 1 from the article) makes your code even harder to maintain because there will be zero changes that are automatically picked up.
Depending on the KV store you use, his second alternative may not have this problem.
TLDR:
Hand writing sql comes with a cost, but the overal cost of "ORM + a bit of handwritten sql" is likely to be much less than "everything handwritten".
In general, people don't migrate between databases. In specific, I find myself migrating from sqlite to either Mysql or Postgres relatively frequently.
That depends on what kind of products you make: If it's a website, then you have absolute control over what stack and database to use. But if you're selling any kind of app that needs a database, then you'll need to support different databases. Some customers won't ask questions and take your default database, others (unfortunately ?) will want it to use their Oracle/SQLServer installation that cost them a bazillion.
These are limitations of specific ORMs or ORM classes, not of the concept in general.
> Another point: your ORM would automatically pick up changes to your model and cope with it, not your hand-written SQL, making your code harder to maintain.
You mean, I'd need to re-write LESS hand-written SQL with model changes. This is a selling point, not a down side.
Nonsense. Everything is overridable. ORMs are perfect for a whole lot of standard cases and nobody prevents you from writing SQL code for custom cases and performance improvements.
we use Datamapper at work and have an increasing need to write custom sql queries. A common problem we face is that we now have non-standardized data coming out of the model. In other words does this method return a datamapper object or is it an array of custom-defined fields ? a quick fix would be to use the custom query to return ids and then use those ids to perform a Datamapper query that returns DM objects - which is clearly not a good programming practice.
In my experience this article is spot on. It's easy to say "oh sure just use custom sql for this edge case" Only those edge cases turn out to be increasingly common and it is increasingly hard to work with data that may be coming at you in multiple formats.
So your data is coming back at you in 2 different formats. We have the same issue and while it's not wonderful it's certainly not the end of the world. Naming conventions can help you know the return type of functions.
And if you understand the types of queries being done under the hood by your ORM then sometimes using a custom query to retrieve ids to be used in a simpler ORM query is not such a bad idea. I think it's a case of doing things consistently in your code base so you use your ORM when it helps you and don't fight it when it's not going to help you.
There's no reason why an ORM can't allow custom SQL and still produce the same output format. Or close enough that duck typing will get you the rest of the way.
The problem is that, plainly, your ORM sucks. Serious ORMS allow you to inject custom SQL and still return objects; many can just take the result of a plain SQL query and map it to objects as you please.
Really, there are good and bad ORMs. Bad ORMs produce the results you mention. Good ones are much more adaptable.
thanks for this! You are right I had no idea this existed. Just goes to show I really should read the source over depending on datamapper.org/docs/ too much.
If you use the second solution mentioned in the article and just use only sql in your model, you would end up with a more varied amount of non-standardized data coming out.
The other existing solution provided by the author is to switch to a key-value datastore, which still would not solve your problem.
Instead of trying to use a relational database in an OO way, maybe it's better to go the other way and make code fit the relational model better. Databases (and the data within them) often far outlive software.
Ideally we'd have languages impedance matched to making assertions and queries in the logical language of relations. Prolog? (Googling "Prolog SQL" turns up this Stack Overflow discussion about translating Prolog to SQL http://stackoverflow.com/questions/724377/prolog-to-sql-conv...)
I wouldn't go so far as to describe ORM as an anti-pattern. I'd rather just say that I'm more productive in the long term without it. Like the author of this post, I always eventually end up dropping raw sql to get things done.
I don't even think necessarily much of an initial productivity boost. SQL doesn't take all that long to write, and once you do, you have complete control over your app.
I'm not completely sure what's meant by the statement "Use SQL in the Model." I think I agree, though I'm hoping this doesn't mean including SQL in the model class itself. I'd avoid coding references to how an object will be persisted in the model class itself. I'd lean toward creating a DAO (or some other API for persisting/retrieving an object) that you can use/call without worrying about how it is happening. The difference is that I'd rather implement that API through SQL than through an ORM framework.
ORM isn't a pattern. Most people complaining about ORM libraries are complaining about Active Record. I thus automatically assume anyone making this claim is misinformed, and roll my eyes.
Active Record is a pattern. Ruby's ActiveRecord and Django's ORM are implementations of that pattern. The Active Record pattern has some of the problems mentioned in this article, with convenience being the major benefit.
Data Mapper is another pattern. Python's SQLAlchemy and Java's Hibernate are implementations of that pattern. These will get you a lot farther without bumping into limitations. And honestly, I've written some terribly ugly queries in SQLAlchemy and it still worked. I've even started with an ugly query I wanted to make in raw SQL and then made sure that it was possible using just the library and no SQL strings.
Why does this old junk keep getting reposted? Is someones boss making them use an orm against their will? Go work somewhere else and stop dumping on our tools.
Let's be fair for a moment. Now I admit to skimming the article, but it sounds like the author is more complaining that he hasn't seen an ORM implementation he liked. Perhaps he hasn't used SQLAlchemy yet. :-)
I'm not saying that to be snarky. If I hadn't used a good ORM like SQLAlchemy (although I'm sure there are others out there), I'd be inclined to agree.
Laurie, this is a good restatement of the age-old problem of Object/Relational impedance, the perennial bugaboo of many an architect, developer, and DBA. It's true that ORMs are merely bandaids on what is essentially an intractable set-theoretical problem, but we've learned over the years that in the right hands an ORM is a tremendous productivity boost, provided the team is aware of the limitations. In fact Martin Fowler's original definition of the ActiveRecord pattern in Patterns of Enterprise Application Architecture describes some of these limitations from the very beginning.
"Unfortunately, these advantages disappear as the project increases in complexity: the abstraction breaks down, forcing the dev to use and understand SQL"
"Forced" to use and understand SQL? I think you have a basic misunderstanding of the purpose of ORMs, Laurie. The developer who can't use and understand SQL really has no business going near the ORM. They probably need to stay somewhere near the front-end doing design, layout and UI-centric work.
The purpose of an ORM, properly used, is not at all to completely remove SQL from the developer's toolkit, but to help developers who fully understand SQL and Objects eliminate boilerplate SQL. This has a huge dual benefit of productivity boost plus elimination of bugs by the reduction of code/SQL duplication.
If an ORM developer is not aware of SQL (i.e., watching their rails log for odd queries/joins) and the natural problems that arise, such as n+1, etc. then they probably shouldn't be writing data-layer concerns. ORMs are for experienced developers who know how to use things like the ActiveRecord or DataMapper DSLs when appropriate, and can also drop into straight SQL when appropriate. ORMs are power tools for serious developers who can use them for what they do well, and know how to avoid their weaknesses, which you outline well here.
Because of their benefit and the cumulative experience we've had in the last 10 years or so getting to know how to use ORMs effectively, they're not going away any time soon. However, they certainly have their pitfalls and it's important that we have posts like these every few years or so to remind us what those pitfalls are. So thanks!
> The developer who can't use and understand SQL really has no business going near the ORM.
Absolutely. First we have developers trying to use ORM (a.k.a. HideSQL) to avoid learning SQL. That fails. Now we have developers trying to use NoSQL to avoid learning SQL. This "avoid learning SQL" thing is shaping up to be a fantastic anti-pattern. I wonder what's next.
Years ago, before ActiveRecord, I started writing my own ORM for Ruby. I got a reasonable way into it, too.
I gave it up, because I realised that it was much, much simpler to write many small PL/SQL views and write-triggers and use temporary local objects and/or lazy writes (etc). The DB could do much better query-optimisation this way, for one thing. But anyway, that was a long time ago.
TBH: If you want all your relational logic in your app, NoSQL systems make an awful lot more sense these days -- esp. if you've got a decent API/wrapper-layer that can automatically manage the freshness of locally cached data for you.
Somebody correct me if I'm wrong, but aren't most of those complaints related mostly to the SQL generation rather than the "mapping" part?
I find that the problem when discussing ORMs is that we gave them two completely different responsibilities: generating queries and mapping the results... In fact, those two are already separated in Rails (AREL?).
I don't see much of a criticism to the mapping part (and the benefits given to you in form of validation/metadata/migration/schema versioning)... In fact, unless you're going to access the records directly via the database driver API (giving way to hideous coding), you'll probably reinvent the wheel or do the O-R mapping by hand to fill POCOs/POJOs/etc, as I've seen on so many enterprise projects (in different levels of elegance).
But then, again, somebody please correct me if I'm wrong.
ORMs leak abstractions? Yes. ORMs are an anti-pattern? Thats a little hard to swallow. As many people have pointed out on this thread, I use ORMs as just a tool to avoid writing the darned boilerplate code. As many times I have started writing simple code to abstract the boilerplate, I inevitably endup writing a half-baked ORM tool, at which point, I throw it and just use an existing one. The key advantage for me is not that it abstracts SQL, it does not do it at all, but that it abstract so many other unimportant details that I must solve to fix the "impedance mismatch" between two different systems - an OO language and a relational system. I use an ORM to do just that.
The thing with ORMs is they generally are really more like data structs than full-on objects, but ORMs cause some devs think that's what objects are. When in fact an object is just a bundle of capabilities. While the ORM operations (select, delete, etc.) are capabilities, they don't specifically model the capabilities of the system, and in fact many times these operations should be unexposed outside of the object.
He lost me when he constructed a false problem by claiming that the result of a query sent through the ORM has to result in discrete objects mapping 100% to the "base-form" of the objects, ie their table-based definition and that this causes immense problems when you have relations and dependencies based on those.
In C#, using LInQ I can specify exactly what columns I want, how I want things joined, and how the resulting object should composed, without the need for adding additional named classes or augment my data-model. I get control of what gets sent to my DB and I get only the information I need returned, in the form I desire. And I certainly hope that C# still qualifies as an OOP language.
His rant seems to be that inadequate ORMs is an anti-pattern. But isn't investing anything in something you know to be inadequate by definition an anti-pattern? He is making his own circular argument and then trying to sneak up the scope beyond what is valid.
Needless to say, I think his argument is simplified, based on dated information and this invalid.
Hand-coding and maintaining bog-standard glue logic at the start of a project isn't good either. Auto-generation of database and glue logic for your app is possible, and the task usually falls to an ORM. The problem as described is that the glue logic is trying to hide the implementation, making it hard to remake the code when it becomes clear that the out-of-the-box implementation is not appropriate.
This doesn't seem like an insoluble problem; rather than use an ORM to generate an impenetrable glue layer, it would be better to use code generation tools to create a clean database model and a glue layer that can be easily overloaded as required.
What tools exist that give you ORM-esque generation at the start of your project, but allow you to do maintenance on the glue layer later in the project?
> This doesn't seem like an insoluble problem; rather than use an ORM to generate an impenetrable glue layer, it would be better to use code generation tools to create a clean database model and a glue layer that can be easily overloaded as required.
Glad you brought that up. I too thought about this as a middle ground solution between "boring your self to death by writing dozens of SQL queries and hand mapping to your model" and "black box ORM that generates crazy SQL queries".
I'm not sure it would work though. One of the problems I see is inherent with code generation approach: what happens when you had to modify the generated code and then changed your meta-model and want to regenerate the model again ?
Besides, the problem of "fitting square pigs in a round hole" would remain: SQL is much more versatile and flexible than objects/structs: you simply can't just work with the a table=an object abstraction, which tends to yield inefficient database queries and/or headaches from the developers side. Example:
update table set one_field=value where some_condition_on_another_field.
How would you expect an ORM to generate a "dense" query like this with it's simplistic view of things ?
The same applies for the select queries, especially for reporting and data aggregation needs, where you would select some columns from multiple tables, possibly with formulas and computed columns.
I think the problem per se isn't treating rows as objects and just querying one row to read the data for one customer or transaction or whatever. I think that the taxing problem comes from fitting these records of data into an overly object-oriented style of programming, and feeling the pain when you're actually hitting into this mismatch.
Then you might go all the way and try to abstract away the fact that you're reading a record from a database. For what, I don't know, but it feels often that there's some perceived benefit in pretending that everything, including data, is a native object and all objects are alike--even if you have a heavy marshalling mechanism to prove that isn't the case.
Also, learning a different ORM every time you switch environments is costly. Switching from, say, Django to LINQ2Entities has nearly no knowledge transfer. Dealing with the quirks between MSSQL, postgres, MySQL is comparatively simple.
It's definitely just ActiveRecord and not ORM that cause the problem in my opinion.I've started work on an alternative which focuses on automating just the most tedious parts of writing sql then getting out of the way (and requires no boilerplate classes - it intuits the relationships from the key names). Oh and it's fast as hell and uses very little memory.
I am very much liking Entity Framework's Code-First process. Instead of generating code to match a relational database, generate a relational database to match my object model. (So you have full control over what your data objects does)
Combine that with LINQ and you have a 80-90% coverage of complex joins.
Also combine it with CQRS principles and I hope that when my project requires scaling (and I so hope it does) then I can easily scale
I don't have a ton of experience with ORM, but I've always wondered how much time is saved trying to abstract SQL queries away from SQL when SQL is supposed to be relatively easy for most people to get into. At least for the simple use cases that ORM is supposed to be good at, the SQL equivalent should be simple as well right? Or am I not quite understanding the benefits of ORM?
It's not just the SQL. There's also the mapping code between your objects and the SQL. for instance, for a select query:
select first_name, last_name, login, email from users where id = 5;
Depending on the language and ORM you use, you'll need to write code that resembles this:
result = execute(query);
User u = new User();
u.firstName = result.get("first_name);
u.lastName = result.get("last_name);
u.login = result.get("login);
u.email = result.get("email);
The mapping part may also involve data conversion between SQL types and you language types.
The same also applies to the other direction: when you want to generate an SQL query that updates/inserts an object of yours into the database.
ORMs also handle (I didn't say well) relations between objects (1..1, 1..n, n..n).
after being forced to write a crud app using just stored procedures because of 'performance reasons' and 'security reasons' (n+1 queries and queries with sql injection problems were still written including by the person who mandated this) i don't really like people writing articles like this. even though it may be true in some cases it just gives ammunition to idiocy.
This article has one main point that I have come to agree with. If you are using an ORM, consider that you should actually be using a NoSQL database instead of a relational one.
It may be the case that you don't, but really -- if what you want to do with objects is retrieve them, modify them, and save them, then a key value store is more like what you need.
I find the author to be looking at ORM the wrong way, the way I see it: ORM just gives you basic features that you would probably write yourself, you still have need SQL for more complex stuff.
Basically it's just a standard way to do the basic stuff.
If you want optimized select queries, you can still write them, also inserts are optimal with ORMs.
Inserts are not at all necessarily optimal in ORMs. Inserts can be just as complex as select queries, since they can include arbitrary select queries. A trivial case of that being.
INSERT INTO a SELECT * FROM b;
I personally have not felt the need to use and ORM yet. But then I have not built a CRUD heavy app either. Instead most of my time spent on database work is centered around reporting and other complex queries where no ORM I have seen yet helps much.
The answer is obvious - use an ODBMS instead of an RDBMS. No more imedance mismatch. The issue is that there hasn't been as much development, particularly high quality OSS projects, of this type. For example, I can't even find one for PHP.
In the good old times, when sky was more blue and trees were taller, there was two almost distinct approaches to use SQL database. They call them OLTP and Data Warehouse. Some vendors, such as Informix or IBM even offered a different products for those two segments.
The firs one, as one can understand from its name, is about fast and reliable online transaction processing, which means triggers and stored procedures and loads of small queries, mostly inserts or updates. Now all those no-fsync options and noSQL and mem-caching solutions solve similar problems their own way. You can have a thousands of writes and, if your system never crashes, with some data consistency (and forget about triggers) In old good times Informix Dynamic Server was the answer.
Data Warehousing is all about JOINS, big JOINS. Or that thing they call it data-cubes. In that case you need query optimizer, lot and lots of buffers, data partitioning and several layers of caches. You also should use stored procedures, because it is a good way to structure and manage your code, same way modules work for other languages. So, you know, that old lovely DB2.
Even in old times, people who claims that there is a solutions that fits both cases were considered crazy. That is why no sane person considered MSSQL (leave alone MySQL) as something other that a taste-less joke.
Nowadays people forgot about designing in terms of data flows. Everything starts with installing some framework, such as Spring+Hibernate, Rails or some PHP crap. They forgot that not tables itself, but queries (which type, how often) to them is what matters, that indexes optimized for actual query flow is what performance of a server is all about, and that actual structure of tables (and corresponding indexes) must be adapted/redesigned for that particular production flow of data. That was a DBA's job.
Today some people believe that they can eliminate smart but costly engineers (DBAs or sysadmins) by some software which is marketed to them as Easy, Smart, Fast, Zero-thinking or whatever - ready meals for a mediocre crowds. OK, if you're building a 20 pages web-site for a 100 visitors per day, that might work - you can save some money and time, but, if it is a industrial or internet-scale solution, there is no chance that you can run Rails or say Code Igniter crap in production without huge changes or total redesign. No fucking way.
So, all those specialized solutions, such as memcached, redis, membase, mongoDB are about dealing with flows of technical data, such as AJAX queries from UI, logs, authorization requests, chats, photos and other unimportant things, OR about building a huge distributed cache layer above actual data store. But, of course, you cannot build a Data Warehouse out of it. (Or invent a complete different approach to dealing with data, such as map/reduce).
So, ORM is anti-pattern? It is not efficient? Ridiculous. It is just broken by design. ^_^
This certainly doesn't match with my experience, and I've worked on dozens of projects using ActiveRecord. Some of these were hairy production projects with strict performance requirements and complicated schemas.
About 90% of the time, the ORM does the right thing. It creates, updates and deletes individual records, and it generates entirely reasonable queries. And it does this without requiring dozens or hundreds of lines of SQL.
You'll get much better performance out of ActiveRecord if you know a few tricks, however:
- Read the SQL queries in development.log to make sure they're sane, and use New Relic to find slow queries in production.
- Use 'include' to load a model and its associated objects in 2 queries instead of 1+N queries.
- Use 'select' to load only the necessary columns from your tables.
- Don't try to force it: If you hit something weird, never hesitate to fall back to SQL. ActiveRecord provides a dozen different places where you can inject small SQL fragments into your queries, or you can just call 'find_by_sql' and do everything by hand.
If you start out with basic ORM models, benchmark your database, and add some raw SQL as you scale, you can get reasonable rcesults with ActiveRecord. If it breaks down for 100% of projects, you either have a weird definition of "breaks down", or you're doing it wrong.