### Install Development Dependencies

Source: https://github.com/vitaly-t/pg-promise/wiki/Testing

Installs the necessary development dependencies for the pg-promise library using npm. This command should be run after cloning the repository.

```shell
$ npm install
```

--------------------------------

### Clone Repository

Source: https://github.com/vitaly-t/pg-promise/wiki/Testing

Clones the pg-promise repository from GitHub to your local machine. This is the first step to get the project source code.

```shell
$ git clone https://github.com/vitaly-t/pg-promise
```

--------------------------------

### Install pg-promise

Source: https://github.com/vitaly-t/pg-promise/blob/master/jsdoc/README.md

Installs the pg-promise library using npm. This is the first step to integrate the library into your Node.js project.

```bash
$ npm install pg-promise
```

--------------------------------

### Run Tests with Native Bindings

Source: https://github.com/vitaly-t/pg-promise/wiki/Testing

Runs all tests for pg-promise while utilizing native bindings for potentially faster execution. This requires the native bindings to be installed and configured.

```shell
$ npm run test:native
```

--------------------------------

### pg-promise Connection String Example

Source: https://github.com/vitaly-t/pg-promise/wiki/Connection-Syntax

Demonstrates initializing a pg-promise database instance directly with a connection string.

```js
const pgp = require('pg-promise')();

const db = pgp('postgres://john:pass123@localhost:5432/products');
```

--------------------------------

### pg-promise Configuration Object Example

Source: https://github.com/vitaly-t/pg-promise/wiki/Connection-Syntax

Illustrates how to create a pg-promise database instance using a configuration object with common connection parameters and pool settings.

```js
const pgp = require('pg-promise')();

const cn = {
    host: 'localhost',
    port: 5432,
    database: 'my-database-name',
    user: 'user-name',
    password: 'user-password',
    max: 30 // use up to 30 connections

    // "types" - in case you want to set custom type parsers on the pool level
};

const db = pgp(cn);
```

--------------------------------

### External SQL File Example: findUser.sql

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

An example of an external SQL file (`findUser.sql`) that uses comments and named parameters (`${id}`) for clarity and dynamic value injection.

```sql
/*
    multi-line comments are supported
*/
SELECT name, dob -- single-line comments are supported
FROM Users
WHERE id = ${id}
```

--------------------------------

### Run All Tests

Source: https://github.com/vitaly-t/pg-promise/wiki/Testing

Executes all test suites for the pg-promise library using npm. This is the primary command to verify the library's functionality.

```shell
$ npm test
```

--------------------------------

### Nested Transactions Example

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

Provides an example of nested transactions in pg-promise, demonstrating how multiple levels of transactions can be initiated. The library handles connection sharing and savepoint management automatically.

```js
db.tx(t => {
    const queries = [
        t.none('DROP TABLE users;'),
        t.none('CREATE TABLE users(id SERIAL NOT NULL, name TEXT NOT NULL)')
    ];
    for (let i = 1; i <= 100; i++) {
        queries.push(t.none('INSERT INTO users(name) VALUES($1)', 'name-' + i));
    }
    queries.push(
        t.tx(t1 => {
            return t1.tx(t2 => {
                return t2.one('SELECT count(*) FROM users');
            });
        }));
    return t.batch(queries);
})
    .then(data => {
        // success
    })
    .catch(error => {
        // failure
    });
```

--------------------------------

### Generate Documentation

Source: https://github.com/vitaly-t/pg-promise/wiki/Testing

Generates the project's documentation. This command typically uses a documentation generation tool configured within the project's scripts.

```shell
$ npm run doc
```

--------------------------------

### Run Tests with Coverage

Source: https://github.com/vitaly-t/pg-promise/wiki/Testing

Executes all tests for pg-promise and generates a code coverage report. This helps in understanding which parts of the code are exercised by the tests.

```shell
$ npm run coverage
```

--------------------------------

### Explicit CTF: STPoint Constructor Function Example

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

A functional constructor for `STPoint` that achieves the same result as the class-based example. It defines `toPostgres` and `rawType` properties directly on the instance.

```js
function STPoint(x, y){
    this.rawType = true; // no escaping, because we return pre-formatted SQL
    this.toPostgres = () => pgp.as.format('ST_MakePoint($1, $2)', [x, y]);
}
```

--------------------------------

### pg-promise PromiseAdapter Configuration

