A non-thread safe connection to a SQLite database.

Perform an action with a connection to a SQLite database.

Note: the connection is created with PRAGMA foreign_keys = ON automatically, to work around the fact that SQLite does not automatically enforce foreign key integrity, because it elected to maintain backwards compatibility with code that was written before the foreign key integrity feature was implemented.

Run a transaction on the given connection.

Run a transaction on the given connection, providing a function that can short-circuit (and roll back) the transaction.

Run a transaction wrapped in an ExceptT. If the ExceptT fails, the transaction is rolled back.

Run a transaction that is known to only perform reads.

The action is provided a function that peels off the Transaction newtype without sending the corresponding BEGIN/COMMIT statements.

The transaction is never retried, so it is (more) safe to interleave arbitrary IO actions. If the transaction does attempt a write and gets SQLITE_BUSY, it's your fault!

Run a transaction that is known to perform at least one write.

The action is provided a function that peels off the Transaction newtype without sending the corresponding BEGIN/COMMIT statements.

The transaction is never retried, so it is (more) safe to interleave arbitrary IO actions.

Wrap a transaction with a cache; cache hits will not hit SQLite.

Perform an atomic sub-computation within a transaction; if it returns Left, it's rolled back.

Perform IO inside a transaction, which should be idempotent, because it may be run more than once if the transaction needs to retry.

Warning: attempting to run a transaction inside a transaction will cause an exception!

Unwrap the transaction newtype, throwing away the sending of BEGIN/COMMIT + automatic retry.

A SQL query.

A quasi-quoter for producing a Sql from a SQL query string, using the Haskell variables in scope for each named parameter.

For example, the query

let qux = 5 :: Int

[sql|
  SELECT foo
  FROM bar
  WHERE baz = :qux
|]

would produce a value like

Sql
  { query = "SELECT foo FROM bar WHERE baz = ?"
  , params = [SQLInteger 5]
  }

which, of course, will require a qux with a ToField instance in scope.

There are five valid syntaxes for interpolating a variable:

• :colon, which denotes a single-field variable
• @at, followed by 1+ bare @, which denotes a multi-field variable
• $dollar, which denotes an entire Sql fragment
• IN :colon, which denotes an IN expression, where the right-hand side is a list of scalars
• VALUES :colon, which denotes an entire VALUES literal (1+ tuples)

As an example of the @at syntax, consider a variable plonk with a two-field ToRow instance. A query that interpolates plonk might look like:

[sql|
  SELECT foo
  FROM bar
  WHERE stuff = @plonk
    AND other = @
|]

As an example of $dollar syntax,

let foo = [sql| bar |] in [sql| $foo baz |]

splices foo into the second fragment, and is equivalent to

[sql| bar baz |]

As an example of IN :colon syntax, the query

[sql| IN :foo |]

will require a list "foo" to be in scope, whose elements have ToField instances, and will expand to SQL that looks like

IN (?, ?, ?, ?)

depending on how man elements "foo" has.

As an example of VALUES :colon syntax, the query

[sql| VALUES :foo |]

will require a non-empty list "foo" to be in scope, whose elements have ToRow instances, and will expand to SQL that looks like

VALUES (?, ?), (?, ?), (?, ?)

depending on how many elements "foo" has, and how wide its rows are.

Escape special characters for LIKE matches.

Prepared statements prevent sql injection, but it's still possible some user may be able to craft a query using a fake "hash" that would let them see more than they ought to.

You still need to provide the escape char in the sql query, E.g.

@ SELECT * FROM table WHERE txt LIKE ? ESCAPE '\' @

>>> likeEscape '\\' "Nat.%"
"Nat.\%"

https://www.sqlite.org/pragma.html#pragma_journal_mode

VACUUM, and return whether or not the vacuum succeeded. A vacuum fails if the connection has any open transactions.

VACUUM INTO

The root exception for all exceptions thrown by this library.

SomeException (from base)
  └── SomeSqliteException
        └── SqliteConnectException
        └── SqliteQueryException

A SomeSqliteException should not be inspected or used for control flow when run in a trusted environment, where the database can be assumed to be uncorrupt. Rather, wherever possible, the user of this library should write code that is guaranteed not to throw exceptions, by checking the necessary preconditions first. If that is not possible, it should be considered a bug in this library.

When actions are run on an untrusted codebase, e.g. one downloaded from a remote server, it is sufficient to catch just one exception type, SomeSqliteException.

An exception thrown during establishing a connection.

A SqliteQueryException represents an exception thrown during processing a query, paired with some context that resulted in the exception.

A SqliteQueryException may result from a number of different conditions:

• The underlying sqlite library threw an exception.
• A postcondition violation of a function like queryMaybeRow, which asserts that the resulting relation will have certain number of rows,
• A postcondition violation of a function like queryListRowCheck, which takes a user-defined check as an argument.

A SqliteQueryException should not be inspected or used for control flow when run in a trusted environment, where the database can be assumed to be uncorrupt. Rather, wherever possible, the user of this library should write code that is guaranteed not to throw exceptions, by checking the necessary preconditions first. If that is not possible, it should be considered a bug in this library.

When actions are run on an untrusted codebase, e.g. one downloaded from a remote server, it is sufficient to catch just one exception type, SqliteQueryException.

A type that is intended to be used as additional context for a sqlite-related exception.

A query was expected to return exactly one row, but it did not. The exception carries a string representation of the rows that were actually returned.

A query was expected to return exactly one row, but it did not. The exception carries a string representation of the rows that were actually returned.

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
  ....

A type that may be converted from a SQL 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 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.

Generic implementation

Since version 0.4.18.1 it is possible in some cases to derive a generic implementation for FromRow. With a Generic instance for User, the example above could be written:

instance FromRow User where

With -XDeriveAnyClass -XDerivingStrategies the same can be written:

deriving anyclass instance FromRow User

For more details refer to GFromRow.

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

A type that may be used as a single parameter to a SQL query.

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

Since version 0.4.18.1 it is possible in some cases to derive a generic implementation for ToRow. Refer to the documentation for FromRow to see how this can be done.