@cookielab.io/postgres-client

Postgres Client

Installation

$ yarn add @cookielab.io/postgres-client pg

The library requires pg to be its peer dependency and thus it needs to added too. This ensures that both, the root project, and the library use the same pg version.

Usage

Recommendation

Every model function working with the database (put bluntly, everything in your model/ directory) should require a database connection in its parameters, which should then be passed from a higher level of the application (routes). This way we can ensure that transactions run correctly if they are nested.

Connecting

import {Client, SQL} from '@cookielab.io/postgres-client';
import config from '/config';
import {Pool} from 'pg';

const pool = new Pool({
    host: config.database.host,
    database: config.database.database,
    user: config.database.user,
    password: config.database.password,
    port: config.database.port,
});

const client = new Client(pool);

export default client;
export {SQL, isUniqueViolation} from '@cookielab.io/postgres-client';

The pool is the pool exported by pg and can be configured as such.

Querying

import database, {SQL} from './connection';

const email = '[e-mail]';
database.query(SQL`SELECT * FROM table WHERE email = ${email}`);

Be aware! Calling any function on the connection exported from connection.js may be executed on its own connection. To ensure that queries are called serially on one connection, transactions have to be used.

Transactions

Transactions can be nested as deeply as needed, savepoints are used automatically. If a transaction throws an error (fails), it is correctly rolled back and the original error is rethrown. The connection has to be passed around, otherwise the queries would not run in a transaction. The value returned from the transaction callback is returned from the transaction function.

import database, {SQL} from './connection';
import type {Connection} from '@cookielab.io/postgres-client';

const result = await database.transaction(async (transaction: Connection): Promise<number> => {
    await transaction.query(SQL`…`);

    await transaction.transaction(async (nestedTransaction: Connection): Promise<void> => {
        await transaction.query(SQL`…`);
    });

    return 42;
});

// result === 42

SQL modifiers

The option to use the SQL`…` syntax comes from package pg-async and is re-exported by this library. The available modifiers are:

For an identifier name (table/column name):

• id
• ident
• identifier
• name

SQL`SELECT * FROM $name${tableName}`

For a list of identifiers separated by a comma:

• columnNames

SQL`SELECT $columnNames${columns} FROM table`

For a literal:

• (empty)
• literal

SQL`SELECT * FROM table WHERE email = ${email}`;
SQL`SELECT * FROM table WHERE email = $literal${email}`;

For a raw value

• !

SQL`SELECT * FROM table WHERE email = $!${thisWillNotBeEscaped}`;

For an object:

• insert_object

const object = {
    column: 'value',
};

SQL`INSERT INTO table $insert_object${object}`; // INSERT INTO table (column) VALUES ('value')

For an assignment:

• assign

const object = {
    column: 'value',
};

SQL`UPDATE table SET $assign${object}`; // UPDATE table SET column = 'value'

For a list of values:

• values

const values = [
    'value',
    1234,
];

SQL`INSERT INTO table (string, number) VALUES ($values${values})`; // INSERT INTO table (string, number) VALUES ('value', 1234)

For a multi insert:

• multiInsert

const values = [
    {
        string: 'value',
        number: 1234,
    },
    {
        string: 'value',
        number: 1234,
    },
];

SQL`INSERT INTO table $multiInsert${values})`; // INSERT INTO table (string, number) VALUES ('value', 1234), ('value', 1234)

Types mapping

The library automatically casts types in both ways (Insert and Select).

Insert & Update

When inserting, types are casted by Postres, meaning you can insert string to INTEGER postgres column and INTEGER will be inserted. For a date, you can insert a Date object.

Warning

Since version 7.0.0, automatic conversion for object with toSQL was removed. Use custom types.

Select & Where conditions

When selecting values from Postgres, values are casted in following manner:

PG column type	JS type	PG column value	JS value
ANY	object	NULL	NULL
BOOLEAN	boolean	true	true
DATETIME	Date	2019-09-30T08:49:52.157Z	Mon Sep 30 2019 08:48:59 GMT+0000 (GMT)
TIMESTAMP	Date	2019-09-30T08:49:52.157Z	Mon Sep 30 2019 08:48:59 GMT+0000 (GMT)
TIMESTAMP WITH TIMEZONE	Date	2019-09-30T08:49:52.157Z	Mon Sep 30 2019 08:48:59 GMT+0000 (GMT)
SMALLINT	number	42	42
INTEGER	number	42	42
DOUBLE PRECISION	number	42	42
REAL	number	42	42
DECIMAL	string	42	'42'
NUMBER	string	42	'42'
BIGINT	string	42	'42'
NUMERIC	string	42	'42'
NUMERIC(PRECISION)	string	42	'42'
NUMERIC(PRECISION,SCALE)	string	42	'40.00' (for scale = 2)

Custom types

The library allows to register transformers in both directions:

• database value -> javascript value
• javascript value -> database value

Database value -> javascript value

The following code causes every datetime value to be converted to false;

import database from './connection';

await database.registerDatabaseTypes([
    {
        name: 'datetime', // the database type name, its oid is found automatically
        parser: (value: string | undefined): Date | null => {
            if (value == null) {
                return null;
            }

            return new Date(value);
        }
    }
]);

Javascript value -> database value

The following code causes every TuringMachine value to be converted into string via its encode method.

import database from './connection';

database.registerJavascriptTypes([ // does not return a promise
    {
        match: (value: any) => value instanceof TuringMachine,
        convert (value: TuringMachine) => value.encode(),
    }
]);

