F# Raw SQL Access

With my start in working with F#, I came to the point to access a database. When you read my last post, you know that an ORM was out of question. What me really surprised at the end, was that the code is much less than every other DB access I wrote.

So how does it look?

The general idea was, to first create a record that holds the SQL statement and the parameters, that gets then executed.

The record enables you to write tests for your database access.

For accessing the ADO.Net functions, I use FsSql.

So beginn with the entity with represents one row of the table:

type Weather() =
    member val Id = 0 with get,set

    member val LocationId = Guid.Empty with get,set
    member val DataFrom = DateTimeOffset.MinValue with get,set
    member val DataTo = DateTimeOffset.MinValue with get,set
    member val Temperature = 0.0m with get,set
    member val Humidity = 0.0m with get,set
    member val Rain = 0.0m with get,set
    member val WindSpeed = 0.0m with get,set
    member val WindDirection = 0.0m with get,set
    member val Clouds = 0.0m with get,set
    member val Pressure = 0.0m with get,set

It’s a simple store for weather data for a location.

To insert an entry to the table, we need a function to get the wrapper record:


let private insert (weather : Weather ) =
{
    query = "INSERT INTO \"intersect\".\"Weather\"(
                         locationid, datafrom, datato, temperature, humidity, rain,
                         windspeed, winddirection, clouds, pressure)
             VALUES (@locationid, @datafrom, @datato, @temperature, @humidity, @rain,
                     @windspeed, @winddirection, @clouds, @pressure);
             RETURNING id;";
    parameters = [
                 P("@locationid", weather.LocationId);
                 P("@datafrom", weather.DataFrom);
                 P("@datato", weather.DataTo);
                 P("@temperature", weather.Temperature);
                 P("@humidity", weather.Humidity);
                 P("@rain", weather.Rain);
                 P("@windspeed", weather.WindSpeed);
                 P("@winddirection", weather.WindDirection);
                 P("@clouds", weather.Clouds);
                 P("@pressure", weather.Pressure);
             ]
}

The wrapper has the query as string and a list of SqlParameters.

type changeQueryObject =
{
    query : string;
    parameters : Sql.Parameter list;
}

The P- function is a shortcut for the FsSql Sql.Parameter.make.

To execute the insert statement, we pass the changeQueryObject to following function:

let executeScalar (queryObj : changeQueryObject) =
    sql.ExecScalar queryObj.query queryObj.parameters

With the “RETURNING id” at the end of the SQL statement and executing it with ExecScalar, we get the primary key of the new entry. Everything we want after the insert.

The same works for updates:

let private update (weather : Weather ) =
{
    query = "UPDATE \"intersect\".\"Weather\"
             SET locationid=@locationid, datafrom=@datafrom, datato=@datato, temperature=@temperature, humidity=@humidity,
             rain=@rain, windspeed=@windspeed, winddirection=@winddirection, clouds=@clouds, pressure=@pressure
             WHERE id=@id;
             SELECT @id;
             ";
    parameters = [
                    P("@id", weather.Id);
                    P("@locationid", weather.LocationId);
                    P("@datafrom", weather.DataFrom);
                    P("@datato", weather.DataTo);
                    P("@temperature", weather.Temperature);
                    P("@humidity", weather.Humidity);
                    P("@rain", weather.Rain);
                    P("@windspeed", weather.WindSpeed);
                    P("@winddirection", weather.WindDirection);
                    P("@clouds", weather.Clouds);
                    P("@pressure", weather.Pressure);
                 ]
}

And with some little pattern matching, we get a nice save function:

let save (weather : Weather) =
    match weather.Id with
    | 0 -> insert weather
    | _ -> update weather

But what is with querying and some filtering? Now F# plays it’s strength.

For that we need an little changed query wrapper:

