SQL views in Yesod/persistent

Question

In http://www.yesodweb.com/book/persistent there is no mention of SQL views.

I (even in imperative languages) have been very fond of immutable database schema design. i.e. only INSERTs and SELECTs - UPDATEs and DELETEs are not used.

This has the advantage of preserving all history, at the expense of making the current 'state' a relatively expensive pure function of the history in the DB.

e.g. there is not a 'user' table, just 'user_created', 'user_password_updated' and 'user_deleted' tables, which are unified in a 'user' SQL VIEW, showing the current state of users.

How should I work with VIEWs in Persistent? Should I use Persistent at all - is it (ironically for Haskell) too tightly focused on a mutable DB for my use-case?

Answer 1

It's been a long time since the question was asked, but I thought it was worth responding because seven years later the answer has not changed and I really like your idea about keeping the old versions of tables around and reading them with views! One drawback of this approach is that using Template Haskell in persistent will slow down compile times a lot. Once I had a database of about 50 tables in persistent Template Haskell and it took over half an hour to compile if it was ever changed.

Yesod's persistent does not support SQL views and I doubt it ever will because it intends to be database agnostic. Currently it looks like it supports CouchDB, MongoDB, MySQL , PostgreSQL, Redis and SQLite. Not all of these databases support SQL style views so it would be hard to abstract over all of them.

Where persistent excels at is at providing an easy way to create a set of Haskell types that serialize to and from different databases. It provides with type class instances and functions to do single table queries and these work really well. If you want to do join queries on an SQL database that you are interfacing with persistent, then you can use esqueleto, a type safe EDSL for SQL join queries.

As far as handling SQL Views in Haskell, I have not come across any tool yet. You can either use rawQuery which will work but be harder to maintain or you can build your own tool around one the Haskell DB interfaces like postgresql-simple, which is what persistent does. In fact, you can even start with the persistent source code of whatever database you are using and build an SQL View EDSL as you need. In a closed-source project I helped build a custom PostgreSQL interface based on some of persistent's ideas and types, but without using an Template Haskell because the compile time was too slow.