So you want to write a data layer?

Massive is the data layer I wish I had written. It’s small enough to just drop into any project and sensible enough that you won’t have a massive learning overhead. This is exactly what I needed when I got handed a legacy project recently and needed to make some page loads faster.

SQL Profiler reported an insane number of database calls for what looked like a relatively simple (but frequently visited) page in this application. Delving into the code left me feeling giddy as I navigated through layers of object oriented obscurity down to a custom data layer, finally discovering the root of the problem.

Recreating a cut down version of this application will afford us the pleasure of bypassing a whole heap of code with Massive. Rob Conery has my thanks and an open offer for some beers on my dime if he’s ever in London.

The Scenario

For argument’s sake, we will say we are writing a Sales system for a large organisation with a central office and several regional locations. Central needs an application to collect data from the regions. The spec calls for us to hold one row per item sold per region per quarter. A quick and dirty data diagram might look like this:

Sales Data Diagram

Sales are, as mentioned, line items; one item sold in one region in one quarter = one row in the Sales table. Let’s not argue too much about the data structure. It’s good enough for now.

The Code

Stored Procedure

In keeping with the wisdom of the time when the app was written, stored procedures are used – here’s our “get” proc for sales:

We’ve only one parameter in this procedure. In reality, the app I’m working on has ten or twelve with logic in the stored procedure to match. The “if” statements, as you can imagine, multiply enough to confuse the most battle hardened SQL Guru. Ok, maybe not Joe Celko, but certainly the rest of us.

Data Layer

We need an interface – interfaces are good.

Every class in our data layer will implement our interface. It’s pretty scanty up there, more messy in real life.

Now, of course, our implementation in the form of a Sales class:

The actual Sales class fleshes things out; GetById will return a single row from the database based on the Id passed in (Guids all round). GetByParameters calls out a common function that creates a connection, command, executes a stored procedure and returns the result. A simple (and probably badly coded) implementation could look like this:

Business Layer

So far so good. Now we need a business layer. No interface this time, I’m not sure why.

And hey presto – we now have class that can inflate itself to represent a single row of Sales data. The constructor takes a guid, this then calls the Populate method which in turn calls the GetDataReader method on the base class, passing in the guid and an instance of the Datalayer.Sales class. GetDataReader makes use of our IDataAccess interface reducing our coupling.

All is peaceful in the land of sales until we want to return a list of all sales data. At which point, for some unknown reason, those who designed the system figured this would be the most logical implementation:

What we have here is a class that will, eventually, implement one (or more) methods for each business object, returning lists of objects as and when we need them. The lovely method GetSalesList first uses the GetIdList to query the database and return a list of each and every Id in the Sales table by calling the GetByParameters in our Sales class in the Data Layer. The Sales class, if you remember, calls out to our Common GetDataReader function which actually calls the stored proc, passing in the parameters. In this case, there are none and so, the lovely if statement in the stored procedure falls into a ‘Select *’.

Back in GetSalesList, we loop through the list of guids and construct a list of Business Layer Sales objects, instantiating each one by passing in its Id field. Our business object, of course, inflates itself through the hierarchy of objects, calling eventually, the stored procedure with a parameter.

In case this wasn’t clear, to return a list of Sales objects, we first hit the database for a list of guids, then hit the database once for each row of sales data (selecting a single row based on it’s guid), inflating the business objects one by one. As you can appreciate, this pattern makes for a gradual slow down as the dataset increases. The application literally collapses under it’s own weight.

The kicker

As if this wasn’t enough, the consuming code that rendered the page was doing this:

Having gone to all the trouble of bringing back sales objects one by one, all we use the list for is a count.


This pattern is endemic in this application. It’s the reason I decided to implement a new datalayer rather than try and patch what is currently there. Adding to the weight of the layers of code seemed to me, counter productive when what I really want is to redesign the data and business logic layer. I considered bolting on some methods in ListCatastrophe to return various aggregates. I also considered changing some of the base classes, either in the data layer or business layer, but it felt like I was just bloating an already over complicated situation.

Clean up

After I decided to side-step the current application anatomy, I thought about writing my own minimal data layer. Then, I stumbled on Massive and thought I would give it crack first.

Massive relies on the .NET DynamicObject and ExpandoObject. Dropping the single file in our application allows us to (among other things) execute a quick piece of SQL like this:

