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.