direct-sqlite-2.3.23: Low-level binding to SQLite3. Includes UTF8 and BLOB support.

Safe HaskellNone




This API is a slightly lower-level version of Database.SQLite3. Namely:

  • It returns errors instead of throwing them.
  • It only uses cheap conversions. None of these bindings convert from String or Text.


Connection management

setTrace :: Database -> Maybe (Utf8 -> IO ()) -> IO () Source #

Enable/disable tracing of SQL execution. Tracing can be disabled by setting Nothing as the logger callback.

Warning: If the logger callback throws an exception, your whole program will crash. Enable only for debugging!

getAutoCommit :: Database -> IO Bool Source #

Return True if the connection is in autocommit mode, or False if a transaction started with BEGIN is still active.

Be warned that some errors roll back the transaction automatically, and that ROLLBACK will throw an error if no transaction is active. Use getAutoCommit to avoid such an error:

 autocommit <- getAutoCommit conn
 when (not autocommit) $
     exec conn "ROLLBACK"

setSharedCacheEnabled :: Bool -> IO (Either Error ()) Source #

Enable or disable shared cache for all future connections.

Simple query execution

execWithCallback :: Database -> Utf8 -> ExecCallback -> IO (Either (Error, Utf8) ()) Source #

Like exec, but invoke the callback for each result row.

If the callback throws an exception, it will be rethrown by execWithCallback.

type ExecCallback Source #


 = ColumnCount

Number of columns, which is the number of items in the following lists. This will be the same for every row.

-> [Utf8]

List of column names. This will be the same for every row.

-> [Maybe Utf8]

List of column values, as returned by columnText.

-> IO () 

Statement management

prepare :: Database -> Utf8 -> IO (Either Error (Maybe Statement)) Source #

If the query contains no SQL statements, this returns Right Nothing.

reset :: Statement -> IO (Either Error ()) Source #


  • If the most recent step call failed, this will return the corresponding error.
  • This does not reset the bindings on a prepared statement. Use clearBindings to do that.

finalize :: Statement -> IO (Either Error ()) Source #

Warning: If the most recent step call failed, this will return the corresponding error.

clearBindings :: Statement -> IO () Source #

Set all parameters in the prepared statement to null.

statementSql :: Statement -> IO (Maybe Utf8) Source #

Return a copy of the original SQL text used to compile the statement.

Parameter and column information

bindParameterCount :: Statement -> IO ParamIndex Source #

This returns the index of the largest (rightmost) parameter. Note that this is not necessarily the number of parameters. If numbered parameters like ?5 are used, there may be gaps in the list.

See ParamIndex for more information.

Binding values to a prepared statement

Reading the result row

control loading of extensions

Result statistics

changes :: Database -> IO Int Source #

Return the number of rows that were changed, inserted, or deleted by the most recent INSERT, DELETE, or UPDATE statement.

totalChanges :: Database -> IO Int Source #

Return the total number of row changes caused by INSERT, DELETE, or UPDATE statements since the Database was opened.

Create custom SQL functions

createFunction Source #


:: Database 
-> Utf8

Name of the function.

-> Maybe ArgCount

Number of arguments. Nothing means that the function accepts any number of arguments.

-> Bool

Is the function deterministic?

-> (FuncContext -> FuncArgs -> IO ())

Implementation of the function.

-> IO (Either Error ())

Create a custom SQL function or redefine the behavior of an existing function.

createAggregate Source #


:: Database 
-> Utf8

Name of the function.

-> Maybe ArgCount

Number of arguments.

-> a

Initial aggregate state.

-> (FuncContext -> FuncArgs -> a -> IO a)

Process one row and update the aggregate state.

-> (FuncContext -> a -> IO ())

Called after all rows have been processed. Can be used to construct the returned value from the aggregate state.

-> IO (Either Error ()) 

Like createFunction except that it creates an aggregate function.

deleteFunction :: Database -> Utf8 -> Maybe ArgCount -> IO (Either Error ()) Source #

Delete an SQL function (scalar or aggregate).

Extract function arguments

Set the result of a function

Create custom collations

createCollation Source #


:: Database 
-> Utf8

Name of the collation.

-> (Utf8 -> Utf8 -> Ordering)

Comparison function.

-> IO (Either Error ()) 

deleteCollation :: Database -> Utf8 -> IO (Either Error ()) Source #

Delete a collation.

Interrupting a long-running query

interrupt :: Database -> IO () Source #

Cause any pending operation on the Database handle to stop at its earliest opportunity. This simply sets a flag and returns immediately. It does not wait for the pending operation to finish.

You'll need to compile with -threaded for this to do any good. Without -threaded, FFI calls block the whole RTS, meaning interrupt would never run at the same time as step.

Incremental blob I/O

blobOpen Source #


:: Database 
-> Utf8

The symbolic name of the database (e.g. "main").

-> Utf8

The table name.

-> Utf8

The column name.

-> Int64

The ROWID of the row.

-> Bool

Open the blob for read-write.

-> IO (Either Error Blob)

Open a blob for incremental I/O.

blobRead Source #


:: Blob 
-> Int

Number of bytes to read.

-> Int

Offset within the blob.

-> IO (Either Error ByteString) 

blobReadBuf :: Blob -> Ptr a -> Int -> Int -> IO (Either Error ()) Source #

Online Backup API

backupInit Source #


:: Database

Destination database handle

-> Utf8

Destination database name

-> Database

