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. Faster to debug unit-test than spin the full environment again and again.
  2. Refactoring: To ensure what the original functionality is actually doing before you modify.

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:

|> Event.add (fun e -> System.Console.WriteLine (e.ToRawSqlWithParamInfo()))

But unit-testing is good addition in scenarios where:


There are 2 helper functions to mock the database connection:

You just feed an 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 DB-field-names, not 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
                [|  {| 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 database, and obj is an array of anonymous records. The mock is meant to help creating 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:

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

Multiple items
type LiteralAttribute = inherit Attribute new: unit -> LiteralAttribute

new: unit -> LiteralAttribute
[<Literal>] val resolutionPath: string = "C:\git\SQLProvider\docs\content\core/../../files/sqlite"
[<Literal>] val connectionString: string = "Data Source=C:\git\SQLProvider\docs\content\core\..\northwindEF.db;Version=3;Read Only=false;FailIfMissing=True;"
Multiple items
namespace FSharp

namespace Microsoft.FSharp
Multiple items
namespace FSharp.Data

namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
type sql = SqlDataProvider<...>
type SqlDataProvider
<summary>Typed representation of a database</summary> <param name='ConnectionString'>The connection string for the SQL database</param> <param name='ConnectionStringName'>The connection string name to select from a configuration file</param> <param name='DatabaseVendor'> The target database vendor</param> <param name='IndividualsAmount'>The amount of sample entities to project into the type system for each SQL entity type. Default 50. Note GDPR/PII regulations if using individuals with ContextSchemaPath.</param> <param name='UseOptionTypes'>If set, F# option types will be used in place of nullable database columns. If not, you will always receive the default value of the column's type even if it is null in the database.</param> <param name='ResolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types. Types used in desing-time: If no better clue, prefer .NET Standard 2.0 versions. Semicolon to separate multiple.</param> <param name='Owner'>Oracle: The owner of the schema for this provider to resolve. PostgreSQL: A list of schemas to resolve, separated by spaces, newlines, commas, or semicolons.</param> <param name='CaseSensitivityChange'>Should we do ToUpper or ToLower when generating table names?</param> <param name='TableNames'>Comma separated table names list to limit a number of tables in big instances. The names can have '%' sign to handle it as in the 'LIKE' query (Oracle and MSSQL Only)</param> <param name='ContextSchemaPath'>The location of the context schema previously saved with SaveContextSchema. When not empty, will be used to populate the database schema instead of retrieving it from then database.</param> <param name='OdbcQuote'>Odbc quote characters: Quote characters for the table and column names: `alias`, [alias]</param> <param name='SQLiteLibrary'>Use System.Data.SQLite or Mono.Data.SQLite or select automatically (SQLite only)</param> <param name='SsdtPath'>A path to an SSDT .dacpac file.'</param>
namespace FSharp.Data.Sql.Common
[<Struct>] type DatabaseProviderTypes = | MSSQLSERVER = 0 | SQLITE = 1 | POSTGRESQL = 2 | MYSQL = 3 | ORACLE = 4 | MSACCESS = 5 | ODBC = 6 | FIREBIRD = 7 | MSSQLSERVER_DYNAMIC = 8 | MSSQLSERVER_SSDT = 9 | DUCKDB = 10
Common.DatabaseProviderTypes.SQLITE: Common.DatabaseProviderTypes = 1
[<Struct>] type SQLiteLibrary = | SystemDataSQLite = 0 | MonoDataSQLite = 1 | AutoSelect = 2 | MicrosoftDataSqlite = 3
Common.SQLiteLibrary.SystemDataSQLite: Common.SQLiteLibrary = 0
[<Struct>] type CaseSensitivityChange = | ORIGINAL = 0 | TOUPPER = 1 | TOLOWER = 2
Common.CaseSensitivityChange.ORIGINAL: Common.CaseSensitivityChange = 0
val assert_equal: x: 'a -> y: 'b -> unit
val x: 'a
val y: 'b
val assert_contains: x: 'a -> y: 'b -> unit
module QueryEvents from FSharp.Data.Sql.Common
val SqlQueryEvent: IEvent<Common.QueryEvents.SqlEventData>
<summary> This event fires immediately before the execution of every generated query. Listen to this event to display or debug the content of your queries. </summary>
Multiple items
module Event from Microsoft.FSharp.Control

type Event<'T> = new: unit -> Event<'T> member Trigger: arg: 'T -> unit member Publish: IEvent<'T>

type Event<'Delegate,'Args (requires delegate and 'Delegate :> Delegate and reference type)> = new: unit -> Event<'Delegate,'Args> member Trigger: sender: obj * args: 'Args -> unit member Publish: IEvent<'Delegate,'Args>

new: unit -> Event<'T>

new: unit -> Event<'Delegate,'Args>
val add: callback: ('T -> unit) -> sourceEvent: IEvent<'Del,'T> -> unit (requires delegate and 'Del :> System.Delegate)
val e: Common.QueryEvents.SqlEventData
namespace System
type Console = static member Beep: unit -> unit + 1 overload static member Clear: unit -> unit static member GetCursorPosition: unit -> struct (int * int) static member MoveBufferArea: sourceLeft: int * sourceTop: int * sourceWidth: int * sourceHeight: int * targetLeft: int * targetTop: int -> unit + 1 overload static member OpenStandardError: unit -> Stream + 1 overload static member OpenStandardInput: unit -> Stream + 1 overload static member OpenStandardOutput: unit -> Stream + 1 overload static member Read: unit -> int static member ReadKey: unit -> ConsoleKeyInfo + 1 overload static member ReadLine: unit -> string ...
<summary>Represents the standard input, output, and error streams for console applications. This class cannot be inherited.</summary>
System.Console.WriteLine() : unit
   (+0 other overloads)
System.Console.WriteLine(value: uint64) : unit
   (+0 other overloads)
System.Console.WriteLine(value: uint32) : unit
   (+0 other overloads)
System.Console.WriteLine(value: string) : unit
   (+0 other overloads)
System.Console.WriteLine(value: float32) : unit
   (+0 other overloads)
System.Console.WriteLine(value: obj) : unit
   (+0 other overloads)
System.Console.WriteLine(value: int64) : unit
   (+0 other overloads)
System.Console.WriteLine(value: int) : unit
   (+0 other overloads)
System.Console.WriteLine(value: float) : unit
   (+0 other overloads)
System.Console.WriteLine(value: decimal) : unit
   (+0 other overloads)
member Common.QueryEvents.SqlEventData.ToRawSqlWithParamInfo: unit -> string
type OrderDateFilter = | OrderDate | ShippedDate | Either
val someProductionFunction: ctx: SqlDataProvider<...>.dataContext -> orderType: OrderDateFilter -> untilDate: DateTime -> Threading.Tasks.Task<(string * decimal) array>
val ctx: SqlDataProvider<...>.dataContext
type dataContext = member ClearUpdates: unit -> List<SqlEntity> member CreateConnection: unit -> IDbConnection member GetUpdates: unit -> List<SqlEntity> member SaveContextSchema: unit -> SaveContextResponse member SubmitUpdates: unit -> Unit member SubmitUpdatesAsync: unit -> Task member ``Design Time Commands`` : DesignTimeCommands member Main: mainSchema member Pragma: Pragma nested type DesignTimeCommands ...
val orderType: OrderDateFilter
val untilDate: DateTime
Multiple items
[<Struct>] type DateTime = new: year: int * month: int * day: int -> unit + 10 overloads member Add: value: TimeSpan -> DateTime member AddDays: value: float -> DateTime member AddHours: value: float -> DateTime member AddMilliseconds: value: float -> DateTime member AddMinutes: value: float -> DateTime member AddMonths: months: int -> DateTime member AddSeconds: value: float -> DateTime member AddTicks: value: int64 -> DateTime member AddYears: value: int -> DateTime ...
<summary>Represents an instant in time, typically expressed as a date and time of day.</summary>

DateTime ()
   (+0 other overloads)
DateTime(ticks: int64) : DateTime
   (+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, calendar: Globalization.Calendar) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: DateTimeKind) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: Globalization.Calendar) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: DateTimeKind) : DateTime
   (+0 other overloads)
