Connection strings

Graphile Worker accepts a "connection string" to indicate how to connect to the database. Connection strings have a number of formats depending on the protocol prefix used; the most common format is:

postgres://user:password@host:port/dbname

If you have more specific requirements, then read on for more detail, otherwise skip ahead to Task executors.

`postgres://` protocol

Examples

postgres:///dbname?...
postgres:///dbname?host=/path/to/socket&...
postgres://user:password@host:port/dbname?...

info

pg:// and postgresql:// protocols are generally equivalent to postgres:// protocol for the purposes of Graphile Worker, but we've found that postgres:// connection strings are the most compatible between all the various tools in the PostgreSQL ecosystem so that is the form we'll discuss.

Overview

The postgres:// protocol can be used for all connection types (TCP, domain socket) and the URL takes the form:

postgres://[user[:password]@][host[:port]]/[dbname][?...]

where square brackets ([]) indicate optional parameters.

TCP socket

To connect to a TCP socket, the host and port (if different from the default 5432) should be specified. Note that if you use localhost or localhost:port then it's still possible that a domain socket will be used, to prevent this behavior use 127.0.0.1 as the host instead.

TCP + SSL

If your TCP connection is encrypted via SSL then you should add ?ssl=true to your connection string. There are other SSL-related options (for example ?sslrootcert=/path/to/cert.pem, see Amazon RDS example below), but these change based on the version of pg/pg-connection-string in use - please see those projects for details.

Domain socket

If you want to connect to a Unix domain socket rather than a TCP socket then you should omit the host/port from the connection string and instead indicate it via a query parameter (e.g. postgres:///dbname?host=/path/to/socket or postgres://user:pass@/dbname?host=/path/to/socket).

Example: Amazon RDS

Amazon RDS uses TCP connections, which you can secure by forcing SSL (please see your RDS configuration). You can indicate the RDS database to use and how to authenticate with a CLI that looks a little like this:

graphile-worker -c \
  "postgres://user:[email protected]:5432/dbname?ssl=true&sslrootcert=/path/to/amazon-rds-ca-cert.pem"

For this to work the SSL of the server needs to already be trusted by your system or you need to specify the CA certificate so that trust can be established. Establishing trust can be done by explicitly specifying the SSL root CA certificate via the sslrootcert= query parameter shown above.

Click to view amazon-rds-ca-cert.pem

Store this into a file amazon-rds-ca-cert.pem (or whatever you want to call it) and specify the full path to this file via the query string.

note

You can also use a relative path, but this is dependent on process.cwd() and thus is more susceptible to errors, so we don't recommend it.

danger

For debugging you can tell Node to accept any SSL certificates (e.g. via NODE_TLS_REJECT_UNAUTHORIZED=0 or ?ssl=no-verify) but doing so is INCREDIBLY DANGEROUS and should not be used in production under any circumstances.

`socket:` protocol

Only used for Unix domain sockets.

Examples

socket:/path/to/socket?db=dbname&...
socket://user:password@/path/to/socket?db=dbname&...

Overview

The socket protocol is only used to connect to Unix domain sockets. You may prefer this simpler syntax, or you may prefer the postgres:///dbname?host=/path/to/socket form instead.

If you need to indicate the username / password to use when connecting over the domain socket then these should be provided via the @ syntax shown above, but no host should be specified (i.e. you should not provide localhost in ~~postgres://user:password@localhost/path/to/socket~~).

Example: Google Cloud SQL

When using Graphile Worker with a PostgreSQL database hosted on Google Cloud SQL, it's recommended to connect through a Unix domain socket for enhanced security and reliability. This method requires the use of the Cloud SQL Proxy, which provides a stable connection to your Cloud SQL instance.

The connection string can be constructed using the socket: protocol, followed by the URI-encoded username and password, the socket path, and the URI-encoded database name as a query parameter (?db=), for example:

/** Your database username, URI-encoded */
const username = encodeURIComponent("your_database_username");
/** Your database password, URI-encoded */
const password = encodeURIComponent("your_database_password");
/** The name of your database, URI-encoded */
const dbname = encodeURIComponent("your_database_name");
/**
 * The connection name of your Google Cloud SQL instance, typically in the
 * format of `project-id:region:instance-id`
 */
const instanceConnectionName = "your_project_id:your_region:your_instance_id"; // As provided by Google Cloud SQL
/** The path to the Unix domain socket created by the Cloud SQL Proxy */
const socketPath = `/cloudsql/${instanceConnectionName}`;

const connectionString = `socket://${username}:${password}@${socketPath}?db=${dbname}`;

In the CLI it might look like:

graphile-worker -c "socket://username:password@/cloudsql/project:region:instance?db=dbname"

Schemaless connection strings

Not recommended.

Examples

dbname
/path/to/socket

Overview

A protocol is not strictly required for a connection string, you could just specifiy the database name directly, or the path to the Unix domain socket to use. However, these shorthand versions should generally be avoided because their interpretation is more ambiguous and may change between pg/pg-connection-string versions.

We recommend that you use the postgres:// format if you are connecting to a TCP socket, and either the postgres:// format or the the socket: format if you're connecting to a Unix domain socket.

If in doubt, use the postgres:// format.

Further information

Under the hood, the connection string is passed to the pg.Pool constructor (which inherits some options, including connectionString from pg.Client) for interpretation. pg may or may not use the pg-connection-string package to perform parsing of the connection string depending on which version of pg is in use.

postgres:// protocol​

Examples​

Overview​

TCP socket​

TCP + SSL​

Domain socket​

Example: Amazon RDS​

socket: protocol​

Examples​

Overview​

Example: Google Cloud SQL​

Schemaless connection strings​

Examples​

Overview​

Further information​

`postgres://` protocol

Examples

Overview

TCP socket

TCP + SSL

Domain socket

Example: Amazon RDS

`socket:` protocol

Examples

Overview

Example: Google Cloud SQL

Schemaless connection strings

Examples

Overview

Further information