One database hit no matter how many rows. Much better. It feels good to regain control. Although I’m perhaps now more tightly coupled, I think stripping all the way back to this is a good thing. Backfilling the application a little at a time will allow me to refactor code incrementally rather than try and second guess a massive (hehehe) upfront design that will be just as bad as the one I’m replacing. I will most probably end up with a new structure that will have model classes that inherit from Massive’s DynamicModel (see Rob’s doc’s), into which I’ll move the various business layer and stored procedure logic (since in fact the logic encoded into the stored procs is pretty much the business logic of the app).

Most importantly, my users now have a super fast page load and hopefully, a restored faith in the power of code to help them do their jobs more efficiently.

Django REST Framework – Could not resolve URL for hyperlinked relationship using view name

django rest framework hyperlinked relationship error

Here’s an error that’s all too easy to stumble on if you are just hacking your way into an API using Django REST Framework

Could not resolve URL for hyperlinked relationship using view name “model-detail”. You may have failed to include the related model in your API, or incorrectly configured the lookup_field attribute on this field

Recreating the error

I’ll assume you have, at least, a basic Django site up and running. Perhaps you are a little impatient (like me) and you skim the Django REST Framework’s homepage. You add the following in to

And then, somehow, you skip he rest of the tutorial and you end up with something like this:

Make sure you run your migration or sync your db.
Now you should be able to browse your api with the following url:

Things are looking good, job done? Not so fast……spend a moment inserting a record into the Pet table – create a fixture, write some raw sql, whatever floats your boat. Refresh your api endpoint and you’ll get a glorious error:

“Could not resolve URL for hyperlinked relationship using view name “pet-detail”. You may have failed to include the related model in your API, or incorrectly configured the lookup_field attribute on this field.”

How do you dig yourself out of this one?

Two methods – the first (possibly simplest) is to cut the following out of (commented out so you don’t skim my post and add it in again):

In actual fact, removing just the following two lines will fix it for you:

The second is to create a class like so:

and update your as follows:

and you will be back in the land of the living.

Why does this happen?

Well, the key lies in the Default Model Serializer Class – when it is set to HyperlinkedModelSerializer, the rest framework needs a serializer to work with.


The error message here is really, really unhelpful for a anyone not familiar with the Django Rest Framework.

Could not resolve URL for hyperlinked relationship using view name “pet-detail”

made me start questioning my sanity – I had neither a detail suffix view defined, nor any sort of relationship with another model – both things I thought would be obvious causes of the error.

The added annoyance of the bug on rearing its head when data is populated also irked me somewhat because as part of my bug exploration, I created a new stripped down project (as above) to test the damn thing out. I didn’t include any fixtures in my test project, so initially, it looked like it worked and spent a hunk of time back in my original application looking for bugs in “my” code rather than default settings of the REST framework itself.

Ultimately the lesson is: RTFM? Well yes and no. I do think that projects should have an “at a glance” page, and I erroneously thought the Django REST Framework’s homepage would be it – you know “whack this in your app and you’ll have a basic json response going” type of thing. Of course this is a case where I should have read just a little bit more to save myself a headache.

Aah well, live and learn.

Models – Rails and ASP.NET MVC, Properties and Constraints

Some rather vague thoughts on models in Rails and ASP.NET MVC. Mostly out of interest in the different approaches rather than as a critique of either framework, because, well because that’s a different blog post….Let’s imagine, for this little ditty, that we are dealing with a blogging engine.


In Rails, properties don’t have to be explicitly declared in the models, so you get something like this:

class Post < ActiveRecord::Base

It's nice and clean, nice and simple, but, looking at the model, you have no idea what's in there. Instead, if you're a new developer on the project, you can scan through the schema.rb or dive straight into the database to find out.

Contrast that with ASP.NET MVC which takes a more explicit approach:

namespace YetAnotherBlogEngine.Models
    public class Post
      public int Id (get; set;}
      public string Title {get; set;}
      public string Body {get; set;}
      public int Category_Id {get; set;}
      public datetime Date_Published {get; set;}

So a new developer bounced in to the project knows what properties this model has by looking at the model rather than a schema.