Api

Queries

Transactions

transaction<T>(callback: (connection: Connection) => Promise<T> | T): Promise<T>

Used to initialize transaction. Callback takes connections as first parameter. This parameter contains transaction and should be propagated down to any functions that require database connection.

Any query

query<T>(input: QueryConfig | string, values?: readonly any[]): Promise<QueryResult<T>>

Runs any query on the first available client in the pool or on the active connection during an active transaction.

Example:

await client.query<void>(SQL`DELETE FROM table WHERE 1=1`);

Insert

insert<T>(table: string, values: T): Promise<void>

Inserts values into the given table Values are translated as literals. Column names are changed into snake_case.

Example:

const object = {
    column: 'value',
};

await client.insert<void>(SQL`INSERT INTO table $insert_object${object}`);

Select

findOne<T>(input: QueryConfig | string, values?: readonly any[]): Promise<T | null>

Returns one row if only one row is found. Returns null if no row is found. Throws OneRowExpectedError if more rows are found.

Example:

const row = await client.findOne<{id: number, name: string}>(SQL`SELECT id, name FROM table LIMIT 1`);

findOneColumn<T>(input: QueryConfig | string, values?: readonly any[], columnIndex?: number): Promise<T | null>

Return value of specified column index if only one row is found. Returns null if no row is found. Columns are indexed from 0. If no column index is specified, 0 is used as default. Throws OneRowExpectedError if more rows are found. Throws NonExistentColumnIndexError if non existent column index is requested.

Example:

const name = await client.findOneColumn<string>(SQL`SELECT id, name FROM table LIMIT 1`, [], 1);

getOne<T>(input: QueryConfig, error: {new(...parameters: readonly any[]): Error}): Promise<T>

Returns one row if only one row is found. Throws given Error if no row is found. Throws OneRowExpectedError if more rows are found.

Example:

const oneRow = await client.getOne<{id: number, name: string}>(SQL`SELECT id, name FROM table LIMIT 1`);

getColumn<T>(input: QueryConfig | string, values?: readonly any[], columnIndex?: number): Promise<readonly T[]>

Returns array of requested column index values. Columns are indexed from 0. If no column index is specified, 0 is used as default. Throws NonExistentColumnIndexError if non existent column index is requested.

Example:

const ids = await client.getColumn<number>(SQL`SELECT id, name FROM table`, [], 0);

getOneColumn<T>(input: QueryConfig | string, values?: readonly any[], columnIndex?: number): Promise<T>

Returns value of specified column index if only one row is found. Columns are indexed from 0. If no column index is specified, 0 is used as default. Throws OneRowExpectedError if either no row is found or more rows are found. Throws NonExistentColumnIndexError if non existent column index is requested.

Example:

const oneId = await client.getOneColumn<number>(SQL`SELECT id, name FROM table LIMIT 1`);

getRow<T>(input: QueryConfig | string, values?: readonly any[]): Promise<T>

Returns one row if only one row is found. Throws OneRowExpectedError if either no row is found or more rows are found.

Example:

const oneRow = await client.getRow<{id: number, name: string}>(SQL`SELECT id, name FROM table LIMIT 1`);

getRows<T>(input: QueryConfig | string, values?: readonly any[]): Promise<readonly T[]>

Returns array of found rows.

Example:

const rows = await client.getRows<{id: number, name: string}>(SQL`SELECT id, name FROM table`);

Collectors

BatchInsertCollector

Collects rows and deletes them in batches

The default maximum number of rows per batch is 1000. Rows are added by add() method. Rows are inserted by multiInsert SQL modifier when the flush() method is called OR when the add() method is called and the rows collected are already at the limit of batch size. Number of rows per insert batch can be set by batchSize property of options object. Query suffix can be set by querySuffix property of options object.

ALWAYS call await flush() when you are done adding more rows.

import database, {BatchInsertCollector, SQL} from './connection';

await database.transaction(async (transaction) => {
    const ids = [{id: 1}, {id: 2}, {id: 3}];

    const insertCollector = new BatchInsertCollector(transaction, 'table', {
        batchSize: 500, // default is set to 1000
        querySuffix: 'ON CONFLICT (id) DO NOTHING', // Not needed, however, you can use whatever suffix query for the multi-insert including SELECT ... FROM
    });
    
    for (const id of ids) {
       insertCollector.add(id);
    }

    await insertCollector.flush(); // Insert rows
    
    const insertedRowsCount = insertCollector.getInsertedRowCount() // returns 3
}); 

BatchDeleteCollector

Collects rows and deletes them in batches

The default maximum number of rows per batch is 1000. Key values to be deleted are added by add() method. Key name to be deleted is set by keyName property of options object. If no value is supplied, id is used as default. Number of keys per delete batch can be set by batchSize property of options object.

Rows are deleted by following syntax (after all SQL modifiers are applied):

DELETE FROM table WHERE keyName IN ($1, $2, $3);

ALWAYS call await flush() when you are done adding more rows.

import database, {BatchDeleteCollector, SQL} from './connection';

await database.transaction(async (transaction) => {
    const names = ['name1', 'name2', 'name3'];

    const deleteCollector = new BatchDeleteCollector(transaction, 'table', {
       keyName: 'name', // if not supplied, default value is id 
       batchSize: 500, // default is set to 1000
    });
    
    for (const name of names) {
       deleteCollector.add(name);
    }

    await deleteCollector.flush(); // Insert rows
    
    const deletedRowsCount = deleteCollector.getDeletedRowCount() // returns 3
});