Number in javascript is only able to represent 2⁵³-1 safely which means that types in PostgreSQLs like bigint and numeric won’t fit into Number.

Since Node.js v10.4 we can use BigInt to match the PostgreSQL type bigint which is returned for eg. count(*). Unfortunately, it doesn’t work with JSON.stringify out of the box, so Postgres.js will return it as a string.

If you want to use BigInt you can add this custom type:

const sql = postgres({
  types: {
    bigint: postgres.BigInt
  }
})

There is currently no guaranteed way to handle numeric / decimal types in native Javascript. These [and similar] types will be returned as a string. The best way in this case is to use custom types.

Result Array

The Result Array returned from queries is a custom array allowing for easy destructuring or passing on directly to JSON.stringify or general Array usage. It includes the following properties.

.count

The count property is the number of affected rows returned by the database. This is usefull for insert, update and delete operations to know the number of rows since .length will be 0 in these cases if not using RETURNING ....

.command

The command run by the query – eg. one of SELECT, UPDATE, INSERT, DELETE

.columns

The columns returned by the query useful to determine types, or map to the result values when using .values()

{
  name  : String,    // Column name,
  type  : oid,       // PostgreSQL oid column type
  parser: Function   // The function used by Postgres.js for parsing
}

.statement

The statement contains information about the statement implicitly created by Postgres.js.

{
  name    : String,  // The auto generated statement name
  string  : String,  // The actual query string executed
  types   : [oid],   // An array of oid expected as input parameters
  columns : [Column] // Array of columns - same as Result.columns
}

.state

This is the state { pid, secret } of the connection that executed the query.

Connection details

All Postgres options

const sql = postgres('postgres://username:password@host:port/database', {
  host                 : '',            // Postgres ip address[es] or domain name[s]
  port                 : 5432,          // Postgres server port[s]
  path                 : '',            // unix socket path (usually '/tmp')
  database             : '',            // Name of database to connect to
  username             : '',            // Username of database user
  password             : '',            // Password of database user
  ssl                  : false,         // true, prefer, require, tls.connect options
  max                  : 10,            // Max number of connections
  max_lifetime         : null,          // Max lifetime in seconds (more info below)
  idle_timeout         : 0,             // Idle connection timeout in seconds
  connect_timeout      : 30,            // Connect timeout in seconds
  prepare              : true,          // Automatic creation of prepared statements
  types                : [],            // Array of custom types, see more below
  onnotice             : fn,            // Default console.log, set false to silence NOTICE
  onparameter          : fn,            // (key, value) when server param change
  debug                : fn,            // Is called with (connection, query, params, types)
  socket               : fn,            // fn returning custom socket to use
  transform            : {
    undefined          : undefined,     // Transforms undefined values (eg. to null)
    column             : fn,            // Transforms incoming column names
    value              : fn,            // Transforms incoming row values
    row                : fn             // Transforms entire rows
  },
  connection           : {
    application_name   : 'postgres.js', // Default application_name
    ...                                 // Other connection parameters
  },
  target_session_attrs : null,          // Use 'read-write' with multiple hosts to
                                        // ensure only connecting to primary
  fetch_types          : true,          // Automatically fetches types on connect
                                        // on initial connection.
})

Note that max_lifetime = 60 * (30 + Math.random() * 30) by default. This resolves to an interval between 45 and 90 minutes to optimize for the benefits of prepared statements and working nicely with Linux’s OOM killer.

SSL

Although vulnerable to MITM attacks, a common configuration for the ssl option for some cloud providers is to set rejectUnauthorized to false (if NODE_ENV is production):

const sql =
  process.env.NODE_ENV === 'production'
    ? // "Unless you're using a Private or Shield Heroku Postgres database, Heroku Postgres does not currently support verifiable certificates"
      // https://help.heroku.com/3DELT3RK/why-can-t-my-third-party-utility-connect-to-heroku-postgres-with-ssl
      postgres({ ssl: { rejectUnauthorized: false } })
    : postgres()

For more information regarding ssl with postgres, check out the Node.js documentation for tls.

Multi-host connections – High Availability (HA)

Multiple connection strings can be passed to postgres() in the form of postgres('postgres://localhost:5432,localhost:5433', ...). This works the same as native the psql command. Read more at multiple host URIs.

Connections will be attempted in order of the specified hosts/ports. On a successful connection, all retries will be reset. This ensures that hosts can come up and down seamlessly.

