X

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.

Liked this post? Empower your friends by sharing it!

View Comments (6)

  • Another case for micro orms, Dapper in particular, is when you want direct control of the query that is being executed. No guessing what the full orm will generate and execute.

  • The downside with Dapper compared to EF Core today is testability. You have to use functional tests with a real database to test Dapper while EF Core can mostly be tested in-memory.

  • It seems to me that when using Micro ORMs one should avoid thinking in entities and write classes corresponding to each use case. Instead of loading Person.Address you make a query for PersonAddress (i.e. a Person with an Address in flattened form) to display on a certain page. In this way most mapping will be simple because there will be a specific class for each query.

    I don't get the problem with Dapper and unit testing. Just wrap Dapper in an interface and mock it as much as you want.

  • Good point, Stilgar. Instead of entities we should more think in terms of DTO-s, I think. We still want to benefit from classes but with smaller scenarios we don't want complex object construction and other things like this that full ORM-s do out of box.

Related Post