[Haskell-cafe] Typesafe multi-table PostgreSQL queries in Yesod with schema names?

[Manuel Gómez]

Hi list,

I’m looking for a rather specific point in the design space for
database access libraries in Haskell and I’m having some trouble
figuring out whether any existing library covers my use cases.

Specifically, I need to

1.  get a Yesod application to access a PostgreSQL database without
having to reinvent the universe to get them decently integrated,

2.  write queries in an EDSL with a good approach to composability and
decent static verification against some declared form of the database
objects,

3.  be able to perform joins

4.  be able to specify the schema name for each database object used in queries



These are the closest solutions I’ve found:

*   HaskellDB: seems like the best approach to the EDSL issue and it’s
certainly capable of performing joins, and I haven’t looked into
schema names, but it seems possible — but it seems to be poorly
maintained and somewhat abandoned, and comfortable Yesod integration
doesn’t sound like a lot of fun.

*   Persistent: it’s perfectly integrated into Yesod, and together
with Esqueleto, it provides a nice, typesafe and fully expressive EDSL
— but, as far as I can tell, there is no support for specifying schema
names, which is essential for my use case.

*   Groundhog: Yesod integration is reasonable since Persistent
borrowed a good bit of its design, and after a few weeks of
experimenting, I’ve got it pretty much worked out by now, but while it
certainly takes an interesting and solid approach to the EDSL issue,
it seems completely incapable of performing joins, and hence its
usability is very heavily limited — I don’t fancy creating SQL views
for each and every join I need.

*   postgresql-simple: minimal Yesod integration shouldn’t be too
uncomfortable, since Persistent uses it internally anyway, and it can
certainly do joins and use whatever fancy SQL magic I could ever want
to write, certainly including schema names — but it doesn’t really do
anything for statically checking queries against a schema
specification or for composability and code reuse, and I might as well
just use Persistent to do raw SQL.



I’ve breifly looked at modifying Persistent to add support for schema
names, but I got lost in the source code very quickly, and I worry
about the fact that it requires serial primary keys — Groundhog is
*very* nice in that it has a great approach to DBMS-specific syntax
and other things I’ll probably need (non-numeric and composite keys),
but it’s really a shame that it can’t do joins, and the workaround
with views sort of defeats the purpose of SQL abstraction.

I’m not sure what to do about this.  Is there some grand unified
relational database access library I just haven’t found, or is a pull
request the only solution?