Deprecated synonym for SqlBackend.

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

An SQL backend which can only handle read queries

An SQL backend which can handle read or write queries

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

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

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

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

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

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

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

A backend which is a wrapper around SqlBackend.

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

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

Examples

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

import qualified Data.UUID as UUID
instance PersistField UUID where
  toPersistValue = PersistDbSpecific . toASCIIBytes
  fromPersistValue (PersistDbSpecific 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 PersistDbSpecific, received: "-- >  <> T.pack (show x)

instance PersistFieldSql UUID where
  sqlType _ = SqlOther "uuid"

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"

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.

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

Since: 2.0.0

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

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

Prints a migration.

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

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

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.

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

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

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.

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 PeristEntity 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

useful for a backend to implement fieldName by adding escaping

Execute a raw SQL statement

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

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)

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

Analoguous to rawExecute

Since: 2.7.2

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

Since: 1.1.5

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

Since: 1.1.5

Commit the current transaction and begin a new one.

Since: 1.2.0

Roll back the current transaction and begin a new one.

Since: 1.2.0

Create the list of columns for the given entity.

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