Micro ORMS: a little play with peta poco and others.

Tags: C#, Micro ORM

Oct 17 2011 9:19 PM

I went to a talk at the Midlands Developer Network, a user group based near Coventry, UK, that try to meet up once a month on Micro ORM’s.

A Micro ORM is essentially a cut-down version of a standard Object Relation Mapper with the primary goal of performance and maintainability over fully fledged functionality.

Ian Russell (@ijrussell) presented an introduction on Dapper.Net, Simple.Data and Massive.Net . This uses the chinook sample database to demo various Micro ORMs. Ian’s demo code is on github, see

https://github.com/ijrussell/MicroORM

During the open coding session, I found another micro-orm called peta-poco which is loosely inspired by massive but relies on Pocos instead of the dynamic keyword.

I included the test class for peta-poco which I have repeated below.

Here is the console program.

   1: using System;
   2:  
   3: class Program
   4: {
   5:     static void Main(string[] args)
   6:     {
   7:         SimpleQuery();
   8:         FilteredQuery();
   9:         PagedQuery();
  10:         Crud();
  11:         SelectProcedure();
  12:  
  13:         Console.ReadLine();
  14:     }
  15:  
  16:     private static void SimpleQuery()
  17:     {
  18:         var petaPocoDb = new PetaPoco.Database("Chinook");
  19:  
  20:         foreach (var customer in petaPocoDb.Query<Customer>("SELECT * FROM customer"))
  21:         {
  22:             ObjectDumper.Write(customer);
  23:         }
  24:     }
  25:  
  26:     private static void FilteredQuery()
  27:     {
  28:         var petaPoco = new PetaPoco.Database("Chinook");
  29:  
  30:         var customers = petaPoco.Query<Customer>("SELECT * FROM customer WHERE Country = 'United Kingdom'");
  31:             
  32:         foreach (var customer in customers)
  33:             ObjectDumper.Write(customer);
  34:     }
  35:  
  36:     private static void PagedQuery()
  37:     {
  38:         var petaPoco = new PetaPoco.Database("Chinook");
  39:  
  40:         var result = petaPoco.Page<Customer>(1, 5, "SELECT * FROM customer");
  41:  
  42:         foreach (var customer in result.Items)
  43:             ObjectDumper.Write(customer);
  44:  
  45:         ObjectDumper.Write(string.Format("There are {0} records matching the criteria", result.TotalItems));
  46:     }
  47:  
  48:     private static void Crud()
  49:     {
  50:         var petaPoco = new PetaPoco.Database("Chinook");
  51:  
  52:         var customer = new Customer { FirstName = "Ian", LastName = "Russell", Email = "i.j.russell@tesco.net" };
  53:  
  54:         petaPoco.Insert(customer);
  55:  
  56:         var id = customer.CustomerId;
  57:  
  58:         customer = petaPoco.Single<Customer>(id);
  59:  
  60:         ObjectDumper.Write(customer);
  61:  
  62:         customer.Country = "United Kingdom";
  63:  
  64:         petaPoco.Update(customer);
  65:  
  66:         customer = petaPoco.Single<Customer>(id);
  67:  
  68:         ObjectDumper.Write(customer);
  69:  
  70:         petaPoco.Delete<Customer>(id);
  71:  
  72:         customer = petaPoco.SingleOrDefault<Customer>(id);
  73:  
  74:         ObjectDumper.Write(customer);
  75:     }
  76:  
  77:     private static void SelectProcedure()
  78:     {
  79:         var petaPoco = new PetaPoco.Database("Chinook");
  80:  
  81:         var customers =
  82:             petaPoco.Query<Customer>(
  83:                 PetaPoco.Sql.Builder.Append("SELECT * FROM customer").Append("WHERE country=@0", "USA"));
  84:  
  85:         foreach (var item in customers)
  86:             ObjectDumper.Write(item);
  87:     }
  88:  
  89: }

The object dumper can be found in the c# samples, please visit here.

The entity class for customers is as follows:

   1: [PetaPoco.TableName("Customer")]
   2: [PetaPoco.PrimaryKey("CustomerId")]
   3: public class Customer
   4: {
   5:     public int CustomerId { get; set; }
   6:     public string FirstName { get; set; }
   7:     public string LastName { get; set; }
   8:     public string Company { get; set; }
   9:     public string Address { get; set; }
  10:     public string City { get; set; }
  11:     public string State { get; set; }
  12:     public string Country { get; set; }
  13:     public string PostalCode { get; set; }
  14:     public string Phone { get; set; }
  15:     public string Fax { get; set; }
  16:     public string Email { get; set; }
  17:     public int? SupportRepId { get; set; }
  18: }

Options… well they all do things differently, I do like having POCOs – but quite like the magic and voodoo in the dapper and massive with the heavy use of IL and dynamic keyword.. Performance wise I can see why they are being used!! In reality if you control your database then it seems like Micro ORM’s maybe a good fit. Although if you don’t control the database structure I can imagine a fully fledged ORM such as NHibernate is probably more appropriate.

Advantages I can see:

Performance
This is the winner really, although the larger ORMs have improved in the end you had to do a lot with their caching features to really speed things up.

No DSL or query language over laying the SQL.
I remember using HQL quite heavily when I used NHibernate in the passed – and in the end I did find myself using custom SQL instead – and it does make you think why not just use SQL! In fact you control the SQL you use in most cases which can be a winner if you have SQL experts in your team. No more crazy SQL being outputted from Entity Framework or NHibernate.

Single classes in the case of Massive, Peta Poco and Dapper
No digging around an entire API to find out what is going on. A surprising amount of functionality is available in the number of lines in these code files have!!

Convention based code
I do like for example in Simple.data the querying ability where it works out from the method name what it is trying to search.

The only main disadvantage I can see is if you don’t have control over your data source, I would say you are then better off using NHibernate!

Right tools and all that.. But of course you can always have different libraries for reporting and reads etc. say compared to create, update, delete operations… And of course you can always wrap them behind interfaces so that you can swap in and out modules into your architecture.

I’ve got a pet project to try these out in Smile.

Add a Comment