Cloud Function for MySQL access

Once we have our MySQL tables build we can use cloud function to access the data

MySQL CLoud Function Setup

We will use the connection data from the MySQL build in the function

Function is named SQLACCESS

const mysql = require('mysql');

/**
 * TODO(developer): specify SQL connection details
 */
const connectionName =
  process.env.INSTANCE_CONNECTION_NAME || 'tor3d-244800:us-east1:dbfor3d';
const dbUser = process.env.SQL_USER || 'root';
const dbPassword = process.env.SQL_PASSWORD || 'turnofriver';
const dbName = process.env.SQL_NAME || 'tor';

const mysqlConfig = {
  connectionLimit: 1,
  user: dbUser,
  password: dbPassword,
  database: dbName,
};
if (process.env.NODE_ENV === 'production') {
  mysqlConfig.socketPath = `/cloudsql/${connectionName}`;
}

// Connection pools reuse connections between invocations,
// and handle dropped or expired connections automatically.
let mysqlPool;

exports.SQLACCESS = (req, res) => {
  // Initialize the pool lazily, in case SQL access isn't needed for this
  // GCF instance. Doing so minimizes the number of active SQL connections,
  // which helps keep your GCF instances under SQL connection limits.
  if (!mysqlPool) {
    mysqlPool = mysql.createPool(mysqlConfig);
  }

  mysqlPool.query('SELECT * FROM customers;', (err, results) => {
    if (err) {
      console.error(err);
      res.status(500).send(err);
    } else {
      res.send(JSON.stringify(results));
    }
  });

  // Close any SQL resources that were declared inside this function.
  // Keep any declared in global scope (e.g. mysqlPool) for later reuse.
};

In order for the function to build properly the node.js libraries need to be installed in the local shell of the project.

Node.js setup

also because we are using mysql it must be loaded via npm

npm install mysql

the package.json needs to be set up for all the external node.js modules

Found on Github

{
  "name": "cloudsql-mysql-mysql",
  "description": "Node.js Cloud SQL MySQL Connectivity Sample",
  "version": "0.0.1",
  "private": true,
  "license": "Apache-2.0",
  "author": "Google Inc.",
  "repository": {
    "type": "git",
    "url": "https://github.com/GoogleCloudPlatform/nodejs-docs-samples.git"
  },
  "engines": {
    "node": ">=8.0.0"
  },
  "scripts": {
    "system-test": "mocha test/*.test.js --timeout=60000 --exit",
    "test": "npm run system-test"
  },
  "dependencies": {
    "@google-cloud/logging-winston": "^1.0.0",
    "body-parser": "1.19.0",
    "express": "^4.17.1",
    "promise-mysql": "^4.0.0",
    "prompt": "^1.0.0",
    "pug": "^2.0.3",
    "winston": "^3.1.0"
  },
  "devDependencies": {
    "@google-cloud/nodejs-repo-tools": "^3.3.0",
    "mocha": "^6.0.0",
    "proxyquire": "^2.1.0",
    "supertest": "^4.0.0",
    "sinon": "^7.1.1"
  },
  "cloud-repo-tools": {
    "requiresKeyFile": true,
    "requiresProjectId": true,
    "test": {
      "app": {
        "requiredEnvVars": [
          "DB_USER",
          "DB_PASS",
          "DB_NAME",
          "CLOUD_SQL_INSTANCE_NAME"
        ],
        "args": [
          "server.js"
        ]
      },
      "build": {
        "requiredEnvVars": [
          "DB_USER",
          "DB_PASS",
          "DB_NAME",
          "CLOUD_SQL_INSTANCE_NAME"
        ]
      }
    }
  }
}