Sails on Google App Engine with Cloud SQL

Question

I have followed this guide https://cloud.google.com/community/tutorials/run-sailsjs-on-google-app-engine on how to deployed Sails to Google App Engine. However, my app used MySQL as the database and I have set it up like this in "connection.js".

mysql: {
      adapter: 'sails-mysql',
      host: '104.199.196.99', // Internal IP Address of the Cloud SQL Instance
      user: 'root', 
      password: 'xxxxxx', 
      database: 'xxxxxxxx' 
    }

The error I got when trying to connect with Cloud SQL (when I make a requrest to log-in which involved connection to database) was:

Sending 500 ("Server Error") response:
Error (E_UNKNOWN) :: Encountered an unexpected error
Could not connect to MySQL:
Error: connect ETIMEDOUT

Did I set it up correctly?

Please enlighten me , is the cloud SQL running on default port (3306)? Else it wouldn't connect as you have not mentioned the port in the connections.js file ? — UchihaItachi
I have tried specifying the port to be 3306 without success. When in Google Cloud Shell, I tried "telnet <cloud-sql-internal-ip-address> 3306" and it could not be reached. Instead, the Cloud SQL instance was reachable via port 3307. I have tried specifying both port in the connection.js file and still there was no result. I have another Compute Engine instance which has a permission to connect to Cloud SQL (I assigned static IP address to it) a simple command of "mysql --host=<cloud-sql-ip-address> --user=root --password" without port could connect. — Nutchanon Phongoen
Furthermore, Cloud SQL has the permission that allow every app in this project to connect. — Nutchanon Phongoen
you have put both port ? I am not getting it . have you used mysql: { adapter: 'sails-mysql', host: '104.199.196.99', // Internal IP Address of the Cloud SQL Instance user: 'root', password: 'xxxxxx', database: 'xxxxxxxx' port : 3007 } — UchihaItachi

Nutchanon Phongoen Nutchanon Phongoen · Accepted Answer · 2017-06-21T09:27:11

For those who seek the solution for connecting to Google Cloud SQL using Google App Engine with Sails.js, I have figured it out.

Connecting to the Google Cloud SQL using host address doest not work here, because the Firewall Rules and the Access Control of the Cloud SQL will prevent this kind of connection to the whitelisted addresses. Unfortunately, Google App Engine could not be assigned with Static IP Address. Therefore, the App Engine could not be whitelisted by the Cloud SQL or you would need to whitelist 0.0.0.0/0 which is everything and that is not secure.

The solution is to use this configuration in your connection.js.

googleCloudSQL: {
   adapter: 'sails-mysql',
   socketPath: '/cloudsql/' + process.env.INSTANCE_CONNECTION_NAME,
   user: 'XXXXX',
   password: 'XXXXXXX',
   database: 'XXXXXXXX'
}

By connecting to the Cloud SQL via socket path using the Instance Connection Name of the Cloud SQL, this is the native way of doing it. If your App Engine is already in the same project with your Cloud SQL then it will already be authorized to connect. However, if they are in different projects, you would need to set up service account and IAM.

Sails on Google App Engine with Cloud SQL

1 Answers