Using F# to verify sql queries in C# projects at build time

When using sql queries in a project it is really hard to guarantee that all the sql queries remain valid after they have been coded, they could stop working for various reasons and there is a possibility that it might get through QA. In F# there are Type Providers that we could use to guarantee that this does not happen. We could use Type Providers as described in this article to validate all the sql queries in a C# project. There might be other ways to validate sql queries at build time but I am not aware of those, please let me know if there are other way of validating sql queries at build time.

We will create 3 projects, a console app in C#, a library in C# which will have all the sql queries and one in F# which we will use to validate sql queries against our database. We create a solution with all the projects as shown in the following screenshot.

FSharpCSharpDB solution

ConsoleApp references DBLibrary where all our sql queries are. All our sql queries for the solution will be in file Sqls.cs. Let’s create a MSSQL db called `FSharpCSharpDB` and create a customer table using the following sql

create table customer(
id bigint not null identity(1,1) primary key,
first_name varchar(150),
last_name varchar(150),
)

Let’s reference DBLibrary in ConsoleApp and use the sql as shown below.

using DBLibrary;
using System.Data.SqlClient;
namespace ConsoleApp
{
  class Program
  {
    static void Main(string[] args)
    {
      var conStr = @"Server=(local)\SQLEXPRESS;Database=FSharpCSharpDB;;Trusted_Connection=True";
      using (var con = new SqlConnection(conStr))
      using (var cmd = new SqlCommand(Sqls.SELECT_CUSTOMERS, con))
      {
        con.Open();
        using (var reader = cmd.ExecuteReader())
        {
          while (reader.Read())
          {
          }
        }
      }
    }
  }
}

Let’s build and run the ConsoleApp, it builds and runs fine.

Let’s now reference DBLibrary in SqlValidator and add nuget FSharp.Data.SqlClient to it. Add following code to SqlTests.fs.

namespace SqlValidator
open FSharp.Data
open DBLibrary
module SqlTests =
[<Literal>]
let conStr = @"Server=(local)\SQLEXPRESS;Database=FSharpCSharpDB;;Trusted_Connection=True"
let testSelectCustomers() =
  let cmd = 
    new SqlCommandProvider<Sqls.SELECT_CUSTOMERS, conStr>(conStr)
  cmd.Execute()

Please visit http://fsprojects.github.io/FSharp.Data.SqlClient/ to find out more about how to work with this Type Provider. If we build the solution we see that the build succeeds.

Let’s change the sql query in file Sqls.cs to

"select first_name_x, last_name from customer"

and build ConsoleApp. It builds fine, but if we build SqlValidator the build fails with following error

The type provider 'FSharp.Data.SqlCommandProvider' reported an error: Invalid column name 'first_name_x'.

We can now catch these errors at build times instead of runtime and stop committing incorrect code.

Let’s change the column name to first_name_x in our table and build the solution, we see that the solution builds successfully.

Conclusion

Validating sql queries using F# Type Provides picks up errors if the sql queries in the app changes or if the db changes. The downside to this way of validating sql queries is that a F# function has to be added for every sql query used in the project, the other way to validate sql queries at build time is to use F# Type Providers in a separate library and reference it in C# projects.

The solution for this article is on github.