Connecting Node.js app on Google Cloud App Engine

Question

I have a Node app which uses MySQL, connecting via a config json:

{
    "client": "mysql",
    "connection": {
        "host": "something",
        "user": "something",
        "password": "something",
        "database": "daimonion-db",
        "debug": false
    }
}

I've created a Google Cloud Platform SQL instance. I'm seeing an IP address and instance connection name.

I've also deployed the Node app to Google Cloud App Engine in a flexible environment.

How do I connect the Node app to the SQL instance? I'm seeing this explanation: https://cloud.google.com/sql/docs/mysql/connect-app-engine which tells me to add a settings string to my app.yaml to connect with either a Unix domain socket or TCP connection, but how do I connect to these from my Node app?

Answer 1

include beta_settings to app.yaml to enable cloud proxy on the instance in production, and specify the UNIX socket socketPath in config, so your flex app can connect to the instance via proxy.

socketPath should be in config only if the app is running in production on App Engine. For local development, the TCP socket is used with the proxy client, that you need to install and start with the following commands:

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy

chmod +x cloud_sql_proxy

./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306

here's an example of Node app that connects and queries a Cloud MySQL instance using the proxy. The if statement permits the app to switch configuration dev-local/prod-appengine automatically, using environment variables .

app.yaml

runtime: nodejs
env: flex
env_variables:
  SQL_USER: [SQL_USER]
  SQL_PASSWORD: [SQL_PASSWORD]
  SQL_DATABASE: [DATABASE_NAME]
  INSTANCE_CONNECTION_NAME: [INSTANCE_CONNECTION_NAME]
beta_settings:
  cloud_sql_instances: [INSTANCE_CONNECTION_NAME]

package.json

{
  "engines": {
    "node": "8.x.x"
  },
  "dependencies": {
    "express": "4.16.3",
    "mysql": "^2.15.0"
  },
  "scripts": {
    "start": "node server.js"
  }
}

server.js

const express = require('express');
const mysql = require('mysql');

const app = express();

var config = {
    user: process.env.SQL_USER,
    database: process.env.SQL_DATABASE,
    password: process.env.SQL_PASSWORD
}

if (process.env.INSTANCE_CONNECTION_NAME && process.env.NODE_ENV === 'production') {
    config.socketPath = `/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}`;
  }

var connection = mysql.createConnection(config);

connection.connect();

app.get('/', (req, res) => {
    connection.query(
        'SELECT * FROM entries', function(err, result, fields){
            if (err) throw err;
            res.send(result);
        }
    );
});

const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`App listening on port ${PORT}`);
  console.log('Press Ctrl+C to quit.');
});

Answer 2

There are two ways to connect Cloud SQL instance from local desktop:

Example for postgresql

1. Using public IP and psql tool: https://cloud.google.com/sql/docs/postgres/connect-admin-ip

   a. Add your external IP to Cloud SQL - CONNECTIONS - Authorized networks

   b. Modify your connection.host to Cloud SQL instance public IP

2. Using cloud_sql_proxy tool and INSTANCE_CONNECTION_NAME: https://cloud.google.com/sql/docs/postgres/connect-admin-proxy