[Haskell-cafe] Generic use of queries postgresql-simple

[Evan Laforge]

On Thu, Feb 9, 2023 at 10:32 AM Pietro Grandinetti
<pietro.gra at hotmail.it> wrote:
>
> Hello--
>
> I use postgresql-simple and for selecting rows from one table I have the following function.
>
> runMyQuery :: Connection
>            -> String           -- query with '?' params to bind
>            -> Int              -- first param
>            -> Int              -- second param
>            -> IO [MyTable1]
> runMyQuery conn dbQuery param1 param2 = query conn dbQuery $ (param2, param2)
>
> data MyTable1 = MyTable1 {col1 :: Int, col2 :: Int, col3 :: String}
>
> instance FromRow MyTable1 where fromRow = MyTable1 <$> field <*> field <*> field
>
> There are some things I'd like to improve:
>
> I will have to write "MyTable1", "MyTable2", ... datatypes, one for each table, but would like to have only one function runMyQuery where the return type is a generic IO [MyModel]. My problem is that I don't know how to write it for tables with different number of columns and different SQL types.
> The 2nd to the penultimate arguments of runMyQuery are the arguments to bind into the SQL string query. This means that I should write several different version of "runMyQuery" each with a different number of arguments, but would like to have only one.

You would use a typeclass overloaded on tuples of various sizes.  But
that's what the existing query function does, so I'm not sure what
you're asking for.  If you want to wrap the query function, you can do
that, just retain the class context.  You didn't say what "MyModel" is
so I can't tell what would be generic about it.

As an aside, I noticed the FromRow feature in postgresql-simple, but
decided to not use it.  If I'm going to hand-write the deserialization
anyway, it's just as easy to put it in a function and the typeclass
adds nothing.  And much of the time I use scalars or tuples, or
multiple types, types are meant to reflect haskell level needs and not
to directly correspond to some SQL query or external schema.  The
value it seems to provide is if you use generic deriving with it,
which I won't do because I'm not thinking I'm going to break a query
at runtime just by rearranging a data declaration.  Since
postgresql-simple won't check the types from the SELECT line to
FromRow it seems like a dangerous convenience.

The comment about "one datatype for each table" implies you're
thinking of a rigid exact correspondence, I wouldn't do that.  Query
what you need to, and return it in whatever form is useful, that's why
there is a query language instead of just being an on disk hash table.