Connecting your Node JS application (local) with Google Cloud MySQL

How the connection works for Node JS application running in your local system to communicate with MySQL instance running in Google Cloud?

To keep the cloud MySQL instance secure, any connections to that instance using a public IP must be authorized using either the Cloud SQL Auth proxy or authorized networks. We will see how to make the application work through Cloud SQL Auth proxy.

Connection set up through Cloud SQL Auth proxy

The above picture depicts the connection flow between the local client machine and Cloud SQL instance. The application i makes it request through Cloud SQL Auth Proxy client running in the local system and it in-turn communicates with the Google Cloud SQL instance through TCP secure tunnel through Cloud SQL Auth Proxy server.

Please reproduce the following steps to complete the set up,

Setting up your local machine for Node JS development

Step 1: Install nvm in your local machine

Step 2: Install node.js and npm

nvm install stable // install the latest version of Node.js

npm is normally installed alongside Node.js

Creating Cloud SQL Instance

Once you activated your google cloud account with a project selected, follow the below steps,

Step 1:, Create Cloud SQL Instance

Give the specifications you wish to give for your SQL instance and choose “Create Instance”

Step 2: Get the instance connection name to use it in the application for connecting with SQL instance

In the overview page of the SQL instance, you can find the Instance Connection Name. Copy it.

Note down the instance connection name, database user, and database password that you create.

Create a database in the above created MySQL instance based on your requirements.

Database name: medium_demo

In the overview page of the MySQL instance, click databases and create one by giving the name for the database.

Create a service account with “Cloud SQL Client” permission

A service account is a special kind of account used by an application, rather than a person. Applications use service accounts to make authorized API calls. In our case, it helps to authenticate our connection to the google cloud SQL Instance through Cloud SQL Auth Proxy.

Step 1: Choose “Create Service Account” under IAM & Admin section.

Step 2: Make sure you give one of the following roles for the service account by choosing under “Select a Role”

• Cloud SQL > Cloud SQL Client
• Cloud SQL > Cloud SQL Editor
• Cloud SQL > Cloud SQL Admin

Step 3: Download the private key for the service account created, by clicking actions on the service account created above and click “create new key”. The private key will get downloaded to your system by choosing “Create” with type as “Json”.

Create a table in the database created

Step 1: In the overview page, click the “open cloud shell” option to connect to the SQL instance using gcloud. Using the shell, you can perform SQL operations on the database created.

Give the following gcloud sql command to establish the connection

gcloud sql connect myinstance — user=root — project=medium-354300

A table “users” is created with USER_ID, firstName, lastName columns.

Install CloudSQL Auth Proxy Client

Install CloudSQL Auth Proxy Client in your local system (based on your operating system and 32-bit or 64-bit kernel).

Export the information to the console (your local system where you will be running CloudSQL Auth Proxy Client)

export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json

export INSTANCE_CONNECTION_NAME=’<MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>’

export DB_USER=’my-db-user’

export DB_PASS=’my-db-pass’

export DB_NAME=’my_db’

Run cloud_sql_proxy locally with a TCP connection

Launching cloud_sql_proxy locally

./cloud_sql_proxy -instances=<project-id>:<region>:<instance-name>=tcp:3306 -credential_file=$GOOGLE_APPLICATION_CREDENTIALS // It runs cloud_sql_proxy.
The command I used:
./cloud_sql_proxy -instances=medium-354300:us-west2:myinstance=tcp:3306 -credential_file=$GOOGLE_APPLICATION_CREDENTIALS

Test the application

Navigate to the directory where the files for your Node JS application present and run the commands.

npm install
npm start

Once the application starts running, you can test if the connection is successful by posting requests. It will connect to the remote MySQL instance in google cloud through cloud_sql_proxy and retrieve the results based on the requirement.

The files used for developing the Node JS Web application:

check_deployement.js :

