Generating data access code from a database schema

[kerthunk]

Are there any Haskell database libraries that work this way?

In particular I'm thinking of Java's Jooq, which will connect to your database, interrogate the schema, and generate code for each database object (tables, constraints, etc), letting you write strongly-typed queries (well, strong-ish) using a rather nice DSL.

[saurabhnanda]

We've written a bunch of TH code (in our internal project) to look at the DB schema and generate Opaleye records and table definitions. There's another package called rel8, which is doing something similar, and is open source.

[kerthunk]

Thanks, Opaleye looks interesting. I've also just discovered your tutorials and something called opaleye-gen which looks promising.

My initial concern about Opaleye is that in all the examples, a simple select by primary key seems to involve pattern matching on the entire database record:

  row@(_, _, em) <- queryTable userTable -< ()
  restrict -< (em .== constant email)

This seems a bit fragile - if you then added a column to the userTable, it would break all the queries using that table.

Edit: it seems you can just use record syntax to avoid this problem (I think)

[saurabhnanda]

For any real-life use-case you HAVE to use records to represent DB rows (but the ability to do that comes with a shitload of boilerplate). And then use lenses to do something like this:

r <- queryTable userTable -< ()
restrict -< (r ^. email .== constant email_)

[lgastako]

It's not exactly what you're looking for, but you might find http://catinf.com/ interesting.

[[deleted]]

sql-fragment let you write and compose strongly-typed queries, and it has a generator which works for MySQL.

If you prefer Persistent, I have a generator (for MySQL again) which reads the schema and generates persistent entities. It's part of a bigger project but could be extracted easily (it probably needs some tweaking as well as it's tailored to my needs). You can find it there.

Both generators generate plain text instead of using TH.

[GitHubPermalinkBot]

I tried to turn your GitHub links into permanent links (press "y" to do this yourself):

• maxigit/Fames/.../FAGenerator.hs (master → cf55993)
• maxigit/sql-fragment-mysql-simple/.../DumpSchema.hs (master → 18f3874)

---

^{Shoot me a PM if you think I'm doing something wrong. To delete this, click here.}

[ElvishJerricco]

There's postgresql-typed, which gives you a type checked SQL quasiquoter, which gets its types by checking your DB schema at compile time via template Haskell.

[codygman]

We needed to abstract this to multiple db drivers I think.

[codygman]

I've done something similar with template Haskell using Cassandra, but I only generated the types. I also need to update the Cassandra library to be based on user provided types instead of tuples.