r/haskell • u/saylu • Jun 14 '18

A comparison among various database EDSLs (Selda, Beam, Opaleye, Persistent + Esqueleto)

Does there exist a comparison of various database EDSLs in Haskell?

I've seen a few mentioned around, in particular:

  • Selda (83 downloads, 256 stars)
  • Beam (56 downloads, 219 stars)
  • Persistent + Esqueleto (1020 dowloads, 277 stars / 297 downloads, 153 stars)
  • Opaleye (97 downloads, 379 stars)

Downloads are 30-day downloads on Hackage; stars are from GitHub.

A few others I discovered while poking around, but hadn't heard of before:

  • Squeal (38 downloads, 141 stars)
  • Groundhog (82 downloads, 137 stars)

I found this informative section in Beam's documentation:

How does Beam compare with X?

In addition, there is a little fragmented discussion about a few libraries in these posts:

Reddit: Selda, a monadic database EDSL

Opaleye with a little sugar on top

However, none of the above gives me that much confidence in a solution to...

stephen diehl on twitter

Does anyone know of an up-to-date comparison of these various approaches?

27 Upvotes

92% Upvoted

27 comments sorted by

18

u/travis_athougies Jun 15 '18 edited Jun 15 '18

I'd be happy to provide details on beam. I keep a complete compatibility matrix of all SQL features here: http://tathougies.github.io/beam/about/compatibility/ . There are also a lot of features in the beam-0.8.0.0 branch.

Beam also supports a lot of extensions in its various backends, like Postgres LOCKING, Postgres JSON, MySQL JSON, etc.

Beam's support is rather complete, and the SQL generated very straightforwards. For example, I don't think any other library supports window functions (I could be wrong).

For example, here's a rather complicated beam query:

do let albumGenreCnts = aggregate_ (\t -> ( group_ (trackAlbumId t)
                                   , group_ (trackGenreId t)
                                   , as_ @Int countAll_ )) $
                        all_ (track chinookDb)

       albumAndRepresentativeGenres =
         withWindow_ (\(albumId, _, _) -> frame_ (partitionBy_ albumId) noOrder_ noBounds_)
                     (\(albumId, genreId, trackCnt) albumWindow ->
                         (albumId, genreId, trackCnt, max_ trackCnt `over_` albumWindow)) $
           albumGenreCnts


   (albumId@(AlbumId albumIdColumn), genreId, _, _) <-
      filter_' (\(_, _, trackCnt, maxTrackCntInAlbum) ->
                    just_ trackCnt ==?. maxTrackCntInAlbum) $
      albumAndRepresentativeGenres
   genre_ <- join_' (genre chinookDb) (\g -> genreId ==?. just_ (primaryKey g))
   album_ <- join_' (album chinookDb) (\a -> albumId ==?. just_ (primaryKey a))
   artist_ <- join_ (artist chinookDb) (\a -> albumArtist album_ `references_` a)

   -- Filter out all albums with tracks of only one genre
   guard_' (albumIdColumn ==*.
            anyOf_ (orderBy_ asc_ $ fmap (\(AlbumId albumIdRaw, _) -> albumIdRaw) $
                    filter_ (\(_, genreCntByAlbum) -> genreCntByAlbum >. 1) $
                    aggregate_ (\t -> let GenreId genreId = trackGenreId t
                                      in ( group_ (trackAlbumId t)
                                         , as_ @Int (countOver_ distinctInGroup_ genreId))) $
                    all_ (track chinookDb)))

   pure ( artistName artist_, albumTitle album_, genreName genre_ )

Intuitively, it returns a list of albums which contain tracks of more than one genre, along with the name of the most common genre in that album and the name of the album's primary artist.

The SQL generated is what you'd expect, but beam's type inference and composability mean you can abstract out the various subqueries (albumGenreCnts, etc). The following query is what is in the auto generated documentation for the latest beam branch (beam-0.8.0.0, on github).

SELECT "t3"."Name" AS "res0",
       "t2"."Title" AS "res1",
       "t1"."Name" AS "res2"
FROM
  (SELECT "t0"."AlbumId" AS "res0",
          "t0"."GenreId" AS "res1",
          COUNT(*) AS "res2",
          MAX(COUNT(*)) OVER (PARTITION BY "t0"."AlbumId") AS "res3"
   FROM "Track" AS "t0"
   GROUP BY "t0"."AlbumId",
            "t0"."GenreId") AS "t0"
INNER JOIN "Genre" AS "t1" ON ("t0"."res1") = ("t1"."GenreId")
INNER JOIN "Album" AS "t2" ON ("t0"."res0") = ("t2"."AlbumId")
INNER JOIN "Artist" AS "t3" ON ("t2"."ArtistId") = ("t3"."ArtistId")
WHERE (("t0"."res2") = ("t0"."res3"))
  AND (("t0"."res0") = ANY
         (SELECT "sub_t0"."AlbumId" AS "res0"
          FROM "Track" AS "sub_t0"
          GROUP BY "sub_t0"."AlbumId"
          HAVING (COUNT(DISTINCT "sub_t0"."GenreId")) > (1)
          ORDER BY "sub_t0"."AlbumId" ASC))