Source: https://github.com/vitaly-t/pg-promise/wiki/Promise-Adapter

Configures pg-promise to use a custom PromiseAdapter. This allows pg-promise to work with promise libraries that do not conform to its expected signature. The example shows creating an adapter for a standard ES6 Promise.

```js
const pgpLib = require('pg-promise');

const adapter = new pgpLib.PromiseAdapter({
    create: cb => new Promise(cb),
    resolve: value => Promise.resolve(value),
    reject: reason => Promise.reject(reason),
    all: iterable => Promise.all(iterable)
});

const initOptions = {
    promiseLib: adapter
};

const pgp = pgpLib(initOptions);
```

--------------------------------

### Initialize Test Database

Source: https://github.com/vitaly-t/pg-promise/wiki/Testing

Initializes the local test database with required test data. This script requires the connection details in test/db/header.js to be correctly configured.

```shell
$ node test/db/init.js
```

--------------------------------

### SQL CREATE TABLE Statement

Source: https://github.com/vitaly-t/pg-promise/wiki/Data-Imports

Defines the 'products' table structure with an auto-incrementing primary key, product title, price, and units. This table serves as the destination for data import examples.

```sql
CREATE TABLE products(
    id SERIAL PRIMARY KEY, -- auto-incremented product id
    title TEXT NOT NULL, -- product title/name
    price NUMERIC(15,6) NOT NULL, -- product price
    units INT NOT NULL -- availability / quantity
);
```

--------------------------------

### Execute Multiple Queries with db.task (Async/Await)

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

Shows the same functionality as the Promise-based example but utilizes ES7 async/await syntax for cleaner asynchronous code within the task callback.

```javascript
db.task(async t => {
    const count = await t.one('SELECT count(*) FROM events WHERE id = $1', 123, a => +a.count);
    if(count > 0) {
        const logs = await t.any('SELECT * FROM log WHERE event_id = $1', 123);
        return {count, logs};
    }
    return {count};
})
    .then(data => {
        // success, data = either {count} or {count, logs}
    })
    .catch(error => {
        // failed    
    });
```

--------------------------------

### Sending Notifications with NOTIFY (pg-promise)

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Provides an example of sending a notification to a specified channel with an optional payload using the NOTIFY command in pg-promise. This is typically used in conjunction with LISTEN.

```js
db.none('NOTIFY $1:name, $2', ['my-channel', 'my payload string'])
    .then(() => {
        console.log('Notification sent.');
    })
    .catch(error => {
        console.log('NOTIFY error:', error);
    });
```

--------------------------------

### pg-promise txIf with Default Condition

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

Demonstrates using db.txIf without a condition. It starts a transaction if not in one, otherwise starts a task. Useful for avoiding nested transactions.

```javascript
db.txIf(t => {
    // transaction is started, as the top level doesn't have one
    return t.txIf(t2 => {
        // a task is started, because there is a parent transaction        
    });
})
```

--------------------------------

### Simple INSERT Query

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Shows how to perform a simple INSERT operation using `db.none`, which is suitable for queries that do not return any data. It includes basic promise handling for success and error.

```javascript
db.none('INSERT INTO users(name, active) VALUES($1, $2)', ['John', true])
    .then(() => {
        // success;
    })
    .catch(error => {
        // error;
    });
```

--------------------------------

### QueryFile: Loading and Executing an External SQL File

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

Demonstrates how to create a `QueryFile` instance for an SQL file (`findUser.sql`) and use it with `db.one` to execute the query, including basic error handling for `QueryFileError`.

```js
// Create a QueryFile globally, once per file:
const sqlFindUser = sql('./sql/findUser.sql');

db.one(sqlFindUser, {id: 123})
    .then(user => {
        console.log(user);
    })
    .catch(error => {
        if (error instanceof pgp.errors.QueryFileError) {
            // => the error is related to our QueryFile
        }
    });
```

--------------------------------

### Explicit CTF: ST_MakePoint Class Example

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

An example class `STPoint` that formats coordinates into an SQL `ST_MakePoint` function call. It uses `rawType: true` to ensure the generated SQL string is injected directly.

```js
class STPoint {
    constructor(x, y) {
        this.x = x;
        this.y = y;
        this.rawType = true; // no escaping, because we return pre-formatted SQL
    }
    
    toPostgres(self) {
        return pgp.as.format('ST_MakePoint($1, $2)', [this.x, this.y]);
    }
}
```

