node-postgres

https://node-postgres.com
Admin
node-postgres is a collection of node.js modules for interfacing with your PostgreSQL database.
Tokens:36,106
Snippets:273
Trust Score:9.6
Update:3 weeks ago
Show doc for...
Tokens:
Raw
### client.release basic example

Source: https://node-postgres.com/apis/pool

Example of checking out a client and releasing it back to the pool.

```javascript
import { Pool } from 'pg'
 
const pool = new Pool()
 
// check out a single client
const client = await pool.connect()
 
// release the client
client.release()
```

--------------------------------

### Install pg and pg-native

Source: https://node-postgres.com/features/native

Install both the pg and pg-native packages via npm.

```bash
$ npm install pg pg-native
```

--------------------------------

### client.release with destroy example

Source: https://node-postgres.com/apis/pool

Example showing pool counts before and after connecting and releasing with destroy=true. The pool destroys the client instead of returning it to the pool.

```javascript
import { Pool } from 'pg'
 
const pool = new Pool()
assert(pool.totalCount === 0)
assert(pool.idleCount === 0)
 
const client = await pool.connect()
await client.query('SELECT NOW()')
assert(pool.totalCount === 1)
assert(pool.idleCount === 0)
 
// tell the pool to destroy this client
await client.release(true)
assert(pool.idleCount === 0)
assert(pool.totalCount === 0)
```

--------------------------------

### new Client(config: Config)

Source: https://node-postgres.com/apis/client

Documentation for the Client constructor, including the configuration object and an example.

```APIDOC
## new Client(config: Config)

### Description
Creates a new PostgreSQL client instance. All configuration fields are optional and environment variables are used for missing values.

### Constructor
`new Client(config: Config)`

### Parameters
#### Config Object
- **user** (string) - Optional - Default: `process.env.PGUSER || process.env.USER`
- **password** (string or function) - Optional - Default: `process.env.PGPASSWORD`
- **host** (string) - Optional - Default: `process.env.PGHOST`
- **port** (number) - Optional - Default: `process.env.PGPORT`
- **database** (string) - Optional - Default: `process.env.PGDATABASE || user`
- **connectionString** (string) - Optional - e.g. `postgres://user:password@host:5432/database`
- **ssl** (any) - Optional - Passed directly to `node.TLSSocket`, supports all `tls.connect` options
- **types** (any) - Optional - Custom type parsers
- **statement_timeout** (number) - Optional - Number of milliseconds before a statement in query will time out, default is no timeout
- **query_timeout** (number) - Optional - Number of milliseconds before a query call will timeout, default is no timeout
- **lock_timeout** (number) - Optional - Number of milliseconds a query is allowed to be in lock state before it's cancelled due to lock timeout
- **application_name** (string) - Optional - The name of the application that created this Client instance
- **connectionTimeoutMillis** (number) - Optional - Number of milliseconds to wait for connection, default is no timeout
- **keepAlive** (boolean) - Optional - If true, enable keepalive on the `net.Socket`
- **keepAliveInitialDelayMillis** (number) - Optional - Number of milliseconds between last data packet received and first keepalive probe, default is 0 (keep existing value); no effect unless `keepAlive` is true
- **idle_in_transaction_session_timeout** (number) - Optional - Number of milliseconds before terminating any session with an open idle transaction, default is no timeout
- **client_encoding** (string) - Optional - Specifies the character set encoding that the database uses for sending data to the client
- **fallback_application_name** (string) - Optional - Provide an application name to use if `application_name` is not set
- **options** (string) - Optional - Command-line options to be sent to the server
- **pipeline** (boolean) - Optional - When true, enables query pipelining. See /features/pipelining for details. Default false.

### Example
```javascript
import { Client } from 'pg'

const client = new Client({
  user: 'database-user',
  password: 'secretpassword!!',
  host: 'my.database-server.com',
  port: 5334,
  database: 'database-name',
})
```
```

--------------------------------

### Run setup commands with onConnect

Source: https://node-postgres.com/apis/pool

Uses the onConnect callback to run setup commands on each new client before it is made available to the pool. The client is fully connected and queryable at this point. If the callback throws or rejects, the client is destroyed and the error is returned to the caller.

```javascript
import { Pool } from 'pg'
 