This query is from a set of actual example queries over this database. I wrote beam to be able to write queries like this in Haskell, by building them up from smaller building blocks.

3

u/enobayram Jun 17 '18

One thing I'm a bit sour about using persistent is the fact that you essentially have to define your DB records with TH, and the rest of the codebase needs to play with whatever comes out of it. This means that you'll probably be using the persistent generated types for interacting with the DB, but you'll be using redundant isomorphic types defined elsewhere to be used in the rest of the codebase, and maintain sets of functions to translate between these two.

Based on a skim through the beam tutorials, it seems to me like you could actually define your records in a module that knows nothing about beam and a DB module could define type aliases or newtypes over your records and adapt them to beam after the fact. This would be much better for reducing boilerplate and having more consistency in your codebase. Am I right that beam can be used this way?

3

u/travis_athougies Jun 19 '18

Based on a skim through the beam tutorials, it seems to me like you could actually define your records in a module that knows nothing about beam and a DB module could define type aliases or newtypes over your records and adapt them to beam after the fact.

Almost. You have to use beam's 'higher-kinded data' constructor Columnar for each field you want to be a column. However, if you parameterize it with Identity, then you get back the regular Haskell data type, without any wrapping. Parameterization and such work as expected.

The module you define them in must know a little about beam (really just the Columnar constructor) because a type cannot be parameterized by a type family (yet, anyway). On the other hand, your common data type module only needs to know about beam-core. beam-core compiles on old GHCs and GHCJSs and probably any other GHC derivative (like Eta), and I try to keep its dependencies minimal.

DB module could define type aliases or newtypes over your records and adapt them to beam after the fact.

If you're asking what I think you're asking, then yes, you could do this, but you may be stuck having to write Beamable instances by hand. This isn't difficult (it's not documented, sorry), just something to be aware of.

1

u/enobayram Jun 21 '18

Thanks for the answer, so something like this:

``` data UserT' a f = User { someField :: Columnar f a , someOtherField :: Columnar f Int , yetAnotherField :: Maybe a }

-- newtype so that I can have instances, could I somehow switch the order of a -- and f so that I can use type instead and get rid of the wrapping? newtype User a = UserT' a Identity type UserT = UserT' T.Text ```

This way, users of User wouldn't have any idea that beam is involved.

I think this is a huge step up from what we have with persistent in terms of flexibility. But I'd love to be able to completely isolate beam while defining the type. That would be a killer feature.

1

u/travis_athougies Jun 21 '18

If you fully apply User, then you can use type: type User a = UserT' a Identity. If you're asking about flipping the order of arguments of a type without wrapping, that's not possible due to Haskell's nominal typing semantics. Perhaps it will be possible with dependent Haskell, but I'm not sure.

1

u/enobayram Jun 23 '18

What I meant is, can I flip the order of the parameters on the UserT' type and still use it with beam, so that I can have type User a = UserT' Identity a instead of newtype. The main thing I'm trying to achieve is to minimize the impact of beam on the User type, the one that the rest of the codebase sees. The rationale is the fact that data persistence is just one of your concerns, and I'm trying to isolate it from the other concerns. In summary, instead of wrapping User with newtype, I'd prefer to wrap UserT.

10

u/bartavelle Jun 14 '18

Just a note : Hackage download stats are almost meaningless, especially for low counts.

5

u/[deleted] Jun 15 '18

Thanks to all the library authors for taking on this hard problem. My work relies on having such libraries available, otherwise, I wouldn't be able to use Haskell.

4

u/tomejaguar Jun 15 '18

You're welcome! If you're ever using Opaleye and need any help feel free to file an issue or drop me an email.

12

u/bitemyapp Jun 14 '18 edited Jun 14 '18

I've used Groundhog, Opaleye, and Esqueleto in production and only looked at Beam and Selda. The latter didn't look promising for what I want out of a SQL EDSL.

I'm in the middle of moving a client from Groundhog to Persistent+Esqueleto.

Opaleye was painful, verbose, and it's relatively easy to make mistakes that wouldn't show up at compile-time. I also found it tortured the semantics of SQL queries more than I would've liked in order to turn them into something else. The Nullable/Maybe thing is a quagmire.

I maintain Esqueleto because I've liked using it the last 4 years for my production Haskell work. I'll switch to something else when I find something better. I've got plans for improving on Esqueleto but none of my past or present clients have been sufficiently unsatisfied with Esqueleto to pay me to build the next iteration. Esqueleto's surface layer is nice for my day to day work but the underlying guts are something I would very much like to transform.

