Is SqlHydra a wrapper around Npgsql? (or: SqlHydra vs. Npgsql)

[toraritte]

Just getting my feet wet with F# for a new project where I also need to connect to a PostgreSQL database. fsharp.org's SQL Data Access guide is quite overwhelming, but after going through the list, the projects that stood out for me were Npgsql.FSharp (and npgsql.org) and SqlHydra.

1. Is my understanding correct is that Npgsql is specifically for PostgreSQL while SqlHydra aims to collect all database .NET providers under one roof?

2. If I only use PostgreSQL databases, would there be any benefits in using SqlHydra instead of just going with Npgsql?
   (If I had to connect to other types of databases, it would be a no brainer to go with SqlHydra. Perhaps SqlHydra provides features I don't know of yet that would make it easier to use, set up, etc. Npgsql? Also, my requirements may change, necessitating changes in DB vendors, in which case it would pay off starting out with SqlHydra.)

Thank you!

[JordanMarr]

Both SqlHydra and Npgsql.FSharp use the same underlying Npgsql provider library.

Here are a couple things that may help you decide:

SqlHydra will generate record types for your tables which you can use to create strongly typed queries using the query syntax. I personally like to have strongly typed entities to query against as it makes it harder to make mistakes. (Note that Npgsql.FSharp also has a separate query analyzer that will check your query.)

I suppose a side benefit is that you could still use SqlHydra if you changed database providers, but I've rarely ever needed to do that in all my years of software development.
 
OTOH, if you prefer to write your SQL queries manually, then Npgsql.FSharp is a good choice.

It really just depends on which API you prefer.

Npgsql.FSharp example with manually crafted SQL query:

let getActiveUsers (connectionString: string) =
    connectionString
    |> Sql.connect
    |> Sql.query "SELECT * FROM users WHERE is_active = @active"
    |> Sql.parameters [ "active", Sql.bit true ]
    |> Sql.executeAsync (fun read ->
        {
            Id = read.int "user_id"
            FirstName = read.text "first_name"
            LastName = read.textOrNone "last_name"
        })

SqlHydra query using generated types:

let getActiveUsers () = 
    selectAsync HydraReader.Read (Create openContext) {
        for p in people.users do
        where p.is_active
    }

[toraritte]

Thank you! My initial reaction was to go with Npgsql.FSharp, because the SqlHydra query looks intimidating as a newcomer (and some SQL query builders I know have some limitations), but then I saw that SqlKata also supports raw SQL as an escape hatch (1, 2). Thanks again for your time!

[JordanMarr]

It looks like you've done your research already!
You can even access SqlKata from within a query:

https://github.com/JordanMarr/SqlHydra#custom-sqlkata-queries

let getCustomers filters = 
  select {
      for c in main.Customer do
      where (c.FirstName = "John")
      kata (fun query -> 
          match filters.LastName with
          | Some lastName -> query.Where("c.LastName", lastName)
          | None -> query
      )
      kata (fun query -> 
          query.OrderBy(filters.SortColumns)
      )
  }

That way, if you need to write some complex SQL manually, you still can.

At the end of the day, it's all about which style you prefer. I made SqlHydra because it's exactly what I like.
Some other people seem to like it too. Some others prefer writing SQL queries by hand.

Feel free to post any questions on the discussion board.