val task: TaskBuilder
val ignoreOrderDate: bool
val ignoreShippedDate: bool
union case OrderDateFilter.OrderDate: OrderDateFilter
union case OrderDateFilter.ShippedDate: OrderDateFilter
union case OrderDateFilter.Either: OrderDateFilter
val tomorrow: DateTime
DateTime.AddDays(value: float) : DateTime
union case CanonicalOp.Date: CanonicalOp
val someLegacyCondition: int
val itms: Linq.IQueryable<string * decimal>
val query: Linq.QueryBuilder
val order: SqlDataProvider<...>.dataContext.main.OrdersEntity
property SqlDataProvider<...>.dataContext.Main: SqlDataProvider<...>.dataContext.mainSchema with get
property SqlDataProvider<...>.dataContext.mainSchema.Orders: SqlDataProvider<...>.dataContext.mainSchema.main.Orders with get
<summary> The table Orders belonging to schema main</summary>
custom operation: join var in collection on (outerKey = innerKey). Note that parentheses are required after 'on' Calls Linq.QueryBuilder.Join
val cust: SqlDataProvider<...>.dataContext.main.CustomersEntity
property SqlDataProvider<...>.dataContext.mainSchema.Customers: SqlDataProvider<...>.dataContext.mainSchema.main.Customers with get
<summary> The table Customers belonging to schema main</summary>
property SqlDataProvider<...>.dataContext.main.CustomersEntity.CustomerId: string with get, set
<summary>CustomerID: nchar(5)</summary>
custom operation: where (bool) Calls Linq.QueryBuilder.Where
property SqlDataProvider<...>.dataContext.main.CustomersEntity.City: string with get, set
<summary>City: nvarchar(15)</summary>
property SqlDataProvider<...>.dataContext.main.OrdersEntity.OrderDate: DateTime with get, set
<summary>OrderDate: datetime</summary>
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShippedDate: DateTime with get, set
<summary>ShippedDate: datetime</summary>
property SqlDataProvider<...>.dataContext.main.OrdersEntity.Freight: decimal with get, set
<summary>Freight: money</summary>
custom operation: select ('Result) Calls Linq.QueryBuilder.Select
property SqlDataProvider<...>.dataContext.main.CustomersEntity.PostalCode: string with get, set
<summary>PostalCode: nvarchar(10)</summary>
val res: (string * decimal) array
type Array = interface ICollection interface IEnumerable interface IList interface IStructuralComparable interface IStructuralEquatable interface ICloneable member Clone: unit -> obj member CopyTo: array: Array * index: int -> unit + 1 overload member GetEnumerator: unit -> IEnumerator member GetLength: dimension: int -> int ...
<summary>Provides methods for creating, manipulating, searching, and sorting arrays, thereby serving as the base class for all arrays in the common language runtime.</summary>
val executeQueryAsync: query: Linq.IQueryable<'a> -> Threading.Tasks.Task<'a array>
<summary> Execute SQLProvider query and release the OS thread while query is being executed. </summary>
val sampleDataMap: Map<string,obj>
type obj = Object
Multiple items
module Map from Microsoft.FSharp.Collections

type Map<'Key,'Value (requires comparison)> = interface IReadOnlyDictionary<'Key,'Value> interface IReadOnlyCollection<KeyValuePair<'Key,'Value>> interface IEnumerable interface IStructuralEquatable interface IComparable interface IEnumerable<KeyValuePair<'Key,'Value>> interface ICollection<KeyValuePair<'Key,'Value>> interface IDictionary<'Key,'Value> new: elements: ('Key * 'Value) seq -> Map<'Key,'Value> member Add: key: 'Key * value: 'Value -> Map<'Key,'Value> ...

new: elements: ('Key * 'Value) seq -> Map<'Key,'Value>
val ofList: elements: ('Key * 'T) list -> Map<'Key,'T> (requires comparison)
val mockContext: SqlDataProvider<...>.dataContext
module OfflineTools from FSharp.Data.Sql.Common
val CreateMockSqlDataContext: dummydata: Map<string,obj> -> 'T
<summary> This can be used for testing. Creates fake DB-context entities.. Example: FSharp.Data.Sql.Common.OfflineTools.CreateMockSqlDataContext ["schema.MyTable1"; [| {| MyColumn1 = "a"; MyColumn2 = 0 |} |] :&gt; obj] |&gt; Map.ofList See project unit-test for more examples. NOTE: Case-sensitivity. Tables and columns are DB-names, not Linq-names. Limitation of mockContext: You cannot Create new entities to the mock context. </summary>
property Array.Length: int with get
<summary>Gets the total number of elements in all the dimensions of the <see cref="T:System.Array" />.</summary>
<exception cref="T:System.OverflowException">The array is multidimensional and contains more than <see cref="F:System.Int32.MaxValue" /> elements.</exception>
<returns>The total number of elements in all the dimensions of the <see cref="T:System.Array" />; zero if there are no elements in the array.</returns>

Type something to start searching.