Database.Persist.Sql

Documentation

type Connection = SqlBackend

Deprecated: Please use SqlBackend instead

Deprecated synonym for SqlBackend.

data Column

Constructors

Column

Fields

• cName :: !DBName
• cNull :: !Bool
• cSqlType :: !SqlType
• cDefault :: !(Maybe Text)
• cDefaultConstraintName :: !(Maybe DBName)
• cMaxLen :: !(Maybe Integer)
• cReference :: !(Maybe (DBName, DBName))

Instances

Eq Column

Methods

(==) :: Column -> Column -> Bool

(/=) :: Column -> Column -> Bool

Ord Column

Methods

compare :: Column -> Column -> Ordering

(<) :: Column -> Column -> Bool

(<=) :: Column -> Column -> Bool

(>) :: Column -> Column -> Bool

(>=) :: Column -> Column -> Bool

max :: Column -> Column -> Column

min :: Column -> Column -> Column

Show Column

Methods

showsPrec :: Int -> Column -> ShowS

show :: Column -> String

showList :: [Column] -> ShowS

data PersistentSqlException

Constructors

StatementAlreadyFinalized Text

Couldn'tGetSQLConnection

Instances

Show PersistentSqlException

Methods

showsPrec :: Int -> PersistentSqlException -> ShowS

show :: PersistentSqlException -> String

showList :: [PersistentSqlException] -> ShowS

Exception PersistentSqlException

Methods

toException :: PersistentSqlException -> SomeException

fromException :: SomeException -> Maybe PersistentSqlException

displayException :: PersistentSqlException -> String

type SqlPersistT = ReaderT SqlBackend

type SqlPersist = SqlPersistT

Deprecated: Please use SqlPersistT instead

type SqlPersistM = SqlPersistT (NoLoggingT (ResourceT IO))

type Sql = Text

type CautiousMigration = [(Bool, Sql)]

type Migration = WriterT [Text] (WriterT CautiousMigration (ReaderT SqlBackend IO)) ()

type ConnectionPool = Pool SqlBackend

Although it covers most of the useful cases, persistent's API may not be enough for some of your tasks. May be you need some complex JOIN query, or a database-specific command needs to be issued.

To issue raw SQL queries, use rawSql. It does all the hard work of automatically parsing the rows of the result. It may return:

• An Entity, that which selectList returns. All of your entity's fields are automatically parsed.
• A Single a, which is a single, raw column of type a. You may use a Haskell type (such as in your entity definitions), for example Single Text or Single Int, or you may get the raw column value with Single PersistValue.
• A tuple combining any of these (including other tuples). Using tuples allows you to return many entities in one query.

The only difference between issuing SQL queries with rawSql and using other means is that we have an entity selection placeholder, the double question mark ??. It must be used whenever you want to SELECT an Entity from your query. Here's a sample SQL query sampleStmt that may be issued:

SELECT ??, ??
FROM "Person", "Likes", "Object"
WHERE "Person".id = "Likes"."personId"
AND "Object".id = "Likes"."objectId"
AND "Person".name LIKE ?

To use that query, you could say

do results <- rawSql sampleStmt ["%Luke%"]
   forM_ results $
     \( Entity personKey person
      , Entity objectKey object
      ) -> do ...

Note that rawSql knows how to replace the double question marks ?? because of the type of the results.

newtype Single a

A single column (see rawSql). Any PersistField may be used here, including PersistValue (which does not do any processing).

Constructors

Single

Fields

• unSingle :: a

Instances

Eq a => Eq (Single a)

Methods

(==) :: Single a -> Single a -> Bool

(/=) :: Single a -> Single a -> Bool

Ord a => Ord (Single a)

Methods

compare :: Single a -> Single a -> Ordering

(<) :: Single a -> Single a -> Bool

(<=) :: Single a -> Single a -> Bool

(>) :: Single a -> Single a -> Bool

(>=) :: Single a -> Single a -> Bool

max :: Single a -> Single a -> Single a

min :: Single a -> Single a -> Single a

Read a => Read (Single a)

Methods

readsPrec :: Int -> ReadS (Single a)

