| Safe Haskell | None |
|---|---|
| Language | Haskell2010 |
Database.PostgreSQL.Query
- module Database.PostgreSQL.Query.Entity
- module Database.PostgreSQL.Query.Functions
- module Database.PostgreSQL.Query.SqlBuilder
- module Database.PostgreSQL.Query.TH
- module Database.PostgreSQL.Query.Types
- data Connection :: *
- connect :: ConnectInfo -> IO Connection
- defaultConnectInfo :: ConnectInfo
- connectPostgreSQL :: ByteString -> IO Connection
- data ConnectInfo :: * = ConnectInfo {}
- class ToField a where
- class ToRow a where
- class FromField a where
- class FromRow a where
- newtype Query :: * = Query {}
- newtype Only a :: * -> * = Only {
- fromOnly :: a
- newtype In a :: * -> * = In a
- newtype Oid :: * = Oid CUInt
- data Values a :: * -> * = Values [QualifiedIdentifier] [a]
- data h :. t :: * -> * -> * = h :. t
- newtype PGArray a :: * -> * = PGArray {
- fromPGArray :: [a]
- newtype HStoreList :: * = HStoreList {
- fromHStoreList :: [(Text, Text)]
- newtype HStoreMap :: * = HStoreMap {
- fromHStoreMap :: Map Text Text
- class ToHStore a where
- data HStoreBuilder :: *
- hstore :: (ToHStoreText a, ToHStoreText b) => a -> b -> HStoreBuilder
- parseHStoreList :: ByteString -> Either String HStoreList
- class ToHStoreText a where
- data HStoreText :: *
Common usage modules
module Database.PostgreSQL.Query.TH
Some re-exports from postgresql-simple
data Connection :: * #
Instances
connect :: ConnectInfo -> IO Connection #
Connect with the given username to the given database. Will throw an exception if it cannot connect.
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" }connectPostgreSQL :: ByteString -> IO Connection #
Attempt to make a connection based on a libpq connection string. See https://www.postgresql.org/docs/9.5/static/libpq-connect.html#LIBPQ-CONNSTRING for more information. Also note that environment variables also affect parameters not provided, parameters provided as the empty string, and a few other things; see https://www.postgresql.org/docs/9.5/static/libpq-envars.html for details. 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
https://www.postgresql.org/docs/9.5/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 https://www.postgresql.org/docs/9.5/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 connection parameters. In
particular, sslmode needs to be set to require, verify-ca, or
verify-full in order to perform certificate validation. When sslmode
is require, then you will also need to specify 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. See
https://www.postgresql.org/docs/9.5/static/libpq-ssl.html
for detailed information regarding libpq and SSL.
data ConnectInfo :: * #
Constructors
| ConnectInfo | |
Fields
| |
Instances
A type that may be used as a single parameter to a SQL query.
Minimal complete definition
Instances
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 type that may be converted from a SQL type.
Minimal complete definition
Methods
fromField :: FieldParser a #
Convert a SQL value to a Haskell value.
Returns a list of exceptions if the conversion fails. In the case of
library instances, this will usually be a single ResultError, but
may be a UnicodeException.
Note that retaining any reference to the Field argument causes
the entire LibPQ. to be retained. Thus, implementations
of ResultfromField should return results that do not refer to this value
after the result have been evaluated to WHNF.
Note that as of postgresql-simple-0.4.0.0, the ByteString value
has already been copied out of the LibPQ. before it has
been passed to ResultfromField. This is because for short strings, it's
cheaper to copy the string than to set up a finalizer.
Instances
| FromField Bool | bool |
| FromField Char | "char" |
| FromField Double | int2, int4, float4, float8 (Uses attoparsec's |
| FromField Float | int2, float4 (Uses attoparsec's |
| FromField Int | int2, int4, and if compiled as 64-bit code, int8 as well. This library was compiled as 64-bit code. |
| FromField Int16 | int2 |
| FromField Int32 | int2, int4 |
| FromField Int64 | int2, int4, int8 |
| FromField Integer | int2, int4, int8 |
| FromField () | void |
| FromField ByteString | bytea, name, text, "char", bpchar, varchar, unknown |
| FromField ByteString | bytea, name, text, "char", bpchar, varchar, unknown |
| FromField Scientific | int2, int4, int8, float4, float8, numeric |
| FromField Text | name, text, "char", bpchar, varchar |
| FromField UTCTime | timestamptz |
| FromField Value | json |
| FromField Text | name, text, "char", bpchar, varchar |
| FromField Oid | oid |
| FromField HStoreList | hstore |
| FromField HStoreMap | |
| FromField Null | compatible with any data type, but the value must be null |
| FromField LocalTimestamp | timestamp |
| FromField UTCTimestamp | timestamptz |
| FromField ZonedTimestamp | timestamptz |
| FromField Date | date |
| FromField LocalTime | timestamp |
| FromField ZonedTime | timestamptz |
| FromField TimeOfDay | time |
| FromField Day | date |
| FromField UUID | uuid |
| FromField InetText # | |
| FromField [Char] | name, text, "char", bpchar, varchar |
| FromField a => FromField (Maybe a) | For dealing with null values. Compatible with any postgresql type
compatible with type |
| FromField (Ratio Integer) | int2, int4, int8, float4, float8, numeric |
| FromField a => FromField (IORef a) | Compatible with the same set of types as |
| FromField a => FromField (MVar a) | Compatible with the same set of types as |
| FromField (CI Text) | citext |
| FromField (CI Text) | citext |
| FromField (Binary ByteString) | bytea |
| FromField (Binary ByteString) | bytea |
| (FromField a, Typeable * a) => FromField (PGArray a) | any postgresql array whose elements are compatible with type |
| (FromField a, Typeable * a) => FromField (Vector a) | |
| (FromField a, Typeable * a) => FromField (IOVector a) | |
| (FromField a, FromField b) => FromField (Either a b) | Compatible with both types. Conversions to 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 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
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.
Constructors
| Query | |
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) -> {- ... -}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 |
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 ON TRUE
) 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 or serial, _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 https://www.postgresql.org/docs/9.5/static/sql-values.html for more information.
Constructors
| Values [QualifiedIdentifier] [a] |
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 |
Wrap a list for use as a PostgreSQL array.
Constructors
| PGArray | |
Fields
| |
newtype HStoreList :: * #
Constructors
| HStoreList | |
Fields
| |
Instances
| Show HStoreList | |
| ToHStore HStoreList | hstore |
| ToField HStoreList | |
| FromField HStoreList | hstore |
Constructors
| HStoreMap | |
Fields
| |
hstore :: (ToHStoreText a, ToHStoreText b) => a -> b -> HStoreBuilder #
class ToHStoreText a where #
Minimal complete definition
Methods
toHStoreText :: a -> HStoreText #
Instances
| ToHStoreText ByteString | Assumed to be UTF-8 encoded |
| ToHStoreText ByteString | Assumed to be UTF-8 encoded |
| ToHStoreText Text | |
| ToHStoreText Text | |
| ToHStoreText HStoreText | |
data HStoreText :: * #
Represents escape text, ready to be the key or value to a hstore value
Instances