SQL Provider

There are many ways to talk with a database from .NET code including ADO.NET, light-weight libraries like Dapper, ORMs like Entity Framework or NHibernate. To have more fun, we'll do something completely different, namely try an awesome F# feature called Type Providers. In short, Type Providers allows to automatically generate a set of types based on some type of schema. To learn more about Type Providers, check out this resource.

SQLProvider is example of a Type Provider library, which gives ability to cooperate with a relational database. Because we'll be using Postgres, we'll also need a .NET driver to Postgres - Npgsql. We can install both SQLProvider and Npgsql with Paket:

nuget SQLProvider 1.1.8
nuget Npgsql 3.1.9

Note: Once again, we pin versions so that this tutorial doesn't get outdated with newer versions of packages.

Don't forget to both packages to paket.references as well as running paket install!

One more thing before we go further: Because we're using the new .NET SDK to build the project, and Type Providers are not yet fully supported for .NET SDK based project, we'll have to apply a special workaround for SQLProvider to work. For more details reach out to this resource.

What we need to do is basically add a special instruction for .NET SDK build to use standard .NET / Mono F# compiler, and then we're fine. Add following XML chunk under Project node in the .fsproj:

  <PropertyGroup>
    <IsWindows Condition="'$(OS)' == 'Windows_NT'">true</IsWindows>
    <IsOSX Condition="'$([System.Runtime.InteropServices.RuntimeInformation]::IsOSPlatform($([System.Runtime.InteropServices.OSPlatform]::OSX)))' == 'true'">true</IsOSX>
    <IsLinux Condition="'$([System.Runtime.InteropServices.RuntimeInformation]::IsOSPlatform($([System.Runtime.InteropServices.OSPlatform]::Linux)))' == 'true'">true</IsLinux>
  </PropertyGroup>  
  <PropertyGroup Condition="'$(IsWindows)' == 'true'">
    <FscToolPath>C:\Program Files (x86)\Microsoft SDKs\F#\4.1\Framework\v4.0</FscToolPath>
    <FscToolExe>fsc.exe</FscToolExe>
  </PropertyGroup>
  <PropertyGroup Condition="'$(IsOSX)' == 'true'">
    <FscToolPath>/Library/Frameworks/Mono.framework/Versions/Current/Commands</FscToolPath>
    <FscToolExe>fsharpc</FscToolExe>
  </PropertyGroup>
  <PropertyGroup Condition="'$(IsLinux)' == 'true'">
    <FscToolPath>/usr/bin</FscToolPath>
    <FscToolExe>fsharpc</FscToolExe>
  </PropertyGroup>

Having installed the SQLProvider, let's add Db.fs file to the beginning of our project - before any other *.fs file.

In the newly created file, first open FSharp.Data.Sql module:

Db.fs

   1: 
   2: 
   3: 

module SuaveMusicStore.Db

open FSharp.Data.Sql

Next, comes the most interesting part:

Db.fs

   5: 
   6: 
   7: 
   8: 
   9: 
  10: 
  11: 
  12: 
  13: 
  14: 
  15: 
  16: 

[<Literal>]
let ConnectionString = 
    "Server=192.168.99.100;"    + 
    "Database=suavemusicstore;" + 
    "User Id=suave;"            + 
    "Password=1234;"

type Sql = 
    SqlDataProvider< 
        ConnectionString      = ConnectionString,
        DatabaseVendor        = Common.DatabaseProviderTypes.POSTGRESQL,
        CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL >

You'll need to adjust the above connection string, so that it can access the SuaveMusicStore database. At least you need to make sure that the server instance part is correct - verify that the IP reflects you docker host machine:

  • if you're running Docker natively use loopback IP address 127.0.0.1
  • if you're running Docker using Docker Toolbox (Win, Mac), check IP of the docker host with docker-machine ip <docker VM name>

After the SQLProvider can access the database, it will generate a set of types in background - each for single database table, as well as each for single database view. This might be similar to how Entity Framework generates models for your tables, except there's no explicit code generation involved - all of the types reside under the Sql type defined.

Note: If using Visual Studio Code with Ionide, you might need to trigger Reload Window command before Type Provider can succesfully read from the DB.

The generated types have a bit cumbersome names, but we can define type aliases to keep things simpler:

Db.fs

  18: 
  19: 
  20: 
  21: 

