Composite queries

Sometimes writing arbitrary SQL command is not enough. Especially, when the query accepts complex, user-provided search criteria. Of course, the criteria changeability can be achieved with some smart combination of NULL checking and CASE WHEN:

    select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy
    where (@name is null or name like '%' + @name + '%')
      and (@title is null or title like '%' + @title + '%')
      and (@content is null or content like '%' + @content + '%')
    order by case 
                 when @order = 1 then createdAt
                 when @order = 2 then author
                 when @order = 3 then status
             end

The ugly truth is, that whenever a @param is null or order by case is used, the query optimizer abandons use of indexes (at least when we use MS SQL).
With SqlFun, it's easy to replace parameter-based ordering with dynamic SQL generation, since the command is an ordinary string, but managing changeable where clause is harder, because the query function must be provided with variable parameter list.

SqlFun provides a simple mechanism of composition, based on recursive function type definition. The composition blocks are query parts:

    type IQueryPart = 
        abstract member Combine: string -> 't

where the parameter is a query template and return value is a query function. The whole point is to define Combine method:

    type FilterByName(string name, next: IQueryPart) =

        interface IQueryPart with
            override this.Combine (template: string) : 't = 
                let exp = template |> expandTemplate "WHERE-CLAUSE" "where " " and " "name like '%' + @name + '%'"
                let f = this.Combine<string -> 't> exp
                f name

by passing to next query part modified command template and modified function type, i.e. with additional parameter, then applying provided parameter value to returned function.
The last query part in a chain should be an object of the FinalQueryPart, that generates needed function and caches it.
The expandTemplate placeholder clause separator value function, used in the example above, allows to incrementally replace {{placeholder}} pattern in template with a value, adding a specified clause and separators when needed.

At first sight, it's to much boilerplate. But this technique is not about ad-hoc querying, it's about building query DSL-s, that need more effort. If we apply it to five parameters, using list of discriminated unions, it starts to make much more sense:

    type PostCriteria =
        | TitleContains of string
        | ContentContains of string
        | AuthorIs of string
        | CreatedAfter of DateTime
        | CreatedBefore of DateTime

    type FilterPostsPart(criteria: PostCriteria list, next: QueryPart) =
        inherit ListQueryPart<PostCriteria>(criteria, next) 

        override this.CombineItem template item remainingItems: 't = 
            match item with
            | TitleContains title ->
                let exp = template |> expandWhere "title like '%' + @title + '%'"
                let f = this.CombineList<string -> 't> exp remainingItems
                f title
            | ContentContains content ->
                let exp = template |> expandWhere "content like '%' + @content + '%'"
                let f = this.CombineList<string -> 't> exp remainingItems
                f content
            | AuthorIs author ->
                let exp = template |> expandWhere "author = @author"
                let f = this.CombineList<string -> 't> exp remainingItems
                f author
            | CreatedAfter date -> 
                let exp = template |> expandWhere "createdAt >= @date"
                let f = this.CombineList<DateTime -> 't> exp remainingItems
                f date
            | CreatedBefore date -> 
                let exp = template |> expandWhere "createdAt <= @date"
                let f = this.CombineList<DateTime -> 't> exp remainingItems
                f date

Composing with another query parts is fairly easy. Let's define query part for sorting posts

    type PostSortOrder = 
        | Title
        | CreationDate
        | Author
        | Desc of PostSortOrder

    type SortOrderQueryPart (orders: PostSortOrder list, next: IQueryPart) =

        interface IQueryPart with 
            override this.Combine template: 't = 
                let cols = orders |> Seq.map getOrderSql |> String.concat ", "
                let exp = template |> expandOrderBy cols
                next.Combine exp

Since class constructors can not be curried, we must define functions to make it possible to use pipelining:

    let filterPostsBy = FilterPostsPart |> curry

    let sortPostsBy = SortOrderQueryPart |> curry

Before we can use the FinalQueryPart class, we have to bind the function creating connection to it:

    let createConnection () = new SqlConnection(connectionString)
    
    let buildQuery ctx = 
        FinalQueryPart(ctx, createConnection, None, defaultParamBuilder)

The last step is to specify SQL template:

    let selectPosts (next: QueryPart): Post list = 
        next.Combine "select p.id, p.blogId, p.name, p.title, p.content, p.author, p.createdAt, p.modifiedAt, p.modifiedBy, p.status
                      from post p
                      {{WHERE-CLAUSE}}
                      {{ORDER-BY-CLAUSE}}"

And then, we can compose parts:

    let findPosts (criteria: PostSearchCriteria list) (orders: PostSortOrder list) (ctx: DataContext) = 
        ctx 
        |> buildQuery
        |> filterPostsBy criteria
        |> sortPostsBy orders
        |> selectPosts

Or without sorting:

    let findPosts (criteria: PostSearchCriteria list) (ctx: DataContext) = 
        ctx 
        |> buildQuery
        |> filterPostsBy criteria
        |> selectPosts

The downside of composite queries is, that they are not generated during module initialization, but during their first use, and as such, they don't participate in usual SqlFun type safety mechanisms. Thus, each of them should have it's own tests defined. I recommend to use FsCheck for comprehensive query DSL testing.