Enum Support

[wiredsister]

Hi there,

Wondering if Enumerated Types would ever be supported at a library level? Would you be open to that type of feature as a contribution?

Example: let's say I have a union type

type User = Developer | API | Person

and in my Postgres database i have

CREATE TYPE user AS ENUM ('Developer', 'API', 'Person');

Would this make sense as an API?

open Npgsql.FSharp
open System

type UserData = {
  id: Guid
  kind: User
}

let getUser (uuid:Guid) : UserData = 
  Sql.connect Storage.connectionString 
  |> Sql.query $"SELECT * FROM {Storage.Users.table} WHERE id={uuid}"
  |> Sql.execute (fun read -> 
          { 
            id = read.uuid "id" 
            kind = read.enum<User> "kind" 
          }
        )

Or perhaps something even simpler, like:

open Npgsql.FSharp
open System

let toUser =
  function
  | "Developer" -> User.Developer
  | "API" -> User.API
  | "Person" -> User.Person 
  | _ -> failwith "FATAL: Unrecognized User Type"

type UserData = {
  id: Guid
  kind: User
}

let getUser (uuid:Guid) : UserData = 
  Sql.connect Storage.connectionString 
  |> Sql.query $"SELECT * FROM {Storage.Users.table} WHERE id={uuid}"
  |> Sql.execute (fun read -> 
          { 
            id = read.uuid "id" 
            kind = (read.enum "kind" |> toUser)
          }
        )

Or is this a solved problem that I'm just not finding anywhere?

Thank you for your great library.

Best,
Gina

[Zaid-Ajaj]

Hi there Gina, I believe in your case when you SELECT * you should cast the enum to a string, then read.text "kind" |> toUser will work as expected. So your query should look something like this:

SELECT id, kind::text FROM table_name

I don't recommend using WHERE id={uuid} instead use parameterized queries:

SELECT id, kind::text FROM table_name WHERE id=@id

Then

let getUser (userId:Guid) : UserData = 
  Sql.connect Storage.connectionString 
  |> Sql.query $"SELECT id, kind::text FROM {Storage.Users.table} WHERE id=@id"
  |> Sql.parameters [ "@id", SqlValue.Uuid userId ]
  |> Sql.execute (fun read -> 
          { 
            id = read.uuid "id" 
            kind = toUser(read.text "kind")
          })

[wiredsister]

Thanks @Zaid-Ajaj, I'll give this a try and let you know how it goes. Cheers.

[KanagawaMarcos]

How would a usage for writing it work @Zaid-Ajaj?

[Zaid-Ajaj]

@KanagawaMarcos similar to reading, in your F# code you write the value as string when supplying the parameters but in your SQL code you cast the input string to the enum type (if I am not mistaken) @input::user

[gubser]

I wrote some generic extensions and helper functions to achieve this:

[<Extension>]
type RowReaderExtensions() =
    [<Extension>]
    static member enum<'T when 'T :> Enum>(read: RowReader, fieldName) =
        read.NpgsqlReader.GetFieldValue<'T>(read.NpgsqlReader.GetOrdinal(fieldName))

    [<Extension>]
    static member enumOrNone<'T when 'T :> Enum and 'T: null>(read: RowReader, fieldName) =
        read.NpgsqlReader.GetFieldValue<'T>(read.NpgsqlReader.GetOrdinal(fieldName))
        |> Option.ofObj

    [<Extension>]
    static member enumArray<'T when 'T :> Enum>(read: RowReader, fieldName) =
        read.NpgsqlReader.GetFieldValue<'T array>(read.NpgsqlReader.GetOrdinal(fieldName))

module Sql =
    let enum<'T when 'T :> Enum> (d: 'T) =
        Sql.parameter (NpgsqlParameter(Value = d))

    let enumOrNone<'T when 'T :> Enum> (d: 'T option) =
        match d with
        | None -> SqlValue.Null
        | Some value -> enum value

    let enumArray<'T when 'T :> Enum> (d: 'T[]) =
        Sql.parameter (NpgsqlParameter(Value = d))

Usage:

type User =
    | Developer = 0
    | API = 1
    | Person = 2

let registerCustomTypes () =
        // register enums
        NpgsqlConnection.GlobalTypeMapper
            .MapEnum<User>()
        |> ignore

type UserData = {
  id: Guid
  kind: User
}

let getUser (uuid:Guid) : UserData = 
  Sql.connect Storage.connectionString 
  |> Sql.query $"SELECT id, kind::text FROM {Storage.Users.table} WHERE id=@id"
  |> Sql.parameters [ "@id", SqlValue.Uuid userId ]
  |> Sql.execute (fun read -> 
          { 
            id = read.uuid "id" 
            kind = read.enum<User> "kind" 
          }
        )

Hope it helps :-)