What is Micro ORM?

Micro ORM-s are useful small ORM-s that are lightweight and map between objects and database queries. This blog post discusses when to use micro ORM and provides example code where Micro ORM called Dapper is used.

ORM and Micro ORM

ORM is Object-Relational Mapper and it works as querying and object creation runtime between client code and relational database. ORM-s provide multiple powerful features like object and query caching, concurrency control, object oriented query languages and much more.

Micro ORM is basically mapper that creates objects based on database query. It saves you sometimes load of dirty work but it usually doesn’t come with any powerful features like full ORM-s do.

Debate: ORM or Micro ORM?

LIke always with similar concepts there are some confusion, many opinions and different understandings. Some guys are too eager to go with Micro ORM-s although they march directly to troubles with their projects. Others tend to build their simple applications too complex by using full ORM-s although they are really not needed for such a small application.

I don’t go too deep to topic here. I just list some cases when it’s safe to go with Micro ORM.

1. Small number of queries. You need small amount of queries that are not complex and that return simple results. Break this rule and soon your codebase looks like soup mixed together of object-oriented and SQL code.
2. Thin communication interface. You have complex database but all complexities are hidden behind views and stored procedures. You write simple queries that use views or call stored procedures. Typical case is perhaps custom reporting.
3. Utility applications. Utility applications in enterprises often run some queries, modify some data and persist or send processed data to somewhere. Although these applications may use pretty ugly queries there is still limited number of queries and usually they are independent of each other.

For all cases bigger than these three I suggest to go with full ORM.

When not to use Micro ORM? John-Daniel Trask published nice overview about subject to Mindscape blog “5 reasons not to use a micro ORM”. It’s not long post but it summarizes the points very well. Of course, it doesn’t mean that Micro ORM-s should not be used at all.

Dapper Micro ORM

Dapper is Micro ORM that runs on .NET Framework. It is lightweight compared to HNibernate and Entity Framework and it is good selection for projects where really simple database or data interface is used. If our application has to manage simple to-do list we can go with Micro ORM.

Simple to-do items controller can be look like shown below (don’t forget to add reference to Dapper NuGet package).

public class ToDoController : Controller
{
    private readonly string _connectionString;
 
    public ToDoController(IConfiguration configuration)
    {
        _connectionString = configuration.GetValue<string>("ConnectionStrings:Default");
    }
 
    private IDbConnection GetConnection()
    {
        var connection = new SqlConnection(_connectionString);
        connection.Open();
 
        return connection;
    }
 
    public IActionResult Index()
    {
        using (var connection = GetConnection())
        {
            var items = connection.Query<ToDoItem>("select * from ToDoItem");
 
            return View(items);
        }
    }
 
    public IActionResult Create()
    {
        return View();
    }
 
    public IActionResult Create(ToDoItem model)
    {
        if(!ModelState.IsValid)
        {
            return View(model);
        }
 
        using (var connection = GetConnection())
        {
            connection.Execute("insert into ToDoItem (Title, Done) values(@Title, @Done)", model);
 
            return RedirectToAction("Index");
        }
    }
 
 
    public IActionResult Edit(int id)
    {
        using (var connection = GetConnection())
        {
            var item = connection.QueryFirst<ToDoItem>("select * from ToDoItem where Id=@Id", new { Id = id });
 
            return View(item);
        }
    }
 
    public IActionResult Edit(ToDoItem model)
    {
        if (!ModelState.IsValid)
        {
            return View(model);
        }
 
        using (var connection = GetConnection())
        {
            connection.Execute("update ToDoItem set Title=@Title, Done=@Done where Id=@Id", model);
 
            return RedirectToAction("Index");
        }
    }
}

We inject configuration object to controller and read connection string. We have method that returns us new and opened connection that we always use in using() block. Smelly thing here is direct instantiation of SqlConnection but again – in small scenarios we usually have one database and it is not moving from one platform to another with very good reason.

Source code of this post with sample ASP.NET Core application is available in my GitHub repository DapperDemo.

Repository pattern with Dapper. TechBrij has nice article ASP.NET Core with PostgreSQL and Dapper – CRUD Operations Example where they introduce how to write repositories that use Dapper and how to use these repositories with ASP.NET Core.

Wrapping up

Micro ORM-s can be excellent way to shoot many bullets to own legs but also they can be very valuable tools for smaller and simpler database scenarios when we don’t need most of powerful features that full ORM-s offer. Micro ORM-s expect us to write SQL and this is something where things can get bad when we apply Micro ORM on application that has growing domain model and where logic is getting more and more complex. For smaller and lightweight scenarios Micro ORM-s can be better as they usually perform faster.