I wonder, to myself mainly, if the differences are a function of the duck typed vs strongly typed nature of ruby vs c#, and the fact that with ASP.NET MVC the toolest (Visual Studio with its autocomplete and continual compilation process) was there before the framework, whereas Rails was developed as a framework before the toolset (Texmate, Vim + plugins, Host of other 3rd Party dev environments and obviously Sublime Text).


Let's continue and add some sort of constraint - perhaps a blog post must have a title. That makes sense. I'm looking at the code first method in ASP.NET MVC because I think it provides a little more of a like-with-like comparison to Rails.

Rails takes the approach that model level validations are the way to go:

Model-level validations are the best way to ensure that only valid data is saved into your database. They are database agnostic, cannot be bypassed by end users, and are convenient to test and maintain

So, we can enhance our model like so:

class Post < ActiveRecord::Base
  validates :title, :presence => true

This, by itself, will not persist your constraint to the database schema. You would need to do that in your migration in Rails.

The Active Record way claims that intelligence belongs in your models, not in the database. As such, features such as triggers or foreign key constraints, which push some of that intelligence back into the database, are not heavily used.


Although Active Record does not provide any tools for working directly with such features, the execute method can be used to execute arbitrary SQL.

ASP.NET MVC on the other hand, is pretty strongly tied to SQL Server, or at least, the implicit assumption is that you're going to be using SQL Server, so applying constraints to a model (called data annotations) and then running your update-database (roughly the equivalent of a Rails migration) will apply your constraint at the database level as well as the model level (now I haven't validated every data annotation, but I'm talking about things like foreign keys and non-null values, and obviously this won't work with custom annotations).

So, our ASP.NET MVC model would look something like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace YetAnotherBlogEngine.Models
    public class Post
      public int Id (get; set;}
      public string Title {get; set;}
      public string Body {get; set;}
      public int Category_Id {get; set;}
      public datetime Date_Published {get; set;}

I've added in the using statements, because you have to explicitly pop the DataAnnotations in there.

What I like about the Rails style, is looking at your model, you get a quick sense of what validations are going on because they are all collected at the top. With ASP.NET MVC, getting an overview of the constraints on a model takes a bit longer. Especially when your model grows in size, because you have to scan through all the properties to single out which ones have data annotations. Also, annotations in ASP.NET MVC are a bit of a mixed bag, because apart from constraints, you can also specify the front end display of a field. For example:

    [Display(Name = "Post Title")]
    public string Title {get; set;}

will cause your views to render "Post Title" as the label for the Title field by default. Anyway, lets not get into that too much because the data annotations are actually part of the entity framework rather than the mvc framework per se.

A true like for like comparison between ASP.NET MVC and Rails is not really possible. ASP.NET MVC, actually wants you to develop in a Model, View, View-Model, Controller paradigm. This extra layer in .NET makes more sense when you start to work with the strongly typed views and you realise that .NET doesn't actually want your views to use your models in anything but the most basic examples. Though often it feels like you are writing code for code's sake.

I'm not sure there is any punch line to this post other than maybe accepting that each framework has a philosophy and you should "render unto Caesar" in each framework and go with the overriding ideas, idioms and philosophies.

Setting up your own certificate authority on IIS7 using OpenSSL and securing your web api with client certificates

Creating self signed certificates isn’t really all that complicated, but it can be a little intimidating the first time you do it.

What are we trying to achieve?

1) A web api that is protected by client certificates hosted on IIS7.
2) A way to test it out from our browser.


Well, the use case is a web api that is not open to the public. We want to secure it such that only clients with the relevant certificates can access the api.


A web site that is protected by a valid SSL certificate
OpenSSL – I installed this on a 64bit version of Win7. Be warned, you need Visual C++ 2008 redistributable installed first. Be warned, yet again, that even though open ssl should install in 64bit mode, I couldn’t get it working so I just took the 32 bit. Hasn’t done me any harm….. :)


Testing your api from a browser like IE requires you to have a p12 client certificate to import into your personal certificate store. This one caught me out for a while.

Setting up your root certificate authority

First create a key pair that you will use to sign your certificate:

openssl genrsa -des3 -out root-ca.key 1024

Enter a strong pass phrase. This is the most vital pass phrase you will ever come up with – your root certificate is what you will use to sign client certificates and it is what will be installed on your IIS7. Basically, if someone gets your root cert and your passphrase for it, they can create their own client certificates and your web api will trust them. If you are super paranoid, disconnect the server that you are using to create this certificate from the network forever.