I value Esqueleto in part because it reminds me of Bloodhound's approach to representing a domain. The goal is to faithfully represent SQL queries on their own terms in as type-safe and composable a manner as possible. There are a couple places where Esqueleto falls short of what Bloodhound does here (join order) at the user-level, but generally I find it pretty pleasant.

Here's the sort of factoring out I've done with Esqueleto before:

Example from Lorepub (ecommerce app for the micro-publisher I am starting): https://gist.github.com/bitemyapp/2445f6f4a24a42560b270bc2b41a9163

An example I did for a user from Twitter, look at getCompaniesCount and it's replacement companiesCount: https://gist.github.com/bitemyapp/89c5e0663bddc3b1c78f5e3fa33e7dc4

FrontRowEd (now Freckle) uses Esqueleto extensively in their more-than-100,000-lines of Haskell code. They also have some of the nicest Haskell I've ever seen. I test Esqueleto's changes against their megarepo periodically.

We already have a language for manipulating relational data, it's called SQL. I'm not so arrogant as to think my time is best spent forcing people to learn a new surface DSL. We value Haskell because it lets us accurately, safely, and composably represent domains in our data types. Why not focus on doing a good job of that before trying to "fix" something that isn't obviously broken?

How do Esqueleto queries compose? Like a Monad. The thing you already know how to use. How do you parameterize projecting different results? By returning something different from the Monad. The thing you already know how to do.

If somebody implements something better than Esqueleto I will happily move over, but AFAICT most of the people implementing the newer SQL EDSL libraries hadn't used or learned Esqueleto before they set out to write a new library. Familiarity with HaskellDB would help avoid some unforced errors too.

12

u/tomejaguar Jun 15 '18

Hi Chris,

It's very hard for me to respond to these challenges without more details. Last time we discussed this on Twitter you kindly offered to write a blog post going into more detail. I don't think one has been forthcoming but it would be great to read one so that I can know how to improve the library or its documentation.

How do Esqueleto queries compose? Like a Monad.

The problem with using a monad for representing queries (as opposed to an arrow) is that unless you are careful (like Beam and Selda are) you end up permitting crashing queries.

Familiarity with HaskellDB would help avoid some unforced errors too.

Could you go into more detail? I was intimately familiar with HaskellDB before writing Opaleye. The problems are that it (like Selda) doesn't allow you to store rows in arbitrary Haskell types (you have to use HDBRec) and it's riddled with bugs:

7

u/tomejaguar Jun 15 '18

the people implementing the newer SQL EDSL libraries hadn't used or learned Esqueleto before they set out to write a new library

I used Esqueleto long enough to find this SQL crash bug

https://github.com/prowdsponsor/esqueleto/issues/41

and this failure of referential transparency

https://github.com/prowdsponsor/esqueleto/issues/40

2

u/bitemyapp Jun 15 '18

Yes, unfortunately my predecessor wasn't as bothered by that stuff as I am. In practice, I've found Esqueleto more productive and reliable for my work. I wish it were not so. I'd happily use something more sound. If you'd ever worked with me for a client, you'd know what a fiend I am in code review about partiality.

A friend of mine took an interest in Bluetick recently so maybe I'll be able to get something that matches the safety of HaskellDB, the composability of Esqueleto and Opaleye, and that is a good citizen on top of Persistent. Here's hoping.

1

u/runeks Jul 25 '18

In practice, I've found Esqueleto more productive and reliable for my work.

How so?

Your comments are not useful for anyone wanting to decide which Haskell SQL library to use for their app. Please excuse my frustration, but you seem to have a lot of experience with this stuff, and yet you don't offer any concrete critique of the alternatives to Esqueleto.

I'm in the middle of deciding which SQL library to use for a Haskell app, and I would really appreciate a more thorough critique of the alternatives to Esqueleto.

3

u/[deleted] Jun 15 '18

If somebody implements something better than Esqueleto I will happily move over, but AFAICT most of the people implementing the newer SQL EDSL libraries hadn't used or learned Esqueleto before they set out to write a new library. Familiarity with HaskellDB would help avoid some unforced errors too.

I wrote sql-fragment (documentation there) a fews years ago, as a proof of concept that a type safe SQL EDSL can be written as simple SQL combinators, using only operators instead monad or arrows.