--------------------------------

### Transactions with BEGIN/COMMIT

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Explains how to initiate and manage database transactions using pg-promise. Transactions are treated as tasks enclosed by BEGIN and COMMIT/ROLLBACK commands, ensuring data integrity.

```javascript
// Example of starting a transaction:
db.tx(async t => {
    // t is the transactional context
    await t.none('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
    await t.none('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
})
.then(data => {
    // success;
})
.catch(error => {
    // error, transaction rolled back
});
```

--------------------------------

### Simple SELECT Query

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Demonstrates executing a simple SELECT query with a parameter using `db.any`. It shows both ES5 and ES7 (async/await) syntax for handling the promise-based results and errors.

```javascript
db.any('SELECT * FROM users WHERE active = $1', [true])
    .then(function(data) {
        // success;
    })
    .catch(function(error) {
        // error;
    });
```

```javascript
try {
    const users = await db.any('SELECT * FROM users WHERE active = $1', [true]);
    // success
}
catch(e) {
    // error
}
```

--------------------------------

### Initialize pg-promise with Options

Source: https://github.com/vitaly-t/pg-promise/blob/master/jsdoc/README.md

Loads and initializes the pg-promise library with custom initialization options. This allows for configuration of the library's behavior.

```js
const initOptions = {/* initialization options */};
const pgp = require('pg-promise')(initOptions);
```

--------------------------------

### Prepared Statement Query

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Illustrates using prepared statements for performance and SQL injection prevention. It defines a query by name, text, and values, then executes it using `db.one`.

```javascript
db.one({
    name: 'find-user',
    text: 'SELECT * FROM users WHERE id = $1',
    values: [1]
})
    .then(user => {
        // user found;
    })
    .catch(error => {
        // error;
    });
```

--------------------------------

### pg-promise Connection String Syntax

Source: https://github.com/vitaly-t/pg-promise/wiki/Connection-Syntax

Shows the general syntax for PostgreSQL connection strings and how they can be used to initialize a pg-promise database instance.

```APIDOC
Connection String Syntax:

`postgres://username:password@host:port/database?ssl=false&application_name=name&fallback_application_name=name&client_encoding=encoding`

Parameters can be appended as query string arguments.
```

--------------------------------

### pg-promise txIf with Custom Value Condition

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

Shows how to use db.txIf with a custom condition object specifying a 'cnd' value. A transaction is started if the condition is truthy.

```javascript
db.txIf({cnd: someValue}, t => {
    // if condition is truthy, a transaction is started
    return t.txIf(t2 => {
        // a task is started, if the parent is a transaction
        // a transaction is started, if the parent is a task
    });
})
```

--------------------------------

### Run TSLint Checks

Source: https://github.com/vitaly-t/pg-promise/wiki/Testing

Executes TSLint checks specifically for the TypeScript parts of the library. This command is important when making changes to the TypeScript codebase.

```shell
$ npm run tslint
```

--------------------------------

### Initialize pg-promise and Access Helpers

Source: https://github.com/vitaly-t/pg-promise/blob/master/lib/helpers/README.md

Demonstrates how to initialize the pg-promise library and access the `helpers` namespace. The `helpers` namespace contains utility functions for common database tasks.

```javascript
const pgp = require('pg-promise')();
const helpers = pgp.helpers; // `helpers` namespace
```

--------------------------------

### Run ESLint Checks

Source: https://github.com/vitaly-t/pg-promise/wiki/Testing

Executes all ESLint checks to ensure code quality and adherence to coding standards. This is crucial for maintaining code consistency.

```shell
$ npm run lint
```

--------------------------------

### Named Parameters Query

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Illustrates using named parameters in pg-promise queries, defined with syntax like ${name}. This improves readability and allows for flexible parameter ordering. Supported delimiters include {}, (), [], <>, //.

```javascript
db.any('SELECT * FROM users WHERE name = ${name} AND active = $/active/',
    {
        name: 'John',
        active: true
    })
    .then(data => {
        console.log('DATA:', data); // print data;
    })
    .catch(error => {
        console.log('ERROR:', error); // print the error;
    });
```

--------------------------------

### QueryFile: Helper for External SQL Files

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

A utility function `sql` that creates `pgp.QueryFile` instances, simplifying the process of linking to external SQL files and applying options like minification.

```js
const {join: joinPath} = require('path');

