| Copyright |
(c) 2011 MailRank Inc.
(c) 2011-2012 Leon P Smith |
|---|---|
| License | BSD3 |
| Maintainer | Leon P Smith <leon@melding-monads.com> |
| Stability | experimental |
| Safe Haskell | None |
| Language | Haskell2010 |
Database.PostgreSQL.Simple
Description
A mid-level client library for the PostgreSQL database, aimed at ease of use and high performance.
Synopsis
- data Connection
- data Query
- class ToRow a
- class FromRow a
- newtype In a = In a
-
newtype
Binary
a =
Binary
{
- fromBinary :: a
-
newtype
Only
a =
Only
{
- fromOnly :: a
- data h :. t = h :. t
- data SqlError = SqlError { }
-
data
ExecStatus
- = EmptyQuery
- | CommandOk
- | TuplesOk
- | CopyOut
- | CopyIn
- | CopyBoth
- | BadResponse
- | NonfatalError
- | FatalError
- | SingleTuple
-
data
FormatError
=
FormatError
{
- fmtMessage :: String
- fmtQuery :: Query
- fmtParams :: [ ByteString ]
- data QueryError = QueryError { }
-
data
ResultError
-
=
Incompatible
{
- errSQLType :: String
- errSQLTableOid :: Maybe Oid
- errSQLField :: String
- errHaskellType :: String
- errMessage :: String
-
|
UnexpectedNull
{
- errSQLType :: String
- errSQLTableOid :: Maybe Oid
- errSQLField :: String
- errHaskellType :: String
- errMessage :: String
-
|
ConversionFailed
{
- errSQLType :: String
- errSQLTableOid :: Maybe Oid
- errSQLField :: String
- errHaskellType :: String
- errMessage :: String
-
=
Incompatible
{
- connectPostgreSQL :: ByteString -> IO Connection
- close :: Connection -> IO ()
- connect :: ConnectInfo -> IO Connection
- withConnect :: ConnectInfo -> ( Connection -> IO c) -> IO c
-
data
ConnectInfo
=
ConnectInfo
{
- connectHost :: String
- connectPort :: Word16
- connectUser :: String
- connectPassword :: String
- connectDatabase :: String
- defaultConnectInfo :: ConnectInfo
- postgreSQLConnectionString :: ConnectInfo -> ByteString
- query :: ( ToRow q, FromRow r) => Connection -> Query -> q -> IO [r]
- query_ :: FromRow r => Connection -> Query -> IO [r]
- queryWith :: ToRow q => RowParser r -> Connection -> Query -> q -> IO [r]
- queryWith_ :: RowParser r -> Connection -> Query -> IO [r]
- data FoldOptions = FoldOptions { }
- data FetchQuantity
- defaultFoldOptions :: FoldOptions
- fold :: ( FromRow row, ToRow params) => Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a
- foldWithOptions :: ( FromRow row, ToRow params) => FoldOptions -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a
- fold_ :: FromRow r => Connection -> Query -> a -> (a -> r -> IO a) -> IO a
- foldWithOptions_ :: FromRow r => FoldOptions -> Connection -> Query -> a -> (a -> r -> IO a) -> IO a
- forEach :: ( ToRow q, FromRow r) => Connection -> Query -> q -> (r -> IO ()) -> IO ()
- forEach_ :: FromRow r => Connection -> Query -> (r -> IO ()) -> IO ()
- returning :: ( ToRow q, FromRow r) => Connection -> Query -> [q] -> IO [r]
- foldWith :: ToRow params => RowParser row -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a
- foldWithOptionsAndParser :: ToRow params => FoldOptions -> RowParser row -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a
- foldWith_ :: RowParser r -> Connection -> Query -> a -> (a -> r -> IO a) -> IO a
- foldWithOptionsAndParser_ :: FoldOptions -> RowParser r -> Connection -> Query -> a -> (a -> r -> IO a) -> IO a
- forEachWith :: ToRow q => RowParser r -> Connection -> Query -> q -> (r -> IO ()) -> IO ()
- forEachWith_ :: RowParser r -> Connection -> Query -> (r -> IO ()) -> IO ()
- returningWith :: ToRow q => RowParser r -> Connection -> Query -> [q] -> IO [r]
- execute :: ToRow q => Connection -> Query -> q -> IO Int64
- execute_ :: Connection -> Query -> IO Int64
- executeMany :: ToRow q => Connection -> Query -> [q] -> IO Int64
- withTransaction :: Connection -> IO a -> IO a
- withSavepoint :: Connection -> IO a -> IO a
- begin :: Connection -> IO ()
- commit :: Connection -> IO ()
- rollback :: Connection -> IO ()
- formatMany :: ToRow q => Connection -> Query -> [q] -> IO ByteString
- formatQuery :: ToRow q => Connection -> Query -> q -> IO ByteString
Writing queries
SQL-based applications are somewhat notorious for their susceptibility to attacks through the injection of maliciously crafted data. The primary reason for widespread vulnerability to SQL injections is that many applications are sloppy in handling user data when constructing SQL queries.
This library provides a
Query
type and a parameter substitution
facility to address both ease of use and security.
The Query type
A
Query
is a
newtype
-wrapped
ByteString
. It intentionally
exposes a tiny API that is not compatible with the
ByteString
API; this makes it difficult to construct queries from fragments of
strings. The
query
and
execute
functions require queries to be
of type
Query
.
To most easily construct a query, enable GHC's
OverloadedStrings
language extension and write your query as a normal literal string.
{-# LANGUAGE OverloadedStrings #-}
import Database.PostgreSQL.Simple
hello :: IO Int
hello = do
conn <- connectPostgreSQL ""
[Only i] <- query_ conn "select 2 + 2"
return i
A
Query
value does not represent the actual query that will be
executed, but is a template for constructing the final query.
Parameter substitution
Since applications need to be able to construct queries with parameters that change, this library provides a query substitution capability.
The
Query
template accepted by
query
and
execute
can contain
any number of "
?
" characters. Both
query
and
execute
accept a third argument, typically a tuple. When constructing the
real query to execute, these functions replace the first "
?
" in
the template with the first element of the tuple, the second
"
?
" with the second element, and so on. If necessary, each
tuple element will be quoted and escaped prior to substitution;
this defeats the single most common injection vector for malicious
data.
For example, given the following
Query
template:
select * from user where first_name = ? and age > ?
And a tuple of this form:
("Boris" :: String, 37 :: Int)
The query to be executed will look like this after substitution:
select * from user where first_name = 'Boris' and age > 37
If there is a mismatch between the number of "
?
" characters in
your template and the number of elements in your tuple, a
FormatError
will be thrown.
Note that the substitution functions do not attempt to parse or
validate your query. It's up to you to write syntactically valid
SQL, and to ensure that each "
?
" in your query template is
matched with the right tuple element.
Type inference
Automated type inference means that you will often be able to avoid supplying explicit type signatures for the elements of a tuple. However, sometimes the compiler will not be able to infer your types. Consider a case where you write a numeric literal in a parameter tuple:
query conn "select ? + ?" (40,2)
The above query will be rejected by the compiler, because it does
not know the specific numeric types of the literals
40
and
2
.
This is easily fixed:
query conn "select ? + ?" (40 :: Double, 2 :: Double)
The same kind of problem can arise with string literals if you have
the
OverloadedStrings
language extension enabled. Again, just
use an explicit type signature if this happens.
Finally, remember that the compiler must be able to infer the type of a query's results as well as its parameters. We might like the following example to work:
print =<< query_ conn "select 2 + 2"
Unfortunately, while a quick glance tells us that the result type should be a single row containing a single numeric column, the compiler has no way to infer what the types are. We can easily fix this by providing an explicit type annotation:
xs <- query_ conn "select 2 + 2" print (xs :: [Only Int])
Substituting a single parameter
Haskell lacks a single-element tuple type, so if you have just one value you want substituted into a query or a single-column result, what should you do?
The obvious approach would appear to be something like this:
instance (ToField a) => ToRow a where
...
Unfortunately, this wreaks havoc with type inference, so we take a
different tack. To represent a single value
val
as a parameter, write
a singleton list
[val]
, use
Just
val
, or use
Only
val
.
Here's an example using a singleton list:
execute conn "insert into users (first_name) values (?)"
["Nuala"]
A row of
n
query results is represented using an
n
-tuple, so
you should use
Only
to represent a single-column result.
Representing a list of values
Suppose you want to write a query using an
IN
clause:
select * from users where first_name in ('Anna', 'Boris', 'Carla')
In such cases, it's common for both the elements and length of the
list after the
IN
keyword to vary from query to query.
To address this case, use the
In
type wrapper, and use a single
"
?
" character to represent the list. Omit the parentheses
around the list; these will be added for you.
Here's an example:
query conn "select * from users where first_name in ?" $
Only $ In ["Anna", "Boris", "Carla"]
If your
In
-wrapped list is empty, the string
"(null)"
will be
substituted instead, to ensure that your clause remains
syntactically valid.
Modifying multiple rows at once
If you know that you have many rows of data to insert into a table,
it is much more efficient to perform all the insertions in a single
multi-row
INSERT
statement than individually.
The
executeMany
function is intended specifically for helping
with multi-row
INSERT
and
UPDATE
statements. Its rules for
query substitution are different than those for
execute
.
What
executeMany
searches for in your
Query
template is a
single substring of the form:
values (?,?,?)
The rules are as follows:
-
The keyword
VALUESis matched case insensitively. -
There must be no other "
?" characters anywhere in your template. -
There must be one or more "
?" in the parentheses. - Extra white space is fine.
The last argument to
executeMany
is a list of parameter
tuples. These will be substituted into the query where the
(?,?)
string appears, in a form suitable for use in a multi-row
INSERT
or
UPDATE
.
Here is an example:
executeMany conn
"insert into users (first_name,last_name) values (?,?)"
[("Boris","Karloff"),("Ed","Wood")]
The query that will be executed here will look like this (reformatted for tidiness):
insert into users (first_name,last_name) values
('Boris','Karloff'),('Ed','Wood')
RETURNING
: modifications that return results
PostgreSQL supports returning values from data manipulation statements
such as
INSERT
and
UPDATE
. You can use these statements by
using
query
instead of
execute
. For multi-tuple inserts,
use
returning
instead of
executeMany
.
For example, were there an auto-incrementing
id
column and
timestamp column
t
that defaulted to the present time for the
sales
table, then the following query would insert two new
sales records and also return their new
id
s and timestamps.
let q = "insert into sales (amount, label) values (?,?) returning id, t" xs :: [(Int, UTCTime)] <- query conn q (15,"Sawdust") ys :: [(Int, UTCTime)] <- returning conn q [(20,"Chips"),(300,"Wood")]
Extracting results
The
query
and
query_
functions return a list of values in the
FromRow
typeclass. This class performs automatic extraction
and type conversion of rows from a query result.
Here is a simple example of how to extract results:
import qualified Data.Text as Text xs <- query_ conn "select name,age from users" forM_ xs $ \(name,age) -> putStrLn $ Text.unpack name ++ " is " ++ show (age :: Int)
Notice two important details about this code:
-
The number of columns we ask for in the query template must
exactly match the number of elements we specify in a row of the
result tuple. If they do not match, a
ResultErrorexception will be thrown. -
Sometimes, the compiler needs our help in specifying types. It
can infer that
namemust be aText, due to our use of theunpackfunction. However, we have to tell it the type ofage, as it has no other information to determine the exact type.
Handling null values
The type of a result tuple will look something like this:
(Text, Int, Int)
Although SQL can accommodate
NULL
as a value for any of these
types, Haskell cannot. If your result contains columns that may be
NULL
, be sure that you use
Maybe
in those positions of your
tuple.
(Text, Maybe Int, Int)
If
query
encounters a
NULL
in a row where the corresponding
Haskell type is not
Maybe
, it will throw a
ResultError
exception.
Type conversions
Conversion of SQL values to Haskell values is somewhat permissive. Here are the rules.
-
For numeric types, any Haskell type that can accurately represent
all values of the given PostgreSQL type is considered "compatible".
For instance, you can always extract a PostgreSQL 16-bit
SMALLINTcolumn to a HaskellInt. The HaskellFloattype can accurately represent aSMALLINT, so it is considered compatible with those types. -
A numeric compatibility check is based only on the type of a
column,
not
on its values. For instance, a PostgreSQL 64-bit
BIGINTcolumn will be considered incompatible with a HaskellInt16, even if it contains the value1. -
If a numeric incompatibility is found,
querywill throw aResultError. -
The
StringandTexttypes are assumed to be encoded as UTF-8. If you use some other encoding, decoding may fail or give wrong results. In such cases, write anewtypewrapper and a customResultinstance to handle your encoding.
Types
data Connection Source #
Instances
| Eq Connection Source # | |
|
Defined in Database.PostgreSQL.Simple.Internal Methods (==) :: Connection -> Connection -> Bool Source # (/=) :: Connection -> Connection -> Bool 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.
A collection type that can be turned into a list of rendering
Action
s.
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.
Instances
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
.
Instances
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
|
|
Instances
| Functor Binary Source # | |
| Eq a => Eq ( Binary a) Source # | |
| Ord a => Ord ( Binary a) Source # | |
|
Defined in Database.PostgreSQL.Simple.Types |
|
| Read a => Read ( Binary a) Source # | |
| Show a => Show ( Binary a) Source # | |
| ToField ( Binary ByteString ) Source # | |
|
Defined in Database.PostgreSQL.Simple.ToField |
|
| ToField ( Binary ByteString ) Source # | |
|
Defined in Database.PostgreSQL.Simple.ToField |
|
| FromField ( Binary ByteString ) Source # |
bytea |
|
Defined in Database.PostgreSQL.Simple.FromField Methods fromField :: FieldParser ( Binary ByteString ) Source # |
|
| FromField ( Binary ByteString ) Source # |
bytea |
|
Defined in Database.PostgreSQL.Simple.FromField Methods fromField :: FieldParser ( Binary ByteString ) Source # |
|
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) -> {- ... -}
Instances
| Functor Only | |
| Eq a => Eq ( Only a) | |
| Data a => Data ( Only a) | |
|
Defined in Data.Tuple.Only Methods gfoldl :: ( forall d b. Data d => c (d -> b) -> d -> c b) -> ( forall g. g -> c g) -> Only a -> c ( Only a) Source # gunfold :: ( forall b r. Data b => c (b -> r) -> c r) -> ( forall r. r -> c r) -> Constr -> c ( Only a) Source # toConstr :: Only a -> Constr Source # dataTypeOf :: Only a -> DataType Source # dataCast1 :: Typeable t => ( forall d. Data d => c (t d)) -> Maybe (c ( Only a)) Source # dataCast2 :: Typeable t => ( forall d e. ( Data d, Data e) => c (t d e)) -> Maybe (c ( Only a)) Source # gmapT :: ( forall b. Data b => b -> b) -> Only a -> Only a Source # gmapQl :: (r -> r' -> r) -> r -> ( forall d. Data d => d -> r') -> Only a -> r Source # gmapQr :: forall r r'. (r' -> r -> r) -> r -> ( forall d. Data d => d -> r') -> Only a -> r Source # gmapQ :: ( forall d. Data d => d -> u) -> Only a -> [u] Source # gmapQi :: Int -> ( forall d. Data d => d -> u) -> Only a -> u Source # gmapM :: Monad m => ( forall d. Data d => d -> m d) -> Only a -> m ( Only a) Source # gmapMp :: MonadPlus m => ( forall d. Data d => d -> m d) -> Only a -> m ( Only a) Source # gmapMo :: MonadPlus m => ( forall d. Data d => d -> m d) -> Only a -> m ( Only a) Source # |
|
| Ord a => Ord ( Only a) | |
| Read a => Read ( Only a) | |
| Show a => Show ( Only a) | |
| Generic ( Only a) | |
| NFData a => NFData ( Only a) | |
|
Defined in Data.Tuple.Only |
|
| ToField a => ToRow ( Only a) Source # | |
| FromField a => FromRow ( Maybe ( Only a)) Source # | |
| FromField a => FromRow ( Only a) Source # | |
| type Rep ( Only a) | |
|
Defined in Data.Tuple.Only |
|
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 |
Instances
| ( Eq h, Eq t) => Eq (h :. t) Source # | |
| ( Ord h, Ord t) => Ord (h :. t) Source # | |
|
Defined in Database.PostgreSQL.Simple.Types |
|
| ( Read h, Read t) => Read (h :. t) Source # | |
| ( Show h, Show t) => Show (h :. t) Source # | |
| ( ToRow a, ToRow b) => ToRow (a :. b) Source # | |
| ( FromRow a, FromRow b) => FromRow (a :. b) Source # | |
Exceptions
Constructors
| SqlError | |
|
Fields |
|
Instances
| Eq SqlError Source # | |
| Show SqlError Source # | |
| Exception SqlError Source # | |
|
Defined in Database.PostgreSQL.Simple.Internal Methods toException :: SqlError -> SomeException Source # fromException :: SomeException -> Maybe SqlError Source # displayException :: SqlError -> String Source # |
|
data ExecStatus Source #
Constructors
| EmptyQuery |
The string sent to the server was empty. |
| CommandOk |
Successful completion of a command returning no data. |
| TuplesOk |
Successful completion of a command returning data (such as a SELECT or SHOW). |
| CopyOut |
Copy Out (from server) data transfer started. |
| CopyIn |
Copy In (to server) data transfer started. |
| CopyBoth |
Copy In/Out data transfer started. |
| BadResponse |
The server's response was not understood. |
| NonfatalError |
A nonfatal error (a notice or warning) occurred. |
| FatalError |
A fatal error occurred. |
| SingleTuple |
The PGresult contains a single result tuple from the current command. This status occurs only when single-row mode has been selected for the query. |
Instances
| Enum ExecStatus | |
|
Defined in Database.PostgreSQL.LibPQ.Enums Methods succ :: ExecStatus -> ExecStatus Source # pred :: ExecStatus -> ExecStatus Source # toEnum :: Int -> ExecStatus Source # fromEnum :: ExecStatus -> Int Source # enumFrom :: ExecStatus -> [ ExecStatus ] Source # enumFromThen :: ExecStatus -> ExecStatus -> [ ExecStatus ] Source # enumFromTo :: ExecStatus -> ExecStatus -> [ ExecStatus ] Source # enumFromThenTo :: ExecStatus -> ExecStatus -> ExecStatus -> [ ExecStatus ] Source # |
|
| Eq ExecStatus | |
|
Defined in Database.PostgreSQL.LibPQ.Enums Methods (==) :: ExecStatus -> ExecStatus -> Bool Source # (/=) :: ExecStatus -> ExecStatus -> Bool Source # |
|
| Show ExecStatus | |
|
Defined in Database.PostgreSQL.LibPQ.Enums |
|
| ToCInt ExecStatus | |
|
Defined in Database.PostgreSQL.LibPQ.Enums Methods toCInt :: ExecStatus -> CInt |
|
| FromCInt ExecStatus | |
|
Defined in Database.PostgreSQL.LibPQ.Enums Methods fromCInt :: CInt -> Maybe ExecStatus |
|
data FormatError Source #
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
| Eq FormatError Source # | |
|
Defined in Database.PostgreSQL.Simple.Internal Methods (==) :: FormatError -> FormatError -> Bool Source # (/=) :: FormatError -> FormatError -> Bool Source # |
|
| Show FormatError Source # | |
|
Defined in Database.PostgreSQL.Simple.Internal |
|
| Exception FormatError Source # | |
|
Defined in Database.PostgreSQL.Simple.Internal Methods toException :: FormatError -> SomeException Source # fromException :: SomeException -> Maybe FormatError Source # displayException :: FormatError -> String Source # |
|
data QueryError Source #
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
| Eq QueryError Source # | |
|
Defined in Database.PostgreSQL.Simple.Internal Methods (==) :: QueryError -> QueryError -> Bool Source # (/=) :: QueryError -> QueryError -> Bool Source # |
|
| Show QueryError Source # | |
|
Defined in Database.PostgreSQL.Simple.Internal |
|
| Exception QueryError Source # | |
|
Defined in Database.PostgreSQL.Simple.Internal Methods toException :: QueryError -> SomeException Source # fromException :: SomeException -> Maybe QueryError Source # displayException :: QueryError -> String Source # |
|
data ResultError Source #
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
| Eq ResultError Source # | |
|
Defined in Database.PostgreSQL.Simple.FromField Methods (==) :: ResultError -> ResultError -> Bool Source # (/=) :: ResultError -> ResultError -> Bool Source # |
|
| Show ResultError Source # | |
|
Defined in Database.PostgreSQL.Simple.FromField |
|
| Exception ResultError Source # | |
|
Defined in Database.PostgreSQL.Simple.FromField Methods toException :: ResultError -> SomeException Source # fromException :: SomeException -> Maybe ResultError Source # displayException :: ResultError -> String Source # |
|
Connection management
connectPostgreSQL :: ByteString -> IO Connection Source #
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. The path of the
pgpass
file may be specified by setting
the
PGPASSFILE
environment variable. 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.
close :: Connection -> IO () Source #
connect :: ConnectInfo -> IO Connection Source #
Connect with the given username to the given database. Will throw an exception if it cannot connect.
withConnect :: ConnectInfo -> ( Connection -> IO c) -> IO c Source #
data ConnectInfo Source #
Constructors
| ConnectInfo | |
|
Fields
|
|
Instances
defaultConnectInfo :: ConnectInfo Source #
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" }
postgreSQLConnectionString :: ConnectInfo -> ByteString Source #
Turns a
ConnectInfo
data structure into a libpq connection string.
Queries that return results
query :: ( ToRow q, FromRow r) => Connection -> Query -> q -> IO [r] Source #
Perform a
SELECT
or other SQL query that is expected to return
results. All results are retrieved and converted before this
function returns.
When processing large results, this function will consume a lot of
client-side memory. Consider using
fold
instead.
Exceptions that may be thrown:
-
FormatError: the query string could not be formatted correctly. -
QueryError: the result contains no columns (i.e. you should be usingexecuteinstead ofquery). -
ResultError: result conversion failed. -
SqlError: the postgresql backend returned an error, e.g. a syntax or type error, or an incorrect table or column name.
query_ :: FromRow r => Connection -> Query -> IO [r] Source #
A version of
query
that does not perform query substitution.
Queries taking parser as argument
queryWith :: ToRow q => RowParser r -> Connection -> Query -> q -> IO [r] Source #
A version of
query
taking parser as argument
queryWith_ :: RowParser r -> Connection -> Query -> IO [r] Source #
A version of
query_
taking parser as argument
Queries that stream results
data FoldOptions Source #
Constructors
| FoldOptions | |
|
Fields |
|
data FetchQuantity Source #
Number of rows to fetch at a time.
Automatic
currently defaults
to 256 rows, although it might be nice to make this more intelligent
based on e.g. the average size of the rows.
defaultFoldOptions :: FoldOptions Source #
defaults to
Automatic
, and
TransactionMode
ReadCommitted
ReadOnly
fold :: ( FromRow row, ToRow params) => Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a Source #
Perform a
SELECT
or other SQL query that is expected to return
results. Results are streamed incrementally from the server, and
consumed via a left fold.
When dealing with small results, it may be simpler (and perhaps
faster) to use
query
instead.
This fold is not strict. The stream consumer is responsible for forcing the evaluation of its result to avoid space leaks.
This is implemented using a database cursor. As such, this requires
a transaction. This function will detect whether or not there is a
transaction in progress, and will create a
ReadCommitted
ReadOnly
transaction if needed. The cursor is given a unique temporary name,
so the consumer may itself call fold.
Exceptions that may be thrown:
-
FormatError: the query string could not be formatted correctly. -
QueryError: the result contains no columns (i.e. you should be usingexecuteinstead ofquery). -
ResultError: result conversion failed. -
SqlError: the postgresql backend returned an error, e.g. a syntax or type error, or an incorrect table or column name.
foldWithOptions :: ( FromRow row, ToRow params) => FoldOptions -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a Source #
The same as
fold
, but this provides a bit more control over
lower-level details. Currently, the number of rows fetched per
round-trip to the server and the transaction mode may be adjusted
accordingly. If the connection is already in a transaction,
then the existing transaction is used and thus the
transactionMode
option is ignored.
Arguments
| :: FromRow r | |
| => Connection | |
| -> Query |
Query. |
| -> a |
Initial state for result consumer. |
| -> (a -> r -> IO a) |
Result consumer. |
| -> IO a |
A version of
fold
that does not perform query substitution.
Arguments
| :: FromRow r | |
| => FoldOptions | |
| -> Connection | |
| -> Query |
Query. |
| -> a |
Initial state for result consumer. |
| -> (a -> r -> IO a) |
Result consumer. |
| -> IO a |
Arguments
| :: ( ToRow q, FromRow r) | |
| => Connection | |
| -> Query |
Query template. |
| -> q |
Query parameters. |
| -> (r -> IO ()) |
Result consumer. |
| -> IO () |
A version of
fold
that does not transform a state value.
Arguments
| :: FromRow r | |
| => Connection | |
| -> Query |
Query template. |
| -> (r -> IO ()) |
Result consumer. |
| -> IO () |
A version of
forEach
that does not perform query substitution.
returning :: ( ToRow q, FromRow r) => Connection -> Query -> [q] -> IO [r] Source #
Execute
INSERT ... RETURNING
,
UPDATE ... RETURNING
, or other SQL
query that accepts multi-row input and is expected to return results.
Note that it is possible to write
in cases where you are only inserting a single row, and do not need
functionality analogous to
query
conn "INSERT ... RETURNING ..." ...
executeMany
.
If the list of parameters is empty, this function will simply return
[]
without issuing the query to the backend. If this is not desired,
consider using the
Values
constructor instead.
Throws
FormatError
if the query could not be formatted correctly.
Queries that stream results taking a parser as an argument
foldWith :: ToRow params => RowParser row -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a Source #
A version of
fold
taking a parser as an argument
foldWithOptionsAndParser :: ToRow params => FoldOptions -> RowParser row -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a Source #
A version of
foldWithOptions
taking a parser as an argument
foldWith_ :: RowParser r -> Connection -> Query -> a -> (a -> r -> IO a) -> IO a Source #
A version of
fold_
taking a parser as an argument
foldWithOptionsAndParser_ Source #
Arguments
| :: FoldOptions | |
| -> RowParser r | |
| -> Connection | |
| -> Query |
Query. |
| -> a |
Initial state for result consumer. |
| -> (a -> r -> IO a) |
Result consumer. |
| -> IO a |
A version of
foldWithOptions_
taking a parser as an argument
forEachWith :: ToRow q => RowParser r -> Connection -> Query -> q -> (r -> IO ()) -> IO () Source #
A version of
forEach
taking a parser as an argument
forEachWith_ :: RowParser r -> Connection -> Query -> (r -> IO ()) -> IO () Source #
returningWith :: ToRow q => RowParser r -> Connection -> Query -> [q] -> IO [r] Source #
A version of
returning
taking parser as argument
Statements that do not return results
execute :: ToRow q => Connection -> Query -> q -> IO Int64 Source #
Execute an
INSERT
,
UPDATE
, or other SQL query that is not
expected to return results.
Returns the number of rows affected.
Throws
FormatError
if the query could not be formatted correctly, or
a
SqlError
exception if the backend returns an error.
execute_ :: Connection -> Query -> IO Int64 Source #
A version of
execute
that does not perform query substitution.
executeMany :: ToRow q => Connection -> Query -> [q] -> IO Int64 Source #
Execute a multi-row
INSERT
,
UPDATE
, or other SQL query that is not
expected to return results.
Returns the number of rows affected. If the list of parameters is empty,
this function will simply return 0 without issuing the query to the backend.
If this is not desired, consider using the
Values
constructor instead.
Throws
FormatError
if the query could not be formatted correctly, or
a
SqlError
exception if the backend returns an error.
For example, here's a command that inserts two rows into a table with two columns:
executeMany c [sql|
INSERT INTO sometable VALUES (?,?)
|] [(1, "hello"),(2, "world")]
Here's an canonical example of a multi-row update command:
executeMany c [sql|
UPDATE sometable
SET y = upd.y
FROM (VALUES (?,?)) as upd(x,y)
WHERE sometable.x = upd.x
|] [(1, "hello"),(2, "world")]
Transaction handling
withTransaction :: Connection -> IO a -> IO a Source #
Execute an action inside a SQL transaction.
This function initiates a transaction with a "
begin
transaction
" statement, then executes the supplied action. If
the action succeeds, the transaction will be completed with
commit
before this function returns.
If the action throws
any
kind of exception (not just a
PostgreSQL-related exception), the transaction will be rolled back using
rollback
, then the exception will be rethrown.
For nesting transactions, see
withSavepoint
.
withSavepoint :: Connection -> IO a -> IO a Source #
Create a savepoint, and roll back to it if an error occurs. This may only be used inside of a transaction, and provides a sort of "nested transaction".
See https://www.postgresql.org/docs/9.5/static/sql-savepoint.html
begin :: Connection -> IO () Source #
Begin a transaction.
commit :: Connection -> IO () Source #
Commit a transaction.
rollback :: Connection -> IO () Source #
Rollback a transaction.
Helper functions
formatMany :: ToRow q => Connection -> Query -> [q] -> IO ByteString Source #
Format a query string with a variable number of rows.
This function is exposed to help with debugging and logging. Do not use it to prepare queries for execution.
The query string must contain exactly one substitution group,
identified by the SQL keyword "
VALUES
" (case insensitive)
followed by an "
(
" character, a series of one or more "
?
"
characters separated by commas, and a "
)
" character. White
space in a substitution group is permitted.
Throws
FormatError
if the query string could not be formatted
correctly.
formatQuery :: ToRow q => Connection -> Query -> q -> IO ByteString Source #
Format a query string.
This function is exposed to help with debugging and logging. Do not use it to prepare queries for execution.
String parameters are escaped according to the character set in use
on the
Connection
.
Throws
FormatError
if the query string could not be formatted
correctly.