const pool = new Pool({
  onConnect: async (client) => {
    await client.query('SET search_path TO my_schema')
  },
})
```

--------------------------------

### pool.connect usage example

Source: https://node-postgres.com/apis/pool

Example of acquiring a client from the pool, running a query, and releasing it back to the pool.

```javascript
import { Pool } from 'pg'
 
const pool = new Pool()
 
const client = await pool.connect()
await client.query('SELECT NOW()')
client.release()
```

--------------------------------

### Connect, query, and disconnect with Client

Source: https://node-postgres.com/

Connects to PostgreSQL, runs a parameterized query, logs the result, and disconnects using async/await. This is the simplest way to get started with node-postgres.

```javascript
import { Client } from 'pg'
const client = await new Client().connect()
 
const res = await client.query('SELECT $1::text as message', ['Hello world!'])
console.log(res.rows[0].message) // Hello world!
await client.end()
```

--------------------------------

### Listen/notify example with pg.Client

Source: https://node-postgres.com/apis/client

Example of using listen/notify events. After connecting, LISTEN on a channel, attach a notification listener, then NOTIFY with a payload. The listener logs the channel and payload.

```javascript
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!'`)
```

--------------------------------

### Install pg and pg-cursor

Source: https://node-postgres.com/apis/cursor

Installs the pg and pg-cursor packages via npm.

```bash
$ npm install pg pg-cursor
```

--------------------------------

### Install node-postgres with npm

Source: https://node-postgres.com/

Installs the node-postgres library via npm. Run this in your project directory before using the pg module.

```bash
$ npm install pg
```

--------------------------------

### Create a pg.Client with specific connection information

Source: https://node-postgres.com/apis/client

Example of instantiating a pg.Client with explicit connection parameters. All fields are optional; missing values default to environment variables.

```javascript
import { Client } from 'pg'
 
const client = new Client({
  user: 'database-user',
  password: 'secretpassword!!',
  host: 'my.database-server.com',
  port: 5334,
  database: 'database-name',
})
```

--------------------------------

### Plain Text Query Example

Source: https://node-postgres.com/apis/client

Connects a client, executes a simple SELECT NOW() query, logs the result, and ends the connection.

```javascript
import { Client } from 'pg'
const client = new Client()
 
await client.connect()
 
const result = await client.query('SELECT NOW()')
console.log(result)
 
await client.end()
```

--------------------------------

### new Pool(config: Config)

Source: https://node-postgres.com/apis/pool

Documentation for the Pool constructor, including the Config type and usage examples.

```APIDOC
## new Pool(config: Config)

### Description
Constructs a new pool instance. The pool is initially created empty and will create new clients lazily as they are needed. Every field of the `config` object is entirely optional.

### Method
Constructor

### Parameters
#### Config Object
- **connectionTimeoutMillis** (number) - Optional - Number of milliseconds to wait before timing out when connecting a new client. Default is 0 (no timeout).
- **idleTimeoutMillis** (number) - Optional - Number of milliseconds a client must sit idle in the pool and not be checked out before it is disconnected from the backend and discarded. Default is 10000 (10 seconds). Set to 0 to disable auto-disconnection of idle clients.
- **max** (number) - Optional - Maximum number of clients the pool should contain. Default is 10.
- **min** (number) - Optional - Minimum number of clients the pool should hold on to and not destroy with the idleTimeoutMillis. Default is 0.
- **allowExitOnIdle** (boolean) - Optional - When set to true, allows the node event loop to exit as soon as all clients in the pool are idle. Default is false.
- **maxUses** (number) - Optional - Number of times a client can be checked out from the pool before it is disconnected and a new client is created in its place. Default is Infinity.
- **maxLifetimeSeconds** (number) - Optional - Sets a max overall life for the connection. Default is disabled (value of zero).
- **onConnect** (function) - Optional - Called once when a new client is created, before it is made available to the pool. Can be a regular function or an async function. If the function throws or returns a promise that rejects, the client is destroyed and the error is returned to the caller requesting the connection.
- **pipeline** (boolean) - Optional - When set to true, enables query pipelining on every client the pool creates. Default is false.

### Request Example
```js
import { Pool } from 'pg'

const pool = new Pool({
  host: 'localhost',
  user: 'database-user',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
  maxLifetimeSeconds: 60,
})
```