// Helper for linking to external query files:
function sql(file) {
    const fullPath = joinPath(__dirname, file);
    return new pgp.QueryFile(fullPath, {minify: true});
}
```

--------------------------------

### Formatting Queries with pgp.as

Source: https://github.com/vitaly-t/pg-promise/wiki/FAQ

Shows how to use the `pgp.as.format` method to format SQL queries with parameters without executing them. This is useful for debugging, logging, or generating SQL strings dynamically.

```javascript
const pgp = require('pg-promise');
const s = pgp.as.format("SELECT * FROM table WHERE field1 = $1", 123);
console.log(s);
```

--------------------------------

### pg-promise Streaming Query Results (to JSON)

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Demonstrates streaming query results from the database using `pg-promise` and `pg-query-stream`. The results are piped through `JSONStream` to `process.stdout`. This method is efficient for large datasets and requires the `pg-query-stream` package.

```typescript
import QueryStream from 'pg-query-stream';
import JSONStream from 'JSONStream';

// you can also use pgp.as.format(query, values, options)
// to format queries properly, via pg-promise;
const qs = new QueryStream('SELECT * FROM users');

await db.stream(qs, s => {
    s.pipe(JSONStream.stringify()).pipe(process.stdout);
});
//=> resolves with: {processed, duration}
```

--------------------------------

### Initialize pg-promise without Options

Source: https://github.com/vitaly-t/pg-promise/blob/master/jsdoc/README.md

Loads and initializes the pg-promise library without any specific initialization options. This uses the default configuration.

```js
const pgp = require('pg-promise')();
```

--------------------------------

### Explicit CTF: Custom Type Formatting

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

Demonstrates how to define a custom type in JavaScript that pg-promise will format for SQL queries. The `toPostgres` function is called to get the value, and `rawType: true` prevents escaping.

```js
const obj = {
    toPostgres(self) {
        // self = this = obj
        
        // return a value that needs proper escaping
    }
}
```

--------------------------------

### pg-promise Streaming Data Into Database

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Illustrates streaming data from a file into the database using `pg-promise` and the `spex` library. It defines a `receiver` function to process data chunks and a `source` function to provide data, then uses `db.tx` with `spex.stream.read` for efficient bulk inserts.

```typescript
import {createReadStream} from 'fs';

const streamRead = pgp.spex.stream.read;
const rs = createReadStream('primes.txt');

function receiver(_, data) {
    
    function source(index) {
        if (index < data.length) {
            return data[index];
        }
    }

    function dest(index, data) {
        return this.none('INSERT INTO primes VALUES($1)', data);
    }

    return this.sequence(source, {dest});
}

await db.tx(t => streamRead.call(t, rs, receiver));
```

--------------------------------

### Test Receiving BigInt Array

Source: https://github.com/vitaly-t/pg-promise/wiki/BigInt

Tests querying an array of BIGINT values from PostgreSQL and verifies that the result is a JavaScript array containing BigInt objects. This validates the array type parser setup.

```javascript
await db.one('SELECT ARRAY[1, 2]::bigint[] as value'); //=> {value: [1n, 2n]}
```

--------------------------------

### Format BigInt for CSV Output

Source: https://github.com/vitaly-t/pg-promise/wiki/BigInt

Shows how to use pg-promise's query-formatting engine to include BigInt values when formatting data as a CSV string. The example demonstrates that BigInts are correctly converted to their string representation.

```typescript
pgp.as.format('${this:csv}', {
    a: 123456789012345678,
    b: 123456789012345678n
});
//=> 123456789012345680,123456789012345678
```

--------------------------------

### Accessing Tags in pg-promise Event Handlers

Source: https://github.com/vitaly-t/pg-promise/wiki/Tags

Provides an example of how to access the 'tag' property within the context object (e.g., e.ctx.tag) of various pg-promise event handlers like query, task, and transact for detailed logging.

```javascript
// Initialization Options;
const options = {
    query(e) {
        if (e.ctx) {
            // e.ctx.tag - is the tag set;
            // the query is part of a task or transaction;
            if (e.ctx.isTX) {
                // log the transaction details;
            } else {
                // log the task details;
            }
        }
        console.log('QUERY:', e.query);
    },
    task(e) {
        if (e.ctx.finish) {
            console.log('Task/Finish:', e.ctx.tag);
        } else {
            console.log('Task/Start:', e.ctx.tag);
        }
    },
    transact(e) {
        if (e.ctx.finish) {
            console.log('TX/Finish:', e.ctx.tag);
        } else {
            console.log('TX/Start:', e.ctx.tag);
        }
    }
};
```

--------------------------------

### Parameterized Query Object

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Shows how to execute a query with parameters using an object structure, specifying `text` and `values`. This is useful for complex queries or when using QueryFile objects.

```javascript
db.one({
    text: 'SELECT * FROM users WHERE id = $1',
    values: [1]
})
    .then(user => {
        // user found;
    })
    .catch(error => {
        // error;
    });
