Unable to connect to Cloud MySQL from Node JS in App Engine

user_13681860 0
Viewed 429

I have a NodeJS app with MySQL that works locally, but now I have deployed it in APP Engine and I need to connect it with MySQL Google Cloud, I have created the MySQL instance and the database but I can't connect my app (NodeJS in App Engine) with my database in MySQL cloud, I am using the following code:

const mysql = require('mysql');


var con = mysql.createConnection({
  host: "host-IP",
  socketPath: "/cloudsql/project:region:instance",
  user: "USERNAME",
  password: "USERPASSWORD"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});

module.exports = con;

Archivo .YAML---------------------------

runtime: nodejs
env: flex
env_variables:
  SQL_USER: "USERNAME"
  SQL_PASSWORD: "USERPASSWORD"
  SQL_DATABASE: "DBNAME"
  INSTANCE_CONNECTION_NAME: "project:region:instance"
beta_settings:
  cloud_sql_instances: "project:region:instance"

Please help me.

node.js google-cloud-platform google-app-engine mysql
1 Answers

There are two options to connect to a Cloud SQL instance from App Engine Flexible. You can either connect through the Public or the Private IP of the instance.

My suggestion would be to connect through Private IP because connections over private IP provide lower latency and limited attack vectors, as they don't require traversing the internet.

Assuming that you already have an instance and connecting through Public IP here are the steps:

  1. Create a database and a user in your Cloud SQL instance.

  2. Get the instance_connection_name for your instance. It can be found by the instance Overview, the gcloud sql instances describe [INSTANCE_NAME] command. It has a projectID:region:instanceName format.

  3. Grant the service account your app uses to authenticate calls to Cloud SQL one of the following Cloud SQL roles that include the cloudsql.instances.connect and cloudsql.instances.get permissions:

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

    Two things to keep in mind here is that App Engine, by default uses the App Engine flexible environment service account, which has Editor role granted and that if the service account which is used belongs to a different project than the Cloud SQL instance, the Cloud SQL Admin API and IAM permissions will need to be added for both projects.

  4. Configure your app.yaml according to the option you wish to connect through:

    Unix sockets

    beta_settings:
cloud_sql_instances: INSTANCE_CONNECTION_NAME

    TCP port

    beta_settings:
  cloud_sql_instances: INSTANCE_CONNECTION_NAME:port

  5. Include the following env variables:

     env_variables:
   DB_USER: MY_DB_USER
   DB_PASS: MY_DB_PASSWORD
   DB_NAME: MY_DATABASE
   # e.g. my-awesome-project:us-central1:my-cloud-sql-instance
   CLOUD_SQL_CONNECTION_NAME: <MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>

Keep in mind that DB_NAME is the name of the database you created earlier where INSTANCE-NAME is the name of your Cloud SQL instance.

  1. Finally, use the following snippet to configure the connection:

     const createUnixSocketPool = async (config) => {
   const dbSocketPath = process.env.DB_SOCKET_PATH || "/cloudsql"

   // Establish a connection to the database
   return await mysql.createPool({
     user: process.env.DB_USER, // e.g. 'my-db-user'
     password: process.env.DB_PASS, // e.g. 'my-db-password'
     database: process.env.DB_NAME, // e.g. 'my-database'
     // If connecting via unix domain socket, specify the path
     socketPath: `${dbSocketPath}/${process.env.CLOUD_SQL_CONNECTION_NAME}`,
     // Specify additional properties here.
     ...config
   });
 }

You may find all the details in Google Documentation and Github

user_119281300
Related