Module Rel_sqlite3

SQLite3 support.

Thread safety. The connections exposed by this module are not thread safe. Besides using different connections with different threads provides proper isolation. If you are using multiple threads Rel_pool the connections.

Concurrency. Reader and writer concurrency can be improved by setting your database in WAL mode.

Warning. Functions of this module may raise Invalid_argument if any string given to C contains null bytes.

References.

TODO.

Errors

type error

The type for errors.

module Error : sig ... end

Errors.

val string_error : ('a, error) Stdlib.result -> ('a, string) Stdlib.result

string_error r is Result.map_error Error.message r.

Library configuration and information

val version : unit -> string

version () is the version of the SQLite library.

Database connection

type mode =
  1. | Read
  2. | Read_write
  3. | Read_write_create
    (*

    Created if missing (default).

    *)
  4. | Memory
    (*

    In memory.

    *)

The type for connection modes.

type mutex =
  1. | No
    (*

    Multi-threaded mode (this is not a typo).

    *)
  2. | Full
    (*

    Serialized mode.

    *)

The type for specifying the threading mode.

type t

The type for SQLite3 connections. Warning. Rel_sqlite3's abstraction of connections is not thread-safe.

val open' : ?foreign_keys:bool -> ?stmt_cache_size:int -> ?vfs:string -> ?uri:bool -> ?mutex:mutex -> mode:mode -> string -> (t, error) Stdlib.result

open' file opens a connection on file file:

  • mode defines the connection mode.
  • mutex defines the threading mode. Defauls to Full.
  • uri, if true (default) the URI syntax is allowed for file.
  • vfs is the vfs to use.
  • stmt_cache_size is the connection's statement cache size, it defaults to 10.
  • foreign_keys's value is used to immediately invoke the foreign_keys pragma on the database connection. Defaults to true to enforce constraints, this is not SQLite's default.

See sqlite3_open_v2 for more details about the parameters (except stmt_cache_size and foreign_keys).

val close : t -> (unit, error) Stdlib.result

close db closes the connection to database db.

This will only ever error if there are ressources of db that were not disposed properly. For example if you use the low-level statement interface and forget to dispose the statements before closing the database.

val busy_timeout_ms : t -> int -> (unit, error) Stdlib.result

busy_timout_ms db d sets the busy timeout to d milliseconds. If you are planning to perform concurrent writes you should, among other things, set this to a suitable amount.

val changes : t -> int

changes db is the number of rows modified, inserted or deleted by the last executed statement on db.

val last_insert_rowid : t -> int64

last_insert_rowid db is the rowid (or INTEGER PRIMARY KEY) of the most recent successful INSERT into a rowid table.

Prepared statement cache

val stmt_cache_size : t -> int

stmt_cache_size d is d's maximal number of cached prepared statements.

val set_stmt_cache_size : t -> int -> unit

set_stmt_cache_size d max sets the maximal number of cached prepared statements to max in d. Note that this clears the cache.

val clear_stmt_cache : t -> unit

clear_stmt_cache d clears the cache of prepared statements.

SQL execution

val exec_sql : t -> string -> (unit, error) Stdlib.result

exec_sql db sql executes the SQL statements sql on db and ignores the result. sql is neither prepared nor cached. Use this to execute SQL scripts. If you are doing lots of inserts or updates make a transaction to ensure good performance.

val fold : t -> 'r Rel_sql.Stmt.t -> ('r -> 'c -> 'c) -> 'c -> ('c, error) Stdlib.result

fold db st f acc folds with f over the results of the single statement st. st is compiled to a prepared statement which is cached. If st is made of more than one statement subsequent statements are ignored.

val first : t -> 'r Rel_sql.Stmt.t -> ('r option, error) Stdlib.result

first db st is the first row (if any) of the result of the single statement st. Subsequent rows are discarded. st is compiled to a prepred statement which is cached. If st is made of more than one statement subsequent statements are ignored.

val exec : t -> unit Rel_sql.Stmt.t -> (unit, error) Stdlib.result

exec db st is like fold but executes statement sql only for its side effect.

type transaction_kind = [
  1. | `Deferred
  2. | `Immediate
  3. | `Exclusive
]

The type for transaction kinds.

val with_transaction : transaction_kind -> t -> (t -> ('a, 'b) Stdlib.result) -> (('a, 'b) Stdlib.result, error) Stdlib.result

with_transaction kind db f wraps the call to f db in an SQL transaction of given kind. If f raises, returns an error or if the commit fails (including if the error was Error.busy_timeout, FIXME should we include a retry parameter ?) the transaction is rollback.

Note. Nested transactions are not supported so f should not call with_transaction itself (use savepoints if you need nested transactions).

val explain : ?query_plan:bool -> t -> 'a Rel_sql.Stmt.t -> (string, error) Stdlib.result

explain ~query_plan db st explains statement st or its query plan if query_plan is true (defaults to false.

Low-level interface

module Stmt : sig ... end

Low-level prepared statement interface.

module Backup : sig ... end

Low-level backup interface.

SQL

module Dialect : Rel_sql.DIALECT

Dialect implements the sqlite3 SQL dialect.

val dialect : Rel_sql.dialect

dialect is the sqlite3 dialect.

Schema

val schema_of_db : ?schema:Rel.Schema.name -> t -> (Rel.Schema.t * string list, error) Stdlib.result

schema_of_db db derives a best-effort schema value for the live database db. Note that the tables and rows and internal structure is not functional. It is however sufficient for schema renderings and computing schema changes.

The returned list of strings is a list of issues to report to the end-user that indicate that the resulting schema may not faithfully represent db. If empty all is well.