Using ENUM types with PostgreSQL and Castle ActiveRecord

One thing I really love about PostgreSQL is ENUM types, which basically allow you to create enumerations as datatypes and use them just as you would any intrinsic type.  For example, I have an ENUM called UnitsEnum that allows me to represent a unit of measurement (cup, teaspoon, gallon, etc).  I then use this data type in various database columns, functions, views, whatever.

These data types are simple to create with the following command:

CREATE TYPE UnitsEnum AS ENUM ('Unit', 'Teaspoon', 'Tablespoon', 'FluidOunce', 'Cup', 'Pint', 'Quart', 'Gallon', 'Gram', 'Ounce', 'Pound');

From that point on, you can use UnitsEnum just as you would a varchar or an int or a Boolean datatype, using it to define column definitions, using it as a function parameter or return value, etc. For all practical purposes, it’s now built into Postgres:

CREATE TABLE RecipeIngredients
(
  -- Stuff here
  Unit UnitsEnum NOT NULL --Unit column of type UnitsEnum
);

select count(1) from RecipeIngredients where Unit = 'Teaspoon';
--Yay!

select count(1) from RecipeIngredients where Unit = 'Crapload';
--Error is thrown

As far as the user is concerned, the data type appears to be a string that simply errors out when the value is not within a certain allowed set, and in fact strings can implicitly convert to an enum (but not numeric types which I’ve found odd).

Using an ENUM as a column type would be somewhat analogous to creating a text column, but creating a foreign key constraint on a table of measurements which contained the strings in the enumeration above. However, on the heap table the ENUM is actually stored in its numeric representation which could possibly make binary searches a bit faster. One could argue that you could also do the same by using a numeric data type to hold enumerations, and then set CHECK constraints to limit the values. This would be equally nice, however I really like being able to see the string representations of these types when I’m performing ad-hoc queries on my database. This is the best of both worlds.

While this is awesome from a database design perspective, what would be even more awesome is bridging that data type with the business logic code. In other words, I want the models in my ORM to use C# enums that map perfectly to the defined Postgres type.

Luckily, that’s possible to do with Castle ActiveRecord, and actually quite easy.

First, we’d need a C# enum. It would need to match the types in the Postgres ENUM (the names, that is, as the numeric values don’t actually matter here):

public enum Units
{
   //Individual items
   Unit = 0,

   //Volume
   Teaspoon = 1,
   Tablespoon = 2,
   FluidOunce = 3,
   Cup = 4,
   Pint = 5,
   Quart = 6,
   Gallon = 7,

   //Weight
   Gram = 8,
   Ounce = 9,
   Pound = 10,
}

Now, let’s use this in a model called RecipeIngredient, which represents a usage of an ingredient within a certain recipe:

[ActiveRecord("RecipeIngredients")]
public class RecipeIngredient
   : ActiveRecordLinqBase<RecipeIngredient>
{
   // Various column mappings go here
}

First, we’d add a new column mapping called Unit:

private Units unit;

[Property(SqlType = "UnitsEnum", ColumnType = "KPCServer.DB.UnitsMapper, Core")]
public Units Unit
{
   get { return unit; }
   set { unit = value; }
}

We now have a Unit property of type Units, our C# enumeration of valid units of measurements. So, now the model is restricted to only valid units of measurements, using all the type safety of C#.

There’s a few things to look at on the [Property] attribute for this mapping. First is SqlType. This property defines what type in SQL will be used for this column. This will cause ActiveRecord to use this type when it’s writing database creation scripts and what not.

Next, the ColumnType attribute specifies a managed type that’s responsible for type mapping information used by NHibernate. I’m using KPCServer.DB.UnitsMapper, in the Core.dll assembly. Unfortunately, a mapper class can only represent a single ENUM type. Thus, if you have a bunch of enums, you’ll need to repeat a lot of this logic. To DRY this out a bit, I’ve created a helper base class called PgEnumMapper:

public class PgEnumMapper<T> : NHibernate.Type.EnumStringType<T>
{
   public override NHibernate.SqlTypes.SqlType SqlType
   {
      get
      {
         return new NHibernate.SqlTypes.SqlType(DbType.Object);
      }
   }
}

The reason we need to do this is by default, NHibernate will convert C# enums to their numeric representations (which would be much more database agnostic,) attempting to store their value as a number in your database. Postgres will not cast numeric values to an ENUM type, so you’ll get an error trying to do this. The mapper above says, “No, I need this to be a string in the SQL you generate.”

Now, we can use this PgEnumMapper for each of our enums:

public class UnitTypeMapper : PgEnumMapper<UnitType>
{
}

No need to add anything to UnitTypeMapper, it’s perfectly fine the way it is. You’d want to create a PgEnumMapper derived class for each C# enum you want to support in your ORM of course.

That’s all there is to it! You can now use ENUM types in Postgres and magically have them map to C# enums. Wow I love Postgres.