Database.Persist.Sql

A helper function to tell GHC what the EntityWithPrefix prefix should be. This allows you to use a type application to specify the prefix, instead of specifying the etype on the result.

As an example, here's code that uses this:

myQuery :: SqlPersistM [Entity Person]
myQuery = fmap (unPrefix @"p") $ rawSql query []
  where
    query = "SELECT ?? FROM person AS p"

This newtype wrapper is useful when selecting an entity out of the database and you want to provide a prefix to the table being selected.

Consider this raw SQL query:

SELECT ??
FROM my_long_table_name AS mltn
INNER JOIN other_table AS ot
   ON mltn.some_col = ot.other_col
WHERE ...

We don't want to refer to my_long_table_name every time, so we create an alias. If we want to select it, we have to tell the raw SQL quasi-quoter that we expect the entity to be prefixed with some other name.

We can give the above query a type with this, like:

getStuff :: SqlPersistM [EntityWithPrefix "mltn" MyLongTableName]
getStuff = rawSql queryText []

The EntityWithPrefix bit is a boilerplate newtype wrapper, so you can remove it with unPrefix, like this:

getStuff :: SqlPersistM [Entity MyLongTableName]
getStuff = unPrefix @"mltn" <$> rawSql queryText []

The symbol is a "type application" and requires the TypeApplications@ language extension.

Tells Persistent what database column type should be used to store a Haskell type.

Examples

Simple Boolean Alternative

data Switch = On | Off
  deriving (Show, Eq)

instance PersistField Switch where
  toPersistValue s = case s of
    On -> PersistBool True
    Off -> PersistBool False
  fromPersistValue (PersistBool b) = if b then Right On else Right Off
  fromPersistValue x = Left $ "File.hs: When trying to deserialize a Switch: expected PersistBool, received: " <> T.pack (show x)

instance PersistFieldSql Switch where
  sqlType _ = SqlBool

Non-Standard Database Types

If your database supports non-standard types, such as Postgres' uuid, you can use SqlOther to use them:

import qualified Data.UUID as UUID
instance PersistField UUID where
  toPersistValue = PersistLiteralEncoded . toASCIIBytes
  fromPersistValue (PersistLiteralEncoded uuid) =
    case fromASCIIBytes uuid of
      Nothing -> Left $ "Model/CustomTypes.hs: Failed to deserialize a UUID; received: " <> T.pack (show uuid)
      Just uuid' -> Right uuid'
  fromPersistValue x = Left $ "File.hs: When trying to deserialize a UUID: expected PersistLiteralEncoded, received: "-- >  <> T.pack (show x)

instance PersistFieldSql UUID where
  sqlType _ = SqlOther "uuid"

User Created Database Types

Similarly, some databases support creating custom types, e.g. Postgres' DOMAIN and ENUM features. You can use SqlOther to specify a custom type:

CREATE DOMAIN ssn AS text
      CHECK ( value ~ '^[0-9]{9}$');

instance PersistFieldSQL SSN where
  sqlType _ = SqlOther "ssn"

CREATE TYPE rainbow_color AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');

instance PersistFieldSQL RainbowColor where
  sqlType _ = SqlOther "rainbow_color"

Class for data types that may be retrived from a rawSql query.

Starts a new transaction on the connection. When the acquired connection is released the transaction is committed and the connection returned to the pool.

Upon an exception the transaction is rolled back and the connection destroyed.

This is equivalent to runSqlConn but does not incur the MonadUnliftIO constraint, meaning it can be used within, for example, a Conduit pipeline.

Like acquireSqlConn, but lets you specify an explicit isolation level.

Creates a pool of connections to a SQL database.

Like runSqlConn, but supports specifying an isolation level.

Get a connection from the pool, run the given action, and then return the connection to the pool.

This function performs the given action in a transaction. If an exception occurs during the action, then the transaction is rolled back.

Note: This function previously timed out after 2 seconds, but this behavior was buggy and caused more problems than it solved. Since version 2.1.2, it performs no timeout checks.

Like runSqlPool, but does not surround the action in a transaction. This action might leave your database in a weird state.

This function is how runSqlPoolWithHooks is defined.

It's currently the most general function for using a SQL pool.

This function is how runSqlPool and runSqlPoolNoTransaction are defined. In addition to the action to be performed and the Pool of conections to use, we give you the opportunity to provide three actions - initialize, afterwards, and onException.

Like runSqlPool, but supports specifying an isolation level.

Create a connection and run sql queries within it. This function automatically closes the connection on it's completion.

Example usage

{-# LANGUAGE GADTs #-}
{-# LANGUAGE ScopedTypeVariables #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE MultiParamTypeClasses #-}
{-# LANGUAGE TypeFamilies#-}
{-# LANGUAGE TemplateHaskell#-}
{-# LANGUAGE QuasiQuotes#-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}

import Control.Monad.IO.Class  (liftIO)
import Control.Monad.Logger
import Conduit
import Database.Persist
import Database.Sqlite
import Database.Persist.Sqlite
import Database.Persist.TH

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Person
  name String
  age Int Maybe
  deriving Show
|]

openConnection :: LogFunc -> IO SqlBackend
openConnection logfn = do
 conn <- open "/home/sibi/test.db"
 wrapConnection conn logfn

main :: IO ()
main = do
  runNoLoggingT $ runResourceT $ withSqlConn openConnection (\backend ->
                                      flip runSqlConn backend $ do
                                        runMigration migrateAll
                                        insert_ $ Person "John doe" $ Just 35
                                        insert_ $ Person "Divya" $ Just 36
                                        (pers :: [Entity Person]) <- selectList [] []
                                        liftIO $ print pers
                                        return ()
                                     )

On executing it, you get this output:

Migrating: CREATE TABLE "person"("id" INTEGER PRIMARY KEY,"name" VARCHAR NOT NULL,"age" INTEGER NULL)
[Entity {entityKey = PersonKey {unPersonKey = SqlBackendKey {unSqlBackendKey = 1}}, entityVal = Person {personName = "John doe", personAge = Just 35}},Entity {entityKey = PersonKey {unPersonKey = SqlBackendKey {unSqlBackendKey = 2}}, entityVal = Person {personName = "Hema", personAge = Just 36}}]

Creates a pool of connections to a SQL database which can be used by the Pool backend -> m a function. After the function completes, the connections are destroyed.

useful for a backend to implement fieldName by adding escaping

get the SQL string for the field that an EntityField represents Useful for raw SQL queries

Your backend may provide a more convenient fieldName function which does not operate in a Monad

get the SQL string for the table that a PersistEntity represents Useful for raw SQL queries

Your backend may provide a more convenient tableName function which does not operate in a Monad

useful for a backend to implement tableName by adding escaping