### Example with onConnect
```js
import { Pool } from 'pg'

const pool = new Pool({
  onConnect: async (client) => {
    await client.query('SET search_path TO my_schema')
  },
})
```
```

--------------------------------

### Route using db adapter in routes/user.js

Source: https://node-postgres.com/guides/project-structure

Example route in routes/user.js that imports the database adapter instead of node-postgres directly, allowing centralized query logging and client management.

```javascript
// notice here I'm requiring my database adapter file
// and not requiring node-postgres directly
import * as db from '../db/index.js'
 
app.get('/:id', async (req, res, next) => {
  const result = await db.query('SELECT * FROM users WHERE id = $1', [req.params.id])
  res.send(result.rows[0])
})
 
// ... many other routes in this file
```

--------------------------------

### Attach notice listener to pg.Client

Source: https://node-postgres.com/apis/client

Example of attaching a notice listener that logs notice messages from the PostgreSQL server using console.warn.

```javascript
client.on('notice', (msg) => console.warn('notice:', msg))
```

--------------------------------

### Parameterized Query Example

Source: https://node-postgres.com/apis/client

Executes a parameterized query using $1 placeholders and an array of values. Logs the result and ends the connection.

```javascript
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()
```

--------------------------------

### Query with QueryConfig and Prepared Statement

Source: https://node-postgres.com/apis/client

Passes a QueryConfig object with a name to create a prepared statement. The example uses rowMode 'array' and logs the result rows.

```javascript
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()
```

--------------------------------

### pool.query usage example

Source: https://node-postgres.com/apis/pool

Example of using pool.query to run a single query without manually checking out or releasing a client. The pool handles acquiring and releasing internally. Do not use pool.query for transactions.

```javascript
import { Pool } from 'pg'
 
const pool = new Pool()
 
const result = await pool.query('SELECT $1::text as name', ['brianc'])
console.log(result.rows[0].name) // brianc
```

--------------------------------

### Use native bindings with pg

Source: https://node-postgres.com/features/native

Access the native bindings by importing pg and destructuring the native property to get Client and Pool constructors.

```javascript
import pg from 'pg'
const { native } = pg
const { Client, Pool } = native
```

--------------------------------

### Using callbacks with pg Pool and Client

Source: https://node-postgres.com/features/callbacks

Shows how to use callbacks with the pg module's Pool and Client. Pass a callback as the last argument to query, connect, and other methods; the callback receives (err, res) or (err, client, release) and no promise is returned. Includes examples for pool.query, pool.connect for transactions, and client.connect.

```javascript
const { Pool, Client } = require('pg')
 
// pool
const pool = new Pool()
// run a query on an available client
pool.query('SELECT NOW()', (err, res) => {
  console.log(err, res)
})
 
// check out a client to do something more complex like a transaction
pool.connect((err, client, release) => {
  client.query('SELECT NOW()', (err, res) => {
    release()
    console.log(err, res)
    pool.end()
  })
 
})
 
// single client
const client = new Client()
client.connect((err) => {
  if (err) throw err
  client.query('SELECT NOW()', (err, res) => {
    console.log(err, res)
    client.end()
  })
})
```

--------------------------------

### db/index.js - pg Pool and query export

Source: https://node-postgres.com/guides/async-express

Initial db/index.js file that creates a pg Pool and exports a query function. This is the starting point for data access code.

```javascript
import { Pool } from 'pg'
 
const pool = new Pool()
 
export const query = (text, params) => pool.query(text, params)
```

--------------------------------

### Vulnerability demonstration with pg.Client

Source: https://node-postgres.com/announcements

Example that demonstrates the code execution vulnerability in node-postgres versions from pg@2.x through pg@7.1.0. Running this in node prints your environment variables to the console, showing that an attacker can execute arbitrary node code within your process.

```javascript
import pg from 'pg'
const { Client } = pg
const client = new Client()
client.connect()
 
const sql = `SELECT 1 AS "\'/*", 2 AS "\'*/
 + console.log(process.env)] = null;
//"`
 
