SQLProvider

MySQL Provider

Parameters

ConnectionString

Basic connection string used to connect to MySQL instance; typical connection string parameters apply here. See MySQL Connector/NET Connection Strings Documentation for a complete list of connection string options.

[<Literal>]
let connString  = "Server=localhost;Database=HR;User=root;Password=password"

To deal with some MySQL data connection problems you might want to add some more parameters to connectionstring: Auto Enlist=false; Convert Zero Datetime=true;

ConnectionStringName

Instead of storing the connection string in the source code, you can store it in the App.config file. This is the name of the connectionString key/value pair stored in App.config (TODO: confirm filename).

// found in App.config (TODO: confirm)
let connexStringName = "DefaultConnectionString"

Database Vendor

Use MYSQL from the FSharp.Data.Sql.Common.DatabaseProviderTypes enumeration.

[<Literal>]
let dbVendor    = Common.DatabaseProviderTypes.MYSQL

Resolution Path

Path to search for assemblies containing database vendor specific connections and custom types. Type the path where Mysql.Data.dll is stored. Both absolute and relative paths are supported.

[<Literal>]
let resPath = __SOURCE_DIRECTORY__ + @"/../../../packages/tests/MySql.Data/lib/net45"

Individuals Amount

Sets the count of records to load for each table. See individuals for further info.

[<Literal>]
let indivAmount = 1000

Use Option Types

If true, F# option types will be used in place of nullable database columns. If false, you will always receive the default value of the column's type even if it is null in the database.

[<Literal>]
let useOptTypes = FSharp.Data.Sql.Common.NullableColumnType.OPTION

Example

type sql = SqlDataProvider<
                dbVendor,
                connString,
                ResolutionPath = resPath,
                IndividualsAmount = indivAmount,
                UseOptionTypes = useOptTypes,
                Owner = "HR"
            >
let ctx = sql.GetDataContext()

let employees = 
    ctx.Hr.Employees 
    |> Seq.map (fun e -> e.ColumnValues |> Seq.toList)
    |> Seq.toList

Working with Type-mappings

Basic types

MySql.Data types are not always the ones you have used to in .NET, so here is a little help:

let myEmp = 
    query {
        for jh in ctx.Hr.JobHistory do
        where (jh.Years > 10u)
        select (jh)
    } |> Seq.head

let myUint32 = 10u
let myInt64 = 10L
let myUInt64 = 10UL

System.Guid Serialization

If you use string column to save a Guid to database, you may want to skip the hyphens ("-") when serializing them:

let myGuid = System.Guid.NewGuid() //e.g. b8fa7880-ce44-4315-8d60-a160e5734c4b

let myGuidAsString = myGuid.ToString("N") // e.g. "b8fa7880ce4443158d60a160e5734c4b"

The problem with this is that you should never forgot to use "N" in anywhere.

System.DateTime Serialization

Another problem with MySql.Data is that DateTime conversions may fail if your culture is not the expected one.

So you may have to convert datetimes as strings instead of using just myEmp.BirthDate <- DateTime.UtcNow:

myEmp.SetColumn("BirthDate", DateTime.UtcNow.ToString("yyyy-MM-dd HH\:mm\:ss") |> box)

Notice that if you use .ToString("s") there will be "T" between date and time: "yyyy-MM-ddTHH\:mm\:ss". And comparing two datetimes as strings with "T" and without "T" will generate a problem with the time-part.

If your DateTime columns are strings in the database, you can use DateTime.Parse in your where-queries:

let longAgo = DateTime.UtcNow.AddYears(-5)
let myEmp = 
    query {
        for emp in ctx.Hr.Employees do
        where (DateTime.Parse(emp.HireDate) > longAgo)
        select (emp)
    } |> Seq.head

You should be fine even with canonical functions like DateTime.Parse(a.MeetStartTime).AddMinutes(10.).

Caveats / Additional Info

Check General, Static Parameters and Querying documentation.

Support for MySqlConnector

MySqlConnector is alternative driver to use instead of MySql.Data.dll. It has less features but a lot better performance than the official driver.

You can use it with SQLProvider: Just remove MySql.Data.dll from your resolutionPath and insert there MySqlConnector.dll instead. (Get the latest from NuGet.) It uses references to System.Buffers.dll, System.Runtime.InteropServices.RuntimeInformation.dll and System.Threading.Tasks.Extensions.dll so copy those files also to your referencePath. You can get them from corresponding NuGet packages.

If you want to use the drivers in parallel, you need two resolution paths:

