Storing binary data in NHibernate / ActiveRecord

I believe the simplest way to store binary data is to just put in the database. Whenever I’ve agreed to throw data to a disk I’ve had issues with deployment, administration or disaster recovery.

Simply put: Once you have a dependency from your database to your file system, you no longer have the luxury of only thinking about recovering the database. You now need to keep two pieces of your system “safe”, both requiring a completely different toolset than the other.

Besides the obvious second point of headache for backup/recovery, you also bring yourself into a world of hurt for deployment / maintenance scenarios.
Filesystem access rights can be a huge pain in the ass, and having to set them right (and keep them that way) is usually a time-bomb waiting to go off.

So, storing your binary data in the db solves many problems, but some new ones arise. Mostly implementation details, but I’d like to show you some things to keep in mind when writing binary data to db.

NHibernate supports no lazy loading of instance fields

While with conventional ADO.NET I’d just put the binary data as a column inside the table it belongs to, NHibernate requires you to do things different. If you map your data like that NHibernate will fetch it whenever you read objects from that table, meaning that you’ll be querying large binary data fields for no reason, causing you application performance to significantly degrade over time.

What you want is to have NHibernate fetch that field only if it is accessed (lazy load it), and that’s not possible for fields inside a class, but it is possible for references. So your database schema should look like this:

And your mapping will look similar to this (I’ll use ActiveRecord for easier understanding):

[ActiveRecord]
public class Invoice : ActiveRecordBase<Invoice>
{
    [PrimaryKey]
    public int Id { get; set; }

    [BelongsTo(Lazy = FetchWhen.OnInvoke, Cascade = CascadeEnum.SaveUpdate)]
    public BinaryData ScannedInvoice { get; set; }
}
[ActiveRecord]
public class BinaryData : ActiveRecordBase<BinaryData>
{
    [PrimaryKey]
    public int Id { get; set; }
    [Property(ColumnType = "BinaryBlob", SqlType = "IMAGE", NotNull = true)]
    public byte[] Data { get; set; }
}

Now whenever your Invoice is saved/inserted NHibernate will also check if BinaryData has to be updated/inserted, while only loading the binary field if you actually access the Invoices.ScannedInvoice field.