Code Reuse with the SqlProgrammabilityProvider

Friday, May 20, 2016

In the process of creating a web service backend for a large web application for Diamler I've come across a few patterns that allow me to provide functionality without bloating my codebase. As the application is largely data driven one of my favorite patterns involves using the SqlProgrammabilityProvider and optional stored procedure parameters.

The problem to solve

Imagine we have an automobile resource that we want to be able to get by id or do some sort of querying to retrieve a list of them.

GET api/Automobiles/:id -> get by id
GET api/Automobiles?make=:make -> get a collection of automobiles matching the query

And for this example let's say our Automobile can be modeled by this simple type.

type Automobile = {
    Id : int
    Make : string
    Model : string
    Year : int
}

Getting our project ready

First step is to install the provider

PM> Install-Package FSharp.Data.SqlClient

Next step is to define our ProgrammabilityProvider

type Db = SqlProgrammabilityProvider<"name=db">

In this case the "name=db" designation tells the provider to look into our .config file for a connection string with the name db. I like this approach because it makes multi environment deploys with config transforms trivial.

<connectionStrings>
  <add name="db" connectionString="" />
</connectionStrings

Writing the stored procedure

Based on our requirements we need to create a stored procedure with 2 parameters, id and make but we only want to filter if the value is provided.

CREATE PROCEDURE [dbo].[spGetAutomobiles]
    @Id int,
    @Make nvarchar(100)
AS
BEGIN 
    SELECT * 
    FROM Automobiles a with (nolock)
    WHERE (@Id = 0 OR a.Id = @Id)
    AND (@Make = '' OR a.Make = @Make)
END
    

If you're wondering why I wrote one procedure with optional parameters rather than creating two stored procedures or simply writing different queries with the SqlCommandProvider stay with me for a bit longer.

Calling the stored procedure

let getById id = async {
    use sproc = new Db.dbo.spGetAutomobiles()
    return! sproc.AsyncExecuteSingle(id, "")
    }
    
let getByMake make = async {
    use sproc = new Db.dbo.spGetAutomobiles()
    return! sproc.AsyncExecute(0, make)
    }

Ok, now we have two functions that match our requirements.

getById returns an Async<Option<'T>> because we're using an async workflow and we used the AsyncExecuteSingle method which returns an Option<'T> to indicate that it may not be able to return a result (for instance if no automobile existed with the provided id).

getByMake returns an Async<IEnumerable<'T>> because we're once again using the async workflow and we've used the AsyncExecute method which returns a collection.

In both cases the generic 'T is the same type because it is generated for us by the Type Provider off of the same stored procedure. If we had multiple stored procedures the generic type would be different for each call.

Our current issue is that 'T isn't Automobile which means our database dependency would escape this function.

Conversion Function

let toAutomobile (x : Db.dbo.spGetAutomobiles.Record) : Automobile =
    { Id = x.Id
      Make = x.Make
      Model = x.Model
      Year = x.Year }

Simple. One of the cool things about the type provider is that our 'T type can be accessed by using the .Record property of the stored procedure.

Now we can modify our two functions to return Automible rather than 'T.

Mapping the result

let getById id = async {
    use sproc = new Db.dbo.spGetAutomobiles()
    let! result = sproc.AsyncExecuteSingle(id, "")
    return result |> Option.map toAutomobile
    }

In this case result is an Option<'T> so we want to use the map function in the Option module

let getByMake make = async {
    use sproc = new Db.dbo.spGetAutomobiles()
    let! result = sproc.AsyncExecute(0, make)
    return result |> Seq.map toAutomobile
    }

Here result is a Seq<'T> so we want to use the map function in the Seq module

Putting it all together

Now in very few lines of code we have the ability to stick with our domain model whether we get one or many automobiles. Because we used a single stored procedure we were able to write a single function that converts our type provider created type into our Automobile type which insulates the rest of our application from potential database structure changes.

type Automobile = {
    Id : int
    Make : string
    Model : string
    Year : int
}

type Db = SqlProgrammabilityProvider<"name=db">

let toAutomobile (x : Db.dbo.spGetAutomobiles.Record) : Automobile =
    { Id = x.Id
      Make = x.Make
      Model = x.Model
      Year = x.Year }

let getById id = async {
    use sproc = new Db.dbo.spGetAutomobiles()
    let! result = sproc.AsyncExecuteSingle(id, "")
    return result |> Option.map toAutomobile
    }
    
let getByMake make = async {
    use sproc = new Db.dbo.spGetAutomobiles()
    let! result = sproc.AsyncExecute(0, make)
    return result |> Seq.map toAutomobile
    }