Don’t write your own ORM

In my last post, I talked about various kinds of patterns of ORMs and how to choose an ORM strategy. From the comments and tweets I got, it seems like some folks still think that their only ORM choices are:

  • NHibernate/EF
  • Roll your own

The point of the discussion was to highlight different ways to do an ORM, and even within some ORMs, different tactics of use. But unless you’re Ayende or Greg Young, you should not write your own ORM. If you have to map relational data to objects, you must use an ORM. Your custom data access layer that is an ADO.NET façade, that is an ORM.

If you are writing code to read from an IDataReader, or manually building up an IDbCommand, stop. As Jeremy Miller put it

if you’re writing ADO.Net code by hand, you’re stealing from your employer or client.

Seriously. Those APIs are horribly outdated and ridiculous to use. All of the manual SqlParameter building, type coercion, DbNull conversions etc. etc. is a solved problem. If you don’t want to bring in a “heavyweight” ORM, that’s great! Instead, choose one of these awesome alternatives:

I’ve seen way too many codebases with hand-rolled abstractions for ADO.NET, and it’s all just extra cognitive weight in the application. Just don’t do it. Except for some very edge cases (which probably means yours is not), there is not a business case to writing ADO.NET code by hand. You’re wasting time writing and maintaining code for a problem that has already been solved.

Minimize the code you have to write for an application. Lean on existing tools. And please, stop writing your own ORM.

Related Articles:

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

About Jimmy Bogard