Now use your key pair to create and sign a root certificate:

openssl req -new -x509 -days 3650 -key root-ca.key -out root-ca.crt

We are generating a certificate that will be valid for 10 years. Make it shorter if you prefer. You’ll be prompted for your root key pair pass phrase and a bunch of info – fill it in, forget about the email address.

You now have a root certificate – this is what you will install on your web server. You will also use the root certificate to sign client certificate requests. Once the client certificate request has been signed by with the root certificate, any requests to your secured api with these client certificates will be implicitly trusted by your web server.

Make your webserver recognise your new certificate as a trusted Certificate Authority

Copy your root-ca.crt file to your webserver. Now, either double click the .crt file or if you prefer, open up a command prompt, “mmc” and follow these instructions:
* Click File->Add/Remove Snap-in
* Select “Certificates” and choose “Computer Account”.
* Expand “Trusted Root Certification Authorities” and right-mouse button, “Import”.
* Find your root-ca.crt and complete the import.

Create a website and require certificates

Create a new site or application in IIS, and then using the IIS manager, select the SSL Settings.

Make sure Require SSL is checked and that the Client Certificates option is set to Require.

If you try to browse your website now, you should get an access is denied message.

Create a client certificate request

You’ve got your root cert. You’ve installed it on your webserver. You’ve locked down your website. All that’s left to do is create the client certificate, install it in your certificate store on the client machine and away you go.

Open up the command prompt on your client machine which has openssl installed on it and:

openssl genrsa -out client-cert.key 1024

As above, we generate a keypair, and then create the certificate request:

openssl req -new -key client-cert.key -out client-cert.csr

Again, you’ll be prompted for all sorts of information – fill it in. When you put the organisation name and common name, use something different from your root certificate above so you can keep tabs on things in your personal certificate store.

Now, we use the client certificate request and create a client certificate, signing the certificate with our root certificate:

openssl x509 -req -day 3650 -CA pathtoroot-ca.crt -CAkey pathtoroot-ca.key -CAcreateserial -in client-cert.csr -out client-cert.crt

You’ll be asked for your root certificate pass phrase – you remember, the one I told you was super-important above.

Very cool, we now have a client certificate that IIS will trust because it has been signed by a root certificate that IIS trusts. Woohooo.

And here is the gotcha – If you simply install this .crt in your certificate store and try to browse your locked down website, it just will not work!

And here is the fix – see Internet Explorer needs the certificate to be in a specific format (pkcs12) for it to actually present the certificate to the webserver when you go a-browsing. Luckily, openssl allows us to fix this issue:

openssl pkcs12 -export -clcerts -in client-cert.crt -inkey client-cert.key -out client-cert.p12

Import your p12 certificate into your local personal certificate store

Again, double click the .p12 file, or go through mmc to import the p12 certificate and you should be away. Close IE, re-open and browse to your locked down website. You should be greeted with your website.

Aaaaah. That wasn’t so bad was it?

Meteor – This is what Asp.Net webforms could have been

Meteor ticks the boxes for a realtime web application; “one language”, “realtime as default”. It looks like it eases the path of development, automatically updating your front end templates when your data changes by implementing an subscription model.

I’m playing with it. Enjoying the feeling of coding *everything* in one language. It’s definitely a smoother process for a developer. And all of a sudden, I get a feeling of deja-vu. Someone else tried to do this before, didn’t they? Someone named Microsoft. I remember back when ASP.NET came out and we all frantically switched from the old-and-inferior-scripting based technology to the all-new-and-improved-web-forms based technology. The promise was the same. No more hacking away in VBScript (erm I mean Asp) for your backend and Javascript for your frontend. No, instead you could write C# in your magical code-behind pages and all but forget about frontend jiggery-pockery.

Web forms really tries. You define your HTML in an aspx file – these are basically your templates. Elements can be set to have a data source which will, for example, infill the data for the element from your database. Elements can also be set to post back to the server anytime they change, get clicked or what have you.

Postbacks are tied to events in the C# code behind pages which means that you can move all your logic server side. It makes developing for the web more like writing a traditional style windows client application.

