@juit/pgproxy-client NPM

PostgreSQL Proxy Client (Base Package)

This package provides the main entry point for clients to work with PGProxy Servers. It acts both as an abstraction layer over the various client implementations and as a registry for them.

Connecting

In the code, you can simply depend on the PGClient class:

import { PGClient } from '@juit/pgproxy-client'

const client = new PGClient()

The client can be constructed with a url as a parameter, indicating the endpoint of the connection and the specific client to be used. If no such parameter is specified, the value of the PGURL environment variable will be used.

Additionally if the URL specified at construction (or in the PGURL environment variable) does not provide ANY authentication information, the PGUSER and PGPASSWORD environment variables will be used to fill in those details.

Specific implementations are registered by simply importing their library:

@juit/pgproxy-client-node: The HTTP client for NodeJS \ handles URLs like: http(s)://secret@host:port/
@juit/pgproxy-client-whatwg: The HTTP client for WhatWG + WebCrypto \ handles URLs like: http(s)://secret@host:port/
@juit/pgproxy-client-psql: The direct LibPQ-based client \ handles URLs like: psql://usrname:password@host:port/database

The ability to abstract client and connection details allows the code to be as portable as possible. For example in an AWS Lambda Function:

// Entry point for AWS Lambda functions

// Import the _node_ client, the PGURL environment variable comes from the
// Lambda definitions and can be specified via the AWS console, it will have
// a format like: https://my-secret@my-ec2-instance:54321/
import '@pgproxy/client-node'

export const handler = async (event: LambdaEvent) => {
  // ... use code that connects to the database using `new PGClient()`
}

Similarly, when running a test requiring a connection to a local database (no need to spin up a whole PGProxy Server to test):

// Entry point for tests

// Import the _psql_ client, which will be registered as a handler for the
// "psql" protocol in PGClient
import '@pgproxy/client-psql'

beforeAll(() => {
  process.env.PGURL = "psql://username:password@localhost:5432/my-database"
})

it('should run tests connecting to the database', async () => {
  // ... test the code using `new PGCLient()`
})

Client

Simple queries can be executed on the database via the query(...) method:

const client = new PGClient()
const result = await client.query('SELECT * FROM test WHERE value = $1', [ 'theValue' ])

More complex queries (e.g. transactions) can be performed using the connect(...) method:

const client = new PGClient()
// here "result" will be the value returned by the callback passed to "connect"
const result = await client.connect(async (connection) => {
  await connection.begin()

  await connection.query(...) // ... all transaction queries

  await connection.commit()
  return result // returned to whatever is awaiting on "connect"
})

The query(...) method requires one parameter, the SQL query to run, and allows parameters (as an array) to be declared as a second, optional parameter.

The object passed to the connect(...) callback provides the following methods:

query(...): as above
begin(): issues the BEGIN SQL statement (starts a transaction)
commit(): issues the COMMIT SQL statement (commits a transaction)
rollback(): issues the ROLLBACK SQL statement (rolls back a transaction)

Uncommitted transactions will always be rolled back by the connection pool code.

Result

The result returned by the query(...) method is a simple object containing:

command (string): The SQL command that generated this result (e.g. SELECT, INSERT, ...)
rowCount (number): The number of rows affected by the query. \ This can be the number of lines returned in rows (for SELECT statements, for example) or the number of lines affected by the query (the number of records inserted by an INSERT query).
rows (Record<string, any>[]): The rows returned by the database query, keyed by the column name.
tuples (any): The tuples returned by the database query, keyed by the column index. */