formatting

Namespace for all query-formatting functions, available from pgp.as before and after initializing the library.

Properties:
Name Type Description
ctf formatting.ctf

Namespace for symbols used by Custom Type Formatting.
alias function

alias - formats an SQL alias.
name function

name - formats an SQL Name/Identifier.
text function

text - formats a text string.
number function

number - formats a number.
buffer function

buffer - formats a Buffer object.
value function

value - formats text as an open value.
json function

json - formats any value as JSON.
array function

array - formats an array of any depth.
csv function

csv - formats an array as a list of comma-separated values.
func function

func - formats the value returned from a function.
format function

format - formats a query, according to parameters.
Source:

Namespaces

ctf

Methods

(static) alias(name) → {string}

Simpler (non-verbose) version of method name, to handle only a regular string-identifier that's mostly used as an SQL alias, i.e. it doesn't support * or an array/object of names, which in the context of an SQL alias would be incorrect. However, it supports . as name-separator, for simpler escaping of composite names.

The surrounding double quotes are not added when the alias uses a simple syntax:

  • it is a same-case single word, without spaces
  • it can contain underscores, and can even start with them
  • it can contain digits and $, but cannot start with those

The method will automatically split the string with ., to support composite SQL names.

When formatting a query, a variable makes use of this method via modifier :alias. See method format.

Parameters:
Name Type Description
name string | function

SQL alias name, or a function that returns it.

The name must be at least 1 character long. And it can contain ., to split into multiple SQL names.

If name doesn't resolve into a non-empty string, it throws TypeError = Invalid sql alias: ...
Source:
See:
Returns:

The SQL alias, properly escaped for compliance with the PostgreSQL standard for SQL Names and identifiers.

Type
string

(static) array(arr, optionsopt) → {string}

Converts an array of values into its PostgreSQL presentation as an Array-Type constructor string: array[].

Top-level empty arrays are formatted as literal {}, to avoid the necessity of explicit type casting, as the server cannot automatically infer type of empty non-literal array.

Parameters:
Name Type Attributes Description
arr Array | function

Array to be converted, or a function that returns one.
options Object <optional>

Array-Formatting Options.

Properties
Name Type Attributes Default Description
capSQL boolean <optional>
 false

When true, outputs ARRAY instead of array.
Source:
Returns:
Type
string

(static) bool(value) → {string}

Converts a truthy value into PostgreSQL boolean presentation.

Parameters:
Name Type Description
value boolean | function

Value to be converted, or a function that returns the value.
Source:
Returns:
Type
string

(static) buffer(obj, rawopt) → {string}

Converts an object of type Buffer into a hex string compatible with PostgreSQL type bytea.

Parameters:
Name Type Attributes Default Description
obj Buffer | function

Object to be converted, or a function that returns one.
raw boolean <optional>
 false

Indicates when not to wrap the resulting string in quotes.

The generated hex string doesn't need to be escaped.
Source:
Returns:
Type
string

(static) csv(values) → {string}

Converts a single value or an array of values into a CSV (comma-separated values) string, with all values formatted according to their JavaScript type.

When formatting a query, a variable makes use of this method via modifier :csv or its alias :list.

When values is an object that's not null or Array, its properties are enumerated for the actual values.

Parameters:
Name Type Description
values Array | Object | value | function

Value(s) to be converted, or a function that returns it.
Source:
See:
Returns:
Type
string

(static) date(d, rawopt) → {string}

Converts a Date-type value into PostgreSQL date/time presentation, wrapped in quotes (unless flag raw is set).

Parameters:
Name Type Attributes Default Description
d Date | function

Date object to be converted, or a function that returns one.
raw boolean <optional>
 false

Indicates when not to escape the value.
Source:
Returns:
Type
string

(static) format(query, valuesopt, optionsopt) → {string}

Replaces variables in a string according to the type of values:

  • Replaces $1 occurrences when values is of type string, boolean, number, bigint, Date, Buffer or when it is null.

  • Replaces variables $1, $2, ...$100000 when values is an array of parameters. It throws a RangeError when the values or variables are out of range.

  • Replaces $*propName*, where * is any of {}, (), [], <>, //, when values is an object that's not a Date, Buffer, QueryFile or null. Special property name this refers to the formatting object itself, to be injected as a JSON string. When referencing a property that doesn't exist in the formatting object, it throws Error = Property 'PropName' doesn't exist, unless option partial is used.

  • Supports Nested Named Parameters of any depth.

By default, each variable is automatically formatted according to its type, unless it is a special variable:

  • Raw-text variables end with :raw or symbol ^, and prevent escaping the text. Such variables are not allowed to be null or undefined, or the method will throw TypeError = Values null/undefined cannot be used as raw text.

    • $1:raw, $2:raw,..., and $*propName:raw* (see * above)
    • $1^, $2^,..., and $*propName^* (see * above)

  • Open-value variables end with :value or symbol #, to be escaped, but not wrapped in quotes. Such variables are not allowed to be null or undefined, or the method will throw TypeError = Open values cannot be null or undefined.

    • $1:value, $2:value,..., and $*propName:value* (see * above)
    • $1#, $2#,..., and $*propName#* (see * above)

  • SQL name variables end with :name or symbol ~ (tilde), and provide proper escaping for SQL names/identifiers:

    • $1:name, $2:name,..., and $*propName:name* (see * above)
    • $1~, $2~,..., and $*propName~* (see * above)

  • Modifier :alias - non-verbose SQL Names escaping.

  • JSON override ends with :json to format the value of any type as a JSON string

  • CSV override ends with :csv or :list to format an array as a properly escaped comma-separated list of values.