Now the html controls, of course, responded to Javascript to make the page post back. But it wasn’t Javascript that the developer had to write. It was auto-generated. Awesome. Awesome. Awesome. One language….C#.

Of course it isn’t without it’s downside. The ViewState – a massive chunk of data that kept….state between page loads. That’s a nasty piece of work. Ingenious in it’s own way, but nasty when you bump up against it. And of course there is the enormous expense of re-rendering the entire page every time a select box that is wired up to post back to the server changes. Large Web forms applications can become sloooooow on the client side.

Meteor does something similar. Only now with baked in Ajax goodness, the applications actually feel useable. I wonder if Microsoft missed the boat? I wonder if they could have leveraged Web forms, or something like it, to be more like Meteor. Maybe they do now – Web forms is still alive and well, but I haven’t (thank goodness) had to use it in a very long time so I’m somewhat out of touch.

Where Microsoft really shine, in my opinion, is with their IDE. Wiring up server side events to a button click really is easy – just open up your aspx file (the template), double click on the button and Visual Studio will wire up the event for you and pop you into the server side event so you can write your code. On a large page, this really does take some pain away. You don’t make stupid typos wiring up your button because you don’t have to type it in. It know Vim is awesome and all, but this part of the developer experience, Microsoft really does well.

Somewhere in the future, I imagine the best of both worlds. Something like Meteor + Visual Studio all open sourced and ready to go.

Customising your json output in Ruby on Rails 3.2

You know how it goes; you’ve found yourself developing a little application to store and display nursery rhyme lyrics because you’re a good parent and want to get the words right for your tot when you sing to them and really, who can remember all seven verses of Twinkle Twinkle Little Star. So, you scaffold something like:

rails g scaffold NurseryRhyme name:string rhyme:text

And you’re away. Typing (or in fact cut’n’pasting from Project Gutenberg). All is bliss. You nail the improvised high note right at the end of Frère Jacques and your kid smiles from ear to ear.

So now, our story breaks down. Suffice it to say you can grab your data from your auto-generated json api. And it looks something like this:

{"name":"Humpty dumpty","updated_at":"2012-04-11T20:07:46Z","rhyme":"Humpty dumpty sat on a wallrnHumpty dumpty had a great fallrnAll the kings horses and all the kings menrnCouldn't put humpty together againrn","id":1,"created_at":"2012-04-11T20:07:46Z"},
{"name":"Jack and Jill","updated_at":"2012-04-11T20:08:18Z","rhyme":"Jack and Jill went up the hillrnTo fetch a pail of waterrnJack fell down and broke his crownrnand Jill came tumbling after","id":2,"created_at":"2012-04-11T20:08:18Z"},
{"name":"Baa baa black sheep","updated_at":"2012-04-11T20:09:02Z","rhyme":"Baa baa black sheeprnHave you any woolrnYes sir Yes sirrnThree bags fullrnOne for the masterrnAnd one for the damernAnd one for the little boy rnWho lives down the lane","id":3,"created_at":"2012-04-11T20:09:02Z"}

But you want to import it into an application, that for some reason expects the json to be somewhat differently described. You need to trash the ids and dates and change “rhyme” to “lyric”. Plus, you need each rhyme nested in a “LovelyRhyme” element. Baaa (baa black sheep).

And here’s the tldr; How to customise your json output. Start by opening up your model. You should have something like this:

class NurseryRhyme < ActiveRecord::Base
  attr_accessible :name, :rhyme

Add in a little code, as follows:

  def as_json(options={})
     { :LovelyRhyme => { :name => name, :lyrics => rhyme } }

And that will render you this:

{"LovelyRhyme":{"lyrics":"Humpty dumpty sat on a wallrnHumpty dumpty had a great fallrnAll the kings horses and all the kings menrnCouldn't put humpty together againrn","name":"Humpty dumpty"}},
{"LovelyRhyme":{"lyrics":"Jack and Jill went up the hillrnTo fetch a pail of waterrnJack fell down and broke his crownrnand Jill came tumbling after","name":"Jack and Jill"}},
{"LovelyRhyme":{"lyrics":"Baa baa black sheeprnHave you any woolrnYes sir Yes sirrnThree bags fullrnOne for the masterrnAnd one for the damernAnd one for the little boy rnWho lives down the lane","name":"Baa baa black sheep"}}]

Awesome. Thanks for reading.