sqlite-simple-0.1.0.0: Mid-Level SQLite client library

Portabilityportable
Stabilityexperimental
MaintainerJanne Hellsten <jjhellst@gmail.com>
Safe HaskellSafe-Infered

Database.SQLite.Simple

Contents

Description

 

Synopsis

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 using execute instead of query).
  • 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.

field :: FromField a => RowParser aSource

data Query 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 Text, and literal Haskell strings that contain Unicode characters will be correctly transformed to UTF-8.

Instances

data Connection Source

Connection to an open database.

class ToRow a Source

A collection type that can be turned into a list of SQLData elements.

Instances

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) 

class FromRow a Source

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 } 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 the caveats associated with user-defined implementations of fromRow.

Instances

FromField a => FromRow [a] 
FromField a => FromRow (Only a) 
(FromField a, FromField b) => FromRow (a, b) 
(FromRow a, FromRow b) => FromRow (:. a b) 
(FromField a, FromField b, FromField c) => FromRow (a, b, c) 
(FromField a, FromField b, FromField c, FromField d) => FromRow (a, b, c, d) 
(FromField a, FromField b, FromField c, FromField d, FromField e) => FromRow (a, b, c, d, e) 
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f) => FromRow (a, b, c, d, e, f) 
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g) => FromRow (a, b, c, d, e, f, g) 
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h) => FromRow (a, b, c, d, e, f, g, h) 
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i) => FromRow (a, b, c, d, e, f, g, h, i) 
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j) => FromRow (a, b, c, d, e, f, g, h, i, j) 

newtype In a Source

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

Constructors

In a 

Instances

Functor In 
Typeable1 In 
Eq a => Eq (In a) 
Ord a => Ord (In a) 
Read a => Read (In a) 
Show a => Show (In a) 
ToField a => ToField (In [a]) 

newtype Only a Source

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

Constructors

Only 

Fields

fromOnly :: a
 

Instances

Functor Only 
Typeable1 Only 
Eq a => Eq (Only a) 
Ord a => Ord (Only a) 
Read a => Read (Only a) 
Show a => Show (Only a) 
FromField a => FromRow (Only a) 
ToField a => ToRow (Only a) 

data h :. t Source

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 

Instances

Typeable2 :. 
(Eq h, Eq t) => Eq (:. h t) 
(Ord h, Ord t) => Ord (:. h t) 
(Read h, Read t) => Read (:. h t) 
(Show h, Show t) => Show (:. h t) 
(FromRow a, FromRow b) => FromRow (:. a b) 
(ToRow a, ToRow b) => ToRow (:. a b) 

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.

Instances

data ResultError Source

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

Instances