type selectQueryObject<'T> =
{
    query : string;
    parameters : Sql.Parameter list;
    deserialisation : (IDataRecord -> 'T) option;
}

The new field “deserialisation” holds an function to get an instance of the entity from a datarecord. This is the one for the weather entity.

let asWeather (r: IDataRecord) =
    new Weather(Id = (r?id).Value, LocationId = (r?LocationId).Value, DataFrom = (r?DataFrom).Value,
    DataTo = (r?DataTo).Value, Temperature = (r?Temperature).Value,
    Humidity = (r?Humidity).Value, Rain = (r?Rain).Value, WindSpeed = (r?WindSpeed).Value,
    WindDirection = (r?WindDirection).Value, Clouds = (r?Clouds).Value, Pressure = (r?Pressure).Value)

How looks the querying function?

let search (fc : WeatherSearchRequest) =
    let query = "SELECT id, locationid, datafrom, datato, temperature, humidity, rain, windspeed, winddirection, clouds, pressure FROM \"intersect\".\"Weather\""

    let locationIdPara = w "locationid" "@locationid" "=" fc.LocationId
    let fomPara = w "datafrom" "@datafrom" ">=" fc.From
    let toPara = w "datato" "@datato" "<=" fc.To
    let datetimeFromPara = w "datafrom" "@datafrom" ">=" fc.DateTime
    let datetimeToPara = w "datato" "@datato" "<=" fc.DateTime

    Some asWeather
    |> emptyQueryPart
    |> combineAnd locationIdPara
    |> combineAnd fomPara
    |> combineAnd toPara
    |> combineAnd datetimeFromPara
    |> combineAnd datetimeToPara
    |> combineQueryParts query

The WeatherSearchRequest is a POCO which holds the various filter values.

Every possible part of the where- Statement is wrapped in an queryPart record.

type queryPart =
{
    where : string;
    parameter : Sql.Parameter option
}

The w- function (for Where) creates such a record:

let private w<'a when 'a : (new : unit -> 'a) and 'a : struct and 'a :> System.ValueType> column parametername operator (value : System.Nullable<'a>) =
    match value.HasValue with
    | true -> { where = sprintf "%s %s %s" column operator parametername; parameter = Some <| P(parametername, value.Value)}
    | false -> {where = ""; parameter = Option<Sql.Parameter>.None}

When the filter value is set, it create a filled queryPart, else we get an empty one.

But what do we do with it now? Let’s look at the next code block.

Some asWeather
|> emptyQueryPart
...

This is to create a selectQueryObject with setted deserialisation function. The selectQueryObject is then piped to multiple combineAnd functions.

...
|> combineAnd locationIdPara
|> combineAnd fomPara
|> combineAnd toPara
|> combineAnd datetimeFromPara
|> combineAnd datetimeToPara
...

let private combineAnd<'T> (x : queryPart) (qo : selectQueryObject<'T>) =
    match (x.parameter, qo.query) with
    | (Some xpart, "") -> { query = x.where; parameters = [x.parameter.Value]; deserialisation = qo.deserialisation}
    | (Some xpart, _) -> {query = qo.query + " AND " + x.where; parameters = x.parameter.Value :: qo.parameters; deserialisation = qo.deserialisation}
    | (None, _) -> { query = qo.query; parameters = qo.parameters; deserialisation = qo.deserialisation}

Once again, we use some pattern matching.

The first case is hit, when we have a filled queryPart but an empty selectQueryObject. So when the first filter criteria is hit.

The second case is hit, when we add the other filter criteria to the selectQueryObject.

The last one is hit, when we have already some data in the selectQueryObject, but the queryPart is empty. So when we have already some filter criteria added, but the current one is not set.

At the end, we pipe the selectQueryObject to the combineQueryParts function.

...
|> combineQueryParts query
...

let private combineQueryParts<'T> (query :string) (qo :selectQueryObject<'T>) =
    let mutable q = query
    if (qo.query.Length > 0) then
        q <- query + " WHERE " + qo.query

    { query = q; parameters = qo.parameters; deserialisation = qo.deserialisation; }

This function simply combines the select- Query with the where- Statement if needed.

At the end, we get a nice filled selectQueryObject which is then passed to a general function.

