Node-postgres: Documentation of UNIX sockets

[chrisjensen]

I had trouble getting UNIX sockets going, and ended up looking through source. I'd like to suggest some documentation to help others.

Below is suggested documentation. I also posted an answer on StackOverflow with some caveats that might be helpful. (it's specific to sequelize, but some of the caveats apply to node-postgres)

Connecting with UNIX sockets
You can connect over a unix socket by:

1. Specifying the socket directory as the host
2. Specifying an absolute path using the socket: protocol

Examples

Specified as Host

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

When specifying a socket as a host, you must provide the absolute path, ie the path must start with /. Paths that do not start with a slash are assumed to be host names.
To use a relative path, you could do something like:

const path = require('path');
const dbConfig = {
  host: path.join(__dirname, './socket_dir'),
  ...
}
const client = new Client(dbConfig);

But be aware of these caveats with __dirname and other approaches to finding the application path, and also that longer paths may result in ENOENT.

Socket Protocol

connectionString = "socket:/path/to/socket_dir?db=database_name";

const client = new Client(connectionString);
// or
const client = new Client({ connectionString });

Note that using this approach you cannot specify a username or password.

[chrisjensen]

You might also want to add an FAQ

I get ENOENT when trying to connect to a socket but the socket exists
If you get the following error:

connect ENOENT /Users/defaultuser/Sites/myproject/cloudsql/myproject-12345:us-central1:my_sql_database/.s.PGSQL.5432

when connecting to a unix socket, but ls <path> shows that the socket does exist, you may need to try a shorter socket path. Paths as short as 111 characters has been known to cause this error.

[DragosRotaru]

Paths as short as 111 characters has been known to cause this error.

Im getting this tattoed on my ass.

@chrisjensen No words can describe the level of appreciation I have for you. Thank you!

The fact that I had to spent all of Saturday trying to figure this out indicates a monumental failure in documentation and error handling on so many levels of abstraction.

Computers were a mistake.

[mkeedlinger]

Hey all, I had the same problem and this issue helped me solve it, so I created a PR with a fix.

Hopefully this helps others!

[willieseabrook]

You might also want to add an FAQ

I get ENOENT when trying to connect to a socket but the socket exists
If you get the following error:

connect ENOENT /Users/defaultuser/Sites/myproject/cloudsql/myproject-12345:us-central1:my_sql_database/.s.PGSQL.5432

when connecting to a unix socket, but ls <path> shows that the socket does exist, you may need to try a shorter socket path. Paths as short as 111 characters has been known to cause this error.

@chrisjensen You sir, are a legend. When deploying to a cloudrun container I kept getting a message similar to:

Cloud SQL instance named .s.PGSQL.5432 is not specified in the list

I deployed a cloudsql instance with a shorter name and thus shorter path and it worked.