SQL Provider Static Parameters

Global parameters

These are the "common" parameters used by all SqlProviders.

All static parameters must be known at compile time, for strings this can be achieved by adding the [<Literal>] attribute if you are not passing it inline.


This is the connection string commonly used to connect to a database server instance. See the documentation on your desired database type to find out more.

let sqliteConnectionString =
    "Data Source=" + __SOURCE_DIRECTORY__ + @"\northwindEF.db;Version=3"


Instead of storing the connection string in the source code / fsx script, you can store values in the App.config file:

  <add name="MyConnectionString"   
   connectionString="Valid Connection String;" />  

Another, usually easier option is to give a runtime connection string as parameter for .GetDataContext(...) method.

In your source file:

let connexStringName = "MyConnectionString"


Select enumeration from Common.DatabaseProviderTypes to specify which database type the provider will be connecting to.

let dbVendor = Common.DatabaseProviderTypes.SQLITE


When using database vendors other than SQL Server, Access and ODBC, a third party driver is required. This parameter should point to an absolute or relative directory where the relevant assemblies are located. See the database vendor specific page for more details.

let resolutionPath =
    __SOURCE_DIRECTORY__ + @"..\..\..\files\sqlite"

The resolution path(s) (as can be semicolon separated if many) should point the database driver files and their reference assemblies) that work on design-time. So depending on your IDE you probably want there .NET Standard 2.0 (or 2.1) versions and not the latest .NET runtime, even when you target your final product to latest .NET.

Note on .NET 5 PublishSingleFile and ResolutionPath

If you are publishing your app using .NET 5's PublishSingleFile mode, the driver will be loaded from the bundle itself rather than from a separate file on the drive. As such, the ResolutionPath parameter will not work for the published app, nor will the automatic assembly resolution implemented within SQLProvider.

SQLProvider attempts to load the assembly from the AppDomain in such case. This means that your driver's assembly must be loaded by your application for SQLProvider to find it. To do so, simply use the types of your driver before calling the .GetDataContext(...) method, such as in this example, using MySqlConnector. The specific type you refer to does not matter.

typeof<MySqlConnector.Logging.MySqlConnectorLogLevel>.Assembly |> ignore
let ctx = sqlType.GetDataContext()


Number of instances to retrieve when using the individuals feature. Default is 1000.

let indivAmt = 500


If set to FSharp.Data.Sql.Common.NullableColumnType.OPTION, all nullable fields will be represented by F# option types. If NO_OPTION, nullable fields will be represented by the default value of the column type - this is important because the provider will return 0 instead of null, which might cause problems in some scenarios.

The third option is VALUE_OPTION where nullable fields are represented by ValueOption struct.

let useOptionTypes = FSharp.Data.Sql.Common.NullableColumnType.OPTION


Defining ContextSchemaPath and placing a file with schema information according to the definition enables offline mode that can be useful when the database is unavailable or slow to connect or access. Schema information file can be generated by calling design-time method SaveContextSchema under Design Time Commands:

ctx.``Design Time Commands``.SaveContextSchema

This method doesn't affect runtime execution. Note that since SQLProvider loads schema information lazily, calling SaveContextSchema only saves the portion of the database schema that is sufficient to compile queries referenced in the scope of the current solution or script. Therefore it is recommended to execute it after the successful build of the whole solution. Type the method name with parentheses, if you then type a dot (.), you should see a tooltip with information when the schema was last saved. Once the schema is saved, the outcome of the method execution is stored in memory, so the file will not be overwritten. In case the database schema changes and the schema file must be updated, remove the outdated file, reload the solution and retype or uncomment a call to SaveContextSchema to regenerate the schema file.

There is a tool method FSharp.Data.Sql.Common.OfflineTools.mergeCacheFiles to merge multiple files together.

let contextSchemaPath =
    __SOURCE_DIRECTORY__ + @".\sqlite.schema"

Platform Considerations


TableNames to filter amount of tables.


TableNames to filter amount of tables, and Owner.

Owner (Used by Oracle, MySQL and PostgreSQL)

This has different meanings when running queries against different database vendors

For PostgreSQL, this sets the schema name where the target tables belong to. Can be also a list separated by spaces, newlines, commas or semicolons.

For MySQL, this sets the database name (Or schema name, for MySQL, it's the same thing). Can be also a list separated by spaces, newlines, commas or semicolons.

For Oracle, this sets the owner of the scheme.


The additional SQLiteLibrary parameter can be used to specify which SQLite library to load.


No extra parameters.


No extra parameters.


No extra parameters.


It is recommended to use named static parameters in your type provider definition like so

type sql = SqlDataProvider<
            ConnectionString = sqliteConnectionString,
            DatabaseVendor = dbVendor,
            ResolutionPath = resolutionPath,
            UseOptionTypes = useOptionTypes

SQL Provider Data Context Parameters

Besides the static parameters the .GetDataContext(...) method has optional parameters:

Multiple items
namespace FSharp

namespace Microsoft.FSharp
Multiple items
namespace FSharp.Data

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

new: unit -> LiteralAttribute
[<Literal>] val connectionString: string = "Data Source=C:\git\SQLProvider\docs\content\core/../../../tests/SqlProvider.Tests/scripts/northwindEF.db;Version=3"
[<Literal>] val resolutionPath: string = "C:\git\SQLProvider\docs\content\core/../../../tests/SqlProvider.Tests/libs"
type sqlType = 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>
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
[<Literal>] val sqliteConnectionString: string = "Data Source=C:\git\SQLProvider\docs\content\core\northwindEF.db;Version=3"
val connexStringName: string
[<Literal>] val dbVendor: Common.DatabaseProviderTypes = 1
[<Literal>] val resolutionPath: string = "C:\git\SQLProvider\docs\content\core..\..\..\files\sqlite"
val typeof<'T> : System.Type
val ignore: value: 'T -> unit
val ctx: obj
val indivAmt: int
[<Literal>] val useOptionTypes: 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>
[<Literal>] val contextSchemaPath: string = "C:\git\SQLProvider\docs\content\core.\sqlite.schema"
type sql = obj

Type something to start searching.