readList :: ReadS [Single a]

readPrec :: ReadPrec (Single a)

readListPrec :: ReadPrec [Single a]

Show a => Show (Single a)

Methods

showsPrec :: Int -> Single a -> ShowS

show :: Single a -> String

showList :: [Single a] -> ShowS

PersistField a => RawSql (Single a)

Methods

rawSqlCols :: (DBName -> Text) -> Single a -> (Int, [Text])

rawSqlColCountReason :: Single a -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (Single a)

data SqlBackend

Constructors

SqlBackend

Fields

• connPrepare :: Text -> IO Statement
• connInsertSql :: EntityDef -> [PersistValue] -> InsertSqlResult

  table name, column names, id name, either 1 or 2 statements to run

• connInsertManySql :: Maybe (EntityDef -> [[PersistValue]] -> InsertSqlResult)

  SQL for inserting many rows and returning their primary keys, for backends that support this functioanlity. If Nothing, rows will be inserted one-at-a-time using connInsertSql.

• connUpsertSql :: Maybe (EntityDef -> Text -> Text)

  Some databases support performing UPSERT _and_ RETURN entity in a single call.

  This field when set will be used to generate the UPSERT+RETURN sql given * an entity definition * updates to be run on unique key(s) collision

  When left as Nothing, we find the unique key from entity def before * trying to fetch an entity by said key * perform an update when result found, else issue an insert * return new entity from db

  Since: 2.6

• connPutManySql :: Maybe (EntityDef -> Int -> Text)

  Some databases support performing bulk UPSERT, specifically "insert or replace many records" in a single call.

  This field when set, given * an entity definition * number of records to be inserted should produce a PUT MANY sql with placeholders for records

  When left as Nothing, we default to using defaultPutMany.

  Since: 2.8.1

• connStmtMap :: IORef (Map Text Statement)
• connClose :: IO ()
• connMigrateSql :: [EntityDef] -> (Text -> IO Statement) -> EntityDef -> IO (Either [Text] [(Bool, Text)])
• connBegin :: (Text -> IO Statement) -> IO ()
• connCommit :: (Text -> IO Statement) -> IO ()
• connRollback :: (Text -> IO Statement) -> IO ()
• connEscapeName :: DBName -> Text
• connNoLimit :: Text
• connRDBMS :: Text
• connLimitOffset :: (Int, Int) -> Bool -> Text -> Text
• connLogFunc :: LogFunc
• connMaxParams :: Maybe Int

  Some databases (probably only Sqlite) have a limit on how many question-mark parameters may be used in a statement

  Since: 2.6.1

Instances

IsPersistBackend SqlBackend

Methods

mkPersistBackend :: BaseBackend SqlBackend -> SqlBackend

HasPersistBackend SqlBackend

Associated Types

type BaseBackend SqlBackend :: *

Methods

persistBackend :: SqlBackend -> BaseBackend SqlBackend

data BackendKey SqlBackend

data BackendKey SqlBackend = SqlBackendKey {

• unSqlBackendKey :: Int64

}

type BaseBackend SqlBackend

type BaseBackend SqlBackend = SqlBackend

data SqlReadBackend

An SQL backend which can only handle read queries

Instances

IsPersistBackend SqlReadBackend

Methods

mkPersistBackend :: BaseBackend SqlReadBackend -> SqlReadBackend

HasPersistBackend SqlReadBackend

Associated Types

type BaseBackend SqlReadBackend :: *

Methods

persistBackend :: SqlReadBackend -> BaseBackend SqlReadBackend

data BackendKey SqlReadBackend

data BackendKey SqlReadBackend = SqlReadBackendKey {

• unSqlReadBackendKey :: Int64

}

type BaseBackend SqlReadBackend

type BaseBackend SqlReadBackend = SqlBackend

data SqlWriteBackend

An SQL backend which can handle read or write queries

Instances

IsPersistBackend SqlWriteBackend

Methods

mkPersistBackend :: BaseBackend SqlWriteBackend -> SqlWriteBackend

HasPersistBackend SqlWriteBackend

Associated Types