‘use strict’;
const express = require(‘express’);
const mysql = require(‘promise-mysql’);
const bodyParser = require(‘body-parser’);
const app = express();
app.set(‘view engine’, ‘pug’);
app.enable(‘trust proxy’);
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(bodyParser.raw());
app.use((req, res, next) => {
res.set(‘Content-Type’, ‘text/html’);
next();
});
// Create a Winston logger that streams to Stackdriver Logging.
const winston = require(‘winston’);
const {LoggingWinston} = require(‘@google-cloud/logging-winston’);
const loggingWinston = new LoggingWinston();
const logger = winston.createLogger({
level: ‘info’,
transports: [new winston.transports.Console(), loggingWinston],
});
const createTcpPool = async config => {
// Extract host and port from socket address
console.log(“Inside TCP”);
// Establish a connection to the database
return await mysql.createPool({
user: ‘root’, // e.g. ‘my-db-user’
password: ‘root’, // e.g. ‘my-db-password’
database: ‘medium_demo’,
host: ‘127.0.0.1’, //dbSocketAddr[0], // e.g. ‘127.0.0.1’
port: ‘3306’, //dbSocketAddr[1], // e.g. ‘3306’
// … Specify additional properties here.
…config,
});
console.log(“End of TCP”);
};
const createUnixSocketPool = async config => {
console.log(“Inside Socket”)
return await mysql.createPool({
user: ‘root’, // e.g. ‘my-db-user’
password: ‘root’, // e.g. ‘my-db-password’
database: ‘medium_demo’, // e.g. ‘my-database’
// If connecting via unix domain socket, specify the path
socketPath: ‘/cloudsql/medium-354300:us-west2:myinstance’,
// Specify additional properties here.
…config,
});
};
// [END cloud_sql_mysql_mysql_create_socket]
const createPool = async () => {
const config = {
connectionLimit: 5,
connectTimeout: 10000, // 10 seconds
acquireTimeout: 10000, // 10 seconds
waitForConnections: true, // Default: true
queueLimit: 0, // Default: 0
};
return await createTcpPool(config);
};
const ensureSchema = async pool => {
// Wait for tables to be created (if they don’t already exist).
console.log(“Ensured that table ‘users’ exists”);
};
const createPoolAndEnsureSchema = async () =>
await createPool()
.then(async pool => {
await ensureSchema(pool);
return pool;
})
.catch(err => {
throw err;
});
let pool;
app.use(async (req, res, next) => {
if (pool) {
return next();
}
try {
pool = await createPoolAndEnsureSchema();
console.log(“Inside createPoolAndEnsureSchema”);
next();
} catch (err) {
logger.error(err);
return next(err);
}
});
app.get(‘/users’, async (req, res) => {
try {
const tabsQuery = pool.query(“SELECT * FROM medium_demo.users;”);
console.log(“Inside query”);
let x = await tabsQuery;
console.log(tabsQuery);
res.json(x);
} catch (err) {
console.error(err);
res
.status(500)
.send(
‘Unable to load page. Please check the application logs for more details.’
)
.end();
}
});
app.get(‘/check’, (req, res) => {
res.send(‘Hello World!’)
});
const PORT = process.env.PORT || 8080;
const server = app.listen(PORT, () => {
console.log(`App listening on port ${PORT}`);
console.log(‘Press Ctrl+C to quit.’);
});
process.on(‘uncaughtException’, function (err) {
console.log(err);
throw err;
});
module.exports = server;

package.json :

{
“name”: “cloudsql-mysql-mysql”,
“description”: “Node.js Cloud SQL MySQL Connectivity Sample”,
“private”: true,
“license”: “Apache-2.0”,
“author”: “Google Inc.”,
“repository”: {
“type”: “git”,
“url”: “https://github.com/GoogleCloudPlatform/nodejs-docs-samples.git"
},
“engines”: {
“node”: “>=10.0.0”
},
“scripts”: {
“start”: “node check_deployement.js”,
“deploy”: “gcloud app deploy”,
“lint”: “samples lint”,
“pretest”: “npm run lint”,
“test”: “samples test app”
},
“dependencies”: {
“@google-cloud/logging-winston”: “⁴.0.0”,
“express”: “⁴.17.1”,
“promise-mysql”: “⁵.0.0”,
“prompt”: “¹.0.0”,
“pug”: “³.0.0”,
“winston”: “³.1.0”
},
“devDependencies”: {
“mocha”: “⁸.0.0”,
“proxyquire”: “².1.0”,
“sinon”: “¹⁰.0.0”,
“supertest”: “⁶.0.0”
}
}

Please leave comment if your have any questions. Happy to help :)