Example of using SAFE with Npgsql.FSharp

Server.fs

module Server

open Fable.Remoting.Server
open Fable.Remoting.Giraffe
open Saturn
open System

open Shared

open Npgsql.FSharp.Tasks
open Npgsql

let rnd = System.Random()

let ranStr n =
    let r = Random()
    let chars = Array.concat([[|'a' .. 'z'|];[|'A' .. 'Z'|]])
    let sz = Array.length chars in
    String(Array.init n (fun _ -> chars.[r.Next sz]))


// Postgres
type DbNpgsqlStorage() =
    // Define the connections string using the connection string builder API
    let connectionString : string =
        Sql.host "localhost"
        |> Sql.database "postgres"
        |> Sql.username "postgres"
        |> Sql.password "postgres"
        |> Sql.port 5432
        |> Sql.formatConnectionString

    member __.GetMarkets () : Market list =        
        connectionString
        |> Sql.connect
        |> Sql.query "SELECT Id, Pair, Price, Quantity FROM market"
        |> Sql.execute (fun read ->
        {
            Id = read.int "Id"
            Pair = read.text "Pair"
            Price = read.double "Price"
            Quantity = read.int "Quantity"
        })

    member __.GetCountMarkets() : int =
        connectionString
        |> Sql.connect
        |> Sql.query "SELECT COUNT(*) as count_markets FROM market"
        |> Sql.executeRow(fun read -> read.int "count_markets")

    member __.AddMarket(market: Market) =
        if Market.isValidPair market.Pair then
            try
                connectionString
                |> Sql.connect
                |> Sql.executeTransaction
                    [                        
                        "INSERT INTO MARKET(Pair,Price,Quantity) VALUES (@pair,@price,@qty)", [
                            [                            
                              ("@pair", Sql.text market.Pair);
                              ("@price", Sql.double market.Price);
                              ("@qty", Sql.int market.Quantity)
                            ]
                        ]
                    ]
                |> ignore
                Ok ()
            with ex ->
                Error "Error on inserting"
        else Error "Invalid Market"

     member __.GenerateRandomRows() =
        for i in 1 .. 10 do
            let pair = ranStr 7
            let qty = rnd.Next()
            let price = rnd.NextDouble()
            let date = DateTime.Now

            let result =
                connectionString
                |> Sql.connect                
                |> Sql.query "INSERT INTO MARKET(Pair,Price,Quantity) VALUES (@pair,@price,@qty)"
                |> Sql.parameters [                            
                              ("@pair", Sql.text pair);
                              ("@price", Sql.double price);
                              ("@qty", Sql.int qty)
                            ]
                |> Sql.executeNonQuery

            printfn "%A" result

let dbNpgsqlStorage = DbNpgsqlStorage()

if dbNpgsqlStorage.GetCountMarkets() = 0 then
    dbNpgsqlStorage.GenerateRandomRows()

// API interface
let marketApi =
    {
        getCount = fun() -> async { return dbNpgsqlStorage.GetCountMarkets() }
        getAll = fun() -> async { return dbNpgsqlStorage.GetMarkets() }
    }

// We can split the build creation to use multiple Protocols
let buildApi() =
    Remoting.createApi()
    |> Remoting.withRouteBuilder Route.builder
    |> Remoting.withDiagnosticsLogger (printfn "%s")

let marketWebApp =
    buildApi()
    |> Remoting.fromValue marketApi
    |> Remoting.buildHttpHandler

let app =
    application {
        url "http://0.0.0.0:8085"
        use_router marketWebApp
        memory_cache
        use_static "public"
        use_gzip
    }

run app

Shared.fs

namespace Shared

open System

module Route =
    (*
        This is a custom route path using the prefix with /api/
    *)
    let builder typeName methodName =
        sprintf "/api/%s/%s" typeName methodName
        
[<CLIMutable>]
type Market = {
    Id : int
    Pair : string
    Quantity : int
    Price : float    
}

module Market =
    let isValidPair (pair: string) =
        String.IsNullOrWhiteSpace pair |> not

// this is the specification of the protocol between our server and client
// that is, our API
 type IMarketApi =
    {
        // All the functions we define in this interface have to return an async
        
        // Count the number of records in the database
        getCount: unit -> Async<int>
        
        // getAll takes no arguments and return a list of Market
        getAll : unit -> Async<Market list>       
    }