Google cloud function with cloud sql

Google cloud function is a serverless framework that helps you automatically run backend code based on triggers. You can trigger the cloud function from firebase or your mobile and web application for eg. You save user data in Firestore on collection user and whenever a new doc is added on firestore you want to save it on some other place or send the notification on slack, you can add trigger in cloud functions for the event create doc on user collection. Google cloud sql is the database service provided by google and you can set up your mysql database there. So if you're using cloud sql for database and want to save some data from google cloud function to database then you have to do some setup so that you can get access to the database to read and write on it.

How to use MySQL database with cloud function

Install the mysql npm library.

npm install mysql

Once mysql is installed then start using it.

const mysql = require(‘mysql’);

We will create a mysql pool. createPool creates a pool where connections get stored and when you request a connection, you will receive a connection that is not being currently used or a new connection and if no connection is available then wait until the connection is available.

mysql.createPool(config);

Here ‘config’ is the configuration used to provide details like sql user, password, database etc as shown below

const config = {
    user: 'user name', 
    password: 'password',
    database: 'database',
    socketPath: 'socket path',
    connectionLimit: 5,// limit of number of connection in pool
    connectTimeout: 10000,
    acquireTimeout: 10000,
    waitForConnections: true,
    queueLimit: 0,
    charset: 'utf8mb4_unicode_ci',
    supportBigNumbers: true,
    bigNumberStrings: true,
  }

You can get the connection from pool and use it for query. Once the query execute successfully you should release the connection so that connection become available for others.

const executeQuery = (query, values, callback) => {
    pool.getConnection((err, connection) => {
      if (err) {
        return callback(err);
      }
      if (connection) {
        connection.query(query, values, (error, results) => {
          connection.release();
          if (error) {
            return callback(error);
          }
          return callback(null, results);
        });
      }
    });
}

Here 'query' is the sql query, values are the value need to pass in query and callback is the callback function use to get the error or result.

If cloud function and cloud sql both are present in same google cloud platform project then no need to do anything but if both are present in different GCP project then you have to provide the access of cloud sql to the gcp project that has the cloud function.

You need the IAM&Admin access to edit it. Open IAM&Admin and select IAM option. Use Add option to add new member. To add new member you need the email of service account. Go to gcp project that has the cloud function. Select IAM&Admin then service account and use the email of app engine service default. Select a role for Client Sql Admin to provide the cloud sql access to the service account that has the cloud function.

Note : In my next post I'll show you how to add authentication in google app script and trigger a cloud function from google sheet.