How can I connect to Google Cloud SQL database as an external mysql database from Google App Maker?

Question

I am trying to connect to a Google Cloud SQL database from my Google App Maker app. Unfortunately, my IT staff hasn't set up Google App Maker to use Google Cloud SQL as the default so I'm trying to connect to the database the same way I have connected to external MySQL databases in the past but it's not working with the public IP address.

I have created a Google Cloud SQL database and I'm able to connect to it from MySQLWorkbench using the public IP address. I had to add my the IP address for my home computer in order to connect the database. I did not need to use SSL.

I created a Google service account for my Google App Maker app. I then included this service account in the Google project that contains the Cloud SQL database. I assigned it permissions for Cloud SQL Admin and Cloud SQL Client.

I am using this code in App Maker to try and connect. It's the same code I have used with other external MySQL databases. The ip address 34.xx.xx.xx is the public IP address listed in the overview page of the Google Cloud SQL instance.

// App Settings

// Important Note: This is for demo purposes only, storing passwords in scripts
// is not recommended in production applications.

// DB Credentials (you need to provide these)
var address = '34.xx.xx.xx';
var db = 'Kenco_IoT_Template';
var dbUrl = 'jdbc:mysql://' + address + '/' + db;
var user = 'real_username';
var userPwd = 'real_password';

I receive this error message:

"Executing query for datasource ActivityTable: (Error) : Failed to establish a database connection. Check connection string, username and password. Please refer to the ReadMe and edit your database settings!"

My guess is the issue is with setting up the Cloud SQL database to accept the connection from Google App Maker. The best solution will be to enable Cloud SQL as the default for Google App Maker but I'm hoping there is some alternative I can use for now.

Any help appreciated.

I think you might need to authorize appscript ip addresses... have you done this ?developers.google.com/apps-script/guides/jdbc#other_databases — Morfinismo
THANK YOU!!! I authorized the IP address ranges for appscript. I also needed to switch to using Jdbc.getCloudSqlConnection(url) instead of Jdbc.getConnection(url). Bingo--worked perfectly! Back in business :) — STEM FabLab
@STEMFabLab could you please post the solution as an answer? This could be helpful to let other users know about the solution in case they are facing the same issue. Thanks! :) — ericcco

Gabe Weiss Gabe Weiss · Accepted Answer · 2019-10-22T16:44:22

To make this an answer so others can more easily find it:

The answer is that you needed to whitelist the IP ranges for appscript. Public IPs on Cloud SQL instances either require whitelisting of the IP addresses for access, or they need to use the Cloud SQL Proxy.

The OP also mentioned that they had to switch which Jdbc method they used from getConnection() to getCloudSqlConnection().

How can I connect to Google Cloud SQL database as an external mysql database from Google App Maker?

1 Answers