Parameters:
Name Type Attributes Description
query string | QueryFile | object

A query string, a QueryFile or any object that implements Custom Type Formatting, to be formatted according to values.
values array | object | value <optional>

Formatting parameter(s) / variable value(s).
options Object <optional>

Formatting Options.

Properties
Name Type Attributes Default Description
capSQL boolean <optional>
 false

Formats reserved SQL words capitalized. Presently, this only concerns arrays, to output ARRAY when required.
partial boolean <optional>
 false

Indicates that we intend to do only a partial replacement, i.e. throw no error when encountering a variable or property name that's missing within the formatting parameters.

NOTE: This option has no meaning when option def is used.
def * <optional>

Sets default value for every variable that's missing, consequently preventing errors when encountering a variable or property name that's missing within the formatting parameters.

It can also be set to a function, to be called with two parameters that depend on the type of formatting being used, and to return the actual default value:

  • For Named Parameters formatting:

    • name - name of the property missing in the formatting object
    • obj - the formatting object, and is the same as this context

  • For Index Variables formatting:

    • index - element's index (starts with 1) that's outside the input array
    • arr - the formatting/input array, and is the same as this context

    You can tell which type of call it is by checking the type of the first parameter.
Source:
Returns:

Formatted query string.

The function will throw an error, if any occurs during formatting.

Type
string

(static) func(func, rawopt, ccopt) → {string}

Calls the function to get the actual value, and then formats the result according to its type + raw flag.

Parameters:
Name Type Attributes Default Description
func function

Function to be called, with support for nesting.
raw boolean <optional>
 false

Indicates when not to escape the result.
cc * <optional>

Calling Context: this + the only value to be passed into the function on all nested levels.
Source:
Returns:
Type
string

(static) json(data, rawopt) → {string}

Converts any value into JSON (includes BigInt support), and returns it as a valid string, with single-quote symbols fixed, unless flag raw is set.

When formatting a query, a variable makes use of this method via modifier :json. See method format.

Parameters:
Name Type Attributes Default Description
data *

Object/value to be converted, or a function that returns it.
raw boolean <optional>
 false

Indicates when not to escape the result.
Source:
See:
Returns:
Type
string

(static) name(name) → {string}

Properly escapes an sql name or identifier, fixing double-quote symbols and wrapping the result in double quotes.

Implements a safe way to format SQL Names that neutralizes SQL Injection.

When formatting a query, a variable makes use of this method via modifier :name or ~. See method format.

Parameters:
Name Type Description
name string | function | array | object

SQL name or identifier, or a function that returns it.

The name must be at least 1 character long.

If name doesn't resolve into a non-empty string, it throws TypeError = Invalid sql name: ...

If the name contains only a single * (trailing spaces are ignored), then name is returned exactly as is (unescaped).

  • If name is an Array, it is formatted as a comma-separated list of SQL Names
  • If name is a non-Array object, its keys are formatted as a comma-separated list of SQL Names

Passing in an empty array/object will throw Error = Cannot retrieve sql names from an empty array/object.
Source:
See:
Returns:

The SQL Name/Identifier, properly escaped for compliance with the PostgreSQL standard for SQL Names and identifiers.

Type
string
Example
// automatically list object properties as sql names:
format('INSERT INTO table(${this~}) VALUES(${one}, ${two})', {
    one: 1,
    two: 2
});
//=> INSERT INTO table("one","two") VALUES(1, 2)

(static) number(num) → {string}

Converts a numeric value into its PostgreSQL number presentation, with support for special values of NaN, +Infinity and -Infinity.

Parameters:
Name Type Description
num number | bigint | function

Number to be converted, or a function that returns one.
Source:
Returns:
Type
string

(static) text(value, rawopt) → {string}

Converts a value into PostgreSQL text presentation, escaped as required.

Escaping the result means:

  1. Every single-quote (apostrophe) is replaced with two
  2. The resulting text is wrapped in apostrophes
Parameters:
Name Type Attributes Default Description
value value | function

Value to be converted, or a function that returns the value.

If the value resolves as null or undefined, while raw=true, it will throw TypeError = Values null/undefined cannot be used as raw text.
raw boolean <optional>
 false

Indicates when not to escape the resulting text.
Source:
Returns:
  • null string, if the value resolves as null or undefined
  • escaped result of value.toString(), if the value isn't a string
  • escaped string version, if value is a string.

The result is not escaped, if raw was passed in as true.

Type
string

(static) value(value) → {string}

Represents an open value, one to be formatted according to its type, properly escaped, but without surrounding quotes for text types.

When formatting a query, a variable makes use of this method via modifier :value or #. See method format.

Parameters:
Name Type Description
value value | function

Value to be converted, or a function that returns the value.

If value resolves as null or undefined, it will throw TypeError = Open values cannot be null or undefined.
Source:
See:
Returns:

Formatted and properly escaped string, but without surrounding quotes for text types.

Type
string