Beginner's question about getting query lists

[jacksonpradolima]

Hi,
I am using Npgsql.FSharp to create an API using SAFE.

I was able to connect to the database and generate records randomly. However, I fail when I try to select all records from my table.

Consideing my "table" as this type:

type Market = {
    Id : int
    Pair : string
    Quantity : int
    Price : float
}

Then, I created another type to manage my database, as well as members. Similar to the example with LiteDB/default from SAFE. A fragment from code is as follow:

type DbNpgsqlStorage() =
    let connectionString : string =
        Sql.host "localhost"
        |> Sql.database "postgres"
        |> Sql.username "postgres"
        |> Sql.password "postgres"
        |> Sql.port 5432
        |> Sql.formatConnectionString

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

    member __.GetCountMarkets() =
        connectionString
        |> Sql.connect
        |> Sql.query "SELECT COUNT(*) as count_markets FROM market"
        |> Sql.executeRow(fun read -> read.int "count_markets")
        |> function
            | Ok count -> count
            | Error e -> 0

The question is: considering GetMarkets(), how can I get the list from the query? When I try to use ignore the result is a "unit". I already used match but without success.

Similar problem I had on GetCountMarkets(), then, I used a bad workaround.

Sorry, I am learning F# and SAFE this week. I have never seen them before. Consequently, I do not know how to deal with F# Railway properly.

Every help is welcome :D

[Zaid-Ajaj]

Hi there @jacksonpradolima, to simplify your code, please use the Npgsql.FSharp.Tasks namespace instead of Npgsql.FSharp because it simplifies the railway programming model. Instead of

open Npgsql.FSharp

write

open Npgsql.FSharp.Tasks

In the next version of the library v4.0 this will be the default.

Then you can change the GetMarkets() to

    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"
        })

and GetCountMarkets() to

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

Alternatively, you can use the async version of the functions

• Sql.execute -> Sql.executeAsync
• Sql.executeRow -> Sql.executeRowAsync

But for beginning it doesn't really matter that much and the synchronous version will be just fine

If you still like the F# railway approach, you can keep using the same namespace and return a Result type from your code as follows:

    member __.GetMarkets () : Result<Market list, string> =
        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"
        })
        |> function
             | Ok markers -> Ok markets
             | Error e -> Error "Database error occurred while retrieving the list of markets" 

So basically, you give textual error back to the client to show to your users.

I hope this helps. Let me know if you have any question 😄

[jacksonpradolima]

Hi @Zaid-Ajaj
Thanks for your help! (and patience)

The change works with GetCount() but with GetMarkers() I received this response from API: Error occured while running the function getAll. The code is the same as in your examples:

 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"
        })

When I change to executeAsync the error mention that Market list is different from 'Sql.SqlProps -> Threading.Tasks.Task<'a list>.

When I try to use Result, I receive that was expected Market list but returns Result<'a,'b> . The code below show how I used, because I try to return an empty list when a problem occurs.

    member __.GetMarkets () : Market list =
        let result =
            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"
            })

        match result with
        | Ok r -> r
        | Error e -> []

But even when I try to use your last example with Result, I received the same error: that was expected Market list but returns Result<'a,'b>:

member __.GetMarkets () : Result<Market list, string> =
        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"
        })
        |> function
             | Ok markers -> Ok markets
             | Error e -> Error "Database error occurred while retrieving the list of markets"

I am using:

• PostgreSQL v10
• .Net 5.0 SDK
• .Net Core 3
• NojeJS/NPM v14.16

My table on the database is simple:

CREATE TABLE MARKET(
    Id Integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Pair  VARCHAR(7) NOT NULL,
    Price NUMERIC(4) NOT NULL,
    Quantity INTEGER NOT NULL
);

What is my mistake?

[Zaid-Ajaj]

What is my mistake?

I can't tell just from the custom error message. Can you try printing your error message?

member __.GetMarkets () : Result<Market list, string> =
        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"
        })
        |> function
             | Ok markers -> Ok markets
             | Error e -> 
                   //  LOGGING ERROR HERE
                   printfn "%A" e
                   Error "Database error occurred while retrieving the list of markets"

[jacksonpradolima]

