PostgreSQL ORM

PostgreSQL-ORM is an Object Relational Mapper (ORM) that complements Simple. It knows how to map high-level operations to PostgreSQL flavored SQL for Haskell types that are instances of the Model type-class, as well as perform join operations on associations between Model instances.

Declaration

Creating a Model in Haskell is easy. We simply declare a datatype using record syntax, include a field of type DBKey to hold the primary key and let Haskell Generics do the rest:

{-# LANGUAGE DeriveGeneric #-}

...

data Post = Post {
  { postId :: DBKey
  , postTitle :: Text
  , postBody :: Text
  } deriving (Generic)

instance Model Post

Voila! Post is now an instance of Model that maps to a SQL table named "post". We can use post to talk to a database containing a table declaration like that matches:

CREATE TABLE "post" {
  postId serial primary key,
  postTitle text NOT NULL,
  postBody text NOT NULL
}

Usage

There are several high level functions that operate on Models:

findAll :: Model a => Connection -> IO [a]
findRow :: Model a => Connection -> DBKey -> IO a
save    :: Model a => Connection -> a -> IO a
destroy :: Model a => Connection -> a -> IO ()

Instances of the Model typeclass implement three methods:

class Model a where
  modelInfo  :: ModelInfo a
  modelRead  :: RowParser a
  modelWrite :: a -> [Action]

• modelInfo contains information about how the Model is stored in the database. This includes the name of the database table, the names of the database columns, the index of the primary key in the list of columns and a function to get the primary key from the Model.
• modelRead returns a RowParser — a type from the underlying PostgreSQL library that takes a SQL row and parses it into a Haskell type (a Model in this case). Assuming the order of columns in modelInfo and the Haskell type is the same, modelRead is trivially implemented using the fieldfunction: MyConstructor <$> field <*> field ...
• modelWrite takes the Model as an argument and returns a list of Actions — another type from the underlying PostgreSQL library which more or less is a wrapper around ByteString. modelWrite must marshal all fieldsexcept for the primary id. This allows the primary id to be included separately for update queries and excluded for insert queries (as it will be auto-generated by PostgreSQL).

That is enough information for the library to implement a low-level typed-SQL API as well as high level operations like save, find a row by key and list all rows. It also allows PostgreSQL-ORM to provide typed model associations (join relations). There are subtleties though. As mentioned above, the implementations of modelInfo, modelRead and modelWrite are closely intertwined, and careless implementations will lead to bugs that cannot be detected at compile time. However, for common cases, where a Model is a record, PostgreSQL-ORM (optionally) uses Haskell Generics to automate the instance definition. In such cases, the a model definition might look like:

data User = User { userId :: DBKey
                 , userFirstName :: String
                 , userLastName :: String
                 , userAge :: Integer }
              deriving (Generic)

instance Model User

Such an instance maps the User Haskell data type to a SQL table called “user” with columns "userId", "userFirstName", "userLastName" and "userAge". Note that the Generic implementation of a modelInfo simply uses the contructor in lower case for the table name and the records as-is for the column names. This makes modelInfo a convenient point of interposition customizing the naming policy. For example, table names can be customized by updating the modelTable field of defaultModelInfo:

instance Model User where
  modelInfo = defaultModelInfo
    { modelTable = "myprefix_user" }

Of course, this task can be standardized with combinators. The library comes with a combinator, underscoreModelInfo which discards a prefix of the column names and converts the remainder from camel-case to underscore notation (a common convention for naming in SQL).