Portability | portable |
---|---|
Stability | experimental |
Maintainer | Janne Hellsten <jjhellst@gmail.com> |
Safe Haskell | Safe-Infered |
- open :: String -> IO Connection
- close :: Connection -> IO ()
- query :: (ToRow q, FromRow r) => Connection -> Query -> q -> IO [r]
- query_ :: FromRow r => Connection -> Query -> IO [r]
- execute :: ToRow q => Connection -> Query -> q -> IO ()
- execute_ :: Connection -> Query -> IO ()
- field :: FromField a => RowParser a
- data Query
- data Connection
- class ToRow a
- class FromRow a
- newtype In a = In a
- newtype Only a = Only {
- fromOnly :: a
- data h :. t = h :. t
- data FormatError
- data ResultError
Examples of use
Create a test database by copy&pasting the below snippet to your shell:
sqlite3 test.db "CREATE TABLE test (id INTEGER PRIMARY KEY, str text);\ INSERT INTO test (str) VALUES ('test string');"
..and access it from Haskell:
import Control.Applicative import Database.SQLite.Simple import Database.SQLite.Simple.FromRow data TestField = TestField Int String deriving (Show) instance FromRow TestField where fromRow = TestField <$> field <*> field main :: IO () main = do conn <- open "test.db" execute conn "INSERT INTO test (str) VALUES (?)" (Only ("test string 2" :: String)) r <- query_ conn "SELECT * from test" :: IO [TestField] mapM_ print r close conn
open :: String -> IO ConnectionSource
Open a database connection to a given file. Will throw an exception if it cannot connect.
Every open
must be closed with a call to close
.
If you specify ":memory:" or an empty string as the input filename, then a private, temporary in-memory database is created for the connection. This database will vanish when you close the connection.
close :: Connection -> IO ()Source
Close a database connection.
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 mismatched with given arguments. -
QueryError
: the result contains no columns (i.e. you should be usingexecute
instead ofquery
). -
ResultError
: result conversion failed.
query_ :: FromRow r => Connection -> Query -> IO [r]Source
A version of query
that does not perform query substitution.
execute :: ToRow q => Connection -> Query -> q -> IO ()Source
Execute an INSERT
, UPDATE
, or other SQL query that is not
expected to return results.
Throws FormatError
if the query could not be formatted correctly.
execute_ :: Connection -> Query -> IO ()Source
A version of execute
that does not perform query substitution.
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 Text
, and literal Haskell strings that
contain Unicode characters will be correctly transformed to UTF-8.
data Connection Source
Connection to an open database.
A collection type that can be turned into a list of SQLData elements.
ToRow () | |
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 defined outside of sqlite-simple, which is often useful. For example, here's an instance for a user-defined pair:
data User = User { name :: String, fileQuota :: Int } instanceFromRow
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 the caveats associated with user-defined implementations of
fromRow
.
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 ?" (In [3,4,5])
In a |
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) -> {- ... -}
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 ....
h :. t |
Exceptions
data FormatError Source
Exception thrown if a Query
was malformed.
This may occur if the number of '?
' characters in the query
string does not match the number of parameters provided.
data ResultError Source
Exception thrown if conversion from a SQL value to a Haskell value fails.