new Client
new Client(config: Config)
Every field of the config object is entirely optional. A Client instance will use environment variables for all missing values.
type Config = { user?: string, // default process.env.PGUSER || process.env.USER password?: string or function, //default process.env.PGPASSWORD host?: string, // default process.env.PGHOST port?: number, // default process.env.PGPORT database?: string, // default process.env.PGDATABASE || user connectionString?: string, // e.g. postgres://user:password@host:5432/database ssl?: any, // passed directly to node.TLSSocket, supports all tls.connect options types?: any, // custom type parsers statement_timeout?: number, // number of milliseconds before a statement in query will time out, default is no timeout query_timeout?: number, // number of milliseconds before a query call will timeout, default is no timeout lock_timeout?: number, // number of milliseconds a query is allowed to be en lock state before it's cancelled due to lock timeout application_name?: string, // The name of the application that created this Client instance connectionTimeoutMillis?: number, // number of milliseconds to wait for connection, default is no timeout keepAliveInitialDelayMillis?: number, // set the initial delay before the first keepalive probe is sent on an idle socket idle_in_transaction_session_timeout?: number, // number of milliseconds before terminating any session with an open idle transaction, default is no timeout client_encoding?: string, // specifies the character set encoding that the database uses for sending data to the client fallback_application_name?: string, // provide an application name to use if application_name is not set options?: string // command-line options to be sent to the server }example to create a client with specific connection information:
import { Client } from 'pg' const client = new Client({ user: 'database-user', password: 'secretpassword!!', host: 'my.database-server.com', port: 5334, database: 'database-name', })client.connect
import { Client } from 'pg' const client = new Client() await client.connect()client.query
QueryConfig
You can pass an object to client.query with the signature of:
type QueryConfig { // the raw query text text: string; // an array of query parameters values?: Array<any>; // name of the query - used for prepared statements name?: string; // by default rows come out as a key/value pair for each row // pass the string 'array' here to receive rows as an array of values rowMode?: string; // custom type parsers just for this query result types?: Types; // TODO: document queryMode?: string; }client.query(text: string, values?: any[]) => Promise<Result>Plain text query
import { Client } from 'pg' const client = new Client() await client.connect() const result = await client.query('SELECT NOW()') console.log(result) await client.end()Parameterized query
import { Client } from 'pg' const client = new Client() await client.connect() const result = await client.query('SELECT $1::text as name', ['brianc']) console.log(result) await client.end()client.query(config: QueryConfig) => Promise<Result>client.query with a QueryConfig
If you pass a name parameter to the client.query method, the client will create a prepared statement.
const query = { name: 'get-name', text: 'SELECT $1::text', values: ['brianc'], rowMode: 'array', } const result = await client.query(query) console.log(result.rows) // ['brianc'] await client.end()client.query with a Submittable
If you pass an object to client.query and the object has a .submit function on it, the client will pass it's PostgreSQL server connection to the object and delegate query dispatching to the supplied object. This is an advanced feature mostly intended for library authors. It is incidentally also currently how the callback and promise based queries above are handled internally, but this is subject to change. It is also how pg-cursor and pg-query-stream work.
import { Query } from 'pg' const query = new Query('select $1::text as name', ['brianc']) const result = client.query(query) assert(query === result) // true query.on('row', (row) => { console.log('row!', row) // { name: 'brianc' } }) query.on('end', () => { console.log('query done') }) query.on('error', (err) => { console.error(err.stack) })client.end
Disconnects the client from the PostgreSQL server.
await client.end() console.log('client has disconnected')events
error
client.on('error', (err: Error) => void) => voidWhen the client is in the process of connecting, dispatching a query, or disconnecting it will catch and forward errors from the PostgreSQL server to the respective client.connect client.query or client.end promise; however, the client maintains a long-lived connection to the PostgreSQL back-end and due to network partitions, back-end crashes, fail-overs, etc the client can (and over a long enough time period will) eventually be disconnected while it is idle. To handle this you may want to attach an error listener to a client to catch errors. Here's a contrived example:
const client = new pg.Client() client.connect() client.on('error', (err) => { console.error('something bad has happened!', err.stack) }) // walk over to server, unplug network cable // process output: 'something bad has happened!' followed by stacktrace :Pend
client.on('end') => voidWhen the client disconnects from the PostgreSQL server it will emit an end event once.
notification
Used for listen/notify events:
type Notification { processId: number, channel: string, payload?: string }const client = new pg.Client() await client.connect() client.query('LISTEN foo') client.on('notification', (msg) => { console.log(msg.channel) // foo console.log(msg.payload) // bar! }) client.query(`NOTIFY foo, 'bar!'`)notice
client.on('notice', (notice: Error) => void) => voidUsed to log out notice messages from the PostgreSQL server.
client.on('notice', (msg) => console.warn('notice:', msg))