type BaseBackend SqlWriteBackend :: *

Methods

persistBackend :: SqlWriteBackend -> BaseBackend SqlWriteBackend

data BackendKey SqlWriteBackend

data BackendKey SqlWriteBackend = SqlWriteBackendKey {

• unSqlWriteBackendKey :: Int64

}

type BaseBackend SqlWriteBackend

type BaseBackend SqlWriteBackend = SqlBackend

data Statement

Constructors

Statement

Fields

• stmtFinalize :: IO ()
• stmtReset :: IO ()
• stmtExecute :: [PersistValue] -> IO Int64
• stmtQuery :: forall m. MonadIO m => [PersistValue] -> Acquire (ConduitM () [PersistValue] m ())

type LogFunc = Loc -> LogSource -> LogLevel -> LogStr -> IO ()

data InsertSqlResult

Constructors

ISRSingle Text

ISRInsertGet Text Text

ISRManyKeys Text [PersistValue]

readToUnknown :: Monad m => ReaderT SqlReadBackend m a -> ReaderT SqlBackend m a

Useful for running a read query against a backend with unknown capabilities.

readToWrite :: Monad m => ReaderT SqlReadBackend m a -> ReaderT SqlWriteBackend m a

Useful for running a read query against a backend with read and write capabilities.

writeToUnknown :: Monad m => ReaderT SqlWriteBackend m a -> ReaderT SqlBackend m a

Useful for running a write query against an untagged backend with unknown capabilities.

type SqlBackendCanRead backend = (BackendCompatible SqlBackend backend, PersistQueryRead backend, PersistStoreRead backend, PersistUniqueRead backend)

A constraint synonym which witnesses that a backend is SQL and can run read queries.

type SqlBackendCanWrite backend = (SqlBackendCanRead backend, PersistQueryWrite backend, PersistStoreWrite backend, PersistUniqueWrite backend)

A constraint synonym which witnesses that a backend is SQL and can run read and write queries.

type SqlReadT m a = forall backend. SqlBackendCanRead backend => ReaderT backend m a

Like SqlPersistT but compatible with any SQL backend which can handle read queries.

type SqlWriteT m a = forall backend. SqlBackendCanWrite backend => ReaderT backend m a

Like SqlPersistT but compatible with any SQL backend which can handle read and write queries.

type IsSqlBackend backend = (IsPersistBackend backend, BaseBackend backend ~ SqlBackend)

A backend which is a wrapper around SqlBackend.

class RawSql a where

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

Minimal complete definition

rawSqlCols, rawSqlColCountReason, rawSqlProcessRow

Methods

rawSqlCols :: (DBName -> Text) -> a -> (Int, [Text])

Number of columns that this data type needs and the list of substitutions for SELECT placeholders ??.

rawSqlColCountReason :: a -> String

A string telling the user why the column count is what it is.

rawSqlProcessRow :: [PersistValue] -> Either Text a

Transform a row of the result into the data type.

Instances

RawSql a => RawSql (Maybe a)

Since: 1.0.1

Methods

rawSqlCols :: (DBName -> Text) -> Maybe a -> (Int, [Text])

rawSqlColCountReason :: Maybe a -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (Maybe a)

(PersistEntity record, (~) * (PersistEntityBackend record) backend, IsPersistBackend backend) => RawSql (Entity record)

Methods

rawSqlCols :: (DBName -> Text) -> Entity record -> (Int, [Text])

rawSqlColCountReason :: Entity record -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (Entity record)

(PersistEntity a, (~) * (PersistEntityBackend a) backend, IsPersistBackend backend) => RawSql (Key a)

Methods

rawSqlCols :: (DBName -> Text) -> Key a -> (Int, [Text])

rawSqlColCountReason :: Key a -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (Key a)

PersistField a => RawSql (Single a)

Methods

rawSqlCols :: (DBName -> Text) -> Single a -> (Int, [Text])

rawSqlColCountReason :: Single a -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (Single a)

(RawSql a, RawSql b) => RawSql (a, b)

Methods

rawSqlCols :: (DBName -> Text) -> (a, b) -> (Int, [Text])

