Can't connect to Cloud SQL using node-postgres

Question

I've been trying to connect to my Cloud SQL instance using the pg module but haven't been successful so far.

I've looked around a lot online but couldn't understand much on the topic. I also would like to deploy my Express app on Cloud Run at some point and have it connect to my Cloud SQL instance but I don't know how to go about doing that.

Here's a list of things I don't understand and would like a brief explanation on:

1. What are Unix socket connections and why should I use them over normal connections?
2. What is a Cloud SQL Proxy? Do I need to use it? If so, why?
3. Would I need to do any extra work to connect to my Cloud SQL instance from Cloud Run?

---

Here are all the connection objects and connection strings I have tried with the pg.Client object:

1. First connection string: postgresql+psycopg2://postgres:password@/cloudsql/myapp:us-central1:mydb?host=/var/lib/postgresql
2. Second connection string: postgresql://postgres:password@hostip:5432/myapp:us-central1:mydb
3. Third connection string: postgresql://postgres:password@hostip:5432/sarcdb
4. Connection object: { host: "/cloudsql/myapp:us-central1:mydb", username: "postgres", password: "password", database: "mydb" }

All of these give me a Connection terminated unexpectedly error.

Answer 1

The Cloud Functions documentation for Node.js & Cloud SQL (scroll down to PostgreSQL) has applicable information on structuring the connection string and the additional configuration needed for credentials.

Once that's in place for your app, you'll need to add the Cloud SQL instance to your Cloud Run service before it will be able to use that connection string to reach the database.

Here's directly copying the code sample from the docs, with Cloud Run the max configuration of 1 might not keep pace with other concurrency settings.

const pg = require('pg');

/**
 * TODO(developer): specify SQL connection details
 */
const connectionName =
  process.env.INSTANCE_CONNECTION_NAME || '<YOUR INSTANCE CONNECTION NAME>';
const dbUser = process.env.SQL_USER || '<YOUR DB USER>';
const dbPassword = process.env.SQL_PASSWORD || '<YOUR DB PASSWORD>';
const dbName = process.env.SQL_NAME || '<YOUR DB NAME>';

const pgConfig = {
  max: 1,
  user: dbUser,
  password: dbPassword,
  database: dbName,
};

if (process.env.NODE_ENV === 'production') {
  pgConfig.host = `/cloudsql/${connectionName}`;
}

// Connection pools reuse connections between invocations,
// and handle dropped or expired connections automatically.
let pgPool;

exports.postgresDemo = (req, res) => {
  // Initialize the pool lazily, in case SQL access isn't needed for this
  // GCF instance. Doing so minimizes the number of active SQL connections,
  // which helps keep your GCF instances under SQL connection limits.
  if (!pgPool) {
    pgPool = new pg.Pool(pgConfig);
  }

  pgPool.query('SELECT NOW() as now', (err, results) => {
    if (err) {
      console.error(err);
      res.status(500).send(err);
    } else {
      res.send(JSON.stringify(results));
    }
  });

  // Close any SQL resources that were declared inside this function.
  // Keep any declared in global scope (e.g. mysqlPool) for later reuse.
};