Wednesday, 2 November 2016

Drapper - An Introduction

I released Drapper about two months ago and, aside from the (rather dull) wiki that I put up for at least some documentation, haven't really written much about it.

As the author, I think it goes without saying that my opinion is completely, utterly and unashamedly biased.

It should also go without saying that I firmly believe that Drapper solves pretty much every single data access need I've ever come across. I've yet to find a case that Drapper couldn't support.

So why Drapper?

I'll start with the name. After many frustrating years of using home-rolled DALs and ORMs I came across the awesome micro ORM Dapper (if you're new to Dapper, go check it out!). Dapper was what I'd been looking for - no hoop jumping, no disgusting auto-generated SQL, no weird syntax or semantics for simple operations, no lots-of-things-I-hated-about-various-flavoured-ORM.

Dapper is simple, intuitive and targets a variety of different databases without blinking. And on top of all that goodness - it's FAST. Win!

It has, in my humblest of opinions, a drawback. It's a minor one, but one that tainted an otherwise very happy picture - mixing SQL with C# (or whichever .NET language you like most).

I don't like these two languages sharing the same space. It feels messy to me. Kludgy. I'm sure it has some benefits and there's more than one way to keep them separated from each other without going to great lengths. Static class perhaps. List of consts maybe. Resource file, even. Or perhaps something else.

I chose Something Else.

And  thus was born, from a simple desire to separate SQL from C#, a wrapper for Dapper - Drapper.

I'm sure there could have been a more original name (this one certainly doesn't come up in search results! :)), but this was the name I gave it (and I checked with Marc Gravell if it was okay to use it).

That's all it is?

Yes and no.

Yes, in that Drapper is a framework which successfully separates SQL from C# & uses Dapper under the hood.

No, in that it turns out that Drapper has some really nifty features built in that overcome some of the limitations you might find in using only Dapper. It also turns out that using Drapper helps to promote better data layer design simply by using it as is. More on those benefits later (or likely in a separate post).

Okay... So what is it then?

Drapper is control. Control over your execution. Control over your mapping. Control over your database. Control over your code, your tests, your design, your choices. Control was a central design tenet in developing Drapper. Not only should you have control over all of these things, but you should have the power to use or abandon using Drapper without impacting your code (too) adversely.

Tell me more

By and large, a great many applications will implement a CRUD repository of some sort. Some will take a more generalized (and in my view, incorrect) approach. Others will be more specialized with a repository per type to be persisted/retrieved. I prefer, and advocate for, specialization as this has more benefit for your code. The trade off is writing more code but it's a trade for more flexible, more maintainable code.

Within a specialized repository, one might have methods for each of the basic CRUD methods, possibly a method for lists of data (possibly paginated for front ends), methods which return object graphs, others which don't. Some repositories might not expose one or more CRUD operations depending on the application needs. And of course, all of this needs to be tested.

That can run into quite a bit of code! It's a daunting prospect, even to seasoned developers. But what if you can get that level of specialization with very little code? That's where Drapper comes in.

One liners

At it's heart, Drapper isn't much more than an abstraction of what Dapper does for you already. There's a single interface called IDbCommander which exposes overloads of two methods Execute and Query.

Execute is used for state changing operations (Create/Update/Delete) whereas Query is used to retrieve data.

Pretty simple, huh? It gets better - with Drapper, the majority of CRUD operations can be written in a single line of code. Need to persist a simple POCO or primitive type? One line of code.

Need to retrieve them later? One line of code.

Need something a little more complex? An object graph perhaps? No problem - supply a Func<> to the Query method and build up the object graph any way you see fit. Drapper supports a Func<T> with up to 16 inputs. You can define the Func<T> in a separate class (recommended) and still have your specialized secret sauce on a single line of code!


And the mapping function defined elsewhere -

internal class Map
        Func<TypeA, TypeB, TypeA>  = MyComplexType (typeA, typeB) =>
            typeA.SomeProperty = typeB;
            return typeA;

Yes, I'll admit that that is something of a cheat as you've written more than one line of code, Still, the intent is clear - keeping your specialized repository code specialized and simple.

But... But... How does it know what SQL to execute?

It's really quite simple & based in large part on the Single Responsibility Principle. The idea is that a CRUD method within a repository will, more often than not, correspond to a single bit of SQL to be executed. For instance, a Create method might call an insert statement, a Delete method a delete statement, and so on.

Taken a step further, we can infer that each method on a repository class corresponds to a SQL statement and knowing what that method is should determine what the corresponding SQL statement is. The repository method itself shouldn't know anything about that SQL statement. Whether it's T-SQL, PL/SQL, a simple statement or a stored procedure are not the concern of the method.

Taken another small step further, we can infer that the fully qualified name of a method could be used to uniquely identify a SQL statement to be executed (we'll talk about overloaded methods shortly). So assuming we had a method called Retrieve on a repository called ContactRepository in a namespace belonging to the fictitious HappyCustomer CRM app we could hypothetically represent that as HappyCustomer.Contacts.ContactRepository.Retrieve & expect that this method would retrieve a single contact from our database.

Drapper leverages this concept by having each overload of both Execute and Query on the IDbCommander use the [CallerMemberName] attribute as an optional argument. Having this salient bit of info gives us the name of the method being called at no cost. To get the full type name, we can (optionally) supply the type name using the typeof construct (e.g. typeof(ContactRespository)) or allow Drapper to use a bit of reflection to determine the full type name.

Command Settings and Readers

Now we have all of the info needed to uniquely identify our Retrieve method and thus, our SQL. Drapper uses a CommandSetting configuration object to represent the SQL to be executed and it should come as no surprise that we use the method name to identify the CommandSetting. Each one holds enough information to control the execution of a SQL statement - whether it's a text statement, a stored procedure, uses a particular transaction locking mechanism or has a specific timeout value, etc.

One or more CommandSetting objects will belong as collection of the type, stored in pretty much any configuration store you'd like to use. JSON and XML config stores are supported out of the box. A CommandSetting is returned from an ICommandReader.

I'll go more into the specifics of how the SQL is retrieved in another post. For the moment, it's enough to know that using the fully qualified name of a method in conjunction with an ICommandReader will return a CommandSetting. The ICommandReader is a dependency of the IDbCommander so you never have to interact with ICommandReader or a CommandSetting directly.

A word on overloads...

Overloads are supported and I'm pretty sure it's easy to see how - as the [CallerMemberName] is optional, you can supply a name to identify a specific CommandSetting. Revisiting our fictitious ContactRepository & assuming we had overloads of the Retrieve method - one to retrieve by an int id & another to retrieve by email address as a string - we could simply do something like

public Contact Retrieve(int id)
    // uses the "default" retrieve statement which expects an int arument
    return _commander.Query<Contact>(new { id }).SingleOrDefault();

public Contact Retrieve(string email)
    // uses the "named" retrieve statement which expects a string arument
    return _commander.Query<Contact>(

        new { email }, 
        method: "RetrieveByEmail").SingleOrDefault();

Allowing you to maintain a nice, clean interface to your repository without exposing the internals or expecting the calling code to know anything more about your repository than absolutely necessary.

That's a wrap! For now...

This post grew way, way longer than intended. I will write follow up posts which go into greater detail but it's really, really late at the moment and I'd like to get this post published :)