Add support for options parameter in connection URLs

[thecodeboss]

Add support for options parameter in connection URLs

Desired Behavior

options added to a connection url get passed to the underlying pg library. For example:

postgres://...?options=-c search_path=test

(I'm omitting proper url-encoding to make the example clear!)

Motivation

We use different database schemas for various environments, especially locally. For our main app, we generally use the public schema, but for running tests we use the test schema.

A workaround has been setting the PGOPTIONS=-c search_path=test environment variable when running tests.

However, now we need to connect to a different database that uses another convention for schemas. Specifically, the test schema name looks like default$test instead of test. It's not possible to set the PGOPTIONS environment variable to two different settings, so ideally we could set options in the connection URL for each database that we connect to.

Note that we are able to work around this by specifying a schema within our queries, for example:

SELECT * FROM ${sql.identifier(['myschema', 'mytable'])}

but that syntax is a lot more verbose compared to simply writing

SELECT * FROM mytable

Thus there is no big rush to implement this, would just be nice to have.

Implementation

In the parseDsn utility, we could extract the options like so (maybe with a more specific name than options):

  const {
    application_name: applicationName,
    options,
    sslmode: sslMode,
    ...unsupportedOptions
  } = Object.fromEntries(url.searchParams);

  if (options) {
    connectionOptions.options = options;
  }

The underlying pg library already supports this parameter thanks to this PR from 2020: brianc/node-postgres#2216

Beyond that, some types would need updating and the param needs to be passed around in the correct places, but I don't anticipate it being a difficult thing to support.

[gajus]

It is not something I have a use case for, so not something I will prioritize/work on, but contributions are welcome.

[github-actions]

🎉 This issue has been resolved in version 37.5.0 🎉

The release is available on:

• GitHub release
• npm package (@latest dist-tag)

Your semantic-release bot 📦🚀