If I try to use your example, using member __.GetMarkets () : Result<Market list, string>, the builds fail when I run the command dotnet fake build -t run. The error is :

- error FS0001: This expression was expected to have type 'Market list' but here has type  'Result<'a,'b>'

The code compile with success if I use as follow:

    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"
        })

But when I access the endpoint http://localhost:8080/api/IMarketApi/getAll the error that appears is:

- Error occured while running the function getAll

To clarify, I put my code on Gits here.

Thanks for your time

[Zaid-Ajaj]

If I try to use your example, using member __.GetMarkets () : Result<Market list, string>, the builds fail when I run the command dotnet fake build -t run. The error is :

You can forget about the error, it only occurs when you are using the Npgsql.Fhaarp namespace as opposed to using Npgsql.FSharp.Tasks namespace which is what you are already doing.

But when I access the endpoint http://localhost:8080/api/IMarketApi/getAll the error that appears is:

This is a runtime error catched by Fable.Remoting, it means an exception has occurred on the backend. To inspect the errors and log them, check out Error Handling section of Fable.Remoting

[jacksonpradolima]

You can forget about the error, it only occurs when you are using the Npgsql.Fhaarp namespace as opposed to using Npgsql.FSharp.Tasks namespace which is what you are already doing.

Ok, I did not understand this part. If I am using Npgsql.FSharp.Tasks namespace why the error appears when I try to use member __.GetMarkets () : Result<Market list, string>?

This is a runtime error catched by Fable.Remoting, it means an exception has occurred on the backend. To inspect the errors and log them, check out Error Handling section of Fable.Remoting

I tried to do this but I do not know on the client-side. Yes, I am totally new xD

BUT

I used a simple print and I got this error:

Exception of type 'CommonExtensionsAndTypesForNpgsqlFSharp+UnknownColumnException' was thrown.

The problem is:

- Could not read column 'Id' as int. Available columns are [id:integer], [pair:character varying(7)], [price:numeric(4)], [quantity:integer]

[Zaid-Ajaj]

Ok, I did not understand this part. If I am using Npgsql.FSharp.Tasks namespace why the error appears when I try to use member __.GetMarkets () : Result<Market list, string>?

This library currently exposes two namespaces: Npgsql.FSharp and Npgsql.FSharp.Tasks. The first namespace has Sql.execute function that returns Result but the same Sql.execute function returns a normal value instead of a result. I know it is a bit confusing and that is why I getting rid of these different namespaces and only using one namespace moving forward for v4 of the library

Could not read column 'Id' as int. Available columns are [id:integer], [pair:character varying(7)], [price:numeric(4)], [quantity:integer]

This is an interesting problem 🤔 basically it is saying that you should the lowercase version of the column name to read it:

Id = read.int "id"

instead of

Id = read.int "Id"

I saw that you created your table with uppercase column names so I am not sure why Npgsql does that, maybe it is just a convention that column names should be lowercased, I will find out why that is the case but it should work for you

[Zaid-Ajaj]

Yeah it seems the case

Identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL. Column names that were created with double-quotes and thereby retained upper-case letters (and/or other syntax violations) have to be double-quoted for the rest of their life:

You should use the lower-case column names if you are not double-quoting them

[jacksonpradolima]

True, I changed the column names, and it is ok! The function is returning the rolls :D

BUT

I added a new column marketdate TIMESTAMP NOT NULL on the database, and I mapped as marketdate : DateTime. When I use on the GetMarkets function marketdate = read.timestamp "marketdate" an error is raised:

- This expression was expected to have type 'DateTimelist' but here has type  'NpgsqlTypes.NpgsqlDateTime'

I tried other options for DateTime without success.

[Zaid-Ajaj]

Did you try read.dateTime "marketdate"? That one returns DateTime instances. read.timestamp returns an internal Npgsql type representing the timestamp so it not useful for you in this case

[jacksonpradolima]

I do not know why but it is working now!
I guess I was trying before change the column names...sorry

Thanks a lot @Zaid-Ajaj !

[Zaid-Ajaj]

No problem, I am glad you were able to fix the problem. I will be adding better docs soon to help with these kinds of issues 😄