client.query(sql, (err, res) => {
  client.end()
})
```

--------------------------------

### Direct SSL negotiation via config or connection string in pg

Source: https://node-postgres.com/features/ssl

Enable direct SSL negotiation for PostgreSQL 17+ servers, which saves one network round-trip by beginning the TLS handshake immediately. The first example shows the config object; the second supplies sslnegotiation=direct in a connection string, which also enables SSL automatically if not otherwise configured. Requires ssl to be enabled and the server to accept direct SSL connections.

```javascript
const config = {
  database: 'database-name',
  host: 'host-or-ip',
  ssl: { rejectUnauthorized: false },
  sslnegotiation: 'direct',
}
```

```javascript
const config = {
  connectionString: 'postgres://user:password@host:port/db?sslmode=require&sslnegotiation=direct',
}
```

--------------------------------

### Query with rowMode 'array' to inspect result.fields

Source: https://node-postgres.com/apis/result

Shows how to use rowMode: 'array' so that result.fields are ordered in the same order as the columns. The query returns two columns and the example logs the field names and the rows array.

```javascript
import pg from 'pg'
const { Pool } = pg
 
const pool = new Pool()
 
const client = await pool.connect()
const result = await client.query({
  rowMode: 'array',
  text: 'SELECT 1 as one, 2 as two;',
})
console.log(result.fields[0].name) // one
console.log(result.fields[1].name) // two
console.log(result.rows) // [ [ 1, 2 ] ]
await client.end()
```

--------------------------------

### Parameterized INSERT query with client.query

Source: https://node-postgres.com/features/queries

Parameterized query example. Pass the query text with $1, $2 placeholders and an array of values as the second argument. The parameters are safely substituted by the PostgreSQL server, avoiding SQL injection. The result rows are logged, showing the inserted user object.

```javascript
const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
const values = ['brianc', 'brian.m.carlson@gmail.com']
 
const res = await client.query(text, values)
console.log(res.rows[0])
// { name: 'brianc', email: 'brian.m.carlson@gmail.com' }
```

--------------------------------

### routes/user.js - async route with express-promise-router

Source: https://node-postgres.com/guides/async-express

routes/user.js defines an express-promise-router that allows async route handlers. The example shows a GET /:id route that queries the database and sends the first row.

```javascript
import Router from 'express-promise-router'
import db from '../db.js'
 
// create a new express-promise-router
// this has the same API as the normal express router except
// it allows you to use async functions as route handlers
const router = new Router()
 
// export our router to be mounted by the parent application
export default router
 
router.get('/:id', async (req, res) => {
  const { id } = req.params
  const { rows } = await db.query('SELECT * FROM users WHERE id = $1', [id])
  res.send(rows[0])
})
```

--------------------------------

### Attach error listener to pg.Client

Source: https://node-postgres.com/apis/client

Example of attaching an error listener to a client. When the client is idle and gets disconnected (e.g., network cable unplugged), the error event fires and logs the error stack.

```javascript
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 :P
```

--------------------------------

### Enable pipelining with Client({ pipeline: true })

Source: https://node-postgres.com/features/pipelining

Enable pipelining by passing `pipeline: true` to the `Client` constructor. All query types (plain text, parameterized, named prepared statements) work with pipelining.

```javascript
import { Client } from 'pg'
 
const client = new Client({ pipeline: true })
await client.connect()
 
const [r1, r2, r3] = await Promise.all([
  client.query('SELECT 1 AS num'),
  client.query('SELECT 2 AS num'),
  client.query('SELECT 3 AS num'),
])
 
console.log(r1.rows[0].num, r2.rows[0].num, r3.rows[0].num) // 1 2 3
 
await client.end()
```

--------------------------------

### Connect a Client

Source: https://node-postgres.com/apis/client

Creates a new Client and connects to the PostgreSQL server. The connection is established by calling await client.connect().

```javascript
import { Client } from 'pg'
const client = new Client()
 
await client.connect()
```

--------------------------------

### Checkout, use, and return a client from the pool

Source: https://node-postgres.com/features/pooling

Shows how to check out a client from the pool, run a query, and return it. Always release the client even on errors to avoid leaking connections and exhausting the pool.

```javascript
import pg from 'pg'
const { Pool } = pg
 
const pool = new Pool()
 
// the pool will emit an error on behalf of any idle clients
// it contains if a backend error or network partition happens
pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err)
  process.exit(-1)
})
 