If you specify target_session_attrs: 'primary' or PGTARGETSESSIONATTRS=primary Postgres.js will only connect to the primary host, allowing for zero downtime failovers.

The Connection Pool

Connections are created lazily once a query is created. This means that simply doing const sql = postgres(...) won’t have any effect other than instantiating a new sql instance.

No connection will be made until a query is made.

For example:

const sql = postgres() // no connections are opened

await sql`...` // one connection is now opened
await sql`...` // previous opened connection is reused

// two connections are opened now
await Promise.all([
  sql`...`,
  sql`...`
])

When there are high amount of concurrent queries, postgres will open as many connections as needed up until max number of connections is reached. By default max is 10. This can be changed by setting max in the postgres() call. Example – postgres('connectionURL', { max: 20 }).

This means that we get a much simpler story for error handling and reconnections. Queries will be sent over the wire immediately on the next available connection in the pool. Connections are automatically taken out of the pool if you start a transaction using sql.begin(), and automatically returned to the pool once your transaction is done.

Any query which was already sent over the wire will be rejected if the connection is lost. It’ll automatically defer to the error handling you have for that query, and since connections are lazy it’ll automatically try to reconnect the next time a query is made. The benefit of this is no weird generic “onerror” handler that tries to get things back to normal, and also simpler application code since you don’t have to handle errors out of context.

There are no guarantees about queries executing in order unless using a transaction with sql.begin() or setting max: 1. Of course doing a series of queries, one awaiting the other will work as expected, but that’s just due to the nature of js async/promise handling, so it’s not necessary for this library to be concerned with ordering.

Since this library automatically creates prepared statements, it also has a default max lifetime for connections to prevent memory bloat on the database itself. This is a random interval for each connection between 45 and 90 minutes. This allows multiple connections to independently come up and down without affecting the service.

Connection timeout

By default, connections will not close until .end() is called. However, it may be useful to have them close automatically when:

• re-instantiating multiple sql`` instances
• using Postgres.js in a Serverless environment (Lambda, etc.)
• using Postgres.js with a database service that automatically closes connections after some time (see ECONNRESET issue)

This can be done using the idle_timeout or max_lifetime options. These configuration options specify the number of seconds to wait before automatically closing an idle connection and the maximum time a connection can exist, respectively.

For example, to close a connection that has either been idle for 20 seconds or existed for more than 30 minutes:

const sql = postgres({
  idle_timeout: 20,
  max_lifetime: 60 * 30
})

Cloudflare Workers support

Postgres.js has built-in support for the TCP socket API in Cloudflare Workers, which is on-track to be standardized and adopted in Node.js and other JavaScript runtimes, such as Deno.

You can use Postgres.js directly in a Worker, or to benefit from connection pooling and query caching, via the Hyperdrive service available to Workers by passing the Hyperdrive connectionString when creating a new postgres client as follows:

// Requires Postgres.js 3.4.0 or later
import postgres from 'postgres'

interface Env {
    HYPERDRIVE: Hyperdrive;
}

export default async fetch(req: Request, env: Env, ctx: ExecutionContext) {
    // The Postgres.js library accepts a connection string directly
    const sql = postgres(env.HYPERDRIVE.connectionString)
    const results = await sql`SELECT * FROM users LIMIT 10`
    return Response.json(results)
}

In wrangler.toml you will need to enable node_compat to allow Postgres.js to operate in the Workers environment:

node_compat = true # required for database drivers to function

Auto fetching of array types

Postgres.js will automatically fetch table/array-type information when it first connects to a database.

If you have revoked access to pg_catalog this feature will no longer work and will need to be disabled.

You can disable this feature by setting fetch_types to false.

Environmental variables

It is also possible to connect to the database without a connection string or any options. Postgres.js will fall back to the common environment variables used by psql as in the table below:

Prepared statements

Prepared statements will automatically be created for any queries where it can be inferred that the query is static. This can be disabled by using the prepare: false option. For instance — this is useful when using PGBouncer in transaction mode.

Custom Types

You can add ergonomic support for custom types, or simply use sql.typed(value, type) inline, where type is the PostgreSQL oid for the type and the correctly serialized string. (oid values for types can be found in the pg_catalog.pg_type table.)

Adding Query helpers is the cleanest approach which can be done like this:

const reserved = await sql.reserve()
await reserved`select * from users`
await reserved.release()