| Safe Haskell | None |
|---|---|
| Language | Haskell2010 |
Database.Persist.Sql
Synopsis
- newtype Single a = Single {
- unSingle :: a
- type ConnectionPool = Pool SqlBackend
- type Migration = WriterT [Text] (WriterT CautiousMigration (ReaderT SqlBackend IO)) ()
- type CautiousMigration = [(Bool, Sql)]
- type Sql = Text
- type SqlPersistM = SqlPersistT (NoLoggingT (ResourceT IO))
- type SqlPersistT = ReaderT SqlBackend
- data PersistentSqlException
- data Column = Column {}
- data SqlBackend = SqlBackend {
- connPrepare :: Text -> IO Statement
- connInsertSql :: EntityDef -> [PersistValue] -> InsertSqlResult
- connInsertManySql :: Maybe (EntityDef -> [[PersistValue]] -> InsertSqlResult)
- connUpsertSql :: Maybe (EntityDef -> NonEmpty UniqueDef -> Text -> Text)
- connPutManySql :: Maybe (EntityDef -> Int -> Text)
- connStmtMap :: IORef (Map Text Statement)
- connClose :: IO ()
- connMigrateSql :: [EntityDef] -> (Text -> IO Statement) -> EntityDef -> IO (Either [Text] [(Bool, Text)])
- connBegin :: (Text -> IO Statement) -> Maybe IsolationLevel -> 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
- connRepsertManySql :: Maybe (EntityDef -> Int -> Text)
- newtype SqlReadBackend = SqlReadBackend {}
- newtype SqlWriteBackend = SqlWriteBackend {}
- data Statement = Statement {
- 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
- readToUnknown :: Monad m => ReaderT SqlReadBackend m a -> ReaderT SqlBackend m a
- readToWrite :: Monad m => ReaderT SqlReadBackend m a -> ReaderT SqlWriteBackend m a
- writeToUnknown :: Monad m => ReaderT SqlWriteBackend m a -> ReaderT SqlBackend m a
- type SqlBackendCanRead backend = (BackendCompatible SqlBackend backend, PersistQueryRead backend, PersistStoreRead backend, PersistUniqueRead backend)
- type SqlBackendCanWrite backend = (SqlBackendCanRead backend, PersistQueryWrite backend, PersistStoreWrite backend, PersistUniqueWrite backend)
- type SqlReadT m a = forall backend. SqlBackendCanRead backend => ReaderT backend m a
- type SqlWriteT m a = forall backend. SqlBackendCanWrite backend => ReaderT backend m a
- type IsSqlBackend backend = (IsPersistBackend backend, BaseBackend backend ~ SqlBackend)
- class RawSql a where
- rawSqlCols :: (DBName -> Text) -> a -> (Int, [Text])
- rawSqlColCountReason :: a -> String
- rawSqlProcessRow :: [PersistValue] -> Either Text a
- class PersistField a => PersistFieldSql a where
- runSqlPool :: (MonadUnliftIO m, BackendCompatible SqlBackend backend) => ReaderT backend m a -> Pool backend -> m a
- runSqlPoolWithIsolation :: (MonadUnliftIO m, BackendCompatible SqlBackend backend) => ReaderT backend m a -> Pool backend -> IsolationLevel -> m a
- runSqlConn :: (MonadUnliftIO m, BackendCompatible SqlBackend backend) => ReaderT backend m a -> backend -> m a
- runSqlConnWithIsolation :: (MonadUnliftIO m, BackendCompatible SqlBackend backend) => ReaderT backend m a -> backend -> IsolationLevel -> m a
- runSqlPersistM :: BackendCompatible SqlBackend backend => ReaderT backend (NoLoggingT (ResourceT IO)) a -> backend -> IO a
- runSqlPersistMPool :: BackendCompatible SqlBackend backend => ReaderT backend (NoLoggingT (ResourceT IO)) a -> Pool backend -> IO a
- liftSqlPersistMPool :: (MonadIO m, BackendCompatible SqlBackend backend) => ReaderT backend (NoLoggingT (ResourceT IO)) a -> Pool backend -> m a
- withSqlPool :: (MonadLogger m, MonadUnliftIO m, BackendCompatible SqlBackend backend) => (LogFunc -> IO backend) -> Int -> (Pool backend -> m a) -> m a
- createSqlPool :: forall m backend. (MonadLogger m, MonadUnliftIO m, BackendCompatible SqlBackend backend) => (LogFunc -> IO backend) -> Int -> m (Pool backend)
- askLogFunc :: forall m. (MonadUnliftIO m, MonadLogger m) => m LogFunc
- withSqlConn :: (MonadUnliftIO m, MonadLogger m, BackendCompatible SqlBackend backend) => (LogFunc -> IO backend) -> (backend -> m a) -> m a
- close' :: BackendCompatible SqlBackend backend => backend -> IO ()
- parseMigration :: MonadIO m => Migration -> ReaderT SqlBackend m (Either [Text] CautiousMigration)
- parseMigration' :: MonadIO m => Migration -> ReaderT SqlBackend m CautiousMigration
- printMigration :: MonadIO m => Migration -> ReaderT SqlBackend m ()
- showMigration :: MonadIO m => Migration -> ReaderT SqlBackend m [Text]
- getMigration :: MonadIO m => Migration -> ReaderT SqlBackend m [Sql]
- runMigration :: MonadIO m => Migration -> ReaderT SqlBackend m ()
- runMigrationQuiet :: MonadIO m => Migration -> ReaderT SqlBackend m [Text]
- runMigrationSilent :: MonadUnliftIO m => Migration -> ReaderT SqlBackend m [Text]
- runMigrationUnsafe :: MonadIO m => Migration -> ReaderT SqlBackend m ()
- runMigrationUnsafeQuiet :: MonadIO m => Migration -> ReaderT SqlBackend m [Text]
- migrate :: [EntityDef] -> EntityDef -> Migration
- reportErrors :: [Text] -> Migration
- reportError :: Text -> Migration
- addMigrations :: CautiousMigration -> Migration
- addMigration :: Bool -> Sql -> Migration
- module Database.Persist
- withRawQuery :: MonadIO m => Text -> [PersistValue] -> ConduitM [PersistValue] Void IO a -> ReaderT SqlBackend m a
- data family BackendKey backend
- 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, BackendCompatible SqlBackend backend, Monad m) => EntityField record typ -> ReaderT backend m Text
- getTableName :: forall record m backend. (PersistEntity record, BackendCompatible SqlBackend backend, Monad m) => record -> ReaderT backend m Text
- tableDBName :: PersistEntity record => record -> DBName
- fieldDBName :: forall record typ. PersistEntity record => EntityField record typ -> DBName
- rawQuery :: (MonadResource m, MonadReader env m, BackendCompatible SqlBackend env) => Text -> [PersistValue] -> ConduitM () [PersistValue] m ()
- rawQueryRes :: (MonadIO m1, MonadIO m2, BackendCompatible SqlBackend env) => Text -> [PersistValue] -> ReaderT env m1 (Acquire (ConduitM () [PersistValue] m2 ()))
- rawExecute :: (MonadIO m, BackendCompatible SqlBackend backend) => Text -> [PersistValue] -> ReaderT backend m ()
- rawExecuteCount :: (MonadIO m, BackendCompatible SqlBackend backend) => Text -> [PersistValue] -> ReaderT backend m Int64
- rawSql :: (RawSql a, MonadIO m, BackendCompatible SqlBackend backend) => Text -> [PersistValue] -> ReaderT backend m [a]
- deleteWhereCount :: (PersistEntity val, MonadIO m, PersistEntityBackend val ~ SqlBackend, BackendCompatible SqlBackend backend) => [Filter val] -> ReaderT backend m Int64
- updateWhereCount :: (PersistEntity val, MonadIO m, SqlBackend ~ PersistEntityBackend val, BackendCompatible SqlBackend backend) => [Filter val] -> [Update val] -> ReaderT backend m Int64
- transactionSave :: MonadIO m => ReaderT SqlBackend m ()
- transactionSaveWithIsolation :: MonadIO m => IsolationLevel -> ReaderT SqlBackend m ()
- transactionUndo :: MonadIO m => ReaderT SqlBackend m ()
- transactionUndoWithIsolation :: MonadIO m => IsolationLevel -> ReaderT SqlBackend m ()
- data IsolationLevel
- getStmtConn :: SqlBackend -> Text -> IO Statement
- mkColumns :: [EntityDef] -> EntityDef -> ([Column], [UniqueDef], [ForeignDef])
- defaultAttribute :: [Attr] -> Maybe Text
- decorateSQLWithLimitOffset :: Text -> (Int, Int) -> Bool -> Text -> Text
Documentation
A single column (see rawSql). Any PersistField may be
used here, including PersistValue (which does not do any
processing).
Instances
| Eq a => Eq (Single a) Source # | |
| Ord a => Ord (Single a) Source # | |
Defined in Database.Persist.Sql.Types | |
| Read a => Read (Single a) Source # | |
| Show a => Show (Single a) Source # | |
| PersistField a => RawSql (Single a) Source # | |
Defined in Database.Persist.Sql.Class Methods rawSqlCols :: (DBName -> Text) -> Single a -> (Int, [Text]) Source # rawSqlColCountReason :: Single a -> String Source # rawSqlProcessRow :: [PersistValue] -> Either Text (Single a) Source # | |
type ConnectionPool = Pool SqlBackend Source #
type Migration = WriterT [Text] (WriterT CautiousMigration (ReaderT SqlBackend IO)) () Source #
A Migration is a four level monad stack consisting of:
WriterT[Text]WriterTCautiousMigrationReaderTSqlBackendSqlPersistTtransformer for database interop.IO
type CautiousMigration = [(Bool, Sql)] Source #
type SqlPersistM = SqlPersistT (NoLoggingT (ResourceT IO)) Source #
type SqlPersistT = ReaderT SqlBackend Source #
data PersistentSqlException Source #
Constructors
| StatementAlreadyFinalized Text | |
| Couldn'tGetSQLConnection |
Instances
| Show PersistentSqlException Source # | |
Defined in Database.Persist.Sql.Types Methods showsPrec :: Int -> PersistentSqlException -> ShowS # show :: PersistentSqlException -> String # showList :: [PersistentSqlException] -> ShowS # | |
| Exception PersistentSqlException Source # | |
Defined in Database.Persist.Sql.Types | |
data SqlBackend Source #
Constructors
| SqlBackend | |
Fields
| |
Instances
newtype SqlReadBackend Source #
An SQL backend which can only handle read queries
The constructor was exposed in 2.10.0.
Constructors
| SqlReadBackend | |
Fields | |
Instances
newtype SqlWriteBackend Source #
An SQL backend which can handle read or write queries
The constructor was exposed in 2.10.0
Constructors
| SqlWriteBackend | |
Fields | |
Instances
Constructors
| Statement | |
Fields
| |
data InsertSqlResult Source #
Constructors
| ISRSingle Text | |
| ISRInsertGet Text Text | |
| ISRManyKeys Text [PersistValue] |
readToUnknown :: Monad m => ReaderT SqlReadBackend m a -> ReaderT SqlBackend m a Source #
Useful for running a read query against a backend with unknown capabilities.
readToWrite :: Monad m => ReaderT SqlReadBackend m a -> ReaderT SqlWriteBackend m a Source #
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 Source #
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) Source #
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) Source #
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 Source #
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 Source #
Like SqlPersistT but compatible with any SQL backend which can handle read and write queries.
type IsSqlBackend backend = (IsPersistBackend backend, BaseBackend backend ~ SqlBackend) Source #
A backend which is a wrapper around SqlBackend.
Class for data types that may be retrived from a rawSql
query.
Methods
rawSqlCols :: (DBName -> Text) -> a -> (Int, [Text]) Source #
Number of columns that this data type needs and the list
of substitutions for SELECT placeholders ??.
rawSqlColCountReason :: a -> String Source #
A string telling the user why the column count is what it is.
rawSqlProcessRow :: [PersistValue] -> Either Text a Source #
Transform a row of the result into the data type.
Instances
class PersistField a => PersistFieldSql a where Source #
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) instancePersistFieldSwitch wheretoPersistValues = case s of On ->PersistBoolTrue Off ->PersistBoolFalsefromPersistValue(PersistBoolb) = if b thenRightOn elseRightOfffromPersistValuex = Left $ "File.hs: When trying to deserialize a Switch: expected PersistBool, received: " <> T.pack (show x) instancePersistFieldSqlSwitch wheresqlType_ =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 instancePersistFieldUUID wheretoPersistValue=PersistDbSpecific. toASCIIBytesfromPersistValue(PersistDbSpecificuuid) = case fromASCIIBytes uuid ofNothing->Left$ "Model/CustomTypes.hs: Failed to deserialize a UUID; received: " <> T.pack (show uuid)Justuuid' ->Rightuuid'fromPersistValuex = Left $ "File.hs: When trying to deserialize a UUID: expected PersistDbSpecific, received: "-- > <> T.pack (show x) instancePersistFieldSqlUUID wheresqlType_ =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}$');instancePersistFieldSQLSSN wheresqlType_ =SqlOther"ssn"
CREATE TYPE rainbow_color AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');instancePersistFieldSQLRainbowColor wheresqlType_ =SqlOther"rainbow_color"
Instances
runSqlPool :: (MonadUnliftIO m, BackendCompatible SqlBackend backend) => ReaderT backend m a -> Pool backend -> m a Source #
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.
runSqlPoolWithIsolation :: (MonadUnliftIO m, BackendCompatible SqlBackend backend) => ReaderT backend m a -> Pool backend -> IsolationLevel -> m a Source #
Like runSqlPool, but supports specifying an isolation level.
Since: 2.9.0
runSqlConn :: (MonadUnliftIO m, BackendCompatible SqlBackend backend) => ReaderT backend m a -> backend -> m a Source #
runSqlConnWithIsolation :: (MonadUnliftIO m, BackendCompatible SqlBackend backend) => ReaderT backend m a -> backend -> IsolationLevel -> m a Source #
Like runSqlConn, but supports specifying an isolation level.
Since: 2.9.0
runSqlPersistM :: BackendCompatible SqlBackend backend => ReaderT backend (NoLoggingT (ResourceT IO)) a -> backend -> IO a Source #
runSqlPersistMPool :: BackendCompatible SqlBackend backend => ReaderT backend (NoLoggingT (ResourceT IO)) a -> Pool backend -> IO a Source #
liftSqlPersistMPool :: (MonadIO m, BackendCompatible SqlBackend backend) => ReaderT backend (NoLoggingT (ResourceT IO)) a -> Pool backend -> m a Source #
Arguments
| :: (MonadLogger m, MonadUnliftIO m, BackendCompatible SqlBackend backend) | |
| => (LogFunc -> IO backend) | create a new connection |
| -> Int | connection count |
| -> (Pool backend -> m a) | |
| -> m a |
createSqlPool :: forall m backend. (MonadLogger m, MonadUnliftIO m, BackendCompatible SqlBackend backend) => (LogFunc -> IO backend) -> Int -> m (Pool backend) Source #
askLogFunc :: forall m. (MonadUnliftIO m, MonadLogger m) => m LogFunc Source #
withSqlConn :: (MonadUnliftIO m, MonadLogger m, BackendCompatible SqlBackend backend) => (LogFunc -> IO backend) -> (backend -> m a) -> m a Source #
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}}]close' :: BackendCompatible SqlBackend backend => backend -> IO () Source #
parseMigration :: MonadIO m => Migration -> ReaderT SqlBackend m (Either [Text] CautiousMigration) Source #
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 Source #
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 () Source #
Prints a migration.
showMigration :: MonadIO m => Migration -> ReaderT SqlBackend m [Text] Source #
getMigration :: MonadIO m => Migration -> ReaderT SqlBackend m [Sql] Source #
runMigration :: MonadIO m => Migration -> ReaderT SqlBackend m () Source #
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.
runMigrationQuiet :: MonadIO m => Migration -> ReaderT SqlBackend m [Text] Source #
Same as runMigration, but does not report the individual migrations on
stderr. Instead it returns a list of the executed SQL commands.
This is a safer/more robust alternative to runMigrationSilent, but may be
less silent for some persistent implementations, most notably
persistent-postgresql
Since: 2.10.2
runMigrationSilent :: MonadUnliftIO m => Migration -> ReaderT SqlBackend m [Text] Source #
Same as runMigration, but returns a list of the SQL commands executed
instead of printing them to stderr.
This function silences the migration by remapping stderr. As a result, it
is not thread-safe and can clobber output from other parts of the program.
This implementation method was chosen to also silence postgresql migration
output on stderr, but is not recommended!
runMigrationUnsafe :: MonadIO m => Migration -> ReaderT SqlBackend m () Source #
Like runMigration, but this will perform the unsafe database
migrations instead of erroring out.
runMigrationUnsafeQuiet :: MonadIO m => Migration -> ReaderT SqlBackend m [Text] Source #
Same as runMigrationUnsafe, but returns a list of the SQL commands
executed instead of printing them to stderr.
Since: 2.10.2
Utilities for constructing migrations
addMigrations :: CautiousMigration -> Migration Source #
Add a CautiousMigration (aka a [() to the
migration plan.Bool, Text)]
Since: 2.9.2
Arguments
| :: Bool | Is the migration safe to run? (eg a non-destructive and idempotent update on the schema) |
| -> Sql | A |
| -> Migration |
Add a migration to the migration plan.
Since: 2.9.2
module Database.Persist
withRawQuery :: MonadIO m => Text -> [PersistValue] -> ConduitM [PersistValue] Void IO a -> ReaderT SqlBackend m a Source #
data family BackendKey backend Source #
Instances
toSqlKey :: ToBackendKey SqlBackend record => Int64 -> Key record Source #
fromSqlKey :: ToBackendKey SqlBackend record => Key record -> Int64 Source #
getFieldName :: forall record typ m backend. (PersistEntity record, PersistEntityBackend record ~ SqlBackend, BackendCompatible SqlBackend backend, Monad m) => EntityField record typ -> ReaderT backend m Text Source #
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, BackendCompatible SqlBackend backend, Monad m) => record -> ReaderT backend m Text Source #
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 Source #
useful for a backend to implement tableName by adding escaping
fieldDBName :: forall record typ. PersistEntity record => EntityField record typ -> DBName Source #
useful for a backend to implement fieldName by adding escaping
rawQuery :: (MonadResource m, MonadReader env m, BackendCompatible SqlBackend env) => Text -> [PersistValue] -> ConduitM () [PersistValue] m () Source #
rawQueryRes :: (MonadIO m1, MonadIO m2, BackendCompatible SqlBackend env) => Text -> [PersistValue] -> ReaderT env m1 (Acquire (ConduitM () [PersistValue] m2 ())) Source #
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
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.
Arguments
| :: (RawSql a, MonadIO m, BackendCompatible SqlBackend backend) | |
| => Text | SQL statement, possibly with placeholders. |
| -> [PersistValue] | Values to fill the placeholders. |
| -> ReaderT backend m [a] |
Execute a raw SQL statement and return its results as a
list. If you do not expect a return value, use of
rawExecute is recommended.
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)
deleteWhereCount :: (PersistEntity val, MonadIO m, PersistEntityBackend val ~ SqlBackend, BackendCompatible SqlBackend backend) => [Filter val] -> ReaderT backend m Int64 Source #
Same as deleteWhere, but returns the number of rows affected.
Since: 1.1.5
updateWhereCount :: (PersistEntity val, MonadIO m, SqlBackend ~ PersistEntityBackend val, BackendCompatible SqlBackend backend) => [Filter val] -> [Update val] -> ReaderT backend m Int64 Source #
Same as updateWhere, but returns the number of rows affected.
Since: 1.1.5
transactionSave :: MonadIO m => ReaderT SqlBackend m () Source #
Commit the current transaction and begin a new one.
This is used when a transaction commit is required within the context of runSqlConn
(which brackets its provided action with a transaction begin/commit pair).
Since: 1.2.0
transactionSaveWithIsolation :: MonadIO m => IsolationLevel -> ReaderT SqlBackend m () Source #
Commit the current transaction and begin a new one with the specified isolation level.
Since: 2.9.0
transactionUndo :: MonadIO m => ReaderT SqlBackend m () Source #
Roll back the current transaction and begin a new one.
This rolls back to the state of the last call to transactionSave or the enclosing
runSqlConn call.
Since: 1.2.0
transactionUndoWithIsolation :: MonadIO m => IsolationLevel -> ReaderT SqlBackend m () Source #
Roll back the current transaction and begin a new one with the specified isolation level.
Since: 2.9.0
data IsolationLevel Source #
Please refer to the documentation for the database in question for a full overview of the semantics of the varying isloation levels
Constructors
| ReadUncommitted | |
| ReadCommitted | |
| RepeatableRead | |
| Serializable |
Instances
getStmtConn :: SqlBackend -> Text -> IO Statement Source #