Creating a Phoenix Framework Application with SQLite

Phoenix defaults to using Postgres, which is probably very sensible. Getting SQLite up and running using Sqlite.Ecto, though, is pretty trivial and requires a couple of config tweaks.

First, add the dependency for sqlite_ecto in mix.exs:

defp deps do
   {:sqlite_ecto, "~> 1.0.0"},

Now update your config/dev.exs to include reference Sqlite.Ecto:

config :widgetapp, Widgetapp.Repo,
  adapter: Sqlite.Ecto,
  database: "path/to/widgetapp.sqlite"

Drop into your console and run mix deps.get to pull in your new dependencies and then run mix ecto.create to get your database set up. If all goes well, you should get a new file created as specified in your dev.exs as above.

From here, just develop as normal.

Using highlight.js with WordPress’ Twenty Fifteen theme

I’ve recently switched over to using highlight.js for my code highlighting on my blog mainly because it supports Elixir syntax highlighting.

Out of the box, running highlight.js with the WorPress Twenty Fifteen theme causes lines of code to wrap.

I wanted lines to run continuously with a scroll bar.

The following css tweaks did it for me:

pre {
    border: 0;
    padding: 0;
    white-space: pre;
    overflow-wrap: normal;
code {
    overflow-x: scroll;

Your mileage may vary, but some quick tests seem to show this working reasonably well.

Of course you might not want to override the pre tag completely and instead push the changes into a class to apply to any pre tags wrapping your code.

And you’ll want to rig this up into a child theme so you don’t jettison your code when you run an update.

Rendering partials from other view modules in Phoenix

Playing with Elixir and Phoenix at the moment, so here is a small snippet.

When you want to render a partial template that sits in a different view module, here’s the syntax:

<%= render ApplicationName.ViewModule, viewname, conn: @conn %>

So, for example, if we have the following application structure:

 - controllers
   - about_controller.ex
   - page_controller.ex
 - templates
   - about
     - index.html.eex
   - page
     - index.html.eex
 - views
   - about_view.ex
   - page_view.ex

And we want to include the `about/index` page on our `page/index`, we can do the following somewhere in our `index.html.eex`:

<%= render Myapp.AboutView, "indexhtml", conn: @conn %>

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:

    @Id uniqueidentifier = null
    IF @Id is null
            SELECT * FROM Sales
            SELECT * FROM Sales WHERE Id = @Id


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.

namespace DataLayerCatastrophe.DataLayer
    public interface IDataAccess
        IDataReader GetById(Guid id);
        IDataReader GetByParameters(DbParameter[] parameters);

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:

namespace DataLayerCatastrophe.DataLayer
    class Sales : IDataAccess
        public IDataReader GetById(Guid id)
            SqlParameter[] p = new SqlParameter[] { new SqlParameter() { ParameterName = "@Id", Value = id } };
            return GetByParameters(p);

        public IDataReader GetByParameters(System.Data.Common.DbParameter[] parameters)
            return Common.Common.GetDataReader(parameters, "GetSales");

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:

namespace DataLayerCatastrophe.Common
    class Common
        public static IDataReader GetDataReader(DbParameter[] p, String procName)
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["production"].ConnectionString);
            SqlCommand cmd = new SqlCommand(procName,conn);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parm in p)
                cmd.Parameters.AddWithValue(parm.ParameterName, parm.Value);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;

Business Layer

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

namespace DataLayerCatastrophe.BusinessLayer
    public abstract class BusinessObject
        protected IDataReader GetDataReader(Guid guid, IDataAccess dataAccess)
            return dataAccess.GetById(guid);

namespace DataLayerCatastrophe.BusinessLayer
    class Sales : BusinessObject
        public Guid Id { get; set; }
        public Guid RegionId { get; set; }
        public Guid ItemId { get; set; }
        public Guid QuarterId { get; set; }
        public decimal PriceSoldAt { get; set; }

        protected void Populate(Guid id)
            IDataReader dataReader = GetDataReader(id, new DataLayer.Sales());
            while (dataReader.Read())
                Id = dataReader.GetGuid(dataReader.GetOrdinal("Id"));
                RegionId = dataReader.GetGuid(dataReader.GetOrdinal("RegionId"));
                ItemId = dataReader.GetGuid(dataReader.GetOrdinal("ItemId"));
                QuarterId = dataReader.GetGuid(dataReader.GetOrdinal("QuarterId"));
                PriceSoldAt = dataReader.GetDecimal(dataReader.GetOrdinal("PriceSoldAt"));
        public Sales(Guid id)

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:

namespace DataLayerCatastrophe.DataLayer
    class ListCatastrophe
        private static List GetIdList(SqlParameter[] p, IDataAccess dataAccess)
            List guidList = new List();
            IDataReader reader = dataAccess.GetByParameters(p);
            while (reader.Read())
            return guidList;
        public static List GetSalesList()
            List sales = new List();
            SqlParameter[] p = new SqlParameter[0];
            List ids = GetIdList(p, new DataLayer.Sales());
            foreach (Guid id in ids)
                sales.Add(new BusinessLayer.Sales(id));
            return sales;

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:

int counter;
counter = ListCatastrophe.GetSalesList().Count;

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:

var db = Massive.DynamicModel.Open("production");
int counter = Convert.ToInt32(db.Scalar("Select count(*) from Sales"));

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

    # Use hyperlinked styles by default.
    # Only used if the `serializer_class` attribute is not set on a view.

    # Use Django's standard `django.contrib.auth` permissions,
    # or allow read-only access for unauthenticated users.

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

class Pet(models.Model):
    name = models.CharField(max_length=250)
    date_of_birth = models.DateTimeField()

    def __unicode__(self):

from .models import Pet
from rest_framework.generics import(

class PetAPIListCreateView(ListCreateAPIView):
    model = Pet

from django.conf.urls import patterns, include, url
from .views import PetAPIListCreateView

urlpatterns = patterns('',
    url(r'^api/$', PetAPIListCreateView.as_view()),

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):

#    # Use hyperlinked styles by default.
#    # Only used if the `serializer_class` attribute is not set on a view.
#        'rest_framework.serializers.HyperlinkedModelSerializer',
#    # Use Django's standard `django.contrib.auth` permissions,
#    # or allow read-only access for unauthenticated users.
#        'rest_framework.permissions.DjangoModelPermissionsOrAnonReadOnly'
#    ]

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


The second is to create a class like so:

from rest_framework import serializers
from .models import Pet

class PetSerializer(serializers.ModelSerializer):
    class Meta:
        fields =('id','name','date_of_birth')

and update your as follows:

from .models import Pet
from .serializers import PetSerializer

from rest_framework.generics import(

class PetAPIListCreateView(ListCreateAPIView):
    queryset = Pet.objects.all()
    serializer_class = PetSerializer

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?