| Safe Haskell | None |
|---|---|
| Language | Haskell98 |
Snap.Snaplet.PostgresqlSimple
Description
This snaplet makes it simple to use a PostgreSQL database from your Snap application and is based on the excellent postgresql-simple library (http://hackage.haskell.org/package/postgresql-simple) by Leon Smith (adapted from Bryan O'Sullivan's mysql-simple). Now, adding a database to your web app takes just two simple steps.
First, include this snaplet in your application's state.
data App = App
{ ... -- Other state needed in your app
, _db :: Snaplet Postgres
}Next, call the pgsInit from your application's initializer.
appInit = makeSnaplet ... $ do
...
d <- nestSnaplet "db" db pgsInit
return $ App ... dNow you can use any of the postgresql-simple wrapper functions defined in this module anywhere in your application handlers. For instance:
postHandler :: Handler App App ()
postHandler = do
posts <- with db $ query_ "select * from blog_post"
...Optionally, if you find yourself doing many database queries, you can eliminate some of the boilerplate by defining a HasPostgres instance for your application.
instance HasPostgres (Handler b App) where getPostgresState = with db get setLocalPostgresState s = local (set (db . snapletValue) s)
With this code, our postHandler example no longer requires the with function:
postHandler :: Handler App App ()
postHandler = do
posts <- query_ "select * from blog_post"
...If you have code that runs multiple queries but you want to make sure that you only use one database connection then you can use the withPG function, like so:
postHandler :: Handler App App ()
postHandler = withPG $ do
posts <- query_ "select * from blog_post"
links <- query_ "select * from links"
...The first time you run an application with the postgresql-simple snaplet, a
configuration file devel.cfg is created in the snaplets/postgresql-simple
directory underneath your project root. It specifies how to connect to your
PostgreSQL server and what user, password, and database to use. Edit this
file and modify the values appropriately and you'll be off and running.
If you want to have out-of-the-box authentication, look at the documentation for the Snap.Snaplet.Auth.Backends.PostgresqlSimple module.
- data Postgres
- class (MonadIO m, MonadBaseControl IO m) => HasPostgres m where
- data PGSConfig = PGSConfig {}
- pgsDefaultConfig :: ByteString -> PGSConfig
- mkPGSConfig :: MonadIO m => Config -> m PGSConfig
- pgsInit :: SnapletInit b Postgres
- pgsInit' :: PGSConfig -> SnapletInit b Postgres
- getConnectionString :: Config -> IO ByteString
- withPG :: HasPostgres m => m b -> m b
- data Connection :: *
- liftPG :: HasPostgres m => (Connection -> m a) -> m a
- liftPG' :: HasPostgres m => (Connection -> IO b) -> m b
- query :: (HasPostgres m, ToRow q, FromRow r) => Query -> q -> m [r]
- query_ :: (HasPostgres m, FromRow r) => Query -> m [r]
- fold :: (HasPostgres m, FromRow row, ToRow params) => Query -> params -> b -> (b -> row -> IO b) -> m b
- foldWithOptions :: (HasPostgres m, FromRow row, ToRow params) => FoldOptions -> Query -> params -> b -> (b -> row -> IO b) -> m b
- fold_ :: (HasPostgres m, FromRow row) => Query -> b -> (b -> row -> IO b) -> m b
- foldWithOptions_ :: (HasPostgres m, FromRow row) => FoldOptions -> Query -> b -> (b -> row -> IO b) -> m b
- forEach :: (HasPostgres m, FromRow r, ToRow q) => Query -> q -> (r -> IO ()) -> m ()
- forEach_ :: (HasPostgres m, FromRow r) => Query -> (r -> IO ()) -> m ()
- execute :: (HasPostgres m, ToRow q) => Query -> q -> m Int64
- execute_ :: HasPostgres m => Query -> m Int64
- executeMany :: (HasPostgres m, ToRow q) => Query -> [q] -> m Int64
- returning :: (HasPostgres m, ToRow q, FromRow r) => Query -> [q] -> m [r]
- withTransaction :: HasPostgres m => m a -> m a
- withTransactionLevel :: HasPostgres m => IsolationLevel -> m a -> m a
- withTransactionMode :: HasPostgres m => TransactionMode -> m a -> m a
- formatMany :: (ToRow q, HasPostgres m) => Query -> [q] -> m ByteString
- formatQuery :: (ToRow q, HasPostgres m) => Query -> q -> m ByteString
- data Query :: *
- newtype In a :: * -> * = In a
- newtype Binary a :: * -> * = Binary {
- fromBinary :: a
- newtype Only a :: * -> * = Only {
- fromOnly :: a
- data SqlError :: * = SqlError {}
- data FormatError :: * = FormatError {
- fmtMessage :: String
- fmtQuery :: Query
- fmtParams :: [ByteString]
- data QueryError :: * = QueryError {}
- data ResultError :: *
- = Incompatible { }
- | UnexpectedNull { }
- | ConversionFailed { }
- data TransactionMode :: * = TransactionMode {}
- data IsolationLevel :: *
- data ReadWriteMode :: *
- begin :: Connection -> IO ()
- beginLevel :: IsolationLevel -> Connection -> IO ()
- beginMode :: TransactionMode -> Connection -> IO ()
- rollback :: Connection -> IO ()
- commit :: Connection -> IO ()
- data h :. t :: * -> * -> * = h :. t
- class ToRow a where
- class FromRow a where
- defaultConnectInfo :: ConnectInfo
- defaultTransactionMode :: TransactionMode
- defaultIsolationLevel :: IsolationLevel
- defaultReadWriteMode :: ReadWriteMode
- field :: FromField a => RowParser a
The Snaplet
The state for the postgresql-simple snaplet. To use it in your app include this in your application state and use pgsInit to initialize it.
Constructors
| PostgresPool (Pool Connection) | |
| PostgresConn Connection |
class (MonadIO m, MonadBaseControl IO m) => HasPostgres m where Source #
Instantiate this typeclass on 'Handler b YourAppState' so this snaplet
can find the connection source. If you need to have multiple instances of
the postgres snaplet in your application, then don't provide this instance
and leverage the default instance by using "with dbLens" in front of calls
to snaplet-postgresql-simple functions.
Minimal complete definition
Methods
getPostgresState :: m Postgres Source #
setLocalPostgresState :: Postgres -> m a -> m a Source #
Instances
| HasPostgres m => HasPostgres (ListT m) Source # | |
| HasPostgres m => HasPostgres (MaybeT m) Source # | |
| HasPostgres m => HasPostgres (IdentityT * m) Source # | |
| HasPostgres m => HasPostgres (StateT w m) Source # | |
| HasPostgres m => HasPostgres (StateT w m) Source # | |
| (Monoid w, HasPostgres m) => HasPostgres (WriterT w m) Source # | |
| (Monoid w, HasPostgres m) => HasPostgres (WriterT w m) Source # | |
| HasPostgres m => HasPostgres (ReaderT * r m) Source # | |
| (Monoid w, HasPostgres m) => HasPostgres (RWST r w s m) Source # | |
| (Monoid w, HasPostgres m) => HasPostgres (RWST r w s m) Source # | |
Data type holding all the snaplet's config information.
Constructors
| PGSConfig | |
Fields
| |
Arguments
| :: ByteString | A connection string such as "host=localhost port=5432 dbname=mydb" |
| -> PGSConfig |
Returns a config object with default values and the specified connection string.
mkPGSConfig :: MonadIO m => Config -> m PGSConfig Source #
Builds a PGSConfig object from a configurator Config object. This function uses getConnectionString to construct the connection string. The rest of the PGSConfig fields are obtained from "numStripes", "idleTime", and "maxResourcesPerStripe".
pgsInit :: SnapletInit b Postgres Source #
Initialize the snaplet
pgsInit' :: PGSConfig -> SnapletInit b Postgres Source #
Initialize the snaplet using a specific configuration.
getConnectionString :: Config -> IO ByteString Source #
Produce a connection string from a config
withPG :: HasPostgres m => m b -> m b Source #
Function that reserves a single connection for the duration of the given action. Nested calls to withPG will only reserve one connection. For example, the following code calls withPG twice in a nested way yet only results in a single connection being reserved:
myHandler = withPG $ do queryTheDatabase commonDatabaseMethod commonDatabaseMethod = withPG $ do moreDatabaseActions evenMoreDatabaseActions
This is useful in a practical setting because you may often find yourself in a situation where you have common code (that requires a database connection) that you wish to call from other blocks of code that may require a database connection and you still want to make sure that you are only using one connection through all of your nested methods.
data Connection :: * #
Instances
liftPG :: HasPostgres m => (Connection -> m a) -> m a Source #
Convenience function for executing a function that needs a database connection.
liftPG' :: HasPostgres m => (Connection -> IO b) -> m b Source #
Convenience function for executing a function that needs a database connection specialized to IO.
Wrappers and re-exports
fold :: (HasPostgres m, FromRow row, ToRow params) => Query -> params -> b -> (b -> row -> IO b) -> m b Source #
foldWithOptions :: (HasPostgres m, FromRow row, ToRow params) => FoldOptions -> Query -> params -> b -> (b -> row -> IO b) -> m b Source #
foldWithOptions_ :: (HasPostgres m, FromRow row) => FoldOptions -> Query -> b -> (b -> row -> IO b) -> m b Source #
executeMany :: (HasPostgres m, ToRow q) => Query -> [q] -> m Int64 Source #
withTransaction :: HasPostgres m => m a -> m a Source #
Be careful that you do not call Snap's finishWith function anywhere
inside the function that you pass to withTransaction. Doing so has been
known to cause DB connection leaks.
withTransactionLevel :: HasPostgres m => IsolationLevel -> m a -> m a Source #
Be careful that you do not call Snap's finishWith function anywhere
inside the function that you pass to withTransactionLevel. Doing so has
been known to cause DB connection leaks.
withTransactionMode :: HasPostgres m => TransactionMode -> m a -> m a Source #
Be careful that you do not call Snap's finishWith function anywhere
inside the function that you pass to withTransactionMode. Doing so has
been known to cause DB connection leaks.
formatMany :: (ToRow q, HasPostgres m) => Query -> [q] -> m ByteString Source #
formatQuery :: (ToRow q, HasPostgres m) => Query -> q -> m ByteString Source #
A query string. This type is intended to make it difficult to construct a SQL query by concatenating string fragments, as that is an extremely common way to accidentally introduce SQL injection vulnerabilities into an application.
This type is an instance of IsString, so the easiest way to
construct a query is to enable the OverloadedStrings language
extension and then simply write the query in double quotes.
{-# LANGUAGE OverloadedStrings #-}
import Database.PostgreSQL.Simple
q :: Query
q = "select ?"The underlying type is a ByteString, and literal Haskell strings
that contain Unicode characters will be correctly transformed to
UTF-8.
Wrap a list of values for use in an IN clause. Replaces a
single "?" character with a parenthesized list of rendered
values.
Example:
query c "select * from whatever where id in ?" (Only (In [3,4,5]))
Note that In [] expands to (null), which works as expected in
the query above, but evaluates to the logical null value on every
row instead of TRUE. This means that changing the query above
to ... id NOT in ? and supplying the empty list as the parameter
returns zero rows, instead of all of them as one would expect.
Since postgresql doesn't seem to provide a syntax for actually specifying an empty list, which could solve this completely, there are two workarounds particularly worth mentioning, namely:
Use postgresql-simple's
Valuestype instead, which can handle the empty case correctly. Note however that while specifying the postgresql type"int4"is mandatory in the empty case, specifying the haskell typeValues (Only Int)would not normally be needed in realistic use cases.query c "select * from whatever where id not in ?" (Only (Values ["int4"] [] :: Values (Only Int)))Use sql's
COALESCEoperator to turn a logicalnullinto the correct boolean. Note however that the correct boolean depends on the use case:query c "select * from whatever where coalesce(id NOT in ?, TRUE)" (Only (In [] :: In [Int]))query c "select * from whatever where coalesce(id IN ?, FALSE)" (Only (In [] :: In [Int]))Note that at as of PostgreSQL 9.4, the query planner cannot see inside the
COALESCEoperator, so if you have an index onidthen you probably don't want to write the last example withCOALESCE, which would result in a table scan. There are further caveats ifidcan be null or you want null treated sensibly as a component ofINorNOT IN.
Constructors
| In a |
Wrap binary data for use as a bytea value.
Constructors
| Binary | |
Fields
| |
A single-value "collection".
This is useful if you need to supply a single parameter to a SQL query, or extract a single column from a SQL result.
Parameter example:
query c "select x from scores where x > ?" (Only (42::Int))Result example:
xs <- query_ c "select id from users"
forM_ xs $ \(Only id) -> {- ... -}Constructors
| SqlError | |
Fields | |
data FormatError :: * #
Exception thrown if a Query could not be formatted correctly.
This may occur if the number of '?' characters in the query
string does not match the number of parameters provided.
Constructors
| FormatError | |
Fields
| |
Instances
data QueryError :: * #
Exception thrown if query is used to perform an INSERT-like
operation, or execute is used to perform a SELECT-like operation.
Constructors
| QueryError | |
Instances
data ResultError :: * #
Exception thrown if conversion from a SQL value to a Haskell value fails.
Constructors
| Incompatible | The SQL and Haskell types are not compatible. |
Fields
| |
| UnexpectedNull | A SQL |
Fields
| |
| ConversionFailed | The SQL value could not be parsed, or could not be represented as a valid Haskell value, or an unexpected low-level error occurred (e.g. mismatch between metadata and actual data in a row). |
Fields
| |
Instances
data IsolationLevel :: * #
Of the four isolation levels defined by the SQL standard,
these are the three levels distinguished by PostgreSQL as of version 9.0.
See https://www.postgresql.org/docs/9.5/static/transaction-iso.html
for more information. Note that prior to PostgreSQL 9.0, RepeatableRead
was equivalent to Serializable.
Constructors
| DefaultIsolationLevel | the isolation level will be taken from
PostgreSQL's per-connection
|
| ReadCommitted | |
| RepeatableRead | |
| Serializable |
data ReadWriteMode :: * #
Constructors
| DefaultReadWriteMode | the read-write mode will be taken from
PostgreSQL's per-connection
|
| ReadWrite | |
| ReadOnly |
begin :: Connection -> IO () #
Begin a transaction.
beginLevel :: IsolationLevel -> Connection -> IO () #
Begin a transaction with a given isolation level
beginMode :: TransactionMode -> Connection -> IO () #
Begin a transaction with a given transaction mode
rollback :: Connection -> IO () #
Rollback a transaction.
commit :: Connection -> IO () #
Commit a transaction.
data h :. t :: * -> * -> * infixr 3 #
A composite type to parse your custom data structures without having to define dummy newtype wrappers every time.
instance FromRow MyData where ...
instance FromRow MyData2 where ...
then I can do the following for free:
res <- query' c "..."
forM res $ \(MyData{..} :. MyData2{..}) -> do
....
Constructors
| h :. t infixr 3 |
A collection type that can be turned into a list of rendering
Actions.
Instances should use the toField method of the ToField class
to perform conversion of each element of the collection.
Instances
| ToRow () | |
| ToField a => ToRow [a] | |
| ToField a => ToRow (Only a) | |
| (ToField a, ToField b) => ToRow (a, b) | |
| (ToRow a, ToRow b) => ToRow ((:.) a b) | |
| (ToField a, ToField b, ToField c) => ToRow (a, b, c) | |
| (ToField a, ToField b, ToField c, ToField d) => ToRow (a, b, c, d) | |
| (ToField a, ToField b, ToField c, ToField d, ToField e) => ToRow (a, b, c, d, e) | |
| (ToField a, ToField b, ToField c, ToField d, ToField e, ToField f) => ToRow (a, b, c, d, e, f) | |
| (ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g) => ToRow (a, b, c, d, e, f, g) | |
| (ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h) => ToRow (a, b, c, d, e, f, g, h) | |
| (ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i) => ToRow (a, b, c, d, e, f, g, h, i) | |
| (ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j) => ToRow (a, b, c, d, e, f, g, h, i, j) | |
A collection type that can be converted from a sequence of fields. Instances are provided for tuples up to 10 elements and lists of any length.
Note that instances can be defined outside of postgresql-simple, which is often useful. For example, here's an instance for a user-defined pair:
data User = User { name :: String, fileQuota :: Int }
instance FromRow User where
fromRow = User <$> field <*> field
The number of calls to field must match the number of fields returned
in a single row of the query result. Otherwise, a ConversionFailed
exception will be thrown.
Note that field evaluates its result to WHNF, so the caveats listed in
mysql-simple and very early versions of postgresql-simple no longer apply.
Instead, look at the caveats associated with user-defined implementations
of fromField.
Instances
defaultConnectInfo :: ConnectInfo #
Default information for setting up a connection.
Defaults are as follows:
- Server on
localhost - Port on
5432 - User
postgres - No password
- Database
postgres
Use as in the following example:
connect defaultConnectInfo { connectHost = "db.example.com" }Orphan instances
| HasPostgres (Handler b Postgres) Source # | Default instance |
| (MonadIO m, MonadBaseControl IO m) => HasPostgres (ReaderT * (Snaplet Postgres) m) Source # | A convenience instance to make it easier to use this snaplet in the Initializer monad like this: d <- nestSnaplet "db" db pgsInit count <- liftIO $ runReaderT (execute "INSERT ..." params) d |
| (MonadIO m, MonadBaseControl IO m) => HasPostgres (ReaderT * Postgres m) Source # | A convenience instance to make it easier to use functions written for this snaplet in non-snaplet contexts. |