Source database handle

-> Utf8

Source database name

-> IO (Either Error Backup) 


newtype FuncContext Source #

The context in which a custom SQL function is executed.


FuncContext (Ptr CContext) 

data FuncArgs Source #

The arguments of a custom SQL function.


FuncArgs CArgCount (Ptr (Ptr CValue)) 

data Blob Source #

The type of blob handles used for incremental blob I/O


Blob Database (Ptr CBlob) 


Eq Blob Source # 


(==) :: Blob -> Blob -> Bool #

(/=) :: Blob -> Blob -> Bool #

Show Blob Source # 


showsPrec :: Int -> Blob -> ShowS #

show :: Blob -> String #

showList :: [Blob] -> ShowS #

data Backup Source #

A handle for an online backup process.


Backup Database (Ptr CBackup) 


Results and errors

data BackupStepResult Source #



There are still more pages to be copied.


All pages were successfully copied.

data Error Source #



Successful result


SQL error or missing database


Internal logic error in SQLite


Access permission denied


Callback routine requested an abort


The database file is locked


A table in the database is locked


A malloc() failed


Attempt to write a readonly database


Operation terminated by sqlite3_interrupt()


Some kind of disk I/O error occurred


The database disk image is malformed


Unknown opcode in sqlite3_file_control()


Insertion failed because database is full


Unable to open the database file


Database lock protocol error


Database is empty


The database schema changed


String or BLOB exceeds size limit


Abort due to constraint violation


Data type mismatch


Library used incorrectly


Uses OS features not supported on host


Authorization denied


Auxiliary database format error


2nd parameter to sqlite3_bind out of range


File opened that is not a database file


sqlite3_step() has another row ready


sqlite3_step() has finished executing

Special types

newtype Utf8 Source #

A ByteString containing UTF8-encoded text with no NUL characters.


Utf8 ByteString 


Eq Utf8 Source # 


(==) :: Utf8 -> Utf8 -> Bool #

(/=) :: Utf8 -> Utf8 -> Bool #

Ord Utf8 Source # 


compare :: Utf8 -> Utf8 -> Ordering #

(<) :: Utf8 -> Utf8 -> Bool #

(<=) :: Utf8 -> Utf8 -> Bool #

(>) :: Utf8 -> Utf8 -> Bool #

(>=) :: Utf8 -> Utf8 -> Bool #

max :: Utf8 -> Utf8 -> Utf8 #

min :: Utf8 -> Utf8 -> Utf8 #

Show Utf8 Source # 


showsPrec :: Int -> Utf8 -> ShowS #

show :: Utf8 -> String #

showList :: [Utf8] -> ShowS #

IsString Utf8 Source #
fromString = Utf8 . encodeUtf8 . pack


fromString :: String -> Utf8 #

Semigroup Utf8 Source # 


(<>) :: Utf8 -> Utf8 -> Utf8 #

sconcat :: NonEmpty Utf8 -> Utf8 #

stimes :: Integral b => b -> Utf8 -> Utf8 #

Monoid Utf8 Source # 


mempty :: Utf8 #

mappend :: Utf8 -> Utf8 -> Utf8 #

mconcat :: [Utf8] -> Utf8 #

newtype ParamIndex Source #

Index of a parameter in a parameterized query. Parameter indices start from 1.

When a query is prepared, SQLite allocates an array indexed from 1 to the highest parameter index. For example:

>Right stmt <- prepare conn "SELECT ?1, ?5, ?3, ?"
>bindParameterCount stmt
ParamIndex 6

This will allocate an array indexed from 1 to 6 (? takes the highest preceding index plus one). The array is initialized with null values. When you bind a parameter with bindSQLData, it assigns a new value to one of these indices.

See for the syntax of parameter placeholders, and how parameter indices are assigned.


ParamIndex Int 


Bounded ParamIndex Source #

Limit min/max bounds to fit into SQLite's native parameter ranges.

Enum ParamIndex Source # 
Eq ParamIndex Source # 
Integral ParamIndex Source # 
Num ParamIndex Source # 
Ord ParamIndex Source # 
Real ParamIndex Source # 
Show ParamIndex Source #

This just shows the underlying integer, without the data constructor.

FFIType ParamIndex CParamIndex Source # 

newtype ColumnIndex Source #

Index of a column in a result set. Column indices start from 0.


ColumnIndex Int 


Bounded ColumnIndex Source #

Limit min/max bounds to fit into SQLite's native parameter ranges.

Enum ColumnIndex Source # 
Eq ColumnIndex Source # 
Integral ColumnIndex Source # 
Num ColumnIndex Source # 
Ord ColumnIndex Source # 
Real ColumnIndex Source # 
Show ColumnIndex Source #

This just shows the underlying integer, without the data constructor.

FFIType ColumnIndex CColumnIndex Source # 

type ColumnCount = ColumnIndex Source #

Number of columns in a result set.

newtype ArgCount Source #

Number of arguments of a user defined SQL function.


ArgCount Int 


Bounded ArgCount Source # 
Enum ArgCount Source # 
Eq ArgCount Source # 
Integral ArgCount Source # 
Num ArgCount Source # 
Ord ArgCount Source # 
Real ArgCount Source # 
Show ArgCount Source #

This just shows the underlying integer, without the data constructor.

FFIType ArgCount CArgCount Source # 

type ArgIndex = ArgCount Source #

Index of an argument to a custom function. Indices start from 0.