type DbContext = Sql.dataContext
type Album = DbContext.``public.albumsEntity``
type Genre = DbContext.``public.genresEntity``
type AlbumDetails = DbContext.``public.albumdetailsEntity``
  • DbContext is our data context,
  • Album and Genre reflect database tables,
  • AlbumDetails reflects database view - it will prove useful when we'll need to display names for the album's genre and artist.
    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

    Full name: Microsoft.FSharp.Core.LiteralAttribute

    --------------------
    new : unit -> LiteralAttribute
    val ConnectionString : string

    Full name: SuaveMusicStore.Db.ConnectionString
    type Sql = SqlDataProvider<...>

    Full name: SuaveMusicStore.Db.Sql
    type SqlDataProvider

    Full name: FSharp.Data.Sql.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 1000.</param>
    <param name='UseOptionTypes'>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.</param>
    <param name='ResolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types.</param>
    <param name='Owner'>The owner of the schema for this provider to resolve (Oracle Only)</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='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>
    namespace FSharp.Data.Sql.Common
    type DatabaseProviderTypes =
    | MSSQLSERVER = 0
    | SQLITE = 1
    | POSTGRESQL = 2
    | MYSQL = 3
    | ORACLE = 4
    | MSACCESS = 5
    | ODBC = 6
    | FIREBIRD = 7

    Full name: FSharp.Data.Sql.Common.DatabaseProviderTypes
    Common.DatabaseProviderTypes.POSTGRESQL: Common.DatabaseProviderTypes = 2
    type CaseSensitivityChange =
    | ORIGINAL = 0
    | TOUPPER = 1
    | TOLOWER = 2

    Full name: FSharp.Data.Sql.Common.CaseSensitivityChange
    Common.CaseSensitivityChange.ORIGINAL: Common.CaseSensitivityChange = 0
    type DbContext = SqlDataProvider<...>.dataContext

    Full name: SuaveMusicStore.Db.DbContext
    type dataContext =
    member ClearUpdates : unit -> List<SqlEntity>
    member CreateConnection : unit -> IDbConnection
    member GetUpdates : unit -> List<SqlEntity>
    member Public : publicSchema
    member SubmitUpdates : unit -> Unit
    member SubmitUpdatesAsync : unit -> Async<Unit>
    nested type public.albumdetails.Individuals
    nested type public.albumdetailsEntity
    nested type public.albums.Individuals
    nested type public.albumsEntity
    ...

    Full name: FSharp.Data.Sql.SqlDataProvider,DatabaseVendor="2",ConnectionString="Server=192.168.99.100;Database=suavemusicstore;User Id=suave;Password=1234;",CaseSensitivityChange="0".dataContext
    type Album = SqlDataProvider<...>.dataContext.public.albumsEntity

    Full name: SuaveMusicStore.Db.Album
    type Genre = SqlDataProvider<...>.dataContext.public.genresEntity

    Full name: SuaveMusicStore.Db.Genre
    type AlbumDetails = SqlDataProvider<...>.dataContext.public.albumdetailsEntity

    Full name: SuaveMusicStore.Db.AlbumDetails
    type IntPath = PrintfFormat<(int -> string),unit,string,string,int>

    Full name: SuaveMusicStore.Path.IntPath
    Multiple items
    type PrintfFormat<'Printer,'State,'Residue,'Result> =
    new : value:string -> PrintfFormat<'Printer,'State,'Residue,'Result>
    member Value : string

    Full name: Microsoft.FSharp.Core.PrintfFormat<_,_,_,_>

    --------------------
    type PrintfFormat<'Printer,'State,'Residue,'Result,'Tuple> =
    inherit PrintfFormat<'Printer,'State,'Residue,'Result>
    new : value:string -> PrintfFormat<'Printer,'State,'Residue,'Result,'Tuple>

    Full name: Microsoft.FSharp.Core.PrintfFormat<_,_,_,_,_>

    --------------------
    new : value:string -> PrintfFormat<'Printer,'State,'Residue,'Result>

    --------------------
    new : value:string -> PrintfFormat<'Printer,'State,'Residue,'Result,'Tuple>
    Multiple items
    val int : value:'T -> int (requires member op_Explicit)

    Full name: Microsoft.FSharp.Core.Operators.int

    --------------------
    type int = int32

    Full name: Microsoft.FSharp.Core.int

    --------------------
    type int<'Measure> = int

    Full name: Microsoft.FSharp.Core.int<_>
    Multiple items
    val string : value:'T -> string

    Full name: Microsoft.FSharp.Core.Operators.string

    --------------------
    type string = System.String

    Full name: Microsoft.FSharp.Core.string
    type unit = Unit

    Full name: Microsoft.FSharp.Core.unit
    val withParam : key:string * value:string -> path:string -> string

    Full name: SuaveMusicStore.Path.withParam
    val key : string
    val value : string
    val path : string
    val sprintf : format:Printf.StringFormat<'T> -> 'T

    Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.sprintf
    val home : string

    Full name: SuaveMusicStore.Path.home
    module Store

    from SuaveMusicStore.Path
    val overview : string

    Full name: SuaveMusicStore.Path.Store.overview
    val browse : string

    Full name: SuaveMusicStore.Path.Store.browse
    val details : IntPath

    Full name: SuaveMusicStore.Path.Store.details
    val browseKey : string

    Full name: SuaveMusicStore.Path.Store.browseKey
    namespace Suave
    module Html

    from Suave
    val cssLink : href:string -> Node

    Full name: SuaveMusicStore.View.cssLink
    val href : string
    val link : attr:Attribute list -> Node

    Full name: Suave.Html.link
    val h2 : s:string -> Node

    Full name: SuaveMusicStore.View.h2
    val s : string
    val tag : tag:string -> attr:Attribute list -> contents:Node list -> Node

    Full name: Suave.Html.tag
    union case Node.Text: string -> Node
    val ul : nodes:Node list -> Node

    Full name: SuaveMusicStore.View.ul
    val nodes : Node list
    val li : (Node list -> Node)

    Full name: SuaveMusicStore.View.li
    val home : Node list

    Full name: SuaveMusicStore.View.home
    val store : genres:string list -> Node list

    Full name: SuaveMusicStore.View.store
    val genres : string list
    val p : (Attribute list -> Node list -> Node)

    Full name: Suave.Html.p
    Multiple items
    module List

    from Microsoft.FSharp.Collections

    --------------------
    type List<'T> =
    | ( [] )
    | ( :: ) of Head: 'T * Tail: 'T list
    interface IEnumerable
    interface IEnumerable<'T>
    member GetSlice : startIndex:int option * endIndex:int option -> 'T list
    member Head : 'T
    member IsEmpty : bool
    member Item : index:int -> 'T with get
    member Length : int
    member Tail : 'T list
    static member Cons : head:'T * tail:'T list -> 'T list
    static member Empty : 'T list

    Full name: Microsoft.FSharp.Collections.List<_>
    val length : list:'T list -> int

    Full name: Microsoft.FSharp.Collections.List.length
    val genre : string
    val url : string
    module Path

    from SuaveMusicStore
    val a : href:string -> attr:Attribute list -> (Node list -> Node)

    Full name: Suave.Html.a
    val browse : genre:string -> Node list

    Full name: SuaveMusicStore.View.browse
    val details : id:int -> Node list

    Full name: SuaveMusicStore.View.details
    val id : int
    val index : container:Node list -> string

    Full name: SuaveMusicStore.View.index
    val container : Node list
    val html : (Attribute list -> Node list -> Node)

    Full name: Suave.Html.html
    val head : (Attribute list -> Node list -> Node)

    Full name: Suave.Html.head
    val title : attr:Attribute list -> s:string -> Node

    Full name: Suave.Html.title
    val body : (Attribute list -> Node list -> Node)

    Full name: Suave.Html.body
    val div : (Attribute list -> Node list -> Node)

    Full name: Suave.Html.div
    val htmlToString : node:Node -> string

    Full name: Suave.Html.htmlToString
    module App

    from SuaveMusicStore
    module Filters

    from Suave
    module Operators

    from Suave
    module RequestErrors

    from Suave
    module Successful

    from Suave
    module Web

    from Suave
    val html : container:Html.Node list -> WebPart

    Full name: SuaveMusicStore.App.html
    val container : Html.Node list
    val OK : body:string -> WebPart

    Full name: Suave.Successful.OK
    module View

    from SuaveMusicStore
    val index : container:Html.Node list -> string

    Full name: SuaveMusicStore.View.index
    val browse : (HttpContext -> Async<HttpContext option>)

    Full name: SuaveMusicStore.App.browse
    val request : apply:(HttpRequest -> HttpContext -> 'a) -> context:HttpContext -> 'a

    Full name: Suave.Http.request
    val r : HttpRequest
    member HttpRequest.queryParam : key:string -> Choice<string,string>
    union case Choice.Choice1Of2: 'T1 -> Choice<'T1,'T2>
    val browse : genre:string -> Html.Node list

    Full name: SuaveMusicStore.View.browse
    union case Choice.Choice2Of2: 'T2 -> Choice<'T1,'T2>
    val msg : string
    val BAD_REQUEST : body:string -> WebPart

    Full name: Suave.RequestErrors.BAD_REQUEST
    val webPart : WebPart<HttpContext>

    Full name: SuaveMusicStore.App.webPart
    val choose : options:WebPart<'a> list -> WebPart<'a>

    Full name: Suave.WebPart.choose
    val path : pathAfterDomain:string -> WebPart

    Full name: Suave.Filters.path
    val home : Html.Node list

    Full name: SuaveMusicStore.View.home
    val store : genres:string list -> Html.Node list

    Full name: SuaveMusicStore.View.store
    val pathScan : pf:PrintfFormat<'a,'b,'c,'d,'t> -> h:('t -> WebPart) -> WebPart

    Full name: Suave.Filters.pathScan
    val details : Path.IntPath

    Full name: SuaveMusicStore.Path.Store.details
    val details : id:int -> Html.Node list

    Full name: SuaveMusicStore.View.details
    val pathRegex : pathAfterDomainRegex:string -> WebPart

    Full name: Suave.Filters.pathRegex
    module Files

    from Suave
    val browseHome : WebPart

    Full name: Suave.Files.browseHome
    val startWebServer : config:SuaveConfig -> webpart:WebPart -> unit

    Full name: Suave.Web.startWebServer
    val defaultConfig : SuaveConfig

    Full name: Suave.Web.defaultConfig

Show code from this section on GitHub

results matching ""

    No results matching ""