const client = await pool.connect()
const res = await client.query('SELECT * FROM users WHERE id = $1', [1])
console.log(res.rows[0])
 
client.release()
```

--------------------------------

### client.connect

Source: https://node-postgres.com/apis/client

Establishes a connection to the PostgreSQL server. Must be called before issuing queries.

```APIDOC
## client.connect

### Description
Establishes a connection to the PostgreSQL server. Must be called before issuing queries.

### Method
connect

### Endpoint
client.connect()

### Parameters
None

### Request Example
```javascript
import { Client } from 'pg'
const client = new Client()
await client.connect()
```

### Response
#### Success Response
No return value (Promise resolves when connected).

```

--------------------------------

### Enable pipelining with Pool({ pipeline: true })

Source: https://node-postgres.com/features/pipelining

Enable pipelining on every client created by a pool by passing `pipeline: true` in the pool config. Use `pool.connect()` to check out a client and send multiple queries; `pool.query()` checks out a client for a single query and releases it immediately, so pipelining has no effect there.

```javascript
import { Pool } from 'pg'
 
const pool = new Pool({ pipeline: true })
 
const client = await pool.connect()
// client.pipeline is already true
 
const [users, orders] = await Promise.all([
  client.query('SELECT * FROM users WHERE id = $1', [1]),
  client.query('SELECT * FROM orders WHERE user_id = $1', [1]),
])
 
client.release()
```

--------------------------------

### Create a new Pool with configuration

Source: https://node-postgres.com/apis/pool

Creates a new pool with custom configuration. The pool is initially empty and creates clients lazily. The config is passed to every client instance the pool creates.

```javascript
import { Pool } from 'pg'
 
const pool = new Pool({
  host: 'localhost',
  user: 'database-user',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
  maxLifetimeSeconds: 60,
})
```

--------------------------------

### client.on('notice')

Source: https://node-postgres.com/apis/client

Registers a listener for the 'notice' event to log notice messages from the PostgreSQL server.

```APIDOC
## client.on('notice', (notice: Error) => void) => void

### Description
Registers a listener for the 'notice' event, used to log out notice messages from the PostgreSQL server.

### Event
`notice`

### Listener Signature
- **notice** (Error) - The notice message object.

### Example
```javascript
client.on('notice', (msg) => console.warn('notice:', msg))
```
```

--------------------------------

### Connect using environment variables

Source: https://node-postgres.com/features/connecting

Uses environment variables for connection information for both Pool and Client. The program queries the database and ends the pool and client.

```javascript
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()
```

--------------------------------

### Run program with environment variables

Source: https://node-postgres.com/features/connecting

Runs the program with environment variables to specify the database connection. The variables are PGUSER, PGPASSWORD, PGHOST, PGPORT, and PGDATABASE.

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

--------------------------------

### Custom type parsers in query config

Source: https://node-postgres.com/features/queries

Pass a custom set of type parsers via the `types` property, which must conform to the Types API. This example returns every value as a string by overriding `getTypeParser`.

```javascript
const query = {
  text: 'SELECT * from some_table',
  types: {
    getTypeParser: () => val => val,
  },
}
```

--------------------------------

### Enable Pipelining with Client Constructor

Source: https://node-postgres.com/apis/client

Sets the pipeline option to true when creating a Client to enable pipelining. The pipeline property is read-only and defaults to false.

```javascript
const client = new Client({ pipeline: true })
await client.connect()
```

--------------------------------

### Error isolation with Promise.allSettled

Source: https://node-postgres.com/features/pipelining

Each pipelined query gets its own error boundary; a failing query in the middle of a batch does not break the others. node-postgres sends a `Sync` message after each query, which delimits error boundaries in the extended query protocol.

```javascript
const results = await Promise.allSettled([
  client.query('SELECT 1 AS num'),
  client.query('SELECT INVALID SYNTAX'),
  client.query('SELECT 3 AS num'),
])
 