```

--------------------------------

### pg-promise Query Formatting: Redundant Named Parameter Re-referencing (JavaScript)

Source: https://github.com/vitaly-t/pg-promise/wiki/Common-Mistakes

Highlights an anti-pattern where named parameters are unnecessarily re-referenced by creating a new object with the same property names. The example shows how to directly pass the source object to the query method for cleaner code.

```javascript
db.query("INSERT INTO table VALUES(${name}, ${title}, ${code})",
    {name: obj.name, title: obj.title, code: obj.code});
```

```javascript
db.query("INSERT INTO table VALUES(${name}, ${title}, ${code})", obj);
```

--------------------------------

### SQL Names/Identifiers Formatting

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Explains how to format SQL names and identifiers (like table or column names) using ':name' or '~' in pg-promise. This ensures proper quoting and escaping for identifiers.

```javascript
db.any('INSERT INTO $1~($2~) VALUES(...)', ['Table Name', 'Column Name']);
// => INSERT INTO "Table Name"("Column Name") VALUES(...)

db.any('SELECT ${column~} FROM ${table~}', {
    column: 'Column Name',
    table: 'Table Name'
});
// => SELECT "Column Name" FROM "Table Name"
```

--------------------------------

### pg-promise Transaction with Results

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Shows how to execute a transaction that returns data from SQL queries. It uses `t.one` to fetch single results and returns an object containing the fetched IDs. The results are then processed in the `.then` block.

```javascript
db.tx(async t => {
    // `t` and `this` here are the same;
    // this.ctx = transaction config + state context;

    const userId = await t.one('INSERT INTO users(name) VALUES($1) RETURNING id', 'John', a => a.id);
    const eventId = await t.one('INSERT INTO events(code) VALUES($1) RETURNING id', 123, a => a.id);

    return {userId, eventId};
})
    .then(data => {
        console.log(data.userId);
        console.log(data.eventId);
    })
    .catch(error => {
        // error
    });
```

--------------------------------

### Enumeration with :csv/:list for Object Properties

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

These examples demonstrate using :csv and :list filters with automatic property enumeration for inserting data from JavaScript objects into SQL tables. The `${this:name}` and `${this:csv}` syntax is used for column names and values respectively.

```javascript
const obj = {first: 123, second: 'text'};

// Using $1:name and $1:csv
await db.none('INSERT INTO table($1:name) VALUES($1:csv)', [obj])
//=> INSERT INTO table("first","second") VALUES(123,'text')
```

```javascript
const obj = {first: 123, second: 'text'};

// Using ${this:name} and ${this:csv}
await db.none('INSERT INTO table(${this:name}) VALUES(${this:csv})', obj)
//=> INSERT INTO table("first","second") VALUES(123,'text')
```

```javascript
const obj = {first: 123, second: 'text'};

// Using $1:name and $1:list
await db.none('INSERT INTO table($1:name) VALUES($1:list)', [obj])
//=> INSERT INTO table("first","second") VALUES(123,'text')
```

```javascript
const obj = {first: 123, second: 'text'};