rawSqlColCountReason :: (a, b) -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (a, b)

(RawSql a, RawSql b, RawSql c) => RawSql (a, b, c)

Methods

rawSqlCols :: (DBName -> Text) -> (a, b, c) -> (Int, [Text])

rawSqlColCountReason :: (a, b, c) -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (a, b, c)

(RawSql a, RawSql b, RawSql c, RawSql d) => RawSql (a, b, c, d)

Methods

rawSqlCols :: (DBName -> Text) -> (a, b, c, d) -> (Int, [Text])

rawSqlColCountReason :: (a, b, c, d) -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (a, b, c, d)

(RawSql a, RawSql b, RawSql c, RawSql d, RawSql e) => RawSql (a, b, c, d, e)

Methods

rawSqlCols :: (DBName -> Text) -> (a, b, c, d, e) -> (Int, [Text])

rawSqlColCountReason :: (a, b, c, d, e) -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (a, b, c, d, e)

(RawSql a, RawSql b, RawSql c, RawSql d, RawSql e, RawSql f) => RawSql (a, b, c, d, e, f)

Methods

rawSqlCols :: (DBName -> Text) -> (a, b, c, d, e, f) -> (Int, [Text])

rawSqlColCountReason :: (a, b, c, d, e, f) -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (a, b, c, d, e, f)

(RawSql a, RawSql b, RawSql c, RawSql d, RawSql e, RawSql f, RawSql g) => RawSql (a, b, c, d, e, f, g)

Methods

rawSqlCols :: (DBName -> Text) -> (a, b, c, d, e, f, g) -> (Int, [Text])

rawSqlColCountReason :: (a, b, c, d, e, f, g) -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (a, b, c, d, e, f, g)

(RawSql a, RawSql b, RawSql c, RawSql d, RawSql e, RawSql f, RawSql g, RawSql h) => RawSql (a, b, c, d, e, f, g, h)

Methods

rawSqlCols :: (DBName -> Text) -> (a, b, c, d, e, f, g, h) -> (Int, [Text])

rawSqlColCountReason :: (a, b, c, d, e, f, g, h) -> String

rawSqlProcessRow :: [PersistValue] -> Either Text (a, b, c, d, e, f, g, h)

class PersistField a => PersistFieldSql a where

Minimal complete definition

sqlType

Methods

sqlType :: Proxy a -> SqlType

Instances

PersistFieldSql Bool

Methods

sqlType :: Proxy * Bool -> SqlType

PersistFieldSql Double

Methods

sqlType :: Proxy * Double -> SqlType

PersistFieldSql Int

Methods

sqlType :: Proxy * Int -> SqlType

PersistFieldSql Int8

Methods

sqlType :: Proxy * Int8 -> SqlType

PersistFieldSql Int16

Methods

sqlType :: Proxy * Int16 -> SqlType

PersistFieldSql Int32

Methods

sqlType :: Proxy * Int32 -> SqlType

PersistFieldSql Int64

Methods

sqlType :: Proxy * Int64 -> SqlType

PersistFieldSql Natural

Methods

sqlType :: Proxy * Natural -> SqlType

PersistFieldSql Rational

Methods

sqlType :: Proxy * Rational -> SqlType

PersistFieldSql Word

Methods

sqlType :: Proxy * Word -> SqlType

PersistFieldSql Word8

Methods

sqlType :: Proxy * Word8 -> SqlType

PersistFieldSql Word16

Methods

sqlType :: Proxy * Word16 -> SqlType

PersistFieldSql Word32

Methods

sqlType :: Proxy * Word32 -> SqlType

PersistFieldSql Word64

Methods

sqlType :: Proxy * Word64 -> SqlType

PersistFieldSql ByteString

Methods

sqlType :: Proxy * ByteString -> SqlType

PersistFieldSql Text

Methods

sqlType :: Proxy * Text -> SqlType

PersistFieldSql UTCTime

Methods

sqlType :: Proxy * UTCTime -> SqlType

PersistFieldSql Text

Methods

sqlType :: Proxy * Text -> SqlType

PersistFieldSql Html

Methods

