DEV Community

Dirt simple SQL queries in F#

Kasey Speakman on October 03, 2017

SQL is something many of us have gone to great lengths to try to abstract away. The Repository pattern, ORMs, and Type Providers are examples of ab...

Read full post

Adam Davies • Jan 22 '18

This looks really good -- in my attempts I am having trouble with null values. I've tried the linked OptionHandler but doesn't seem to work...

Are you able to provide an example handling Options<string>?

Also, have you considered releasing what you've got as a library?
If not, i might once I can work out the details...

Kasey Speakman • Jan 22 '18 • Edited

Here is what I use to setup the optional type handlers. After that string option and many other optional types are handled automatically.

namespace Foo.MsSql

open System
open Dapper

type OptionHandler<'T>() =
    inherit SqlMapper.TypeHandler<option<'T>>()

    override __.SetValue(param, value) = 
        let valueOrNull = 
            match value with
            | Some x -> box x
            | None -> null

        param.Value <- valueOrNull    

    override __.Parse value =
        if isNull value || value = box DBNull.Value then
            None
        else
            Some (value :?> 'T)


module DapperConfig =

    let RegisterOptionTypes () =
        SqlMapper.AddTypeHandler(new OptionHandler<bool>())
        SqlMapper.AddTypeHandler(new OptionHandler<int>())
        SqlMapper.AddTypeHandler(new OptionHandler<int64>())
        SqlMapper.AddTypeHandler(new OptionHandler<string>())
        SqlMapper.AddTypeHandler(new OptionHandler<Guid>())
        SqlMapper.AddTypeHandler(new OptionHandler<DateTime>())
        SqlMapper.AddTypeHandler(new OptionHandler<single>())
        SqlMapper.AddTypeHandler(new OptionHandler<double>())
        SqlMapper.AddTypeHandler(new OptionHandler<decimal>())

Then as part of app startup, I call:

    DapperConfig.RegisterOptionTypes()

To me this is still a bit too jagged of an edge. Probably the way around having to call something at app startup is to include the initialization as a let statement in the Sql module. But I am not sure if it would get optimized away in a production build, since it will never get referenced.

One other gotcha: The column order in the returned query data must match the property order on F# records. Maybe this is because of the way the F# record constructor is generated, but Dapper cannot figure it out if the returned data is in a different order.

I will setup a github repo for it tonight, time permitting. I can include multi queries too (i.e. SELECT ... ; SELECT ... ;).

Kasey Speakman • Jan 23 '18

Created an initial repo. Still need to figure out how to make a NuGet package, add examples, etc.

SlimSql - seems a stupid name, but best I could come up with tonight.

Adam Davies • Mar 13 '18

Thanks for this!

I had the project on hold that could use this, but am restarting it now.

I currently have bits of code from your blogpost, plus other bits I scrapped together, as I discover things I need.

When it settles down I will try and switch to SlimSql... and perhaps add a PR or two :)

Kasey Speakman • Mar 13 '18

Sure thing. I need to figure out how to execute the startup code so the user doesn't have to wire it into their app startup. I hate that Dapper requires this.

Adam Davies • Mar 14 '18

FWIW I am currently calling this as part of opening the connection. The result is a noop since the method checks if the typehandler already exists in the SqlMapper.typeHandlers dictionary.

Looks something like this:

    let registerTypeHandlers() = 
        SqlMapper.AddTypeHandler(OptionHandler<Guid>())
        SqlMapper.AddTypeHandler(OptionHandler<int64>())
        ...

    let dbConnect string = 
        registerTypeHandlers()
        new OracleConnection(string)

PS: Am also forced to use Oracle, so would be nice to make that an option :)

Vladimir Shchur • Oct 28 '17

Is there any way to avoid boxing when passing int parameters?

Kasey Speakman • Oct 29 '17

Sure. You can manually construct the query to include the data as part of the query. That way, you deal with the types without boxing to construct the query. However, this is considered bad practice because it makes SQL Injection attacks possible.

For parameterized queries, this is currently not possible when using ADO.NET (which is used by Dapper). There is an open issue to use parameters without boxing. I'll have to see what implementation they come up with before I know whether/how it will complicate the functions I posted here.

In practice, I have not found the performance to be a problem... considering that IO is typically slower than boxing perf and there is a hard limit to the number of parameters (~2100). Are you just curious or did you have a specific use case where this was a problem?

Vladimir Shchur • Oct 30 '17 • Edited

It's generally better to avoid additional work for GC, but in this case fsharp type checking forces writing box even for reference types, which is inconvenient, as params are of this type Parameters : (string * obj) list. I have do this for both ints and strings:
[
p "paramInt" (box paramInt)
p "paramString" (box paramString)
]

Kasey Speakman • Oct 30 '17 • Edited

It is possible to avoid boxing in F#, but there is no point because the value is going to get boxed anyway by the database client (SqlParameter only takes objects as values). Avoiding boxing in the F# code will just bloat the code to no benefit.

Vladimir Shchur • Oct 30 '17

I see, thank you for the clarifications

Roland Andrag • Apr 16 '20

Hi Kasey, thank you for the useful post.

Is there any problem with or disadvantage to defining p as:
let p name value = (name, (box value))

Which allows you to just type
[ p "paramInt" paramInt ]

Kasey Speakman • Apr 16 '20 • Edited

Edit: I just re-read the preceding conversation and realized I answered the wrong question. Both yours and @lanayx previously.

No it is not a problem at all for the p helper to box the value, to avoid repeating box every time. And in fact that is exactly how I defined it in my own library. I believe it was just an oversight in the post. I will update the post.

Leaving the original response below.

I created this tiny helper due to a couple of factors.

ADO.NET boxes all parameters values to obj anyway
F# will not auto-upcast the values in the list, which can lead to type inference compile errors.

I recall there being an issue on a dotnet repo about having the option to not box sql parameters. But I am not sure if typed SQL parameters ever made it into a release.

So without the helper, the parameter list looked like this:

[
    "@SomeParam", box someParam
    "@OtherParam", box otherParam
]

Not terrible, but removing characters , box to add p on the front seemed a favorable trade.

That ended up being fortuitous because later, using a tuple was not quite enough. Sometimes it is necessary to declare the database type of the parameter. For example, when the value is null. (With Npgsql also when the column is jsonb.) So the parameter tuple became a full record type.

    type SqlParam =
        {
            Name : string
            Value : obj
            Type : DbType option
        }

p can be changed to work with this, and in a way that does not break existing defined queries. And it will be drastically shorter than spelling out the full SqlParam record.

    let p name value =
        {
            Name = name
            Value = value
            Type = None
        }


    let pTyped name value type_ =
        {
            Name = name
            Value = value
            Type = Some type_
        }

Roland Andrag • Apr 17 '20

Hi Kasey, thank you for the quick and helpful reply.

Moving from the F# type providers to Dapper has been a real pleasure for me. For the price of giving up the compile time type checking, I now have simple reusable data transfer objects (DTOs) and functions to convert these from domain and back.

Best regards

Roland

Gage • Oct 8 '17

Thanks for this! I'm an Elm/OCaml programmer and this really makes me want to learn F#

Daniel J. Summers • Oct 10 '17

Nice! I'll have to look into Dapper.