SQLProvider allows you to unit-test your SQL-logic

That's a clear advantage on large-scale projects, where there are multiple developers and the SQL-queries grow more complex over time.

1. Debugging. It is faster to debug the unit test than to spin the full environment again and again.
2. Refactoring: To ensure what the original functionality is actually doing before you modify it.

Why to unit-test?

F# strong typing provides safety over raw SQL: Instead of your customer finding an issue, your code will not compile if the database shape is wrong, for example, someone removed an important column.

SQLProvider does parametrized SQL, you can watch the executed SQL, and you can even open the parameterized SQL parameters for easier debugging:

FSharp.Data.Sql.Common.QueryEvents.SqlQueryEvent 
|> Event.add (fun e -> System.Console.WriteLine (e.ToRawSqlWithParamInfo()))

But unit-testing is a good addition in scenarios where:

• Your database is very dynamic, and data is changing all the time
• You want to ensure the logic works over a period of time
• You have a big project where build-time takes long
• You want Continuous Integration, but your test-data or database is unstable.

How?

There are 2 helper functions to mock the database connection:

• FSharp.Data.Sql.Common.OfflineTools.CreateMockEntities<'T> - With this, you can mock a single table.
• FSharp.Data.Sql.Common.OfflineTools.CreateMockSqlDataContext<'T> - With this, you can mock a context with multiple tables

You just feed anonymous records like they would be database rows. You don't need to add all the columns, just the ones you use in your query. But you can add extra-columns for easier asserts.

Example: executable business logic

open System

type OrderDateFilter =
| OrderDate
| ShippedDate
| Either

let someProductionFunction (ctx:sql.dataContext) (orderType:OrderDateFilter) (untilDate:System.DateTime) =
    task {
        let ignoreOrderDate, ignoreShippedDate =
            match orderType with
            | OrderDate -> false, true
            | ShippedDate -> true, false
            | Either -> false, false

        let tomorrow = untilDate.AddDays(1.).Date
        let someLegacyCondition = 0 // we don't need this anymore

        let itms = 
            query {
                for order in ctx.Main.Orders do
                join cust in ctx.Main.Customers on (order.CustomerId = cust.CustomerId)
                where ((cust.City = "London" || cust.City = "Paris" ) && (
                    (ignoreOrderDate || order.OrderDate < tomorrow) && (someLegacyCondition < 15)) &&
                    (ignoreShippedDate || order.ShippedDate < tomorrow) &&
                    cust.CustomerId <> null && order.Freight > 10m 
                )
                select (cust.PostalCode, order.Freight)
            }
        let! res = itms |> Array.executeQueryAsync

        //maybe some post-processing here...
        return res
    }

Example, unit-test part

Note: CustomerID, not CustomerId. These are the database field-names, not the nice LINQ names.

let ``mock for unit-testing: datacontext``() =
    task {
        let sampleDataMap =
            [ "main.Customers",
                [|  {| CustomerID = "1"; City = "Paris";  PostalCode = "75000";  Description = "This is good";    |} 
                    {| CustomerID = "2"; City = "London"; PostalCode = "E143AB"; Description = "This is good";    |}
                    {| CustomerID = "3"; City = "Espoo";  PostalCode = "02600";  Description = "Ignore this guy"; |}
                |] :> obj
                "main.Orders",
                [|  {| CustomerID = "1"; OrderDate = DateTime(2020,01,01); ShippedDate = DateTime(2020,01,04); Freight =  4m;|}
                    {| CustomerID = "1"; OrderDate = DateTime(2021,02,11); ShippedDate = DateTime(2021,02,12); Freight = 22m;|}
                    {| CustomerID = "2"; OrderDate = DateTime(2022,03,15); ShippedDate = DateTime(2022,03,22); Freight = 20m;|}
                    {| CustomerID = "2"; OrderDate = DateTime(2024,02,03); ShippedDate = DateTime(2024,02,17); Freight = 50m;|}
                    {| CustomerID = "3"; OrderDate = DateTime(2024,02,03); ShippedDate = DateTime(2024,02,17); Freight = 15m;|}
                |] :> obj

                ] |> Map.ofList
        let mockContext = FSharp.Data.Sql.Common.OfflineTools.CreateMockSqlDataContext<sql.dataContext> sampleDataMap

        let! res = someProductionFunction mockContext OrderDateFilter.OrderDate (DateTime(2024,02,04))
        //val res: (string * decimal) list =
        //  [("75000", 22M); ("E143AB", 20M); ("E143AB", 50M)]        

        assert_equal 3 res.Length
        assert_contains ("75000", 22M) res
        assert_contains ("E143AB", 20M) res
        assert_contains ("E143AB", 50M) res
    }

CreateMockSqlDataContext takes a Map<string,obj> where the string is the table name as in the database, and obj is an array of anonymous records. The mock is meant to help create data-context objects to enable easier testing of your LINQ-logic, not to test SQLProvider itself.

There are some limitations with the SQLProvider mock a DB-context:

• The mock-context will not connect the DB, and you can't save entity modifications. SubmitUpdates() will do nothing.
• SQLProvider custom operators (like x |=| xs and y %<> "A%") are not supported. So you have to use LINQ-ones (e.g. xs.Contains x and not y.StartsWith "A") that do work in SQLProvider as well.
• You can call database-table .Create methods to create new instances (it doesn't connect the database). You can call update entity columns x.Col <- Some "hi", but it doesn't really do anything.
• You cannot call stored procedures.
• Names are database names, and they are case-sensitive. If you miss a table, in your mock, there will be a clear error. If you mistyped the anonymous record column name, you will probably just get a zero-result or ValueNone.Value-error or some other unwanted behaviour.

If you are running an off-line solution like SSDT or ContextSchemaPath, you should be able to run also these unit-tests with your CI.