8

I know this question might be closed as opinion-based, but what I need right now are some opinions supported by arguments.

I am building an application with Postgres and Ecto (Elixir) as the persistence-layer. There is an entity that references itself so that you can build a tree-like structure with it. The more I am trying to do this with Ecto, the more frustrated I get.

Are ORMs simply a bad tool for creating complex DB-structures with many associations? The object-oriented way that ORM tries to enforce upon relational data seems to be a bad approach here. Objects are isolated. If they interact with other objects, they (are supposed to) send messages. Their inner details should stay hidden. Relational data form an open, transparent graph. These two worlds seem to be completely incompatible to me.

Yet, ORMs are very common and popular. Does majority of web applications work with rather isolated entities that play well with ORM, or why is that? It seems to me that if you wanted to implement any averagely complex ERD model using an ORM-framework, you have to either sacrifice concise code or performance.

Improve this question
6
  • 1
    Each database system may have a slightly different implementation to support a tree or "hierarchical" data structure. For example, Oracle has START WITH and CONNECT BY and Sql Server has Common Table Expressions (CTEs). So, before picking an ORM, one should determine whether it has the proper support for these types of data structures. Some offer very good support, while others may not cover those scenarios. This question should be re-worded: Does Ecto support tree structures?
    – Jon Raynor
    Commented Mar 9, 2017 at 16:17
  • Since you mentioned postgres specifically, it supports recursive queries for this type of thing (though I haven't used it myself)
    – Izkata
    Commented Mar 9, 2017 at 23:11
  • 1
    Of course ORMs are a bad tool. There's already a language for expressively expressing queries on databases: SQL.
    – mrr
    Commented Mar 14, 2017 at 21:18
  • @Miles Rout In most cases when you use plain SQL instead of an ORM you're repeating yourself a lot.
    – Jimmy T.
    Commented Mar 24, 2017 at 17:37
  • @JimmyT. That's false.
    – mrr
    Commented Mar 25, 2017 at 1:36

3 Answers 3

Reset to default
9

At the end of the day an ORM is just an abstraction which generates sql for you and maps the data to your objects. Saving(tm) you some 'boiler plate' code.

So there is nothing that ORMs as a whole necessarily are bad at by definition. The problem is that you don't use ORMs as a whole, you have to pick a specific one and use that!

An individual ORM may well not do a particular thing very well. Stored Procs, dynamic sql, calculated fields, complicated joins etc can be problematic areas.

A more subtle problem is that as an ORM tries to handle all these scenarios in a generic way it gets bigger and more complicated to use.

If you have a large or complicated application, it's likely that at some point you will hit a problem with the ORM you have chosen. So it makes sense to plan for this in advance and ensure that you hide the ORM behind a repository. That way you are free to swap it out for an alternative, or go back to hand coded SQL if required.

Improve this answer
9
  • 1
    It should be noted that, in lots of ORMs (at least every one that I've used), you can write and execute raw SQL. Therefore, if the ORM does not satisfy some constraint well, you can always resort to doing it with native SQL, while simultaneously reducing the 'boilerplate' operations of SELECT * FROM some_table WHERE x = whatever that are extremely common. Hierarchical structures are handled well in some ORMs, and terribly in others, so this type of consideration should be researched before deciding to forgo ORMs entirely.
    – Chris Cirefice
    Commented Mar 9, 2017 at 17:42
  • you can usually do this, but it doesnt always work the way you want it to! recently had this exact problem with nHibernate and nullable field mapping
    – Ewan
    Commented Mar 9, 2017 at 17:45
  • I've never used nHibernate, but I have used some such as Rails' ActiveRecord, OrmLite (Java), and ActiveJdbc (Java). Each of these handles executing raw SQL differently, e.g. ActiveRecord makes it really easy, and OrmLite made it really difficult. ActiveRecord has been in development for years and years, so surely that has something to do with it. All these little nuances are part of feasibility testing, but sometimes you miss the little things that come back to bite you later...
    – Chris Cirefice
    Commented Mar 9, 2017 at 17:48
  • yeah, thats why i say put a wrapper around it. you can still use the orm, but youre not locked in if lets you down in some way
    – Ewan
    Commented Mar 9, 2017 at 17:50
  • Saving(tm) - I chuckled a bit at this.
    – corsiKa
    Commented Mar 9, 2017 at 19:18
7

Are ORMs simply a bad tool for creating complex DB-structures with many associations?

No. As an example, Ruby on Rails uses ActiveRecord, which handles associations. In the example here : https://stackoverflow.com/questions/5109893/rails-how-do-self-referential-has-many-models-work , the tree-like structure is accomplished with 2 lines of code.

So I would argue that it is waaaaay easier than trying to roll your own sql queries.

Does majority of web applications work with rather isolated entities that play well with ORM, or why is that?

Probably not, but that's just guesswork. The ORMs exist in an ecosystem where they are thriving, suggesting that they are being used. I have used ORMs for systems with over 20 associated models and found them to be fine. I have never forced the objects to be isolated with only message passing.

As a summary opinion, if you are making "complex" ERD models, there is no tool that makes them easy. Only tools that make them work.

Improve this answer
4
  • Yep -- I used Entity Framework for a project which had CustomerId (PK) and ParentCustomerId (nullable FK pointing back to the customer table) to create n levels of customer hierarchy trees. Had no problem here (although many null checks/ .Any() LINQ statements)
    – USER_8675309
    Commented Mar 9, 2017 at 14:43
  • Hibernate (Java) is another good example of an ORM that can handle trees without issue, requiring virtually zero extra effort. Query complexity is hidden from the client. Parents and children can be lazy-loaded. The parent object ID is just another column in the database. An ordered list of child nodes can be transparently supported as well with the addition of a column that holds a list index. (Unordered vs ordered collection considerations are not unique to trees here.)
    – Jason C
    Commented Mar 9, 2017 at 19:50
  • I cant help but feel that this is a misleading answer. Because one ORM handles this case, does not mean that ORMs in general can handle this. Indeed a quick google shows that active record DOES have gotchas and bugs in some versions around self referencing github.com/rails-api/active_model_serializers/issues/211 and coderwall.com/p/4d2utg/…
    – Ewan
    Commented Mar 10, 2017 at 9:37
  • @Ewan - I am open to rephrasing my answer, but I think it adequately answers the question if ORMs in general are bad. It is a different question to ask if all ORMs can handle this case, or if they are the best solution. The existence of a less-suitable ORM tool, or a bug in a version does not change the generalization. I also know I've had bugs in my SQL too.
    – cmonkey
    Commented Mar 10, 2017 at 14:25
5

As noted in a least one other answer here, all ORMs are not the same. Some ORMs make very significant assumptions about how the database should be structured. The tooling should provide some support for going outside of this model but the more you go outside of that model, the less value the ORM has. If you are working with a more domineering ORM tool, you will have a much better experience if you design the database around its implicit assumptions.

I've never really understood why ORMs are seen by many developers and architects as essential. This may be due to the fact that I have almost never had a green-field database to work with and the time and effort required to get the ORM mapping done was far more work than writing the SQL. Either the mapping was simple (and so was the SQL) or it was complicated and I needed SQL (or something similar) anyway. The transactional 'support' included in ORM has been more a source of bugs and problems than a help, in my experience.

Your mileage may vary but I've come to believe that it's better to think about database persistence as just a special case of data serialization. And just as I think it makes no sense to believe you can 'send' an object over the wire, I don't think it really makes sense to think of writing objects to a database. I even think it's completely valid to have multiple object representations of the same data for different needs. Mapping objects to tables and vice-versa has become an end to itself as opposed to a solution to a problem. I'm not convinced most people that use these tools even have a clear reason for wanting to do it. It's become a default.

Improve this answer
6
  • 1
    totes agree, once you resign yourself to typing out the sql and mapping it rarely takes longer in the long run and you have total control
    – Ewan
    Commented Mar 9, 2017 at 21:02
  • @Ewan To be fair, I think it take a bit of experience to know how to build the proper abstractions between the logic and the DB. ORMs can help devs get to something workable quickly. It's the idea that objects can be translated to tables and vice-versa which is where I think there are issues. It's true for a subset of data-models and object-models but not in general.
    – JimmyJames
    Commented Mar 9, 2017 at 21:31
  • 1
    @JimmyJames It's true if all you need are very simple per table CRUD operations. Beyond that, a non-micro ORM is a curse. (And I personally find the idea of creating an object for every different set of columns coming back for a micro-ORM to be annoying, although it may be the lesser of annoying things in a statically typed language.)
    – jpmc26
    Commented Mar 10, 2017 at 0:35
  • 1
    @jpmc26 I want to side-step the static vs. dynamic quagmire but I think you are highlighting the kind of thing I am talking about. If all I want to do is pull some data, minimally transform it, and return is as JSON, for example, I don't really need to map to objects to accomplish this. The object has no real functionality. It acts as a map where data is stored momentarily. But I've seen ORMs used to solve that kind of problem. Often the answer is to generate the classes for those objects, but I think that's just highlights how unnecessary they are.
    – JimmyJames
    Commented Mar 10, 2017 at 21:29
  • Somehow, I left out the thing I originally intended to say: The situation is not better with a brand new database that you're developing. It's only dependent on the kind of queries you need to write. For very simple per table CRUD, it can save a little time. But if you have queries that mix and match data from different tables or queries that are complex (complex grouping, CTEs, subqueries), anything non-micro is automatically much more trouble than it's worth. Even if you can get away with very simple CRUD at the beginning, you're betting that success won't lead to greater complexity.
    – jpmc26
    Commented Mar 10, 2017 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.