pg-promise Documentation

Named parameters support property nesting of any depth. The resolution chain is highly flexible and supports recursion. The last name in the resolution can be:

1. A basic JavaScript type.
2. A function that returns a value, another function, or a Custom Type Formatting object.
3. A Custom Type Formatting object that returns a value, another Custom Type Formatting object, or a function.

Note: Nested parameters are not supported within the helpers namespace.

const obj = {
    one: {
        two: {
            three: {
                value1: 123,
                value2: a => {
                    // a = obj.one.two.three
                    return 'hello';
                },
                value3: function(a) {
                    // a = this = obj.one.two.three
                    return 'world';
                },
                value4: {
                    toPostgres: a => {
                        // Custom Type Formatting
                        // a = obj.one.two.three.value4
                        return a.text;
                    },
                    text: 'custom'
                }
                
            }
        }
    }
};
await db.one('SELECT ${one.two.three.value1}', obj); //=> SELECT 123
await db.one('SELECT ${one.two.three.value2}', obj); //=> SELECT 'hello'
await db.one('SELECT ${one.two.three.value3}', obj); //=> SELECT 'world'
await db.one('SELECT ${one.two.three.value4}', obj); //=> SELECT 'custom'

You can extend the pg-promise protocol (e.g., adding custom methods to the db object) by using the extend event in the initialization options. To maintain type safety, you must define an interface for your extensions and pass it as a type parameter to pgPromise.IInitOptions<IExtensions>.

import * as pgPromise from 'pg-promise';

// your protocol extensions:
interface IExtensions {
    findUser(userId: number): Promise<any>;
}

// pg-promise initialization options:
const options: pgPromise.IInitOptions<IExtensions> = {
    extend(obj) {
        obj.findUser = userId => {
            return obj.one('SELECT * FROM Users WHERE id = $1', [userId]);
        }
    }
};

// initializing the library:
const pgp = pgPromise(options);

// database object:
const db = pgp('postgres://username:password@host:port/database');

// protocol is extended on each level:
const user = await db.findUser(123);

// ...including inside tasks and transactions:
await db.task(async t => {
    const user = await t.findUser(123);
    // ...etc
});

You can define how specific JavaScript objects are formatted for PostgreSQL using Custom Type Formatting (CTF). There are two methods:

1. Explicit CTF

Implement a toPostgres(self) method on your object. The method is called with the object as the this context.

• If you want the returned value to be treated as pre-formatted (Raw Text), set rawType: true on the object.

2. Symbolic CTF

Use ES6 Symbols to define toPostgres and rawType. This allows you to extend types without changing their visible signature (e.g., via pgp.as.ctf).

// Explicit CTF Example
class STPoint {
    constructor(x, y) {
        this.x = x;
        this.y = y;
        this.rawType = true; // Result from toPostgres is injected as Raw Text
    }
    
    toPostgres(self) {
        return pgp.as.format('ST_MakePoint($1, $2)', [this.x, this.y]);
    }
}

// Symbolic CTF Example
const {toPostgres, rawType} = pgp.as.ctf;
const obj = {
    [toPostgres](self) {
        return 'some formatted value';
    },
    [rawType]: true
};

When the values argument is an object, the formatting engine uses Named Parameter syntax. You can use any of the following open-close pairs for variable names: {} , () , <> , [] , or //.

Example usage:

await db.none('INSERT INTO users(first_name, last_name, age) VALUES(${name.first}, $<name.last>, $/age/)', {
    name: {
        first: 'John',
        last: 'Dow'
    },
    age: 30
});

Key Rules:

• Variable Names: Must follow standard JavaScript variable naming rules.
• this keyword: The name this is reserved and refers to the formatting object itself, which is inserted as a JSON-formatted string.
• Missing Properties: If a property does not exist in the object, an error is thrown. If a property is null or undefined, it is formatted as null in the SQL.
• ES6 Template Strings: If you must use ES6 template strings, do not use ${}. Instead, use one of the supported pairs: $(), $<>, $[], or $//.