• features
  • Connecting

Environment variables

node-postgres uses the same environment variables as libpq and psql to connect to a PostgreSQL server. Both individual clients & pools will use these environment variables. Here's a tiny program connecting node.js to the PostgreSQL server:

import pg from 'pg'
const { Pool, Client } = pg
 
// pools will use environment variables
// for connection information
const pool = new Pool()
 
// you can also use async/await
const res = await pool.query('SELECT NOW()')
await pool.end()
 
// clients will also use environment variables
// for connection information
const client = new Client()
await client.connect()
 
const res = await client.query('SELECT NOW()')
await client.end()

To run the above program and specify which database to connect to we can invoke it like so:

$ PGUSER=dbuser \
  PGPASSWORD=secretpassword \
  PGHOST=database.server.com \
  PGPORT=3211 \
  PGDATABASE=mydb \
  node script.js

This allows us to write our programs without having to specify connection information in the program and lets us reuse them to connect to different databases without having to modify the code.

The default values for the environment variables used are:

PGUSER=process.env.USER
PGPASSWORD=null
PGHOST=localhost
PGPORT=5432
PGDATABASE=process.env.USER

Programmatic

node-postgres also supports configuring a pool or client programmatically with connection information. Here's our same script from above modified to use programmatic (hard-coded in this case) values. This can be useful if your application already has a way to manage config values or you don't want to use environment variables.

import pg from 'pg'
const { Pool, Client } = pg
 
const pool = new Pool({
  user: 'dbuser',
  password: 'secretpassword',
  host: 'database.server.com',
  port: 3211,
  database: 'mydb',
})
 
console.log(await pool.query('SELECT NOW()'))
 
const client = new Client({
  user: 'dbuser',
  password: 'secretpassword',
  host: 'database.server.com',
  port: 3211,
  database: 'mydb',
})
 
await client.connect()
 
console.log(await client.query('SELECT NOW()'))
 
await client.end()

Many cloud providers include alternative methods for connecting to database instances using short-lived authentication tokens. node-postgres supports dynamic passwords via a callback function, either synchronous or asynchronous. The callback function must resolve to a string.

import pg from 'pg'
const { Pool } = pg
import { RDS } from 'aws-sdk'
 
const signerOptions = {
  credentials: {
    accessKeyId: 'YOUR-ACCESS-KEY',
    secretAccessKey: 'YOUR-SECRET-ACCESS-KEY',
  },
  region: 'us-east-1',
  hostname: 'example.aslfdewrlk.us-east-1.rds.amazonaws.com',
  port: 5432,
  username: 'api-user',
}
 
const signer = new RDS.Signer()
 
const getPassword = () => signer.getAuthToken(signerOptions)
 
const pool = new Pool({
  user: signerOptions.username,
  password: getPassword,
  host: signerOptions.hostname,
  port: signerOptions.port,
  database: 'my-db',
})

Unix Domain Sockets

Connections to unix sockets can also be made. This can be useful on distros like Ubuntu, where authentication is managed via the socket connection instead of a password.

import pg from 'pg'
const { Client } = pg
client = new Client({
  user: 'username',
  password: 'password',
  host: '/cloudsql/myproject:zone:mydb',
  database: 'database_name',
})

Connection URI

You can initialize both a pool and a client with a connection string URI as well. This is common in environments like Heroku where the database connection string is supplied to your application dyno through an environment variable. Connection string parsing brought to you by pg-connection-string.

import pg from 'pg'
const { Pool, Client } = pg
const connectionString = 'postgresql://dbuser:[email protected]:3211/mydb'
 
const pool = new Pool({
  connectionString,
})
 
await pool.query('SELECT NOW()')
await pool.end()
 
const client = new Client({
  connectionString,
})
 
await client.connect()
 
await client.query('SELECT NOW()')
 
await client.end()
UtilitiesQueries