Connecting to Cloud SQL

Overview

Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational PostgreSQL and MySQL databases in the cloud. You can connect your Cloud Functions to Cloud SQL instances using a local socket interface provided in the Cloud Functions execution environment. This is a private connection to your Cloud SQL instances, which means you don't need to expose them to any external network.

Setting up Cloud SQL

Cloud Functions supports both First and Second Generation MySQL instances as well as PostgreSQL instances. See the relevant Cloud SQL documentation for instructions on setting up a new Cloud SQL instance.

Cross Region

First Generation MySQL instances must be in the same region as your Cloud Function. Second Generation MySQL instances as well as PostgreSQL instances work with Cloud Functions in any region.

Cross Project

Your Cloud Function has access to all Cloud SQL instances in your project. You can access Second Generation MySQL instances as well as PostgreSQL instances in other projects if your Cloud Function's service account (listed on the Cloud Function's General tab in the GCP Console) is added as a member in IAM on the project with the Cloud SQL instance(s) with the Cloud SQL Client role.

Connecting to Cloud SQL

Cloud Functions provides a local socket interface to connect to Cloud SQL instances, which works for both MySQL and PostgreSQL instances. To connect to your Cloud SQL instance you can use the /cloudsql file system path.

The following example uses the mysql npm module:

MySQL

Node.js

const mysql = require('mysql');

const connectionName = '<YOUR INSTANCE CONNECTION NAME>';
const dbUser = '<YOUR DB USER>';
const dbPass = '<YOUR DB PASSWORD>';
const dbName = '<YOUR DB NAME>';

const pool = mysql.createPool({
    connectionLimit : 1,
    socketPath: '/cloudsql/' + connectionName,
    user: dbUser,
    password: dbPass,
    database: dbName
});

exports.cloudSQLTest = (event, callback) => {
    pool.query('SELECT NOW() AS now', (error, results, fields) => {
        callback(error, results);
    });
};

The following example uses the pg npm module:

PostgreSQL

Node.js

const pg = require('pg');

const connectionName = '<YOUR INSTANCE CONNECTION NAME>';
const dbUser = '<YOUR DB USER>';
const dbPass = '<YOUR DB PASSWORD>';
const dbName = '<YOUR DB NAME>';

const pool = new pg.Pool({
    max: 1,
    host: '/cloudsql/' + connectionName,
    user: dbUser,
    password: dbPass,
    database: dbName
});

exports.cloudSQLTest = function cloudSQLTest(event, callback) {
    pool.query('SELECT NOW() as now', (error, results) => {
      callback(error, results);
    });
};

Note that PostgreSQL requires a .s.PGSQL.5432 suffix on the connection string. Libraries like pg add this automatically. However, if you choose an alternative library, make sure to confirm that this has been added. The full connection string for PostgreSQL when using socket paths is:

/cloudsql/<INSTANCE_CONNECTION_NAME>/.s.PGSQL.5432

The INSTANCE_CONNECTION_NAME is the name of your Cloud SQL instance connection, and will usually be in the form:

First Generation

<ProjectID>:<InstanceID>

Second Generation

<ProjectID>:<Region>:<InstanceID>

For example:

my-gcp-project:us-central1:helloworld

You can determine the instance connection name from the Cloud SQL Dashboard in GCP Console. Note that the "Instance connection name" (used above) is not the same as your "Instance ID."

Best Practices for Cloud SQL in Cloud Functions

This section provides guidelines for connecting to Cloud SQL in Cloud Functions.

Connection Pools & Connection Loss

Connections to underlying databases may be dropped, either by the database server itself, or by the infrastructure underlying Cloud Functions. To mitigate this, we recommend that you use a client library that supports connection pools that automatically reconnect broken client connections.

When using a connection pool, it is important to set the maximum connections to 1. This may seem counter-intuitive, however, creating more than one concurrent connection per function instance may cause rapid exhaustion of connection resources (see Maximum Concurrent Connections below for more detail). Cloud Functions limits concurrent executions to 1 per instance. This means you will never have a situation where two requests are being processed by a single function instance at the same time, so in most situations only a single database connection is needed.

Connection Reuse

Where possible, we recommend that you allocate connection objects in global scope. This is both a performance optimization, and a less error-prone approach as it is often the case that connection closure is missed (for example, when unexpected errors occur).

If you use a connection pool in global scope, then we recommend that you not close connections at the end of the function call. If you do not use a pool in global scope, and/or if you create individual connections in function-scope, then you should close these connections before the function returns.

Using a globally scoped connection pool is recommended as this will improve the likelihood that the same connection is reused for subsequent invocations of the function, and the connection will be closed naturally when the function instance is evicted (auto-scaled down).

Maximum Concurrent Connections

Both the MySQL and PostgreSQL editions of Cloud SQL impose a maximum limit on concurrent connections, and these limits may vary depending on the database engine chosen (see the Cloud SQL Quotas and Limits page). If each one of your function instances is creating a connection, your overall throughput will be limited by the number of concurrent connections that can be created. Where possible, you should only open a connection if the function needs to use it. A common anti-pattern here is to deploy a function that retains a reference to the connection in global scope (often considered best practice), but which subsequently doesn't use the connection at all (anti-pattern).

For example, if you have only one function:

Node.js

const mysql = require('mysql');

const connectionName = '<YOUR CONNECTION NAME>';
const dbUser = '<YOUR DB USER>';
const dbPass = '<YOUR DB PASSWORD>';
const dbName = '<YOUR DB NAME>';

// In this case it's fine to allocate the pool in global scope because it's
// always used.
const pool = mysql.createPool({
    connectionLimit : 1,
    socketPath: '/cloudsql/' + connectionName,
    user: dbUser,
    password: dbPass,
    database: dbName
});

module.exports = {
    foo: function foo(event, callback) {
      pool.query('SELECT 1', (error, results) => {
        callback(error, results);
      });
    }
}

If you have multiple functions in one module:

Node.js

const mysql = require('mysql');

const connectionName = '<YOUR CONNECTION NAME>';
const dbUser = '<YOUR DB USER>';
const dbPass = '<YOUR DB PASSWORD>';
const dbName = '<YOUR DB NAME>';

// Allocating the pool here in global scope would be sub-optimal as it is
// not used at all in the "bar" function

// const pool  = mysql.createPool({
//  connectionLimit : 1,
//  socketPath: '/cloudsql/' + connectionName,
//  user: dbUser,
//  password: dbPass,
//  database: dbName
//});

let pool;

module.exports = {
    foo: function foo(event, callback) {
      // We can declare the pool here which means it's not created
      // unnecessarily for the "bar" function, but the preferred solution is
      // to group all functions which require a connection into a common
      // module and declare the pool in global scope.
      if(!pool) {
          pool = mysql.createPool({
            connectionLimit : 1,
            socketPath: '/cloudsql/' + connectionName,
            user: dbUser,
            password: dbPass,
            database: dbName
          });
      }

      pool.query('SELECT 1', (error, results) => {
        callback(error, results);
      });
    },

    bar: function bar(event, callback) {
      // This function doesn't use the connection!!
      console.log('Not doing much here');
      callback();
    }
}

The bar function doesn't use the connection at all, so deploying the bar function as-is would cause a connection to be "wasted" per bar instance.

Was this page helpful? Let us know how we did:

Send feedback about...

Cloud Functions Documentation