sqlType :: Proxy * Html -> SqlType

PersistFieldSql TimeOfDay

Methods

sqlType :: Proxy * TimeOfDay -> SqlType

PersistFieldSql Day

Methods

sqlType :: Proxy * Day -> SqlType

PersistFieldSql PersistValue

Methods

sqlType :: Proxy * PersistValue -> SqlType

PersistFieldSql Checkmark

Methods

sqlType :: Proxy * Checkmark -> SqlType

PersistFieldSql [Char]

Methods

sqlType :: Proxy * [Char] -> SqlType

PersistFieldSql a => PersistFieldSql [a]

Methods

sqlType :: Proxy * [a] -> SqlType

HasResolution a => PersistFieldSql (Fixed a)

Methods

sqlType :: Proxy * (Fixed a) -> SqlType

PersistFieldSql v => PersistFieldSql (IntMap v)

Methods

sqlType :: Proxy * (IntMap v) -> SqlType

(Ord a, PersistFieldSql a) => PersistFieldSql (Set a)

Methods

sqlType :: Proxy * (Set a) -> SqlType

PersistFieldSql a => PersistFieldSql (Vector a)

Methods

sqlType :: Proxy * (Vector a) -> SqlType

(PersistField record, PersistEntity record) => PersistFieldSql (Entity record)

Methods

sqlType :: Proxy * (Entity record) -> SqlType

(PersistFieldSql a, PersistFieldSql b) => PersistFieldSql (a, b)

Methods

sqlType :: Proxy * (a, b) -> SqlType

PersistFieldSql v => PersistFieldSql (Map Text v)

Methods

sqlType :: Proxy * (Map Text v) -> SqlType

runSqlPool :: (MonadUnliftIO m, IsSqlBackend backend) => ReaderT backend m a -> Pool backend -> m a

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

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.

withResourceTimeout

Arguments

:: MonadUnliftIO m

=> Int

Timeout period in microseconds

-> Pool a

-> (a -> m b)

-> m (Maybe b)

Like withResource, but times out the operation if resource allocation does not complete within the given timeout period.

Since: 2.0.0

runSqlConn :: (MonadUnliftIO m, IsSqlBackend backend) => ReaderT backend m a -> backend -> m a

runSqlPersistM :: IsSqlBackend backend => ReaderT backend (NoLoggingT (ResourceT IO)) a -> backend -> IO a

runSqlPersistMPool :: IsSqlBackend backend => ReaderT backend (NoLoggingT (ResourceT IO)) a -> Pool backend -> IO a

liftSqlPersistMPool :: (MonadIO m, IsSqlBackend backend) => ReaderT backend (NoLoggingT (ResourceT IO)) a -> Pool backend -> m a

withSqlPool

Arguments

:: (MonadLogger m, MonadUnliftIO m, IsSqlBackend backend)

=> (LogFunc -> IO backend)

create a new connection

-> Int

connection count

-> (Pool backend -> m a)

-> m a

createSqlPool :: (MonadLogger m, MonadUnliftIO m, IsSqlBackend backend) => (LogFunc -> IO backend) -> Int -> m (Pool backend)

askLogFunc :: forall m. (MonadUnliftIO m, MonadLogger m) => m LogFunc

withSqlConn :: (MonadUnliftIO m, MonadLogger m, IsSqlBackend backend) => (LogFunc -> IO backend) -> (backend -> m a) -> m a

close' :: IsSqlBackend backend => backend -> IO ()

parseMigration :: MonadIO m => Migration -> ReaderT SqlBackend m (Either [Text] CautiousMigration)

Given a Migration, this parses it and returns either a list of errors associated with the migration or a list of migrations to do.

parseMigration' :: MonadIO m => Migration -> ReaderT SqlBackend m CautiousMigration

Like parseMigration, but instead of returning the value in an Either value, it calls error on the error values.

printMigration :: MonadIO m => Migration -> ReaderT SqlBackend m ()

Prints a migration.

showMigration :: MonadIO m => Migration -> ReaderT SqlBackend m [Text]

Convert a Migration to a list of Text values corresponding to their Sql statements.