console.log(results[0].status) // 'fulfilled'
console.log(results[1].status) // 'rejected'
console.log(results[2].status) // 'fulfilled'
```

--------------------------------

### Self-signed certificate TLS configuration for pg Client and Pool

Source: https://node-postgres.com/features/ssl

Configure a client or pool to connect to PostgreSQL over TLS with a self-signed certificate. The ssl object is passed directly to the TLSSocket constructor. This example shows both a Client and a Pool connecting with the same configuration.

```javascript
const config = {
  database: 'database-name',
  host: 'host-or-ip',
  // this object will be passed to the TLSSocket constructor
  ssl: {
    ca: fs.readFileSync('/path/to/server-certificates/root.crt'),
    key: fs.readFileSync('/path/to/client-key/postgresql.key'),
    cert: fs.readFileSync('/path/to/client-certificates/postgresql.crt'),
  },
}
 
import { Client, Pool } from 'pg'
 
const client = new Client(config)
await client.connect()
console.log('connected')
await client.end()
 
const pool = new Pool(config)
const pooledClient = await pool.connect()
console.log('connected')
pooledClient.release()
await pool.end()
```

--------------------------------

### Dynamic password callback with AWS RDS

Source: https://node-postgres.com/features/connecting

Uses a dynamic password callback for short-lived authentication tokens, as supported by many cloud providers. The callback must resolve to a string; here it uses AWS RDS Signer to get an auth token.

```javascript
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(signerOptions)
 
const getPassword = () => signer.getAuthToken()
 
const pool = new Pool({
  user: signerOptions.username,
  password: getPassword,
  host: signerOptions.hostname,
  port: signerOptions.port,
  database: 'my-db',
})
```

--------------------------------

### Create and read from a Cursor

Source: https://node-postgres.com/apis/cursor

Creates a cursor by passing a Cursor instance to client.query, reads 100 rows, and logs the row count. The cursor is a Submittable and must be passed directly to client.query.

```javascript
import { Pool } from 'pg'
import Cursor from 'pg-cursor'
 
const pool = new Pool()
const client = await pool.connect()
const text = 'SELECT * FROM my_large_table WHERE something > $1'
const values = [10]
 
const cursor = client.query(new Cursor(text, values))
 
const { rows } = await cursor.read(100)
console.log(rows.length) // 100 (unless the table has fewer than 100 rows)
client.release()
```

--------------------------------

### pg.escapeIdentifier: Escape a SQL identifier

Source: https://node-postgres.com/apis/utilities

Escapes a string as a SQL identifier. Note that identifiers escaped with this function are case-sensitive; you must always refer to the created table with double quotes, e.g., SELECT * from "MyCaseSensitiveTable", otherwise you will get a 'Non-existent table' error.

```javascript
import { escapeIdentifier } from 'pg';
const escapedIdentifier = escapeIdentifier('FooIdentifier')
console.log(escapedIdentifier) // '"FooIdentifier"'
```

--------------------------------

### Connect using a connection URI

Source: https://node-postgres.com/features/connecting

Initializes a Pool and Client with a connection string URI. Common in environments like Heroku where the connection string is supplied via an environment variable. Parsing is done by pg-connection-string.

```javascript
import pg from 'pg'
const { Pool, Client } = pg
const connectionString = 'postgresql://dbuser:secretpassword@database.server.com: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()
```

--------------------------------

### Project structure for Express with async/await

Source: https://node-postgres.com/guides/async-express

Recommended project structure for an Express application using node-postgres with async/await. The db/index.js file is designated for data access code.

```text
- app.js
- index.js
- routes/
  - index.js
  - photos.js
  - user.js
- db/
  - index.js <--- this is where I put data access code
```

--------------------------------

### Migrate from pg.connect/pg.end to pg.Pool

Source: https://node-postgres.com/guides/upgrading

Shows the deprecated global singleton usage (pg.connect/pg.end) and the replacement user-managed pool (new pg.Pool(), pool.connect, pool.end). The old API is removed in pg@7.0; migrate to the new pool-based approach.

```javascript
// old way, deprecated in 6.3.0:
 
// connection using global singleton
pg.connect(function (err, client, done) {
  client.query(/* etc, etc */)
  done()
})
 
// singleton pool shutdown
pg.end()
 
// ------------------
 
// new way, available since 6.0.0:
 
// create a pool
const pool = new pg.Pool()
 
// connection using created pool
pool.connect(function (err, client, done) {
  client.query(/* etc, etc */)
  done()
})
 
