Connecting Node.js and MySQL on Google Cloud Platform Cloud SQL

October 31, 2020

Disclaimer: This original version of this article was written more than one year ago - the information contained in this article is not necessarily up to date.

This article is a translation of an article I wrote in Japanese. For the original version, see here.

Introduction

Using Google Cloud's generous free credits and relatively easy-to-use interface, I deployed a Node.js application and attempted to connect to it a MySQL database.

I will write down the details in this article as Node.js was not available in the documentation.

Important note: now the documentation has been updated to include steps for Node.js - please do not forget to refer to the documentation also.

I used Google App Engine to host the Node.js application and Google Cloud SQL to manage the MySQL database.

Fees

Although Google App Engine is mostly free for small instances, I was billed for using Cloud SQL.

See the following links for more information.

About free trial：https://cloud.google.com/free/

About Cloud SQL billing：https://cloud.google.com/sql/pricing

Instance connection name

It is necessary to set the instance connection name in order to connect to Cloud SQL. The instance connection name is formatted as follows: project_id:region:instance_id.

The instance connection name is displayed under instance details, inside the box "connect to this instance". Copy and paste it as an environment variable.

INSTANCE_CONNECTION_NAME="foo:asia-northeast1:bar"

MYSQL-related environment variables

Also do not forget to set appropriate variables needed to connect to the database.

# ...
DB_USER="foobar"
DB_PASSWORD="barbaz"
DB_DATABASE="foobaz"

Node.js code

There are many client libraries for connecting Node.js to MySQL. I used the one called mysql.

Because it is not possible to connect to Cloud SQL from App Engine with TCP, we have to use Unix Domain Socket.

I used a package called dotenv to load the environment variables.

require('dotenv').config();

const mysql = require('mysql');

const connection = mysql.createConnection({
  socketPath: `/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}`,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE
});

With App Engine Standard, the socket named /cloudsql/<instance connection name> is automatically provided - there is no need for further action.

Using Pools

Pools for performance can be created similarly.

const pool = mysql.createPool({
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  socketPath: `/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}`
});

Limit on the number of connections

It is also possible to set an upper limit on the number of connections that can be made simultaneously.

const pool = mysql.createPool({
  // ...
  connectionLimit: 5
});

Using TCP for local database connection

If you want to connect to a local database instance using TCP.

const pool = process.env.NODE_ENV === 'production'
  ? mysql.createPool({
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    socketPath: `/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}`
  }) : mysql.createPool({
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    host: 'localhost'
  });

Completion

The connection should be complete after running gcloud app deploy.

If the App Engine service and Cloud SQL instances are created under different projects, it is necessary to change some IAM settings.

Add either Cloud SQL Admin, Cloud SQL Editor, or Cloud SQL Client to the App Engine's service account.