Connecting to Google Cloud SQL from a Cloud Function

For most Node JS developers trying to perform CRUD operations on a SQL or Postgres database, we connect via the database’s IP or a connection string. It might look something like this:

let cn = {
  host: process.env.DATABASE_IP,
  port: 5432,
  database: 'users,
  user: process.env.PG_USER,
  password: process.env.PG_PASSWORD,
}

As I learned recently when I deployed a cloud function that did an UPSERT into a cloud SQL database, Google Cloud Functions cannot connect to a SQL database via the IP address or the connection string.

The work around is to use socket paths!

Cloud SQL requires the use of connection sockets for cloud functions, so the host name must use the /cloudsql/INSTANCE-NAME:DATABASE format. When you deploy your function to GCP, your function will be able to connect to the Cloud SQL database.

There is one catch though, using socket paths will not work when trying to develop locally. You still need to connect with the IP address. So for local development, connect with a connection string that looks like this
let cn = 'postgres://' + process.env.PG_USER + ':' + process.env.PG_PASSWORD + '@' + process.env.DATABASE_IP
+ ':5432/users';

Here is the solution I deployed that got my Cloud Function to connect to my Cloud SQL database (I used pg-promise for my postgres queries)
let cn = {
  user: process.env.PG_USER,
  password: process.env.PG_PASSWORD,
  database: 'vendors',
  host: '/cloudsql/${project}:${region}:${instance}
  logging: false,
  dialect: 'postgres',
  ssl: false,
  dialectOptions: {
    ssl: false
  },
  operatorsAliases: false
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s