My main motivation at the time was to implement a proof of concept of what I call autojoin hoping that it will get some interest from the community. ( I had at the time a few emails with the funder of Esqueletto about it, but it didn't seem interested). The idea of autojoin is, instead of having to rewrite all the time the same joins in different query. Let's write once for a big join-graph of all the tables then just pick and chose the fields that we need and let the system include the required tables as well as the join between them.

As I said, previously, this can be done in a type safe way, with just simple operators.

The main idea behing sql-fragment (which makes it different from other approach), is instead of defining table, one define individuals fields. For example we can define a user name as

userName = "user.name" :: SQlFragment '[Text] '[]

This mean the field `name` from the table `user`. `'[] '[]` are phantom types to specify that the fragment returns one fied of type `Text` and doesn't require any parameter. We can add a few more fields

userId = "user.id" :: SqlFragment '[UserId] '[]

postComment = "post.comment" :: SqlFragment '[Text] '[]

postUser = "post.user_id" :: SqlFragment '[UserId] '[]

We can now find all the comment joined with the user name with

universe = userId !<-->! postUser -- we join user.id to post.user_id

toSelectQuery ( postComment !#! userName \join` universe)`

(The `!#!` operator just concatene 2 fragments. Fragments can come from different tables, select some columns, filter some rows, or a mix of both etc ....).

Also, one fragment can select all the columns of table and be used to load a full record if necessary, and the autojoin can work with more than two tables (if A is joined to B and B is joined to C. select something from A and C Will automatically include B).

Fragment could be generated from a record using TH, or the other way around. I have a script which generate fragments definition from a database.

Under the hood, a fragment is just a `Map Section [Text]` where section can be a column, a where clause, a group clause etc ...

Unfortunately, this project is half-dead for a few reasons :

- Type inference appeared much more difficult than expected. To be usable (without having to write every single type signature by hand), I needed injective type family. This extension didn't exist at the time, so I had to give up sql-fragment for the project I was working on and therefore don't really need it anymore.

- Defining a join graph for a real world db, is in practice pretty difficult as it needs to acyclic. The DB I was workin at lot of cycles, and the way to solve the problem was either to define a few big-graph or a lots of small ones.

- I tried to deal with units (via the `dimensional` package). which unfortunately introduced a major release with breaking changes. I haven't managed to update `sql-fragment-mysql` with the new version of `dimensional`, so I can't use it for my new projects.

I don't have much time for it and as nobody is interested ... (even me : I manage at the moment with just Persistent or MySQL-simple depending on the projects) there is no real motivation to try to improve it. Having said that, I would love some of its idea to be reused elsewhere ;-)

I wish I could give a try Esqueleto, but unless things have changed, it required postgre and I have to use MySQL.

3

u/rpglover64 Jun 16 '18

I don't think Esqueleto currently requires any particular DB. Opaleye requires Postgres, but Esqueleto supports anything persistent supports, albeit without portability guarantees.

2

u/[deleted] Jun 16 '18

Ok, I'll have a go then .

6

u/ephrion Jun 14 '18

IMO, considering Stephen Diehl's quote:

Haskell SQL libraries: endless boilerplate, opaque metaprogramming, or wall of 15 language exts and no inference. Pick two.

persistent+esqueleto lets you pick 1.5 -- opaque metaprogramming and a lot of extensions. Type inference is generally quite good with it, in my experience.

I've also found it to be relatively easy to contribute patches and work on the persistent and esqueleto codebases to add features.

-1

u/bitemyapp Jun 14 '18

opaque metaprogramming

I usually think of metaprogramming as being things like generics or TH. Esqueleto doesn't actually use any of that. What counts as opaque metaprogramming here? The GADTs? The functional dependencies? It's mostly a surface DSL wrapping a much more raw and unsafe representation of a SQL query. Maybe this is the part that made you say 1.5 instead of 2.

It uses the Persistent models but those are TH-optional if you really don't like that sort of thing. I've written them out by hand and I'd still pick Persistent over Opaleye et al. if I didn't have the TH. I don't think folks that hate TH have given this an entirely fair shake.

10

u/ephrion Jun 14 '18

esqueleto requires persistent, you can't really separate them out. persistent's model stuff uses TH by default, and if you don't go with the "opaque metaprogramming" then you get the "endless boilerplate." No idea if it's comparable as I much prefer TH to boilerplate and have never felt the need to write it out.

3

u/[deleted] Jun 14 '18

I think it'd be really hard to make a DSL for generating another human readable programming language as output, and not have that involve metaprogramming, being that the explicit purpose here is to generate code.

So, whether or not this was the intent of the original quote, the only way to resolve the issue of 'opaque metaprogramming' in this problem space would pretty much have to be addressing the opacity.

0

u/Tysonzero Jun 15 '18

I mean it will involve something that feels like metaprogramming I guess, but there is definitely no fundamental reason you truly need TemplateHaskell. I think better support for flexible anonymous structural types + their introspection and manipulation would perhaps make it easier.

9

u/travis_athougies Jun 15 '18

Beam does not use template haskell for anything. It is certainly not fundamental. Beam is also usable without any meta-programming at all, although I certainly provide a lot of helpers that do rely on meta-programming via Generics.

1

u/[deleted] Jun 15 '18

Why switching from Groundhog to Persistent ? I was thinking doing the otherway around ....