This module provides templating of database query strings. It helps mitigate differences between database systems, and provides additional functionality such as variable substitution and safe embedding of values. The representation is also suited for dynamic construction.
There are three ways to construct a template:
Using the parser (of_string, of_string_exn, etc.) if the query template is known at compile time.
t is an intermediate representation of a query string to be send to a database, possibly combined with some hidden parameters used to safely embed values. Apart from embedding values, this representation provides indexed parameter references, independent of the target database system. For databases which use linear parameter references (like ? for MariaDB), the driver will reshuffle, elide, and duplicate parameters as needed.
lit frag expands to frag, literally; i.e. the argument is passed unchanged to the database system as a substring of the query. Do not use this to inject untrusted data into the query string, since it can lead to an SQL injection vulnerability. Even when it can be done safely, it is probably easier and more portable to use the appropriate function from Embedding Values or the quote function.
quote str expands to the literally quoted string str if an reliable escape function is available from the driver library, otherwise quote is equivalent to string.
param i expands to a reference to parameter number i, counting from zero. That is, param 0 expands to "$1" for PostgreSQL and to "?1" for SQLite3. For MariaDB, param i expands to "?" for any i; the driver will instead shuffle, elide, and duplicate the actual arguments to match their order of reference in the query string.
cat q1 q2 expands to the juxtaposition of the expansions of q1 followed by q2. This is an associative alternative to concat when no separator is needed.
This module provides a terser way to compose queries. As an example, consider the dynamic construction of a simple SELECT-request which extracts a list of named columns given a corresponding row type, and where conditions are given as query templates with any values embedded:
Embedding Values
The following functions can be used to embed values into a query, including the generic const, corresponding specialized variants. Additionally const_fields can be used to extract fragments for multiple fields given a row type and a value.
const t x is a fragment representing the value x of field type t, using driver-dependent serialization and escaping mechanisms. Drivers will typically expand this to a parameter reference which will receive the value x when executed, though the value may also be embedded in the query if it is deemed safe.
const_fields t x returns a list of fragments corresponding to the single-field projections of the value x as described by the type descriptor t. Each element of the returned list will be either a const-fragment containing the projected value, or lit "NULL" if the projection is None.
The result can be turned into a comma-separated list with concat, except values of unitary types, i.e. types having no fields, may require special care.
normal q rewrites q to a normal form, flattening nested concatenations and removing empty fragments from the internal representation. This function can be used to post-process queries before using equal and hash.
equal q1 q2 is true iff q1 and q2 has the same internal representation. It may be necessary to pre-process the query templates with normal if they are not constructed by a common deterministic algorithm.
hash q computes a hash over the internal representation of q which is compatible with equal. The hash function may change across minor versions and may depend on architecture. It may be necessary to pre-process the query template with normal, unless the hash is to be used among a collection of query templates constructed by a common deterministic algorithm.
pp ppf q prints a human-readable representation of q on ppf. The printed string is not suitable for sending to an SQL database; doing so may lead to an SQL injection vulnerability.
show q is the same human-readable representation of q as printed by pp. The returned string is not suitable for sending to an SQL database; doing so may lead to an SQL injection vulnerability.
A description of the error caused during expand if the environment lookup function returns an invalid result or fails to provide a value for a variable when the expansion is final.
A partial mapping from variable names to query fragments, which raises Not_found for undefined variables. This is used by expand to resolve variable references, with the special handling of a final period in the variable names described in The Syntax of Query Templates.
expand subst query replaces the occurrence of each variable var with subst var where it is defined, otherwise if final is false, the variable is left unchanged, otherwise raises Expand_error. The result of the substitution function may not contain variable references.
parameterfinal
Whether this is the final expansion, as when invoked by the drivers. Defaults to false.
Matches a single expression terminated by the end of input or a semicolon lookahead. The accepted languages is described in The Syntax of Query Templates.
A variant of angstrom_parser which accepts unquoted semicolons as part of the single statement, as is valid in some cases like in SQLite3 trigger definitions. This is the parser used by Caqti_template.Request, where it's assumed that the input is a single SQL statement.
Matches a sequence of statements while ignoring surrounding white space and end-of-line comments starting with "--". This parser can be used to load schema files with support for environment expansions, like substituting the name of the database schema.
Parses a single expression using angstrom_parser_with_semicolon. The error indicates the byte position of the input string where the parse failure occurred in addition to an error message. See The Syntax of Query Templates for how the input string is interpreted.