postgresql-client

postgresql-client

Enterprise level PostgreSQL client for NodeJs

Library

• Pure JavaScript library completely written in TypeScript
• Works with both CommonJS and ESM module systems
• Well tested
• Strictly typed
• Asynchronous Promise based api

Features

• Both single connection and advanced pooling support
• Full binary wire protocol support for all data types
• Named Prepared Statements
• Cursors with fast double-link cache
• High level implementation for notifications (LISTEN/NOTIFY)
• Extensible data-types and type mapping
• Bind parameters with OID mappings
• Multidimensional arrays with fast binary encoding/decoding
• Low memory utilization and boosted performance with Shared Buffers
• Supports Clear text, MD5 and SASL password algorithms
• Can return both array and object rows
• Auto disposal with "using" syntax (TC30 Explicit Resource Management)

Installation

$ npm install postgresql-client --save

Documentation

Please read :small_orange_diamond: DOCUMENTATION :small_orange_diamond: for detailed usage.

Example usage

Establish a single connection, execute a simple query

import {Connection} from 'postgresql-client';
// Create connection
const connection = new Connection('postgres://localhost');
// Connect to database server
await connection.connect();

// Execute query and fetch rows
const result = await connection.query(
    'select * from cities where name like $1',
    {params: ['%york%']});
const rows: any[] = result.rows;
// Do what ever you want with rows

// Disconnect from server
await connection.close(); 

Establish a pooled connection, create a cursor

import {Pool} from 'postgresql-client';

// Create connection pool
const db = new Pool({
    host: 'postgres://localhost',
    pool: {
       min: 1,
       max: 10,
       idleTimeoutMillis: 5000
    }
});

// Execute query and fetch cursor
const result = await db.query(
    'select * from cities where name like $1',
    {params: ['%york%'], cursor: true});

// Walk through the cursor, and do whatever you want with fetched rows
const cursor = result.cursor;
let row;
while ((row = await cursor.next())) {
  console.log(row);
}
// Close cursor, (Send connection back to the pool)
await cursor.close();

// Disconnect all connections and shutdown pool
await db.close(); 

Using prepared statements

import {DataTypeOIDs} from 'postgresql-client'; 

// .....
const statement = await connection.prepare( 
    'insert into my_table(id, name) values ($1, $2)', {
        paramTypes: [DataTypeOIDs.Int4, DataTypeOIDs.Varchar]
    });

for (let i = 0; i < 100; i++) {
    await statement.execute({params: [i, ('name' + i)]});
}
await statement.close(); // When you done, close the statement to relase resources

Check DOCUMENTATION for other examples.

Type mappings

The table below lists builtin data type mappings.

Posgtres type	JS type	Receive	Send
bool	boolean	text,binary	binary
int2	number	text,binary	binary
int4	number	text,binary	binary
int8	BigInt	text,binary	binary
float4	number	text,binary	binary
float8	number	text,binary	binary
char	string	text,binary	binary
bpchar	string	text,binary	binary
varchar	string	text,binary	binary
date	Date	text,binary	binary
time	Date	text,binary	binary
timestamp	Date	text,binary	binary
timestamptz	Date	text,binary	binary
oid	number	text,binary	binary
bytea	Buffer	text,binary	binary
uuid	string	text,binary	binary
json	object	text,binary	binary
jsonb	object	text,binary	binary
xml	string	text,binary	binary
point	Point	text,binary	binary
circle	Circle	text,binary	binary
lseg	Rectangle	text,binary	binary
box	Rectangle	text,binary	binary
int2Vector	number[]	text,binary	binary
_bool	boolean[]	text,binary	binary
_int2	number[]	text,binary	binary
_int4	number[]	text,binary	binary
_int8	BigInt[]	text,binary	binary
_float4	number[]	text,binary	binary
_float8	number[]	text,binary	binary
_char	string[]	text,binary	binary
_bpchar	string[]	text,binary	binary
_varchar	string[]	text,binary	binary
_date	Date[]	text,binary	binary
_time	Date[]	text,binary	binary
_timestamp	Date[]	text,binary	binary
_timestamptz	Date[]	text,binary	binary
_uuid	string[]	text,binary	binary
_oid	number[]	text,binary	binary
_bytea	Buffer[]	text,binary	binary
_json	object[]	text,binary	binary
_jsonb	object[]	text,binary	binary
_xml	string[]	text,binary	binary
_point	Point[]	text,binary	binary
_circle	Circle[]	text,binary	binary
_lseg	Rectangle[]	text,binary	binary
_box	Rectangle[]	text,binary	binary
_int2Vector	number[][]	text,binary	binary

Support

You can report bugs and discuss features on the GitHub issues page When you open an issue please provide version of NodeJS and PostgreSQL server.

Node Compatibility

• node >= 14.x

License

postgresql-client is available under MIT license.