PetaPoco in the wild

A few brief thoughts on my experiences using PetaPoco micro ORM on a ‘real’ project in a production environment.

The problem

I have been involved with several projects that have used Object Relational Mappers, specifically NHibernate and Entity Framework. For these projects the decision to use an ORM was made at the start of the project and the architecture designed accordingly. The project team had full control of the database and the domain of the project was used to drive out the database schema in a code first style. My entire experience (and biases) of ORMs was based on this kind of usage, together with the learning curve that comes with them.

In my latest project the context was very different for a few reasons:

  • The project was a re-engineering of an existing application, which was in fairly bad shape.
  • It featured a hand rolled data access layer with lots of conflicting and ambiguous helper methods that still required lots of boilerplate to deal with things like mapping the results to POCOs and dealing with connection management.
  • A rather limited domain model, often with one class representing multiple domain entities.
  • Very limited control of the databases in use. One in particular appears to be from a time when Hungarian notation was de rigour and vowels were something to be frowned upon when naming tables and columns. Add to that the spectre of Entity Attribute Value pairs and no primary keys.

After stabilising the code base with sensible separation of concerns and lots of refactoring I wanted to replace the data access layer. I had already separated out the data access stuff into repositories, but the hand rolled data access still left a lot to be desired.

Introducing something like Entity Framework (my current ‘full scale’ ORM of choice) didn’t feel like the right thing to do for a few reasons:

  • First and foremost it would be a lot of work to retrofit EF and would cause a lot of disruption for the project, and could seriously impede forward progress. Although not immense, the learning curve for the team would still take time to overcome.
  • The databases are not ORM friendly in any way. Dozens of poorly named tables, each with dozens (some with hundreds) of poorly name columns.
  • EF might just be a bit too heavy weight. The project isn’t huge, and just needs something to take care of the heavy lifting.

Choosing an ORM

I was aware of micro ORMs and their aims through a couple of the more popular examples, Dapper and Massive. Both are notable by their creators. Dapper was created by Sam Saffron (of Stack Overflow) and is used to drive the data access on the Stack Exchange family of sites. Massive was created by Rob Conery of

Although both were promising, neither seemed quite right. Whilst looking at the performance comparisons of ORMs from Dapper’s project page and I noticed another micros ORM called PetaPoco by Topten Software and on closer inspection it appeared to meet most of my vague criteria:

  • It was lightweight. Although not as small as either Dapper or Massive, it is packed with feature that help to take the pain out of data access.
  • It can be used with POCOs without the need for attributes or sub-classing.
  • It uses SQL to interact with the database. In light of the project this seemed sensible as it would aid refactoring of the data access code. It would be possible to do a straight replacement of the repository methods reusing the existing SQL with PetaPoco’s functionality, and then expanding the domain model as required. This would allow for minimum disruption.
  • It has a notion of transactions, including nested transactions. This is useful as I had introduced a unit of work to deal with transactions across different service methods and had need for some transactions.
  • It had to perform relatively well. The project didn’t call for best of class performance, it just had to be good enough. But its good to know that PetaPoco has it covered fairly well.

Getting started

I grabbed the latest version of PetaPoco core from Nuget. There is also a version with some T4 templates for generating POCO entities from your database. I didn’t need this functionality, and in most cases the database should model the domain model, not the other way round. One of the big advantages of working code first was to keep the focus on the domain and not on the persistence.

PetaPoco is added to your project as a single .cs file, not as a referenced assembly. This would turn out to be useful later on. Many of the micro ORMs work in a similar way and are fairly straight forward wrappers for IDBCommand. If you have ever created your own DAL layer using IDB command you have probably been two thirds of the way to a mirco ORM.

PetaPoco makes the most of the latest C# features. The heavy lifting of materialising entities from the database into objects is done through Dynamic Method Invocation. When a particular entity is requested a method is generated on the fly that takes a IDataReader record and returns the required type. The methods are generated by examining the IDataReader’s columns and creating a custom mapping for the IDataReader record to the entity. The generation is done by emitting and then executing IL at runtime. It’s a deceptively powerful technique when applied to problems like this.

If you aren’t afraid of using the Dynamic feature of c# there is support for that also. During the transitional stages of the project I find it useful for the situations where the domain model is not fully realised and data may be coming from multiple tables at once. Just execute a select and a dynamic object is returned with the properties from the select. Similar things can be done for inserts and updates.

First impressions

My first impressions are overwhelmingly positive, but there are a few things to take into account. In summary:

  • It is very easy to get things up and running. Download the package from NuGet and add a connection string and you are good to go in a couple of minutes.
  • The syntax is easy and intuitive. Most things work as you would expect. There is currently no support for updates to tables with composite keys. In my opinion is an serious oversight. I appreciate that PetaPoco is geared towards working with well designed clean databases, with sensible names and keys, however I think it could easily be used with legacy databases with a few tweaks. Half an hour with the source (I told you it would be useful to have the .cs file) and I had added composite key support for updates. I would hope to see composite key support added at some point.
  • Although I was initially drawn to the POCO support, the syntax is more friendly when using attributes on the entity classes. Indeed some things like result only columns (i.e not persisted) that can only be used through attributes. I found that the more I used PetaPoco the more comfortable with attribute on the classes.

Overall I think that PetaPoco (and micro ORMs) are probably enough for a lot of the application you want to create, especially the type of line of business applications that a lots of people spend their type writing. Before my experiences using a micro ORM on a ‘real’ project I would have always opted for one of the feature rich, enterprisey ORMs, but now I would investigate to see if a micro ORM would work just as well without the weight. They are certainly not only a niche product for hobby projects, and I hope to see more wide scale use in business environments.

Time will tell…


4 Responses to PetaPoco in the wild

  1. Pingback: Homepage

  2. Pingback: Dynamic Method Invocation in C# with PetaPoco « James Heppinstall: On Development

  3. Biju says:

    I am using petapoco as ORM for Mysql .Its return NULL for a nullable date even if table have a date Why? .plz help i have already wrote a lots of code

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: