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

Safe HaskellNone




Connection management

Simple query execution

exec :: Database -> Text -> IO ()Source

Execute zero or more SQL statements delimited by semicolons.

execPrint :: Database -> Text -> IO ()Source

Like exec, but print result rows to stdout.

This is mainly for convenience when experimenting in GHCi. The output format may change in the future.

execWithCallback :: Database -> Text -> ExecCallback -> IO ()Source

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

type ExecCallbackSource


 = ColumnCount

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

-> [Text]

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

-> [Maybe Text]

List of column values, as returned by columnText.

-> IO () 

Statement management

prepare :: Database -> Text -> IO StatementSource

Unlike exec, prepare only executes the first statement, and ignores subsequent statements.

If the query string contains no SQL statements, this fails.

reset :: Statement -> IO ()Source

Note that in the C API, sqlite3_reset returns an error code if the most recent sqlite3_step indicated an error. We do not replicate that behavior here. reset never throws an exception.

clearBindings :: Statement -> IO ()Source

Set all parameters in the prepared statement to null.

Parameter and column information

bindParameterCount :: Statement -> IO ParamIndexSource

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.

bindParameterName :: Statement -> ParamIndex -> IO (Maybe Text)Source

Return the N-th SQL parameter name.

Named parameters are returned as-is. E.g. ":v" is returned as Just ":v". Unnamed parameters, however, are converted to Nothing.

Note that the parameter index starts at 1, not 0.

Binding values to a prepared statement

bindSQLData :: Statement -> ParamIndex -> SQLData -> IO ()Source

If the index is not between 1 and bindParameterCount inclusive, this fails with ErrorRange. Otherwise, it succeeds, even if the query skips this index by using numbered parameters.


> stmt <- prepare conn "SELECT ?1, ?3, ?5"
> bindSQLData stmt 1 (SQLInteger 1)
> bindSQLData stmt 2 (SQLInteger 2)
> bindSQLData stmt 6 (SQLInteger 6)
*** Exception: SQLite3 returned ErrorRange while attempting to perform bind int64.
> step stmt >> columns stmt
[SQLInteger 1,SQLNull,SQLNull]

bind :: Statement -> [SQLData] -> IO ()Source

Convenience function for binding values to all parameters. This will fail if the list has the wrong number of parameters.

Reading the result row

Warning: column and columns will throw a DecodeError if any TEXT datum contains invalid UTF-8.

columnText :: Statement -> ColumnIndex -> IO TextSource

This will throw a DecodeError if the datum contains invalid UTF-8. If this behavior is undesirable, you can use columnText from Database.SQLite3.Direct, which does not perform conversion to Text.

Result statistics

changes :: Database -> IO IntSource

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

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.

interruptibly :: Database -> IO a -> IO aSource

Make it possible to interrupt the given database operation with an asynchronous exception. This only works if the program is compiled with base >= 4.3 and -threaded.

It works by running the callback in a forked thread. If interrupted, it uses interrupt to try to stop the operation.


data SQLError Source

Exception thrown when SQLite3 reports an error.

direct-sqlite may throw other types of exceptions if you misuse the API.




sqlError :: !Error

Error code returned by API call

sqlErrorDetails :: Text

Text describing the error

sqlErrorContext :: Text

Indicates what action produced this error, e.g. exec "SELECT * FROM foo"

Results and errors

data StepResult Source



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 integers

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 


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

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

FFIType ParamIndex CParamIndex 

newtype ColumnIndex Source

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


ColumnIndex Int 


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

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

FFIType ColumnIndex CColumnIndex 

type ColumnCount = ColumnIndexSource

Number of columns in a result set.