Module `Sqlite3`

API for Sqlite 3.* databases

Exceptions

exception InternalError of string: InternalError reason is raised when the bindings detect an unknown/unsupported situation.

exception Error of string: Error reason is raised when some SQL operation is called on a nonexistent handle and the functions does not return a return code, or if there is no error code corresponding to this error. Functions returning return codes communicate errors by returning the specific error code.

exception RangeError of int * int: RangeError (index, maximum) is raised if some column or bind operation refers to a nonexistent column or binding. The first entry of the returned tuple is the specified index, the second is the limit which was violated.

exception DataTypeError of string: DataTypeError msg is raised when you attempt to convert a Data.t structure to an object via an invalid conversion.

exception SqliteError of string: SqliteError err_msg is raised after calling Rc.check on a return code that does not indicate success.

Library Information

val sqlite_version : unit -> int

sqlite_version ()

returns: the version of the SQLite3 library being used, in format 3XXXYYY where XXX is the minor version and YYY is the patch level. For example, 3030001 for 3.30.1.

val sqlite_version_info : unit -> string

sqlite_version_info ()

returns: the version of the SQLite3 library being used in a human-readable string.

Types

type db

Database handle. Used to store information regarding open databases and the error code from the last operation if the function implementing that operation takes a database handle as a parameter.

see https://sqlite.org/threadsafe.html

about thread safety when accessing database handles and also consider using the mutex flag with db_open if necessary.

NOTE: database handles are closed (see db_close) automatically when they are reclaimed by the GC unless they have already been closed earlier by the user. It is good practice to manually close database handles to free resources as quickly as possible.

type stmt

Compiled statement handle. Stores information about compiled statements created by the prepare or prepare_tail functions.

see https://sqlite.org/threadsafe.html: about thread safety when accessing statement handles.

type header = string: Type of name of a column returned by queries.

type headers = header array: Type of names of columns returned by queries.

type row = string option array: Type of row data (with potential NULL-values)

type row_not_null = string array: Type of row data (without NULL-values)

Return codes

module Rc : sig ... end

Column data types

module Data : sig ... end

General database operations

val db_open : ?⁠mode:[ `READONLY | `NO_CREATE ] -> ?⁠uri:bool -> ?⁠memory:bool -> ?⁠mutex:[ `NO | `FULL ] -> ?⁠cache:[ `SHARED | `PRIVATE ] -> ?⁠vfs:string -> string -> db

db_open ?mode ?uri ?memory ?mutex ?cache ?vfs filename opens the database file filename, and returns a database handle.

Special filenames: ":memory:" and "" open an in-memory or temporary database respectively. Behaviour explained here: https://www.sqlite.org/inmemorydb.html

The optional arguments mode, uri, memory and mutex are only meaningful with SQLite versions >= 3.5, cache only for versions >= 3.6.18. For older versions an exception will be raised if any of them is set to a non-default value. The database is opened read-only if `READONLY is passed as mode. The database file will not be created if it is missing and `NO_CREATE is set. The uri parameter enables URI filename interpretation and corresponds to SQLITE_OPEN_URI in the SQLite3 API. The memory parameter opens an in-memory database and corresponds to SQLITE_OPEN_MEMORY in the SQLite3 API. mutex determines how the database is accessed. The mutex parameters `NO and `FULL correspond to SQLITE_OPEN_NOMUTEX and SQLITE_OPEN_FULLMUTEX in the SQLite3 API respectively. The cache parameters `SHARED and `PRIVATE correspond to SQLITE_OPEN_SHAREDCACHE and SQLITE_OPEN_PRIVATECACHE in the SQLite3 API respectively.

parameter mode: default = read-write, create

parameter uri: default = false

parameter memory: default = false

parameter mutex: default = nothing

parameter cache: default = nothing

parameter vfs: default = nothing

val db_close : db -> bool

db_close db closes database db and invalidates the handle.

returns: false if database was busy (database not closed in this case!), true otherwise.

raises SqliteError: if an invalid database handle is passed.

