Lightweight Data Access in .Net: Massive

Tags: web-development, dot-net, lightweight-dot-net, massive, simple-data

Together with the raise of lightweight web development movement in .Net, led by Nancy, there was a need of a lightweight solution for data access. Luckily, in the last year we got not one, but three such components that fit nicely in the picture.

After the last post covering Nancy framework and its role in the lightweight development approach to .Net, this one will cover data access bits and pieces that were left behind. 

Some really interesting developent is happening in this space, too. Contrasting the heavyweight NHibernate or Entity Framework ORMs, three projects seem to gain traction on the lightweight side of things - Simple.Data by Mark Rendle, Dapper by Sam Saffron from the StackOverflow team and Massive by Rob Conery.

Around the same time when I've first noticed Nancy, I've also noticed Simple.Data with its dynamic micro ORM approach. Shortly after that, Rob released Massive with completely different approach of utilizing plain old SQL. Both of them seemed like a great fit for Nancy.

I found Rob's approach interesting, but at the time I preferred Simple.Data for being conceptually closer to NHibernate I'm used to. After trying out Simple.Data, and while waiting for of all the features I needed to be implemented, I still decided to take Massive for a test run. And I fell in love with it!

It was completely dynamic, with no need to write those statically-typed model classes I was used to at all, and it worked like a charm.

This approach of data being populated directly from the hand-crafted SQL query brings another great benefit - the ORM's Select N+1 problem is easily avoided by optimizing SQL queries to fetch all the data needed in a single query.

Show me the code

As mentioned in the Nancy post, and I'll follow the same example of Muzika.hr FanClub microsite, Massive data access code is wrapped in a Repository class:

public FanClubModule()
{
  Get["/{name}"] = x => {
    string view = (string)x.name;

    var articles = Repository.GetFanClubArticles();
    var offers = Repository.GetActiveOffers();

    // ...
    return View[...];
  };
}

So, let's see what's inside the Repository class (full listing also available at Gist): 

public static class Repository
{
  public static IEnumerable<dynamic> GetFanClubArticles()
  {
    var now = DateTime.Now;
    var tblArticles = new Articles();
    return tblArticles.All    // named parameters query, DB agnostic
      ( columns: "publishDate, title, leadText"
      , where: @"publishDate < @0
          AND expiryDate >= @1
          AND deleted = 0"
      , orderBy: "publishDate DESC"
      , limit: 5
      , args: new object[] { now, now }
      );
  }

  public static IEnumerable<dynamic> GetActiveOffers()
  {
    var today = DateTime.Now.Date;
    var tblOffers = new FanClubOffer();
    return tblOffers.Query    // direct SQL query, may be DB specific
      (@" SELECT *
        FROM FanClubOffer 
          INNER JOIN FanClubCategory
ON FanClubOffer.offerCategoryId = FanClubCategory.categoryId INNER JOIN FanClubPartner
ON FanClubOffer.offerPartnerId = FanClubPartner.partnerId WHERE offerStarts <= @0 AND (offerEnds IS NULL OR offerEnds >= @0) ORDER BY FanClubCategory.categoryDisplayOrder ASC , CASE WHEN offerEnds Is NULL Then 1 Else 0 End ASC , offerEnds ASC ", today); } }

Massive offers two ways of creating query – using named arguments, or bypassing the entire abstraction and write the (parametrized) SQL query directly. More on that in Rob's post in section 3.

Both ways are used in this example. Named parameters version suited the simpler, single table query better and direct SQL version was more appropriate for the three-table join query together with the CASE statement in the order-by clause.

And now... The magic happens

This code for querying the database is pretty straightforward, so let's focus on getting the query results. 

Notice the second and third line in each method, where table object is instantiated and queried. That's where all the magic happens, as these classes provide all the information needed by Massive to query the database and pull the data.

The most surprising thing is the amount code that classes contain (all the classes are listed on Gist). The following snippet shows the example of Article class:

public class Articles : Massive.DynamicModel
{
  public Articles() : base(FanClubApp.DatabaseConnectionName)
  {
    TableName = "clanak";          // legacy table name
    PrimaryKeyField = "sifClanak"; // legacy PK column name
  }
}

As you can see, there's almost nothing in there. Article class is an extension of Massive dynamic model class with the database connection name (connection string is pulled from web.config by the specified connection name), primary key column name and table name specifications.

FanClubCategory, FanClubOffer and FanClubPartner classes used in the other query are even one bit simpler – they don't need to specify the table name because it matches the class name (mapped by convention):

public class FanClubOffer : Massive.DynamicModel
{
  public FanClubOffer() : base(FanClubApp.DatabaseConnectionName)
  {
    PrimaryKeyField = "offerId";
  }
}

Notice that all the things specified are just inputs for Massive on how to reach the data in the database. We don't have to tell it anything about the output - the output is dynamic object with properties matching the column names (or SQL query defined aliases).

That's where the real flexibility is. We don't have explicit statically-typed models we're used to. And we don't really need them, what gets pulled by the query ends up in a dynamic object.

Working with dynamic model

From this point, we can reach another level of flexibility by adding new attributes to the dynamic model from a controller or a command, so it can easily become a rich view-model without the need of modeling a separate class for it.

This shows us how dynamic data has a positive impact on development speed and opens up further options. That's where its value lies.

Final thoughts on lightweight data access

In general, all of these frameworks are a great fit for the lightweight approach to web development. Each of them has its own specifics, so check them out and choose what's best for the problem you're currently solving.

As for Massive, it's greatly suited for projects where you'd want to write your own SQL queries, for whatever reason. Whether it's your DBA, or you need flexibility because of the legacy database model, you're covered.

On the other hand, writing SQL by hand may be error-prone after all these years of ORM and generated queries, especially for inexperienced developers. They may end up unprotected from SQL injections if they fail to understand the implications of writing non-parameterized queries. For those, some other, more protective solution might be a better option.

Keep an eye on these frameworks. It's nice to finally have data access options that aren't complex beasts with lot of protocol around using them, but more of swiss-knives that simply get the job done.

Swiss knives in data access provide us with clean models ready to be used in our applications, leaving out all the unnecessary protocol around it. That's real evolution.

Story comments:

blog comments powered by Disqus