Connect with the given username to the given database. Will throw an exception if it cannot connect.

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

Attempt to make a connection based on a libpq connection string. See http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING for more information. Here is an example with some of the most commonly used parameters:

host='db.somedomain.com' port=5432 ...

This attempts to connect to db.somedomain.com:5432. Omitting the port will normally default to 5432.

On systems that provide unix domain sockets, omitting the host parameter will cause libpq to attempt to connect via unix domain sockets. The default filesystem path to the socket is constructed from the port number and the DEFAULT_PGSOCKET_DIR constant defined in the pg_config_manual.h header file. Connecting via unix sockets tends to use the peer authentication method, which is very secure and does not require a password.

On Windows and other systems without unix domain sockets, omitting the host will default to localhost.

... dbname='postgres' user='postgres' password='secret \' \\ pw'

This attempts to connect to a database named postgres with user postgres and password secret ' \ pw. Backslash characters will have to be double-quoted in literal Haskell strings, of course. Omitting dbname and user will both default to the system username that the client process is running as.

Omitting password will default to an appropriate password found in the pgpass file, or no password at all if a matching line is not found. See http://www.postgresql.org/docs/9.3/static/libpq-pgpass.html for more information regarding this file.

As all parameters are optional and the defaults are sensible, the empty connection string can be useful for development and exploratory use, assuming your system is set up appropriately.

On Unix, such a setup would typically consist of a local postgresql server listening on port 5432, as well as a system user, database user, and database sharing a common name, with permissions granted to the user on the database.

On Windows, in addition you will either need pg_hba.conf to specify the use of the trust authentication method for the connection, which may not be appropriate for multiuser or production machines, or you will need to use a pgpass file with the password or md5 authentication methods.

See http://www.postgresql.org/docs/9.3/static/client-authentication.html for more information regarding the authentication process.

SSL/TLS will typically "just work" if your postgresql server supports or requires it. However, note that libpq is trivially vulnerable to a MITM attack without setting additional SSL parameters in the connection string. In particular, sslmode needs to set be require, verify-ca, or verify-full to perform certificate validation. When sslmode is require, then you will also need to have a sslrootcert file, otherwise no validation of the server's identity will be performed. Client authentication via certificates is also possible via the sslcert and sslkey parameters.

A type that may be used as a single parameter to a SQL query.

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.

A type that may be converted from a SQL type.

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 it's 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.

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.

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) -> {- ... -}

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]))

Represents a VALUES table literal, usable as an alternative to executeMany and returning. The main advantage is that you can parametrize more than just a single VALUES expression. For example, here's a query to insert a thing into one table and some attributes of that thing into another, returning the new id generated by the database:

query c [sql|
    WITH new_thing AS (
      INSERT INTO thing (name) VALUES (?) RETURNING id
    ), new_attributes AS (
      INSERT INTO thing_attributes
         SELECT new_thing.id, attrs.*
           FROM new_thing JOIN ? attrs
    ) SELECT * FROM new_thing
 |] ("foo", Values [  "int4", "text"    ]
                   [ ( 1    , "hello" )
                   , ( 2    , "world" ) ])

(Note this example uses writable common table expressions, which were added in PostgreSQL 9.1)

The second parameter gets expanded into the following SQL syntax:

(VALUES (1::"int4",'hello'::"text"),(2,'world'))

When the list of attributes is empty, the second parameter expands to:

(VALUES (null::"int4",null::"text") LIMIT 0)

By contrast, executeMany and returning don't issue the query in the empty case, and simply return 0 and [] respectively. This behavior is usually correct given their intended use cases, but would certainly be wrong in the example above.

The first argument is a list of postgresql type names. Because this is turned into a properly quoted identifier, the type name is case sensitive and must be as it appears in the pg_type table. Thus, you must write timestamptz instead of timestamp with time zone, int4 instead of integer, _int8 instead of bigint[], etcetera.

You may omit the type names, however, if you do so the list of values must be non-empty, and postgresql must be able to infer the types of the columns from the surrounding context. If the first condition is not met, postgresql-simple will throw an exception without issuing the query. In the second case, the postgres server will return an error which will be turned into a SqlError exception.

See http://www.postgresql.org/docs/9.3/static/sql-values.html for more information.

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

Wrap a list for use as a PostgreSQL array.

Represents valid hstore syntax.

Represents escape text, ready to be the key or value to a hstore value