val let& : db -> (db -> 'a) -> 'a

let& db = db_open "..." in ...scope that uses db... ensures that the database db is safely closed at the end of the scope, even if there is an exception somewhere in the scope.

raises Fun.Finally_raised: if the database could not be closed successfully.

val enable_load_extension : db -> bool -> bool

enable_load_extension db onoff enable/disable the SQLite3 load extension.

returns: false if the operation fails, true otherwise.

val errcode : db -> Rc.t

errcode db

returns: the error code of the last operation on database db.

raises SqliteError: if an invalid database handle is passed.

val errmsg : db -> string

errmsg db

returns: the error message of the last operation on database db.

raises SqliteError: if an invalid database handle is passed.

val last_insert_rowid : db -> int64

last_insert_rowid db

returns: the index of the row inserted by the last operation on database db.

raises SqliteError: if an invalid database handle is passed.

val exec : db -> ?⁠cb:(row -> headers -> unit) -> string -> Rc.t

exec db ?cb sql performs SQL-operation sql on database db. If the operation contains query statements, then the callback function cb will be called for each matching row. The first parameter of the callback contains the contents of the row, the second parameter contains the headers of the columns associated with the row. Exceptions raised within the callback will abort the execution and escape exec.

returns: the return code of the operation.

parameter cb: default = no callback

raises SqliteError: if an invalid database handle is passed.

val exec_no_headers : db -> cb:(row -> unit) -> string -> Rc.t

exec_no_headers db ?cb sql performs SQL-operation sql on database db. If the operation contains query statements, then the callback function cb will be called for each matching row. The parameter of the callback is the contents of the row. Exceptions raised within the callback will abort the execution and escape exec_no_headers.

returns: the return code of the operation.

raises SqliteError: if an invalid database handle is passed.

val exec_not_null : db -> cb:(row_not_null -> headers -> unit) -> string -> Rc.t

exec_not_null db ~cb sql performs SQL-operation sql on database db. If the operation contains query statements, then the callback function cb will be called for each matching row. The first parameter of the callback is the contents of the row, which must not contain NULL-values, the second paramater are the headers of the columns associated with the row. Exceptions raised within the callback will abort the execution and escape exec_not_null.

returns: the return code of the operation.

raises SqliteError: if an invalid database handle is passed.

raises SqliteError: if a row contains NULL.

val exec_not_null_no_headers : db -> cb:(row_not_null -> unit) -> string -> Rc.t

exec_not_null_no_headers db ~cb sql performs SQL-operation sql on database db. If the operation contains query statements, then the callback function cb will be called for each matching row. The parameter of the callback is the contents of the row, which must not contain NULL-values. Exceptions raised within the callback will abort the execution and escape exec_not_null_no_headers.

returns: the return code of the operation.

raises SqliteError: if an invalid database handle is passed.

raises SqliteError: if a row contains NULL.

val changes : db -> int

changes db

returns: the number of rows that were changed or inserted or deleted by the most recently completed SQL statement on database db.

Prepared Statements

val prepare : db -> string -> stmt

prepare db sql compile SQL-statement sql for database db into bytecode. The statement may be only partially compiled. In this case prepare_tail can be called on the returned statement to compile the remaining part of the SQL-statement.

NOTE: this really uses the C-function sqlite3_prepare_v2, i.e. avoids the older, deprecated sqlite3_prepare-function.

raises SqliteError: if an invalid database handle is passed.

raises SqliteError: if the statement could not be prepared.

val prepare_or_reset : db -> stmt option Stdlib.ref -> string -> stmt

prepare_or_reset db opt_stmt_ref sql if opt_stmt_ref contains Some stmt, then stmt will be reset and returned. Otherwise fresh statement stmt will be prepared, stored as Some stmt in opt_stmt_ref and then returned. This is useful for executing multiple identical commands in a loop, because we can more efficiently reuse the statement from previous iterations.

raises SqliteError: if the statement could not be prepared or reset.

val prepare_tail : stmt -> stmt option

prepare_tail stmt compile the remaining part of the SQL-statement stmt to bytecode.

returns

None if there was no remaining part, or Some remaining_part otherwise.

NOTE: this really uses the C-function sqlite3_prepare_v2, i.e. avoids the older, deprecated sqlite3_prepare-function.

raises SqliteError: if the statement could not be prepared.

val recompile : stmt -> unit

recompile stmt recompiles the SQL-statement associated with stmt to bytecode. The statement may be only partially compiled. In this case prepare_tail can be called on the statement to compile the remaining part of the SQL-statement. Call this function if the statement expires due to some schema change.

raises SqliteError: if the statement could not be recompiled.

val finalize : stmt -> Rc.t

finalize stmt finalizes the statement stmt. After finalization, the only valid usage of the statement is to use it in prepare_tail, or to recompile it.

returns: the return code of this operation.

raises SqliteError: if the statement could not be finalized.

Data query

val data_count : stmt -> int

data_count stmt

returns: the number of columns in the result of the last step of statement stmt.

raises SqliteError: if the statement is invalid.

val column_count : stmt -> int

column_count stmt

returns: the number of columns that would be returned by executing statement stmt.

raises SqliteError: if the statement is invalid.

val column : stmt -> int -> Data.t

column stmt n

returns: the data in column n of the result of the last step of statement stmt.

raises RangeError: if n is out of range.

raises SqliteError: if the statement is invalid.

val column_bool : stmt -> int -> bool

column_bool stmt n

returns: the data in column n of the result of the last step of statement stmt as a bool.

raises RangeError: if n is out of range.

raises SqliteError: if the statement is invalid.

val column_text : stmt -> int -> string

column_text stmt n

returns: the data in column n of the result of the last step of statement stmt as text (a string).

raises RangeError: if n is out of range.

raises SqliteError: if the statement is invalid.

val column_int : stmt -> int -> int

column_int stmt n

returns: the data in column n of the result of the last step of statement stmt as an int.

raises RangeError: if n is out of range.

raises Failure: if the integer conversion over- or underflows.

raises SqliteError: if the statement is invalid.

val column_nativeint : stmt -> int -> nativeint

column_nativeint stmt n

returns: the data in column n of the result of the last step of statement stmt as a nativeint.

raises RangeError: if n is out of range.

raises Failure: if the integer conversion over- or underflows.

raises SqliteError: if the statement is invalid.

val column_int32 : stmt -> int -> int32

column_int32 stmt n

returns: the data in column n of the result of the last step of statement stmt as an int32. Note that this function exactly corresponds to the C-library function sqlite3_column_int and does not check for over- or underflow during integer conversions.

raises RangeError: if n is out of range.

raises SqliteError: if the statement is invalid.

val column_int64 : stmt -> int -> int64

column_int64 stmt n

returns: the data in column n of the result of the last step of statement stmt as an int64. Note that this function exactly corresponds to the C-library function sqlite3_column_int64 and does not check for over- or underflow during integer conversions.

raises RangeError: if n is out of range.

raises SqliteError: if the statement is invalid.

val column_double : stmt -> int -> float

column_double stmt n

returns: the data in column n of the result of the last step of statement stmt as a double float.

raises RangeError: if n is out of range.

raises SqliteError: if the statement is invalid.

val column_blob : stmt -> int -> string

column_blob stmt n

returns: the blob string in column n of the result of the last step of statement stmt as a string.

raises RangeError: if n is out of range.

raises SqliteError: if the statement is invalid.

val column_name : stmt -> int -> header

column_name stmt n

returns: the header of column n in the result set of statement stmt.

raises RangeError: if n is out of range.

raises SqliteError: if the statement is invalid.

val column_decltype : stmt -> int -> string option

column_decltype stmt n

returns: the declared type of the specified column in the result set of statement stmt as Some str if available, or as None otherwise.

raises RangeError: if n is out of range.

raises SqliteError: if the statement is invalid.

Binding data to statements

val bind : stmt -> int -> Data.t -> Rc.t

bind stmt pos data binds the value data to the free variable at position pos of statement stmt. NOTE: the first variable has index 1!

returns: the return code of this operation.

raises RangeError: if pos is out of range.

raises SqliteError: if the statement is invalid.

val bind_text : stmt -> int -> string -> Rc.t

bind_text stmt pos str binds the string str to the parameter at position pos of the statement stmt as text.

returns: the return code of this operation.

raises RangeError: if pos is out of range.

raises SqliteError: if the statement is invalid.

val bind_bool : stmt -> int -> bool -> Rc.t

bind_bool stmt pos b binds the boolean b to the parameter at position pos of the statement stmt without having to manually convert it to an int64 for use with Data.INT. true is turned into 1, false into 0.

returns: the return code of this operation.

raises RangeError: if pos is out of range.

raises SqliteError: if the statement is invalid.

val bind_int : stmt -> int -> int -> Rc.t

bind_int stmt pos n binds the integer n to the parameter at position pos of the statement stmt without having to manually convert it to an int64 for use with Data.INT.

returns: the return code of this operation.

raises RangeError: if pos is out of range.

raises SqliteError: if the statement is invalid.

val bind_nativeint : stmt -> int -> nativeint -> Rc.t

bind_nativeint stmt pos n binds the integer n to the parameter at position pos of the statement stmt without having to manually convert it to an int64 for use with Data.INT.

returns: the return code of this operation.

raises RangeError: if pos is out of range.

raises SqliteError: if the statement is invalid.

val bind_int32 : stmt -> int -> int32 -> Rc.t

bind_int32 stmt pos n binds the 32-bit integer n to the parameter at position pos of the statement stmt without having to manually convert it to an int64 for use with Data.INT.

returns: the return code of this operation.

raises RangeError: if pos is out of range.

raises SqliteError: if the statement is invalid.

val bind_int64 : stmt -> int -> int64 -> Rc.t

bind_int64 stmt pos n binds the 64-bit integer n to the parameter at position pos of the statement stmt.

returns: the return code of this operation.

raises RangeError: if pos is out of range.

raises SqliteError: if the statement is invalid.

val bind_double : stmt -> int -> float -> Rc.t

bind_double stmt pos n binds the float n to the parameter at position pos of the statement stmt.

returns: the return code of this operation.

raises RangeError: if pos is out of range.

raises SqliteError: if the statement is invalid.

val bind_blob : stmt -> int -> string -> Rc.t

bind_blob stmt pos str binds the string str to the parameter at position pos of the statement stmt as a blob.

returns: the return code of this operation.

raises RangeError: if pos is out of range.

raises SqliteError: if the statement is invalid.

val bind_values : stmt -> Data.t list -> Rc.t

bind_values stmt lst binds the Nth element of lst to the Nth parameter of the statement.

returns: the return code of the first binding that fails, or Rc.OK.

raises RangeError: if there aren't at least as many parameters as there are elements of the list.

raises SqliteError: if the statement is invalid.

val bind_name : stmt -> string -> Data.t -> Rc.t

bind_name stmt name data binds the value data to the named parameter name of statement stmt.

returns: the return code of this operation.

raises Not_found: if name does not exist.

raises SqliteError: if the statement is invalid.

val bind_names : stmt -> (string * Data.t) list -> Rc.t

bind_names stmt lst binds the (name, data) pairs in lst to the parameters of statement stmt.

returns: the return code of the first binding that fails, or Rc.OK.

raises Not_found: if a name does not exist.

raises SqliteError: if the statement is invalid.

val bind_parameter_count : stmt -> int

bind_parameter_count stmt

returns: the number of free variables in statement stmt.

raises SqliteError: if the statement is invalid.

val bind_parameter_name : stmt -> int -> string option

bind_parameter_name stmt pos

returns: Some parameter_name of the free variable at position pos of statement stmt, or None if it is ordinary ("?").

raises RangeError: if pos is out of range.

raises SqliteError: if the statement is invalid.

val bind_parameter_index : stmt -> string -> int

bind_parameter_index stmt name

returns: the position of the free variable with name name in statement stmt.

raises Not_found: if name was not found.

raises SqliteError: if the statement is invalid.

val clear_bindings : stmt -> Rc.t

clear_bindings stmt resets all bindings associated with prepared statement stmt.

returns: the return code of this operation.

raises SqliteError: if the statement is invalid.

Executing statements

val step : stmt -> Rc.t

step stmt performs one step of the query associated with SQL-statement stmt.

returns: the return code of this operation.

raises SqliteError: if the step could not be executed.

val reset : stmt -> Rc.t

reset stmt resets the statement stmt, e.g. to restart the query, perhaps with different bindings.

returns: the return code of this operation.

raises SqliteError: if the statement could not be reset.

val iter : stmt -> f:(Data.t array -> unit) -> Rc.t

iter stmt ~f will call f once per row returned by stepping through stmt. The statement is automatically reset afterwards.

returns: Rc.DONE on success or another return code on error.

raises SqliteError: if the statement is invalid.

val fold : stmt -> f:('a -> Data.t array -> 'a) -> init:'a -> Rc.t * 'a

fold stmt ~f ~init folds over the rows returned by stmt with function f and initial value init. The statement is automatically reset afterwards.

returns: (rc, acc) where acc is the last accumulated value returned by f after being called on a row. rc is Rc.DONE if all rows were processed and if the statement could be reset, otherwise an error code.

raises SqliteError: if the statement is invalid.

Stepwise query convenience functions

val row_blobs : stmt -> string array

row_blobs stmt

returns: the blobs returned by the last query step performed with statement stmt (array of blobs).

raises SqliteError: if the statement is invalid.

val row_data : stmt -> Data.t array

row_data stmt

returns: all data values in the row returned by the last query step performed with statement stmt.

raises SqliteError: if the statement is invalid.

val row_names : stmt -> headers

row_names stmt

returns: all column headers of the row returned by the last query step performed with statement stmt.

raises SqliteError: if the statement is invalid.

val row_decltypes : stmt -> string option array

row_decltypes stmt

returns: all column type declarations of the row returned by the last query step performed with statement stmt.

raises SqliteError: if the statement is invalid.

User-defined functions

val create_funN : db -> string -> (Data.t array -> Data.t) -> unit

create_funN db name f registers function f under name name with database handle db. The function has arity N.

raises SqliteError: if an invalid database handle is passed.

val create_fun0 : db -> string -> (unit -> Data.t) -> unit

create_funN db name f registers function f under name name with database handle db. The function has arity 0.

raises SqliteError: if an invalid database handle is passed.

val create_fun1 : db -> string -> (Data.t -> Data.t) -> unit

create_fun1 db name f registers function f under name name with database handle db. The function has arity 1.

raises SqliteError: if an invalid database handle is passed.

val create_fun2 : db -> string -> (Data.t -> Data.t -> Data.t) -> unit

create_fun2 db name f registers function f under name name with database handle db. The function has arity 2.

raises SqliteError: if an invalid database handle is passed.

val create_fun3 : db -> string -> (Data.t -> Data.t -> Data.t -> Data.t) -> unit

create_fun3 db name f registers function f under name name with database handle db. The function has arity 3.

raises SqliteError: if an invalid database handle is passed.

val delete_function : db -> string -> unit

delete_function db name deletes function with name name from database handle db.

raises SqliteError: if an invalid database handle is passed.

module Aggregate : sig ... end

module Backup : sig ... end

Utility functions

val busy_timeout : db -> int -> unit

busy_timeout db ms sets a busy handler that sleeps for a specified amount of time when a table is locked. The handler will sleep multiple times until at least ms milliseconds of sleeping have accumulated.

raises SqliteError: if an invalid database handle is passed.

val sleep : int -> int

sleep ms sleeps at least ms milliseconds.

returns: the number of milliseconds of sleep actually requested from the operating system.