Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Minimalistic syntax extension for type-safe, convenient execution of SQL statements.
OCaml
branch: master

Merge pull request #2 from Armael/master

Translate README to markdown
latest commit 190a5b3857
@mfp authored
Failed to load latest commit information.
.gitignore
AUTHORS
COPYING
INSTALL
OMakefile
OMakeroot
README.md
_oasis
_tags
example.ml Sqlexpr_sqlite, pa_sql: recover polymorphism in sqlc"..." expressions…
pa_sql.ml
sqlexpr_concurrency.ml
sqlexpr_concurrency.mli
sqlexpr_sqlite.ml
sqlexpr_sqlite.mli Sqlexpr_sqlite: support nested txs using diff conc. monads when possi…
sqlexpr_sqlite_lwt.ml
sqlexpr_sqlite_lwt.mli
t_sqlexpr_sqlite.ml

README.md

ocaml-sqlexpr is a simple library and syntax extension for type-safe, convenient execution of SQL statements, currently compatible with Sqlite3.

The latest version can be found at https://github.com/mfp/ocaml-sqlexpr

Sqlexpr features:

  • automated prepared statement caching, param binding, data extraction, error checking (including automatic stmt reset to avoid BUSY/LOCKED errors in subsequent queries), stmt finalization on db close, etc.
  • HOFs like iter, fold, transaction
  • support for different concurrency models: everything is functorized over a THREAD monad, so you can for instance do concurrent folds/iters with Lwt
  • support for SQL stmt syntax checks and some extra semantic checking (column names, etc)

Sqlexpr is used as follows:

module Sqlexpr = Sqlexpr_sqlite.Make(Sqlexpr_concurrency.Id)
module S = Sqlexpr

let () =
  let db = S.open_db "foo.db" in
  S.iter db
    (fun (n, p) -> Printf.printf "User %S, password %S\n" n p)
    sqlc"SELECT @s{login}, @s{password} FROM users";
  List.iter
    (fun (n, p) -> S.execute db sqlc"INSERT INTO users VALUES(%s, %s)" n p)
    [
     "coder24", "badpass";
     "tokyo3", "12345"
    ]

See also example.ml.

Dependencies

csv, batteries, sqlite3, estring, lwt (>= 2.2.0), lwt.syntax, lwt.unix, unix, threads

Syntax extension

ocaml-sqlexpr includes a syntax extension to build type-safe SQL statements/expressions:

  • sql"..." denotes a SQL statement/expression
  • sqlc"..." denotes a SQL statement/expression that is to be cached
  • sql_check"sqlite" returns a tuple of functions to initialize, check the validity of the SQL statements/expressions and check against an auto-initialized temporary database.
  • sqlinit"..." is equivalent to sql"...", but the statement will be added to the list of statements to be executed in the automatically generated initialization function

sql_check"sqlite" is used as follows:

let auto_init_db, check_db, auto_check_db = sql_check"sqlite"

which creates 3 functions

val auto_init_db : Sqlite3.db -> Format.formatter -> bool
val check_db : Sqlite3.db -> Format.formatter -> bool
val auto_check_db : Format.formatter -> bool

each of them returns false on error, and writes the error messages to the provided formatter.

SQL statement/expression syntax

sql/sqlc literals are similar to Printf's format strings and their precise types depend on their contents. They accept input parameters (similarly to Printf) and, in the case of SQL expressions, their execution will yield a tuple whose type is determined by the output parameters.

Input parameters are denoted with %X where X is one of:

input parameter OCaml type
%d int
%l Int32.t
%L Int64.t
%s string
%S string (handled as BLOB by SQLite)
%f float
%b bool
%a ('a -> string) (resulting string handled as BLOB by SQLite)

A literal '%' is denoted with '%%'.

A parameter is made nullable (turning the OCaml type into a _ option) by appending a '?', e.g. '%d?'.

Output parameters are denoted with @X{SQL expression} where X is one of:

output parameter OCaml type
@d int
@l Int32.t
@L Int64.t
@s string
@S string (handled as BLOB by SQLite)
@f float
@b bool

A literal '@' is denoted with '@@'. As in the case of input parameters, output parameters can be made nullable by appending a '?'.

A sql"..." or sqlc"..." literal is of type _ statement if it has no output parameters, and of type _ expression if it has at least one.

Examples:

sql"SELECT @s{name} FROM users"                   is an expression
sql"SELECT @s{name} FROM users WHERE id = %d"     is an expression
sql"SELECT @s{name}, @s{email} FROM users"        is an expression
sql"DELETE FROM users WHERE id = %d"              is a statement

Statements are executed with execute or insert (which returns the id of the new row); expressions are "selected" with a function from the select* family or a HOF like iter or fold.

Examples:

module Sqlexpr = Sqlexpr_sqlite.Make(Sqlexpr_concurrency.Id)
module S = Sqlexpr

let insert_user_stmt =
    sqlc"INSERT INTO users(login, password, email) VALUES(%s, %s, %s?)"

let insert_user db ~login ?email ~password =
  S.execute db insert_user_stmt login password email

(* insert user and return ID; we use partial application here *)
let new_user_id db = S.insert db insert_user_stmt

let get_password db =
  S.select_one db sqlc"SELECT @s{password} FROM users WHERE login = %s"

let get_email db =
  S.select_one db sqlc"SELECT @s?{email} FROM users WHERE login = %s"

let iter_users db f =
  S.iter db f sqlc"SELECT @L{id}, @s{login}, @s{password}, @s?{email}
                     FROM users"
Something went wrong with that request. Please try again.