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.

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.

Data type holding all the snaplet's config information.

Returns a config object with default values and the specified connection string.

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".

Initialize the snaplet

Initialize the snaplet using a specific configuration.

Produce a connection string from a config

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.

Convenience function for executing a function that needs a database connection.

Convenience function for executing a function that needs a database connection specialized to IO.

See query

See query_

See returning

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.

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.

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.

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.

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.

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:

1. Use postgresql-simple's Values type 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 type Values (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)))

2. Use sql's COALESCE operator to turn a logical null into 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 COALESCE operator, so if you have an index on id then you probably don't want to write the last example with COALESCE, which would result in a table scan. There are further caveats if id can be null or you want null treated sensibly as a component of IN or NOT IN.

Wrap binary data for use as a bytea value.

The 1-tuple type or single-value "collection".

This type is structurally equivalent to the Identity type, but its intent is more about serving as the anonymous 1-tuple type missing from Haskell for attaching typeclass instances.

Parameter usage example:

encodeSomething (Only (42::Int))

Result usage example:

xs <- decodeSomething
forM_ xs $ \(Only id) -> {- ... -}

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.

Exception thrown if query is used to perform an INSERT-like operation, or execute is used to perform a SELECT-like operation.

Exception thrown if conversion from a SQL value to a Haskell value fails.

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.

Begin a transaction.

Begin a transaction with a given isolation level

Begin a transaction with a given transaction mode

Rollback a transaction.

Commit a transaction.

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

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.

You can derive ToRow for your data type using GHC generics, like this:

{-# LANGUAGE DeriveAnyClass #-}
{-# LANGUAGE DeriveGeneric  #-}

import GHC.Generics (Generic)
import Database.PostgreSQL.Simple (ToRow)

data User = User { name :: String, fileQuota :: Int }
  deriving (Generic, ToRow)

Note that this only works for product types (e.g. records) and does not support sum types or recursive types.

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.

You can also derive FromRow for your data type using GHC generics, like this:

{-# LANGUAGE DeriveAnyClass #-}
{-# LANGUAGE DeriveGeneric  #-}

import GHC.Generics (Generic)
import Database.PostgreSQL.Simple (FromRow)

data User = User { name :: String, fileQuota :: Int }
  deriving (Generic, FromRow)

Note that this only works for product types (e.g. records) and does not support sum types or recursive types.

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.

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" }