I'm a technical architect with Headspring in Austin, TX. I focus on DDD, distributed systems, and any other acronym-centric design/architecture/methodology. I created AutoMapper and am a co-author of the ASP.NET MVC in Action books.
This entry was posted in Architecture. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Bogdan Marian

    Howdy Jimmy,

    Since you have mentioned Ayende, here is a rather inspiring article: http://ayende.com/blog/1340/25-reasons-not-to-write-your-own-object-relational-mapper

    Cheers,
    Bogdan

    • Anonymous

      Brilliant! And 6 years old too :)

  • http://twitter.com/Encosia Dave Ward

    I’ve been guilty of this in the past, when messy, brownfield databases wouldn’t map to a sane entity model without a lot of manual coercion. I think the hidden cost in those situations, even worse than the wasted time on completed features, is the friction (sometimes even unconsciously) that it adds to experimental development and new features.

    • http://twitter.com/vkornov Victor Kornov

      Was also going to mention legacy DBs (also, not always MS SQL Server). MicroOrms just don’t have the flexibility and BigOrms are too much of pain to setup. In that case hand rolled, simple stuff is the way to go. If it gets complex, you should have used some other Orm instead :P .

      • http://blog.wekeroad.com robconery

        What *possible* flexibility do you find limiting?

  • Rémi BOURGAREL

    While I agree with you about the fact that we shouldn’t write our own ORM. You are far from reality when you quote “if you’re writing ADO.Net code by hand, you’re stealing from your employer or client.” . I’ve seen many project successful (you know stuff like client makes money, we deliver in time, no bug, we had feature easily …) used writing sql code/mapping by hand, and I’m not sure if they would’ve been so successful with an ORM, because the developers don’t know well those, if you want to use it in professional project, you have to know it very well.

    Become an ORM user is a very slow process (I work since 5 years, and only this last years I used them more than ado.net), and this process starts by discovering by yourself their benefits.

    • Anonymous

      I don’t buy that argument – it goes for any framework you use. Do you hand-build an web server because ASP.NET requires knowledge on how to use? There are very simple options out there that do an effective job of hiding ADO.NET, without much work. Just use those!

      I didn’t mean to imply that projects can’t be successful if they hand-roll ADO.NET, just that it’s a waste of time and resources, in this day and age.

      • Rémi BOURGAREL

         Web Framework are so well done in our days that you can forget about post, get, http headers, session cookies … you maybe can learn about it later for debugging purpose. With ORM it’s the opposite : learn about SQL and RDMS, and then you’ll use ORM because they do part of the job you’re trying to do yourself (but in a better way). But only part, they don’t produce report, they don’t give access to some of the DB features (index, triggers), you can’t do bulk update (I think NH can do that though) …

        • Anonymous

          Yeah, I suppose. Indexes and triggers – those have nothing to do with ORM. That’s about designing the data tier.

          Produce reports? With raw SQL? I do that all the time with NHibernate. NHibernate lets you skip the entity mapping layer to provide a nice SQL facade, where I just give NHibernate a SQL query and a DTO, and it will figure out the rest. That can do bulk update too.

          But again, this wasn’t about NHibernate, but rather that whatever you’re trying to do with hand mapping, it’s already been done.

          • Rémi BOURGAREL

            You think that a beginner should directly use an ORM ? without creating his own ? Don’t you think that this’ll lead to bad practice and bad understanding of the framework ?

          • Anonymous

            A beginner? I’d probably just go with sample exercises of how to use ADO.NET. But in production code? No. ADO.NET is complicated enough, there are much simpler options that are easier to get right than ADO.NET. I don’t think knowing ADO.NET leads to better understanding of ORMs, though. It’s more of an illustration of how bad ADO.NET is to use directly, and instead to use something else that has already solved these problems more elegantly than a beginner could.

            Things I would teach – parameterized queries are important, but how an IDataReader works? Meh, it has nothing to do with solving a business problem. I’d rather spend my time teaching how SQL works and relational DBs work, than focus on the low-level guts of how ADO.NET exposes those features.

  • Anonymous

    Simple.Data (
    https://github.com/markrendle/Simple.Data/) might be worth a mention too Jimmy. Good post, thank you!

  • Jason Walker

    Definitely write your own ORM!  Just don’t do it for a production project.  A lot of the “magic” behind a big framework like NH/EF can be revealed by writing your own LINQ-enabled ORM.  I’m doing this as a brown bag at work here: 
    https://github.com/ungood/EduQuery

    • Anonymous

      Ha yeah, that is a good case!

  • Daniel Marbach

    Hy jimmy

    I’ve looked into PetaPoco, Simple.Data… But in my current project we have a lot of hand rolled monster queries which fill data into datasets. All these frameworks are rather limitted when it comes to those kinds of scenarios. Have you experience migrating legacy databae access to these tools?

    Daniel

    • Anonymous

      If you’re using a DataSet, that’s your ORM of sorts. You’d first need to migrate away from what you’re mapping to (DataSets) into normal DTOs. Then it’s pretty easy to migrate.

      • Daniel Marbach

        Hy jimmy
        We are already doing that. The problem is that the datasets are scathered everywhere. That makes it very time consuming to migrate. I really liked Simple.Data and also Dapper. What I need is a tiny ORM which has native SQL support such as petapoco, dapper and servicestack.ormlight. I really like what the guys from service stack did. Need to dig and compare further. Timemto completely redesign that crappy data access our project relies on.

        Daniel

    • http://gregmaclellan.com/ gregmac

      I am currently working on a huge (and largely brownfield) application that is the same sort of thing. Dapper and PetaPoco have proven to be extremely effective, and after having gone though a few iterations with different ORMs and strategies, using hand-rolled SQL with a micro-ORM and returning DTOs is by far the most efficient way we’ve found to operate. 

      One of the things that the big ORMs tend to push you towards (ESPECIALLY when you use the GUI tools) is having “the one” domain entity model of your database — essentially, every table becomes one entity (class). While this can often work in small apps, the challenge in larger applications is they’re more complex. Sometimes when I load a User object, I want that user’s preferences as well, and othertimes I want all their recent posts. 

      In my view, these are two different DTOs entirely, and two entirely different queries to populate them. I don’t event want to see a .Posts property when I’m working with my UserWithPreferences DTO (it’s confusing, and not obvious that it won’t be populated), and I especially don’t want some big ORM to auto-wire up lazy loading on it (oh look, someone wrote .Posts.Count() on the user list page, and now my ORM is doing SELECT * FROM posts WHERE userid=@userid for every. single. user. Why is my app so slow??). 

      If you get into your head the concept of using DTOs (*no* methods, no lazy loading proxies; the only things valid are simple properties and maybe a default constructor that initializes sane default vales), then effectively using a micro ORM lets you operate just like you do today with datasets, but instead you get a nice strongly- typed object (or collection of them), and all the safe and easy to use parameter binding insulating you from working with ADO.NET directly.

  • Pingback: The Morning Brew - Chris Alcock » The Morning Brew #1153

  • Dave Van den Eynde

    I disagree. You can’t decide for all projects, because you have no clue about all the things that are out there. There may be valid cases for writing your own ADO.NET facade but you’re simply wiping them all off the table with a blank statement to “never” write your own.

    I think it’s dangerous to make such blank statements, especially from a community member with such high regards as yourself.

    • Anonymous

      Perhaps, but I have yet to see a case (besides experimentation) where writing your own ORM is a good idea. What are these cases?

      • Dave Van den Eynde

        Define “ORM”? 
        “if you’re writing ADO.Net code by hand, you’re stealing from your employer or client.”What about batch operations? Legacy systems? Interoperability with systems that are not very well supported by existing ORMs, such as old or mainframe databases for which an incomplete ODBC driver exists and your only choice is to manually materialize your objects? Just to name a few.

        The world is bigger than writing simple web applications, y’know.

        • Anonymous

          Batch operations – supported by ORMs
          Legacy systems/databases – supported by ORMs
          Ones which there is no support for that DB – what isn’t supported by an existing ORM out there? Now I’m really curious!

          How would I define ORM? Anything that maps objects to relational DBs, and vice-versa. If you’re in .NET and have to talk to a relational DB, you HAVE to either write ORM code yourself, or use an existing one.

          Thanks for the discussion!!

        • Hshah

          Agree 100%. I’m guessing people who hold this opinion spend most of their days writing web apps. Ever visit the exciting world of giant financial systems and the back end processing that goes along with it?

          • Anonymous

            I guess I’m missing something – this has nothing to do with simple web applications. I use these techniques with the exact systems you’re referring to. Bulk operations, table hints, optimized queries, bulk copy/insert, MARS etc. I just prefer using ORMs with less code than what ADO.NET forces me to. Why waste my employer’s money writing code for a problem that someone else has already solved?

  • Pingback: Do write your own ORM | Programmer to Developer

  • Kelvin Li

    Very good point.  I hate to see so many lightweight ORMs popping up, as if we don’t already have too many things to learn. It’s a technical triumph for the authors. However for us consumers, I just don’t see a compelling reason to use them. Some may say lightweight ORMs have better performance. That is not true if you precompile the queries,  or stick with ObjectQuery, whose query plan will be cached just like precompiled queries.

    • Anonymous

      Where we’ve seen better performance is mainly around startup times for applications. It’s expensive to load ORM configuration for 100 entities when all you need to do for this one app is to just blast a SELECT query into a DTO.

  • Wayne Molina

    Agree 100%. The only time I see people write their own (and it’s usually bad) is if they don’t know and don’t want to learn NHibernate or EF or think everything must be a stored procedure. I can think of only once where the homegrown ORM made any sense and that was as I recall because when it was conceived ORM support was largely nonexistent.

  • Anonymous

    Don’t forget OrmLite!

  • http://twitter.com/FelicePollano Felice Pollano

    I post about my experience on integrating/moving from NH to dapper here: 
    http://www.felicepollano.com/2012/07/25/FromNHToDapperWhatsMissingFromAFullyFledgedORM.aspx

  • Anonymous

    Whoa! Not true in the least. Coders: if you want to write an ORM please do so.

    “…If you’re writing ADO.Net code by hand, you’re stealing from your employer or client…”

    Subjective. You have assumed no other person has the ability to write more proficient and expressive ADO.Net code which is not a valid argument since others whom you consider “experts” were themselves at one time beginners. Had they followed this advice, they would have never tried.

    “…But unless you’re Ayende or Greg Young, you should not write your own ORM…”

    Quite fortunately others have not followed this type of advice. How did Ayende or Greg Young attain this mastery… by writing their own. Had Mark Zuckerberg been told “unless you are Chris DeWolfe of MySpace…”

    “…You’re wasting time writing and maintaining code for a problem that has already been solved…”

    Yes and Charles Duell in 1899 said “Everything that can be invented has been invented.” He was wrong as well.

    Nicholas de Lioncourt

    • Anonymous

      You bring up some excellent points! Enough dissenters that I’ll write a “When to write your own ORM” post.

      • Anonymous

        It is a fundamental difference in viewpoint between entrepreneurs and the status quo. The entrepreneur (or anyone with an inclination toward the creative) looks around at what has been already created with the determination that either he/she could build it better or use it in ways not yet understood.

        The .Net community suffers too much from the status quo which is precisely one of its failures in the start-up community which assumes LAMP is the embodiment of creativity.

        Someone as Ayende or entrepreneurs as M. Zuckerberg follow the entrepreneur path. Why do what many other more-established database platforms have already done? What a waste of time it must have been to write RavenDB!

        My advice to every coder, new and experienced is: recreate the wheel. Try. Use tools in ways no one intended. If you think you might be able to do something better, there is only one way to find out and being afraid that “you’re stealing from your employer” or “Just don’t do it” is a certain path to failure.

        Nicholas de Lioncourt

        • http://www.Marisic.Net/ dotnetchris

          RavenDB isn’t quite fitting to your premise here. There really is nothing at all like RavenDB on this planet. There’s some similarities with Mongo but they are radically different products. RavenDB really create a new world of databases being a nonrelational but fully ACID database.

          • jbogard

            Before believing the RavenDB marketing, I’d check out readings on what ACID truly means (i.e., “Fully ACID” is not an actual term) http://www.bailis.org/blog/when-is-acid-acid-rarely/ and you can’t cheat the definition by constraining the level in which your transaction applies. ACID is a property of a *database* not a *document*. Saying “indexes are BASE” nullifies ACID.

          • http://www.Marisic.Net/ dotnetchris

            I fully disagree.

            “and you can’t cheat the definition by constraining the level in which your transaction applies”

            You can query Sql Server and other relational databases in a BASE sense. Raven indexes are very similar to executing Sql Server queries with the NOLOCK keyword.

            If you need transactionally consistent data, you do not read data in that fashion and use transactionally consistent methods.

            This is just 1 simple example of how you can interact with Sql Server in a BASE fashion, there are other methods. Being able to consume your data in a BASE fashion that results in higher read performance does not negate ACID. A database does not meet the ACID definition if there is no nominal way to read and write data in transactionally consistent fashion.

          • jbogard

            Fine if you disagree, but it doesn’t change the fact that Raven’s claim of ACID doesn’t match the formal definition. So you can’t claim to be ACID (or “fully ACID”, a meaningless term) by saying “well yes, but truly only Snapshot Isolation” or “well yes, but we live in a Read Committed world” or “well yes, but ACID only applies to a document, not to the system/database as is described in papers that actually defined ACID in the first place”.

            It’s also incorrect to compare concurrency mechanisms and isolation levels to Raven’s secondary indexes. You can’t say, “OK consistency only applies to the document, not the index”. That’s the part where marketing alters the definition of ACID.

          • http://www.Marisic.Net/ dotnetchris

            If RavenDB doesn’t qualify as ACID neither does Sql Server, or probably any other modern database system.

          • jbogard

            Have you read the Bailis post? SQL Server most certainly qualifies as ACID, along with many other DB systems, old and new. But they’re not redefining ACID (with the exception of Oracle).

            Raven DB’s approach certainly isn’t new or unique – many other DBs make compromises in order to achieve alternate goals. Couch DB for one has a nearly identical approach to Raven DB – secondary indexes for querying across documents. But these terms are important – they are not marketing terms or wishy-washy, they’re fundamental properties of a system.

            ACID in the original papers describes the observation of state. The very nature of making indexes BASE means the database is not ACID.

          • http://www.Marisic.Net/ dotnetchris

            You do not need to use indexes whatsoever. You can directly interact with transactionally consistent data, there is even range support (LoadStartingWIth).

            That’s why i find the argument that RavenDB is not ACID to be faulty.

            Using indexes in RavenDB is no different than replicating your Sql Server db to a reporting server. There is inherently some level of inconsistency, even with “real time” replication. RavenDB just replicates your db to a different spot on the same machine for indexes. Replication is really what is happening for indexes, the indexes are not actually operating on the database itself.

            Breaking down this argument even further, if I use Sql Server and create lucene indexes using the data in my sql server, according to your definition I no longer have an ACID database.

          • jbogard

            My biggest issue is how the database is exposed to the end user. Isolation and consistency talk about how an outside observer views the state, and the API exposes this as if it’s from the same store. You’re not allowed to query except through an index.

            So you can say, “RavenDB is ACID”. But it’s not – a subset of it is. Or you can say “RavenDB’s document store is ACID” and say querying is not part of the database.

            Either way, it’s either constraining the scope of what you call the database, or constraining the scope in which ACID applies. Neither is consistent with either the API, which exposes everything through one interface (going against established definitions of consistency and isolation), or the documentation. RavenDB made it easy to create secondary indexes, but Lucene is an implementation detail.

            So there’s no magic unicorn solution here. Compromises and tradeoffs either way – which is fine, just be up front about it. What would be appropriate and accurate to say is “RavenDB’s document store is ACID, and you’ll need to manage index consistency/isolation at the application layer.”

            Contrast with Neo4j. It is ACID – including indexes and constraints, completely in line with decades of established definitions of consistency and isolation.

          • http://www.Marisic.Net/ dotnetchris

            “RavenDB’s document store is ACID, and you’ll need to manage index consistency/isolation at the application layer.”
            is a highly accurate statement.

            I would also say that’s a very long winded way to say “RavenDB is ACID”. You always need to manage index consistency at the application layer. You should have literally zero expectation that the data is consistent the moment it leaves the database and is materialized into .NET objects. By the time the objects exist in .NET literally every single value could already have been mutated in the database.

  • http://blog.andrei.rinea.ro/ Andrei Rinea

    With all my ORM hate I must admit that I really like Dapper :)

    • http://www.Marisic.Net/ dotnetchris

      Because it’s not an ORM. It just creates a beautiful DSL ontop of ADO.NET

      • IObserver

        Trol-a-lol-lol

  • http://www.Marisic.Net/ dotnetchris

    I’m also going to push back on “if you’re writing ADO.Net code by hand, you’re stealing from your employer or client.” with a couple of helper/extension methods you can take out the majority of all wasted effort working with ADO.NET.

    Of course I’d make the argument you’d still be better off using Dapper.NET or PetaPoco. As a junior dev I found ADO.NET to be horrific, as a senior dev I find it to be much more substantive. There’s still nothing faster than direct ADO.NET.

    My view at this point, if you need an ORM you should’ve used a document database. That might not help people who are already shackled to their relational design, but i sure hope people who immediately reach for an ORM strongly consider radical alternatives. ORMs are admissions that relational databases make next to zero sense for OLTP systems. If a RDBMS was a true fit for your OLTP system, ADO.NET would provide next to zero friction because you’d be executing simple mono table inserts/selects.

  • Marcus

    I had a EA force ADO on my last organization. I was appalled by the stupidity of the decision. I also had to play along so I didn’t get fired. EEK!!!

  • http://www.giochigratis100.com/ ha vu

    Through your post I have learned a lot for my job vietets all very good thank you for sharing the good information I hope you also have many articles and more thanks!

    Giochi Friv