Module Fun_postgresql

Use this module for PostgreSQL database queries.

Note: does not support batch_insert out of the box because PostgreSQL uses numbered placeholders, which makes it much more difficult to parse out and modify the insert query with the correct number of placeholders.

include Fun_sql.Query_sig with type ('row, 'r) ret = ('row, 'r) Fun_sql.Query.ret

Database-agnostic query helpers

val sql : ('a, Stdlib.Format.formatter, unit, string) Stdlib.format4 -> 'a

Convenience for formatting query strings with placeholders. Eg

sql "select name from people where id = %a" placeholder 0
type ('row, 'r) ret = ('row, 'r) Fun_sql.Query.ret

A decoder of a single row of the resultset from running a query.

val unit : ('row, unit) ret

unit indicates that the query doesn't return any meaningful output.

val ret : ('row -> 'r) -> ('row, 'r Stdlib.Seq.t) ret

ret decode is a custom return type encoding for a resultset into a sequence of values of the type decoded by decode.

decode constructs a value of the custom type if possible, else raises Failure.

Note that the sequence rows of the resultset is unfolded as it is read from the database. It can only be traversed once, with e.g. List.of_seq or Seq.iter. If traversed multiple times, it will raise Failure.

  • raises Invalid_argument

    if any row cannot be decoded.

  • raises Failure

    if an unexpected result code is encountered.

val one : 'a Stdlib.Seq.t -> 'a option
val all : 'a Stdlib.Seq.t -> 'a list
include Fun_sql.Sql with type db = Postgresql.connection and type arg = string and type row = string array

Database-specific definitions

type db = Postgresql.connection

The database connection or file, etc.

type arg = string

A value sent to the database in the place of a query parameter.

type row = string array

A tuple from the database.

val placeholder : Stdlib.Format.formatter -> int -> unit

A generic way to write placeholders for different database drivers' prepared statement parameters.

ℹ️ Placeholders are 0-indexed.

Query runners

val query : db -> string -> arg list -> (row, 'r) Fun_sql.Query.ret -> 'r

The main function through which queries are run is the query function. This function always creates a prepared statement for each partial call to query db sql. This prepared statement can then be called with the actual arguments (if any) and the resultset row decoder:

let add_person =
  query db (sql "insert into people (name, age) values (%a, %a)" placeholder 0 placeholder 1)
let add_person name age = add_person Arg.[text name; int age] unit
  • raises Invalid_argument

    if trying to create multiple prepared statements for the same SQL query in PostgreSQL. To avoid this, just create the prepared statement once only and call it whenever needed, as shown above.

val exec_script : db -> string -> unit

exec_script db sql executes the sql script (possibly made up of multiple statements) in the database db. Note that it ignores any rows returned by any of the statements.

The script must not have a trailing semicolon.

Binding arguments

These encode OCaml data as data to be bound to the query statement.

module Arg : sig ... end

Helpers to get typed values from columns

val int : int -> row -> int
val bool : int -> row -> bool
val int64 : int -> row -> int64
val float : int -> row -> float
val text : int -> row -> string

Also handles values of all other types. Use this when SQLite can change the exact type of value it returns at runtime, e.g. for very large numbers it can return text.

val opt : (int -> row -> 'a) -> int -> row -> 'a option

opt dec col row is the optional value NULL turns to None at column col of the result row.

include Fun_sql.S with type db := db and type arg := arg

Higher-level utilities that work across supported DBs

exception Bad_migration of string
val migrate : db -> string -> unit

migrate db dir applies the SQL migration scripts in dir on the given database db, keeping track of those that have already been applied.

To apply the migrations in the correct order, the migration scripts must be given filenames that are sorted in lexicographical order of the desired migration order, e.g. 0000_0001_init.sql will be applied before 0000_0002_sec.sql, and so on.

Note that this uses exec_script internally, which means the migration scripts must not have trailing semicolons either.

Any files with extensions other than .sql are ignored.

  • raises Bad_migration

    an error occurs during applying the migrations.

val transaction : db -> (unit -> 'r) -> 'r

transaction db f runs f () inside a transaction in the db. If the operation succeeds, it commits the transaction and returns its result. If it fails with an exception, it rolls back the transaction and re-raises the exception.