getMigration :: MonadIO m => Migration -> ReaderT SqlBackend m [Sql]

Return all of the Sql values associated with the given migration. Calls error if there's a parse error on any migration.

runMigration :: MonadIO m => Migration -> ReaderT SqlBackend m ()

Runs a migration. If the migration fails to parse or if any of the migrations are unsafe, then this calls error to halt the program.

runMigrationSilent :: (MonadUnliftIO m, MonadIO m) => Migration -> ReaderT SqlBackend m [Text]

Same as runMigration, but returns a list of the SQL commands executed instead of printing them to stderr.

runMigrationUnsafe :: MonadIO m => Migration -> ReaderT SqlBackend m ()

Like runMigration, but this will perform the unsafe database migrations instead of erroring out.

migrate :: [EntityDef] -> EntityDef -> Migration

Given a list of old entity definitions and a new EntityDef in val, this creates a Migration to update the old list of definitions with the new one.

module Database.Persist

withRawQuery :: MonadIO m => Text -> [PersistValue] -> ConduitM [PersistValue] Void IO a -> ReaderT SqlBackend m a

toSqlKey :: ToBackendKey SqlBackend record => Int64 -> Key record

fromSqlKey :: ToBackendKey SqlBackend record => Key record -> Int64

getFieldName :: forall record typ m backend. (PersistEntity record, PersistEntityBackend record ~ SqlBackend, IsSqlBackend backend, Monad m) => EntityField record typ -> ReaderT backend m Text

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

getTableName :: forall record m backend. (PersistEntity record, PersistEntityBackend record ~ SqlBackend, IsSqlBackend backend, Monad m) => record -> ReaderT backend m Text

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

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

tableDBName :: PersistEntity record => record -> DBName

useful for a backend to implement tableName by adding escaping

fieldDBName :: forall record typ. PersistEntity record => EntityField record typ -> DBName

useful for a backend to implement fieldName by adding escaping

rawQuery :: (MonadResource m, MonadReader env m, HasPersistBackend env, BaseBackend env ~ SqlBackend) => Text -> [PersistValue] -> ConduitM () [PersistValue] m ()

rawQueryRes :: (MonadIO m1, MonadIO m2, IsSqlBackend env) => Text -> [PersistValue] -> ReaderT env m1 (Acquire (ConduitM () [PersistValue] m2 ()))

rawExecute

Arguments

:: (MonadIO m, BackendCompatible SqlBackend backend)

=> Text

SQL statement, possibly with placeholders.

-> [PersistValue]

Values to fill the placeholders.

-> ReaderT backend m ()

Execute a raw SQL statement

rawExecuteCount

Arguments

:: (MonadIO m, BackendCompatible SqlBackend backend)

=> Text

SQL statement, possibly with placeholders.

-> [PersistValue]

Values to fill the placeholders.

-> ReaderT backend m Int64

Execute a raw SQL statement and return the number of rows it has modified.

rawSql

Arguments

:: (RawSql a, MonadIO m)

=> Text

SQL statement, possibly with placeholders.

-> [PersistValue]

Values to fill the placeholders.

-> ReaderT SqlBackend m [a]

Execute a raw SQL statement and return its results as a list.

If you're using Entitys (which is quite likely), then you must use entity selection placeholders (double question mark, ??). These ?? placeholders are then replaced for the names of the columns that we need for your entities. You'll receive an error if you don't use the placeholders. Please see the Entitys documentation for more details.

You may put value placeholders (question marks, ?) in your SQL query. These placeholders are then replaced by the values you pass on the second parameter, already correctly escaped. You may want to use toPersistValue to help you constructing the placeholder values.

Since you're giving a raw SQL statement, you don't get any guarantees regarding safety. If rawSql is not able to parse the results of your query back, then an exception is raised. However, most common problems are mitigated by using the entity selection placeholder ??, and you shouldn't see any error at all if you're not using Single.

Some example of rawSql based on this schema:

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

Examples based on the above schema:

getPerson :: MonadIO m => ReaderT SqlBackend m [Entity Person]
getPerson = rawSql "select ?? from person where name=?" [PersistText "john"]