let executeReader<'T> (queryObj : selectQueryObject<'T> )=
    sql.ExecReader queryObj.query queryObj.parameters
    |> Seq.ofDataReader
    |> Seq.map queryObj.deserialisation.Value

Nice, or?

The complete code at once can be found here.

So what about the testing? With the query wrapped into a record, we simply can tests the data in the record.

let checkSqlParameter (name : string) (value : obj) (parameter : Sql.Parameter) =
    obj.Equals(parameter.ParameterName,name) && obj.Equals(parameter.Value,value)

let checkForSqlParameter (name : string) (value : obj) parameters =
    parameters |> Seq.exists (fun i -> checkSqlParameter name value i) |> should equal true

let nullableFromPara = new Nullable<DateTimeOffset>(fromPara)
let nullableToPara = new Nullable<DateTimeOffset>(toPara)
let locationIdPara = new Nullable<Guid>(Guid.NewGuid())

[<Fact>]
let ``Check query with LocationId, From and To parameter``() =
    let fc = new WeatherSearchRequest()
    fc.LocationId <- locationIdPara
    fc.From <- nullableFromPara
    fc.To <- nullableToPara

    let queryObj = weather.search fc
    queryObj.parameters |> Seq.length |> should equal 3
    queryObj.parameters |> checkForSqlParameter "@datafrom" fc.From
    queryObj.parameters |> checkForSqlParameter "@datato" fc.To
    queryObj.parameters |> checkForSqlParameter "@locationid" fc.LocationId

    queryObj.query |> should equal "SELECT id, locationid, datafrom, datato, temperature, humidity, rain, windspeed, winddirection, clouds, pressure FROM \"intersect\".\"Weather\" WHERE locationid = @locationid AND datafrom >= @datafrom AND datato <= @datato"

When was the last time, you could test which SQL- query gets executed against your DBS? 😉

No more ORM!

After working some years with different OR- Mappers (NHibernate, Entity Framework, proprietary ones, …) to build a lot of line of business applications, I came to the conclusion to trash them.

So how did I come to this conclusion? Yes, ORMs make it on the first look easy to access your database and query them. That is it all what you get. But you also get some problems, that were not there before.

Some of them are here:

  1. Generated Queries and Performance
    The ORM generates the SQL Query for you. But you will never get the best sql query to get your data. You do not want to fetch all columns of an table? Have fun!
  2. Transactions
    Every ORM has his own idea how to represent a transaction. Why? Plain db transactions are really easy to understand. Why add another complication to your application? When you want a stable and predictable LoB- Application, you want to know exactly when which changes are commited to the database.
  3. Predictability
    Do you know, when which query is executed against your database? How often did you looked for bugs where data not returned by a query, that are caused, because you forget to flush somewhere a session?
  4. Additional Query language
    Most ORM have their own query language or uses LINQ for the queries. LINQ is very nice to query your objects, but when you create more complicated queries, it gets really quick ugly. Group by- Queries? Outer Joins? Hurray!
    Why add another abstraction when you have already a Structured Query Language?
    How often did you fix code of some unexperienced developers that had performance issues, because the queries are done in memory or results in hundred small queries against the db? Simply because the code looks the same and they did not think about it.
  5. Support for different DBS, but no one to 100%
    When did you the last time tried to access a DBS specific feature? You want to use PostGIS? Filestreams on MS SQL? Or much simplier, use timestamptz on PostgreSQL?  No chance.
    Why is this? Simply because the ORM have to provide an API over all supported DBS.
    And when did you wrote a LoB application that have to run on different DBS? I haven’t?
  6. Additional problems that you did not see coming
    This week I was talking with a collegue about some problems with our used ORM and the build queue. WTF? What does the ORM have to do with the build queue?

This post should not be a rant about ORM or dictate to everyone to not use them anymore. I recognized in the last weeks, that the people do not think anymore about the question, if they want to use an ORM. They simply say, “Yeah, let’s use X as ORM”, but did not think if they can achieve their requirements with it. So see this post as an starting point to rethink your technology choices.

ORM are a part of your toolchain, you do not have to fix every problem with your beloved tool. Use the right tool for the job.