// pool shutdown
pool.end()
```

--------------------------------

### client.on('notification')

Source: https://node-postgres.com/apis/client

Registers a listener for the 'notification' event to handle PostgreSQL LISTEN/NOTIFY messages.

```APIDOC
## client.on('notification', (msg: Notification) => void) => void

### Description
Registers a listener for the 'notification' event, used for PostgreSQL's LISTEN/NOTIFY functionality. The event is emitted when a notification is received on a channel the client is listening to.

### Event
`notification`

### Listener Signature
- **msg** (Notification) - The notification message object.

### Notification Object
- **processId** (number) - The process ID of the notifying backend.
- **channel** (string) - The channel name on which the notification was sent.
- **payload** (string, optional) - The payload sent with the notification.

### Example
```javascript
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!'`)
```
```

--------------------------------

### client.query(config)

Source: https://node-postgres.com/apis/client

Executes a query using a QueryConfig object. If a `name` is provided, a prepared statement is created.

```APIDOC
## client.query(config)

### Description
Executes a query using a QueryConfig object. If a `name` is provided, a prepared statement is created.

### Method
query

### Endpoint
client.query(config: QueryConfig) => Promise<Result>

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **text** (string) - Required - The raw query text.
- **values** (array) - Optional - An array of query parameters.
- **name** (string) - Optional - Name of the query for prepared statements.
- **rowMode** (string) - Optional - Set to 'array' to receive rows as arrays.
- **types** (Types) - Optional - Custom type parsers for this query result.
- **queryMode** (string) - Optional - TODO: document.

### Request Example
```javascript
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()
```

### Response
#### Success Response
- **result** (Result) - The query result object.

```

--------------------------------

### app.js - bootstrap Express and mount routes

Source: https://node-postgres.com/guides/async-express

app.js bootstraps Express and mounts all routes via mountRoutes. The main Express app does not need to know about async handlers because express-promise-router handles them.

```javascript
// ./app.js
import express from 'express'
import mountRoutes from './routes.js'
 
const app = express()
mountRoutes(app)
 
// ... more express setup stuff can follow
```

--------------------------------

### result.command: string

Source: https://node-postgres.com/apis/result

The command type last executed: INSERT, UPDATE, CREATE, SELECT etc.

```APIDOC
## result.command: string

### Description
The command type last executed: `INSERT` `UPDATE` `CREATE` `SELECT` etc.

### Type
string
```

--------------------------------

### pool.connect

Source: https://node-postgres.com/apis/pool

Acquires a client from the pool. If there are idle clients, one is returned; if the pool is not full, a new client is created; if the pool is full, the request waits in a FIFO queue until a client becomes available.

```APIDOC
## pool.connect

### Description
Acquires a client from the pool. If there are idle clients, one is returned; if the pool is not full, a new client is created; if the pool is full, the request waits in a FIFO queue until a client becomes available.

### Method
connect

### Signature
`pool.connect() => Promise<pg.Client>`

### Request Example
```javascript
import { Pool } from 'pg'

const pool = new Pool()

const client = await pool.connect()
await client.query('SELECT NOW()')
client.release()
```

### Response
Returns a Promise that resolves to a `pg.Client` instance. The client must be released when finished.

### Notes
- If there are idle clients in the pool, one will be returned on `process.nextTick`.
- If the pool is not full but all current clients are checked out, a new client will be created and returned.
- If the pool is full and all clients are checked out, requests wait in a FIFO queue until a client becomes available.
```

--------------------------------

### Transaction using pg Pool client

Source: https://node-postgres.com/features/transactions

Acquires a client from the pool, begins a transaction, inserts a user and a photo, commits, and rolls back on error before releasing the client. Use the same client for all statements to avoid isolation issues.

```javascript
import { Pool } from 'pg'
const pool = new Pool()
 
const client = await pool.connect()
 
try {
  await client.query('BEGIN')
  const queryText = 'INSERT INTO users(name) VALUES($1) RETURNING id'
  const res = await client.query(queryText, ['brianc'])
 
  const insertPhotoText = 'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)'
  const insertPhotoValues = [res.rows[0].id, 's3.bucket.foo']
  await client.query(insertPhotoText, insertPhotoValues)
  await client.query('COMMIT')
} catch (e) {
  await client.query('ROLLBACK')
  throw e
} finally {
  client.release()
}
```
Tokens:
Raw