// Using ${this:name} and ${this:list}
await db.none('INSERT INTO table(${this:name}) VALUES(${this:list})', obj)
//=> INSERT INTO table("first","second") VALUES(123,'text')
```

--------------------------------

### JavaScript: Get or Insert User ID (Async/Await)

Source: https://github.com/vitaly-t/pg-promise/blob/master/examples/select-insert.md

Implements the same 'getInsertUserId' logic using ES7 async/await syntax for cleaner asynchronous code. It performs a conditional select or insert within a pg-promise task, returning the user's ID.

```javascript
async function getInsertUserId(name) {
    return db.task('getInsertUserId', async t => {
        const userId = await t.oneOrNone('SELECT id FROM Users WHERE name = $1', name, u => u && u.id);
        return userId || await t.one('INSERT INTO Users(name) VALUES($1) RETURNING id', name, u => u.id);
    });
}
```

--------------------------------

### JavaScript: Get or Insert User ID (Callback)

Source: https://github.com/vitaly-t/pg-promise/blob/master/examples/select-insert.md

Implements a pg-promise task to find a user by name. If found, it returns the user's ID; otherwise, it inserts a new user and returns the new ID. Relies on the 'pg-promise' library and a database connection.

```javascript
function getInsertUserId(name) {
    return db.task('getInsertUserId', t => {
            return t.oneOrNone('SELECT id FROM Users WHERE name = $1', name, u => u && u.id)
                .then(userId => {
                    return userId || t.one('INSERT INTO Users(name) VALUES($1) RETURNING id', name, u => u.id);
                });
        });
}
```

--------------------------------

### LISTEN/NOTIFY with Temporary Listener (pg-promise)

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Demonstrates setting up a temporary listener for PostgreSQL notifications using a connection from the pool. The listener is active as long as the connection is held by the pool. It includes handling incoming notifications and releasing the connection.

```js
let sco; // shared connection object

db.connect()
    .then(obj => {
        sco = obj;
        sco.client.on('notification', data => {
            console.log('Received:', data);
            // data.payload = 'my payload string'
        });
        return sco.none('LISTEN $1:name', 'my-channel');
    })
    .catch(error => {
        console.log('Error:', error);
    })
    .finally(() => {
        if (sco) {
            sco.done(); // releasing the connection back to the pool
        }
    });
```

--------------------------------

### Accessing pg-promise Promise Interface

Source: https://github.com/vitaly-t/pg-promise/wiki/Integration

Demonstrates how to access and use the underlying promise library configured by pg-promise. This includes creating new promises, resolved promises, and rejected promises using the `db.$config.promise` interface.

```javascript
const $p = db.$config.promise; // your basic promise interface

// for a new promise object:
return $p(function(resolve, reject) {
   // call resolve/reject as needed
});

// for a resolved promise:
return $p.resolve(value);

// for a rejected promise:
return $p.reject(reason);
```

--------------------------------

### INSERT Binary Data (Buffer) (pg-promise)

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Explains how to insert binary data, such as image files read as Buffers, into PostgreSQL `bytea` columns using pg-promise. The library handles the conversion of Node.js Buffer objects automatically.

```js
const fs = require('fs');

// read in image in raw format (as type Buffer):
fs.readFile('image.jpg', (err, imgData) => {
    // inserting data into column 'img' of type 'bytea':
    db.none('INSERT INTO images(img) VALUES($1)', imgData)
        .then(() => {
            // success;
        })
        .catch(error => {
            // error;
        });
});
```

--------------------------------

### Execute Database Procedure

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Demonstrates calling a PostgreSQL procedure using `db.proc`. It allows passing parameters and handles the promise resolution with any returned data or errors.

```javascript
db.proc('myProcName', [123, new Date()])
    .then(data => {
        console.log('DATA:', data); // print data, if any;
    })
    .catch(error => {
        console.log('ERROR:', error.message || error); // print the error;
    });
```

--------------------------------

### pg-promise Initialization and ColumnSet

Source: https://github.com/vitaly-t/pg-promise/wiki/Data-Imports

Initializes the pg-promise library with capitalized SQL generation and sets up a reusable ColumnSet. The ColumnSet maps data properties to table columns, handles renaming ('cost' to 'price'), and defines default values ('units' defaults to 1 if missing).

```javascript
const pgp = require('pg-promise')({
    capSQL: true // generate capitalized SQL 
});

const db = pgp(/*connection details*/); // your database object

// Creating a reusable/static ColumnSet for generating INSERT queries:    
const cs = new pgp.helpers.ColumnSet([
    'title',
    {name: 'price', prop: 'cost'},
    {name: 'units', def: 1}
], {table: 'products'});
```

--------------------------------

### Execute Database Function

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Shows how to call a PostgreSQL function using `db.func`. It passes parameters to the function and handles the promise resolution with the returned data or errors.

```javascript
db.func('myFuncName', [123, new Date()])
    .then(data => {
        console.log('DATA:', data);
    })
    .catch(error => {
        console.log('ERROR:', error);
    });