type HRFast = SqlDataProvider<Common.DatabaseProviderTypes.MYSQL, connString, ResolutionPath = @"c:\mysqlConnectorPath", Owner = "HR">
type HRProcs = SqlDataProvider<Common.DatabaseProviderTypes.MYSQL, connString, ResolutionPath = @"c:\MysqlDataPath", Owner = "HR">

Example performance difference from our unit tests

One complex query:

MySql.Data.dll:     Real: 00:00:00.583, CPU: 00:00:00.484, GC gen0: 1, gen1: 0, gen2: 0
MySqlConnector.dll: Real: 00:00:00.173, CPU: 00:00:00.093, GC gen0: 1, gen1: 0, gen2: 0

Lot of async queries:

MySQL.Data.dll      Real: 00:00:01.425, CPU: 00:00:02.078, GC gen0: 16, gen1: 1, gen2: 0
MySqlConnector.dll: Real: 00:00:01.091, CPU: 00:00:02.000, GC gen0: 14, gen1: 1, gen2: 0
Multiple items
namespace FSharp

--------------------
namespace Microsoft.FSharp
Multiple items
namespace FSharp.Data

--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
namespace System
Multiple items
type LiteralAttribute = inherit Attribute new: unit -> LiteralAttribute

--------------------
new: unit -> LiteralAttribute
[<Literal>] val connString: string = "Server=localhost;Database=HR;User=root;Password=password"
val connexStringName: string
[<Literal>] val dbVendor: Common.DatabaseProviderTypes = 3
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
Common.DatabaseProviderTypes.MYSQL: Common.DatabaseProviderTypes = 3
[<Literal>] val resPath: string = "C:\git\SQLProvider\docs\content\core/../../../packages/tests/MySql.Data/lib/net45"
[<Literal>] val indivAmount: int = 1000
[<Literal>] val useOptTypes: Common.NullableColumnType = 1
[<Struct>] type NullableColumnType = | NO_OPTION = 0 | OPTION = 1 | VALUE_OPTION = 2
Common.NullableColumnType.OPTION: Common.NullableColumnType = 1
<summary> Option types are Option&lt;_&gt;. (Old true.) </summary>
type sql = obj
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>
val ctx: obj
val employees: obj list list
Multiple items
module Seq from FSharp.Data.Sql

--------------------
module Seq from Microsoft.FSharp.Collections
val map: mapping: ('T -> 'U) -> source: 'T seq -> 'U seq
val e: obj
val toList: source: 'T seq -> 'T list
val myEmp: obj
val query: Linq.QueryBuilder
val jh: obj
custom operation: where (bool) Calls Linq.QueryBuilder.Where
custom operation: select ('Result) Calls Linq.QueryBuilder.Select
val head: source: 'T seq -> 'T
val myUint32: uint32
val myInt64: int64
val myUInt64: uint64
val myGuid: Guid
Multiple items
[<Struct>] type Guid = new: b: byte array -> unit + 5 overloads member CompareTo: value: Guid -> int + 1 overload member Equals: g: Guid -> bool + 1 overload member GetHashCode: unit -> int member ToByteArray: unit -> byte array member ToString: unit -> string + 2 overloads member TryFormat: destination: Span<char> * charsWritten: byref<int> * ?format: ReadOnlySpan<char> -> bool member TryWriteBytes: destination: Span<byte> -> bool static member (<>) : a: Guid * b: Guid -> bool static member (=) : a: Guid * b: Guid -> bool ...
<summary>Represents a globally unique identifier (GUID).</summary>

--------------------
Guid ()
Guid(b: byte array) : Guid
Guid(b: ReadOnlySpan<byte>) : Guid
Guid(g: string) : Guid
Guid(a: int, b: int16, c: int16, d: byte array) : Guid
Guid(a: int, b: int16, c: int16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : Guid
Guid(a: uint32, b: uint16, c: uint16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : Guid
Guid.NewGuid() : Guid
val myGuidAsString: string
Guid.ToString() : string
Guid.ToString(format: string) : string
Guid.ToString(format: string, provider: IFormatProvider) : string
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)
property DateTime.UtcNow: DateTime with get
<summary>Gets a <see cref="T:System.DateTime" /> object that is set to the current date and time on this computer, expressed as the Coordinated Universal Time (UTC).</summary>
<returns>An object whose value is the current UTC date and time.</returns>
DateTime.ToString() : string
DateTime.ToString(format: string) : string
DateTime.ToString(provider: IFormatProvider) : string
DateTime.ToString(format: string, provider: IFormatProvider) : string
val box: value: 'T -> obj
val longAgo: obj
val emp: obj
module Seq from Microsoft.FSharp.Collections
type HRFast = obj
type HRProcs = obj

Type something to start searching.