Database.SQLite.Simple

An example that creates a table test, inserts a couple of rows and proceeds to showcase how to update or delete rows. This example also demonstrates the use of lastInsertRowId (how to refer to a previously inserted row) and executeNamed (an easier to maintain form of query parameter naming).

{-# LANGUAGE OverloadedStrings #-}

import           Control.Applicative
import qualified Data.Text as T
import           Database.SQLite.Simple
import           Database.SQLite.Simple.FromRow

data TestField = TestField Int T.Text deriving (Show)

instance FromRow TestField where
  fromRow = TestField <$> field <*> field

instance ToRow TestField where
  toRow (TestField id_ str) = toRow (id_, str)

main :: IO ()
main = do
  conn <- open "test.db"
  execute_ conn "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, str TEXT)"
  execute conn "INSERT INTO test (str) VALUES (?)" (Only ("test string 2" :: String))
  execute conn "INSERT INTO test (id, str) VALUES (?,?)" (TestField 13 "test string 3")
  rowId <- lastInsertRowId conn
  executeNamed conn "UPDATE test SET str = :str WHERE id = :id" [":str" := ("updated str" :: T.Text), ":id" := rowId]
  r <- query_ conn "SELECT * from test" :: IO [TestField]
  mapM_ print r
  execute conn "DELETE FROM test WHERE id = ?" (Only rowId)
  close conn

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. A Query is a newtype-wrapped Text. It intentionally exposes a tiny API that is not compatible with the Text 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, ScopedTypeVariables #-}

import Database.SQLite.Simple

hello = do
  conn <- open "test.db"
  [[x :: Int]] <- query_ conn "select 2 + 2"
  print x

A Query value does not represent the actual query that will be executed, but is a template for constructing the final query.

Since applications need to be able to construct queries with parameters that change, this library uses SQLite's parameter binding query substitution capability.

This library restricts parameter substitution to work only with named parameters and positional arguments with the "?" syntax. The API does not support for mixing these two types of bindings. Unsupported parameters will be rejected and a FormatError will be thrown.

You should always use parameter substitution instead of inlining your dynamic parameters into your queries with messy string concatenation. SQLite will automatically quote and escape your data into these placeholder parameters; this defeats the single most common injection vector for malicious data.

The Query template accepted by query, execute and fold can contain any number of "?" characters. Both query and execute accept a third argument, typically a tuple. When the query executes, the first "?" in the template will be replaced with the first element of the tuple, the second "?" with the second element, and so on. This substitution happens inside the native SQLite implementation.

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.

Named parameters are accepted by queryNamed, executeNamed and foldNamed. These functions take a list of NamedParams which are key-value pairs binding a value to an argument name. As is the case with "?" parameters, named parameters are automatically escaped by the SQLite library. The parameter names are prefixed with either : or @, e.g. :foo or @foo.

Example:

r <- queryNamed c "SELECT id,text FROM posts WHERE id = :id AND date >= :date" [":id" := postId, ":date" := afterDate]

Note that you can mix different value types in the same list. E.g., the following is perfectly legal:

[":id" := (3 :: Int), ":str" := ("foo" :: String)]

The parameter name (or key) in the NamedParam must match exactly the name written in the SQL query. E.g., if you used :foo in your SQL statement, you need to use ":foo" as the parameter key, not "foo". Some libraries like Python's sqlite3 automatically drop the : character from the name.

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.

Haskell lacks a single-element tuple type, so if you have just one value you want substituted into a query, what should you do?

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

Or you can use named parameters which do not have this restriction.

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.

Conversion of SQL values to Haskell values is somewhat permissive. Here are the rules.

• For numeric types, any Haskell type that can accurately represent an SQLite INTEGER is considered "compatible".

• If a numeric incompatibility is found, query will throw a ResultError.

• SQLite's TEXT type is always encoded in UTF-8. Thus any text data coming from an SQLite database should always be compatible with Haskell String and Text types.

• SQLite's BLOB type will only be conversible to a Haskell ByteString.

You can extend conversion support to your own types be adding your own FromField / ToField instances.

SQLite's datetime allows for multiple string representations of UTC time. The following formats are supported for reading SQLite times into Haskell UTCTime values:

• YYYY-MM-DD HH:MM

• YYYY-MM-DD HH:MM:SS

• YYYY-MM-DD HH:MM:SS.SSS

• YYYY-MM-DDTHH:MM

• YYYY-MM-DDTHH:MM:SS

• YYYY-MM-DDTHH:MM:SS.SSS

The above may also be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z".

When Haskell UTCTime values are converted into SQLite values (e.g., parameters for a query), the following format is used:

• YYYY-MM-DD HH:MM:SS.SSS

The last ".SSS" subsecond part is dropped if it's zero. No timezone indicator is used when converting from a UTCTime value into an SQLite string. SQLite assumes all datetimes are in UTC time.

The parser and printers are implemented in Database.SQLite.Simple.Time.

Read more about SQLite's time strings in http://sqlite.org/lang_datefunc.html