getAge :: MonadIO m => ReaderT SqlBackend m [Single Int]
getAge = rawSql "select person.age from person where name=?" [PersistText "john"]

getAgeName :: MonadIO m => ReaderT SqlBackend m [(Single Int, Single Text)]
getAgeName = rawSql "select person.age, person.name from person where name=?" [PersistText "john"]

getPersonBlog :: MonadIO m => ReaderT SqlBackend m [(Entity Person, Entity BlogPost)]
getPersonBlog = rawSql "select ??,?? from person,blog_post where person.id = blog_post.author_id" []

Minimal working program for PostgreSQL backend based on the above concepts:

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

import           Control.Monad.IO.Class  (liftIO)
import           Control.Monad.Logger    (runStderrLoggingT)
import           Database.Persist
import           Control.Monad.Reader
import           Data.Text
import           Database.Persist.Sql
import           Database.Persist.Postgresql
import           Database.Persist.TH

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

conn = "host=localhost dbname=new_db user=postgres password=postgres port=5432"

getPerson :: MonadIO m => ReaderT SqlBackend m [Entity Person]
getPerson = rawSql "select ?? from person where name=?" [PersistText "sibi"]

liftSqlPersistMPool y x = liftIO (runSqlPersistMPool y x)

main :: IO ()
main = runStderrLoggingT $ withPostgresqlPool conn 10 $ liftSqlPersistMPool $ do
         runMigration migrateAll
         xs <- getPerson
         liftIO (print xs)

sqlQQ :: QuasiQuoter

QuasiQuoter for performing raw sql queries, analoguous to rawSql

This and the following are convenient QuasiQuoters to perform raw SQL queries. They each follow the same pattern and are analogous to the similarly named raw functions. Neither the quoted function's behaviour, nor it's return value is altered during the translation and all documentation provided with it holds.

These QuasiQuoters perform a simple substitution on the query text, that allows value substitutions, table name substitutions as well as column name substitutions.

Here is a small example:

Given the following simple model:

Category
  rgt Int
  lft Int

We can now execute this raw query:

let lft = 10 :: Int
    rgt = 20 :: Int
    width = rgt - lft
 in [sqlQQ|
      DELETE FROM ^{Category} WHERE {CategoryLft} BETWEEN {rgt};
      UPDATE category SET {CategoryRgt} = {CategoryRgt} - #{width} WHERE {CategoryRgt} > #{rgt};
      UPDATE category SET {CategoryLft} = {CategoryLft} - {rgt};
    |]

^{TableName} looks up the table's name and escapes it, @{ColumnName} looks up the column's name and properly escapes it and #{value} inserts the value via the usual parameter substitution mechanism.

Since: 2.7.2

executeQQ :: QuasiQuoter

Analoguous to rawExecute

Since: 2.7.2

deleteWhereCount :: (PersistEntity val, MonadIO m, PersistEntityBackend val ~ SqlBackend, IsSqlBackend backend) => [Filter val] -> ReaderT backend m Int64

Same as deleteWhere, but returns the number of rows affected.

Since: 1.1.5

updateWhereCount :: (PersistEntity val, MonadIO m, SqlBackend ~ PersistEntityBackend val, IsSqlBackend backend) => [Filter val] -> [Update val] -> ReaderT backend m Int64

Same as updateWhere, but returns the number of rows affected.

Since: 1.1.5

transactionSave :: MonadIO m => ReaderT SqlBackend m ()

Commit the current transaction and begin a new one.

Since: 1.2.0

transactionUndo :: MonadIO m => ReaderT SqlBackend m ()

Roll back the current transaction and begin a new one.

Since: 1.2.0

getStmtConn :: SqlBackend -> Text -> IO Statement

Internal

mkColumns :: [EntityDef] -> EntityDef -> ([Column], [UniqueDef], [ForeignDef])

Create the list of columns for the given entity.

defaultAttribute :: [Attr] -> Maybe Text

decorateSQLWithLimitOffset :: Text -> (Int, Int) -> Bool -> Text -> Text

Generates sql for limit and offset for postgres, sqlite and mysql.