🏆 Sequelize connection to Google Cloud SQL (MySQL) Instance and Deployment

You all know, Sequelize is famous ORM for mapping database records into objects. It is one the promise based ORM library which makes your backend desires into reality. Yes, you can treat your database as your array. Let’s see the following snippet.

//getting all students from DB.
User.findAll();

This is the sample code which is simply returning the users from database while User is a model in data. We can simply await User.findAll(); or call then method on it. Its a great taste of asynchronous operations.

Deployment of Sequalize on Google Cloud App Engine with connection to Google Cloud SQL (MySQL) instance is a hard nut to crack. As Google App engines don’t have any static IP to get authorize in instance authorized network. Similarly, we can’t get use public IP address of MySQL instance in app engine in Sequalize configuration. Google officially says on documentation to able TCP port of MySQL instance but this method is under development according to my knowledge. Because Sequalize can’t connect to IP generated by unix port system. So finally, after hard work of two three days, I have found a suitable way for connection.

Follow these steps to connect Google Cloud SQL (MySQL) instance to Google App Engine.

1. 🔒 Authorize App Engine Service account

   Google used service account to create App Engine. So first of all we have to give service account enough roles to access SQL instance. Login to your GCP Console –> Menu (Top left) –> IAM & Admin. Search for account which is managing App Engine Flexible Environment Service Agent. Click on the pencil icon and give it permissions of Cloud SQL Admin. As I am using Google Cloud Storage’s Bucket as well. I gave this permission too.

   

   IAM & Admin – Google Cloud Computing Platform Console

2. 💻 Setup App.yaml file

   App.yaml is a file which is reponsible for setting the environment of Google App Engine. Let me explain a bit more about this file.

   
runtime: nodejs
env: flex
env_variables:
PROJECT_ID: "Project_name"
GCLOUD_STORAGE_BUCKET: "if_you_are_using_bucket"
SMS_API: "my_sms_api"
DB: "enter_your_db_name"
DB_USER: "enter_your_db_user"
DB_PASS: "enter_your_db_pass"
CLOUD_SQL_CONNECTION_NAME: "find_your_instance_connection_in_SQL_panel"
skip_files:
- ^node_modules/.*$


3. 🔗 Setup  Connection String

   I am using config module for setting my environment variables. You can simply call your environment variable with process.env.DB_USER. Remember that following configurations are only for production.
   `sequelize = new Sequelize(
config.get("database.db"),
config.get("database.user"),
config.get("database.pass"),
{
dialect:
"mysql" /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */,
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000,
},
dialectOptions: {
socketPath: `/cloudsql/${config.get(
"database.CLOUD_SQL_CONNECTION_NAME"
)}`,
},
}
);
} catch (e) {
console.log(e);
}`

4. 🚀 Deploy your App to Google App Engine

   You can deploy app to app engine by using following command.
   gcloud app deploy