Haskell maintains persistent connections with Esqueleto

Question

I was looking for a permanent library for interacting with sql databases. Suppose I have a database containing recipes, with tables of recipes, ingredients and RecIng.

My (admittedly limited) understanding of resilience leads me to think that I should define the tables as follows:

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase| Recipe title String Ingredient name String RecIng recId RecipeId ingId IngredientId quantity Int |] 

With this, you can use Esqueleto to get the inner join between these tables:

 select $ from $ \(i `InnerJoin ` ri `InnerJoin` r) -> do on (r ^. RecipeId ==. ri ^. RecIngIngId) on (i ^. IngredientId ==. ri ^. RegIngRecId) return (r, ri, i) 

This returns a tuple (Recipe, RecIng, Ingredient).

What I really want is a recipe request method, which leads to the following:

 data Recipe = Recipe { title :: String , ingredients :: [Ingredient] } data Ingredient = Ingredient { name :: String , quantity :: Integer } 

Besides defining an additional set of data types and converting tuples, is there a best practice for this kind of thing?

Answer 1

+1 to Adam's comment, this is IMO's correct answer.

A separate approach that you could take would be to use inline objects, which would essentially mean JSON encoding the list of ingredients in each recipe. But it will be a bad SQL design, will cause problems with locking the table for updates, and will not scale well for a large number of ingredients.

In other words, there is a mismatch between the Haskell view you want to work with and the correct way to store data in the database. This does not mean that there is a problem with the format of your database or with your Haskell data types: this is a logical difference. The correct answer to this gap is to have two types of data and an intelligent way of converting between them.