PeanutButter.DatabaseHelpers: get your compiler to start error-checking your SQL statements

PeanutButter was born out of a desire to share code between projects, in particular, to take advantage of utility and fixes introduced during the development process on a range of concurrent projects in the realm of code which was specific to none.

Quite basically, I had some code which was already figured out and worked fairly well for what it was intended to do and I was just plain too lazy to maintain multiple versions of that code. I thought it would be great if I could use, say, the Nuget packaging system to spread the most up-to-date versions of code between projects, if only packaging for Nuget wasn't such a pain. The CLI tools work, but aren't easy to use. The official GUI for Nuget packaging looks like a revenge unleashed on the world by an angry development manager trying to prove he can code. No offense.

Ok. Offense. The Nuget GUI tools are horrid and the CLI packaging mechanism is a PITA. Thank goodness for the Nuget Package Template extension for Visual Studio. With a simple post-build event from each relevant package, I can push all 11 (currently) PeanutButter.* Nuget package updates after having run all my tests and switched to Release mode. Win!

But I digress; this article is actually to introduce PeanutButter.DatabaseHelpers and show you how you can effectively get your compiler to error-check your application SQL.

First, taking a step back. If you've been using a decent ORM like Entity, NHibernate, Habanero or even just Linq-to-SQL, you don't have a need for PB's DatabaseHelpers. You can save yourself the effort of reading the rest of this article. One of the primary reasons for using an ORM (imo) is to abstract the SQL away from the application. ORMs which do Linq well are especially good at this -- and that's one reason why I've been a fan of Entity (warts and all) for quite some time. None of them are perfect but they all can take a lot of the pain of dealing with direct database calls away. In particular, an ORM allows you to:

• Switch database backends (eg MSSQL/Firebird/MySQL/PostgreSQL and others). Of course backend support depends on the ORM, but most give you some kind of choice here.
• Not have to type out correct SQL statements in your code. You may be wondering why this is a problem, unless, of course, you've had the experience where SQL in your code worked once and stopped mysteriously. After back-tracking VCS commits, you find that someone accidentally changed a string and there was nothing to pick it up. Or perhaps there was a test and the test was just as wrong as the code it was constraining.
• Get the compiler to check that you've gotten the names of your database entities correct -- if you've misspelled an entity in your code in one place, chances are your code doesn't compile. Which is good -- the earlier up the code/compile/run/debug/package/deploy/test/etc chain you fail, the less it costs everyone.
• Not have to worry about SQL injection attacks.

If you're using a super-light ORM like Dapper or just simply converting a giant project with heaps of direct ADO access or perhaps providing support for an app using an unsupported backend (like Access), you may have to get down to the bare SQL. But it would be great if you didn't have to actually take the risk of writing some SQL. Better still if the tool producing the SQL to run on your backend can be tweaked to target a different backend as required.

So here's a possible approach:

1. Ensure that all of your database entities are defined as string constants in one accessible source file so that you aren't constantly fighting with each other on how to spell "color". Or "colour". However you all choose to spell it. And you aren't left to discover that "received" is spelled "recieved" sometimes in your SQL at go-live time. 
2. Get a tool like PeanutButter to do the boring work for you. Reliably and in a manner you can test, with fluent builder syntax.

Sound like a plan? Great (:

First, I like to stick to using a DataConstants file to hold constants like field names, default values, etc. The other advantage here is that you can reference the same DataConstants in your FluentMigrator migrations (you are using FluentMigrator, aren't you?!). For example:

namespace MyProject.Database 
{ 
    public static class DataConstants 
    { 
        public static class Tables 
        { 
            public static class Employee 
            {  
               // to use where you would reference the name of your table
                public const string NAME = "Employee";
                public static class Columns 
                { 
                  public const string EMPLOYEEID = "EmployeeID";
                  public const string FIRSTNAME = "FirstName";
                  public const string SURNAME = "Surname";
                  public const string DATEOFBIRTH = "DateOfBirth";
                }
            } 
        } 
    } 
}

Next, let's say we wanted to get a list of all Employees whose first names are "Bob". We could do:

var sql = SelectStatementBuilder.Create()
            .WithTable(DataConstants.Tables.Employee.NAME)
            .WithField(DataConstants.Tables.Employee.Columns.EMPLOYEEID)
            .WithField(DataConstants.Tables.Employee.Columns.FIRSTNAME)
            .WithField(DataConstants.Tables.Employee.Columns.SURNAME)
            .WithField(DataConstants.Tables.Employee.Columns.DATEOFBIRTH)
            .WithCondition(DataConstants.Tables.Employee.Columns.FIRSTNAME,
                             Condition.EqualityOperators.Equals,
                             "Bob");

Ok, so this looks a little longwinded, but with a using trick like so:

using _Employee = DataConstants.Tables.Employee;
using _Columns = DataConstants.Tables.Employee.Columns;

// some time later, we can do:
var sql = SelectStatementBuilder.Create()
            .WithTable(_Employee.NAME)
            .WithField(_Columns.EMPLOYEEID)
            .WithField(_Columns.FIRSTNAME)
            .WithField(_Columns.SURNAME)
            .WithField(_Columns.DATEOFBIRTH)
            .WithCondition(_Columns.FIRSTNAME, Condition.EqualityOperators.Equals, "Bob");

Now that's fairly readable and always produces the same, valid SQL. And will break compilation if you misstype something. And you can use Intellisense to help figure out required column names. This is a fairly simple example; just a taste. PeanutButter.DatabaseHelpers includes:

• Statement builders for:
• Select
• Update
• Insert
• Delete
• Data copy (insert into <X> select from <Y>)
• The ability to do Left and Inner joins
• Order clauses
• Where clauses
• Automatic quoting of strings, datetimes and decimals to values which will play nicely at your database (no more SQL injection issues, no more issues with localised decimals containing commas and breaking your SQL)
• Interfaces you can use for injection and testing, for example with NSubstitute (ISelectStatementBuilder, IInsertStatementBuilder, IUpdateStatementBuilder, IDataCopyStatementBuilder, IDeleteStatementBuilder)
• Another helper package PeanutButter.DatabaseHelpers.Testability which sets up NSubstitute mock objects for you so you can easily test without having to stub out all of the myriad builder returns on statement builders
• Executor builders:
• ScalarExecutorBuilder for insert/update/delete statements
• DataReaderBuilder for traditional ADO-style code
• With interfaces for easy testing
• Support for some computed fields (Min, Max, Coalesce, Count)
• Constrained by tests, developed TDD.
• Syntax support for Access, SQLite, MSSQL and Firebird. Additional syntax support can be added upon request, given enough time (:

All in a convenient Nuget package you can use from your .NET application (install-package peanutbutter.databasehelpers), irrespective of target language. PeanutButter.DatabaseHelpers was developed and is maintained in VB.NET simply because of its origins, but that really doesn't matter to you as the consumer (:

Sure, there are features which are missing. This library has been built very much on the premise of extending on requirement. I hope that it can be helpful for someone else though. It has been used in at least 5 decent-sized projects.

I welcome feedback and will implement reasonable requests as and when I have time. Like the rest of PeanutButter, DatabaseHelpers is licensed BSD and you can get the source from GitHub.