```

--------------------------------

### pg-promise Simple Transaction

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Demonstrates a basic transaction using `db.tx` to execute multiple SQL statements. The transaction is committed if all operations succeed, otherwise it is rolled back. It handles success and error callbacks.

```javascript
db.tx(async t => {
    // t.ctx = transaction config + state context;
    await t.none('UPDATE users SET active = $1 WHERE id = $2', [true, 123]);
    await t.none('INSERT INTO audit(status, id) VALUES($1, $2)', ['active', 123]);
})
    .then(() => {
        // success;
    })
    .catch(error => {
        console.log('ERROR:', error);
    });
```

--------------------------------

### pg-promise Streaming Query Results (to CSV)

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Shows how to stream query results directly into a CSV file. It uses `pg-promise` with `pg-query-stream` and `csv-write-stream`. This is useful for exporting large amounts of data from the database.

```typescript
import QueryStream from 'pg-query-stream';
import CsvWriter from 'csv-write-stream';
import {createWriteStream} from 'fs';

const csv = new CsvWriter();
const file = createWriteStream('out.csv');

const qs = new QueryStream('select * from my_table');

await db.stream(query, s => {
    s.pipe(csv).pipe(file);
});
//=> resolves with: {processed, duration}
```

--------------------------------

### Executing Tasks with a Single Connection

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Illustrates the use of `db.task` for executing a sequence of queries against the same database connection. This is useful for operations that require a consistent connection state.

```javascript
db.task(async t => {
    // t.ctx = task config + state context;
    const user = await t.one('SELECT * FROM users WHERE id = $1', 123);
    return t.any('SELECT * FROM events WHERE login = $1', user.name);
})
    .then(events => {
        // success;
    })
    .catch(error => {
        // error
    });
```

--------------------------------

### Function Parameters for Dynamic Values

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Explains how to use functions as parameter values in pg-promise. Functions are executed at query time, allowing for dynamic value generation, such as calculating totals or formatting data.

```javascript
const account = {
    balance: 123.45,
    expenses: 2.7,
    margin: 0.1,
    total: a => {
        const t = a.balance + a.expenses;
        return a.margin ? (t + t * a.margin / 10) : t;
    }
};

db.none('INSERT INTO activity VALUES(${balance}, ${total})', account)
    .then(() => {
        // success;
    })
    .catch(error => {
        // error;
    });
```

--------------------------------

### INSERT with RETURNING ID

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Demonstrates inserting a new record and retrieving its generated ID using `db.one` with the `RETURNING id` clause. It handles the promise resolution with the returned data and potential errors.

```javascript
db.one('INSERT INTO users(name, active) VALUES($1, $2) RETURNING id', ['John', true])
    .then(data => {
        console.log(data.id); // print new user id;
    })
    .catch(error => {
        console.log('ERROR:', error); // print error;
    });
```

--------------------------------

### LISTEN/NOTIFY with Permanent Listener (pg-promise)

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Illustrates setting up a permanent listener for PostgreSQL notifications by using a direct connection outside the connection pool. This listener remains active until the connection fails or is explicitly released.

```js
db.connect({direct: true})
    .then(sco => {
        sco.client.on('notification', data => {
            console.log('Received:', data);
            // data.payload = 'my payload string'
        });
        return sco.none('LISTEN $1:name', 'my-channel');
    })
    .catch(error => {
        console.log('Error:', error);
    });
```

--------------------------------

### Symbolic CTF: Defining Custom Symbols

Source: https://github.com/vitaly-t/pg-promise/blob/master/README.md

Shows how to define custom Symbol properties for `toPostgres` and `rawType` if the global symbols provided by pg-promise are not desired or need to be managed differently.

```js
const ctf = {
    toPostgres: Symbol.for('ctf.toPostgres'),
    rawType: Symbol.for('ctf.rawType')
};
```

--------------------------------

### Multiple Parameters Query

Source: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example

Shows how to pass multiple parameters to a pg-promise query using an array. Parameters are referenced positionally ($1, $2, etc.) and are automatically escaped.

```javascript
db.any('SELECT * FROM users WHERE created < $1 AND active = $2', [new Date(), true])
    .then(data => {
        console.log('DATA:', data); // print data;
    })
    .catch(error => {
        console.log('ERROR:', error); // print the error;
    });
```