Javascript – How to connect Google Cloud SQL from Cloud Functions

firebasegoogle-cloud-functionsgoogle-cloud-platformgoogle-cloud-sqljavascript

I am trying to use Cloud Functions for Firebase to build an API that talks with a Google Cloud SQL (PostgreSQL) instance.

I am using HTTP(S) trigger.

When I white-list my desktop's IP address, I can connect to the Cloud SQL with the function's node.js code from my local machine. But when I deploy, I can't connect, and I can't figure out the HOST IP address of Firebase Function's server, to white-list.

How do you talk to Google Cloud SQL from Cloud Functions for Firebase?

Thanks!

// Code Sample, of what's working on Localhost.
var functions = require('firebase-functions');

var pg = require('pg');
var pgConfig = {
  user: functions.config().pg.user,
  database: functions.config().pg.database,
  password: functions.config().pg.password,
  host: functions.config().pg.host
}

exports.helloSql = functions.https.onRequest((request, response) => {
  console.log('connecting...');
  try {
    client.connect(function(err) {
      if (err) throw err;

      console.log('connection success');
      console.log('querying...');

      client.query('SELECT * FROM guestbook;', function(err, result){
        if (err) throw err;

        console.log('querying success.');
        console.log('Results: ', result);
        console.log('Ending...');

        client.end(function(err){
          if (err) throw err;
          console.log('End success.');
          response.send(result);
        });
      });

    });
  } catch(er) {
    console.error(er.stack)
    response.status(500).send(er);
  }
});

Best Answer

I found answer in further discussion of #36388165.

disclaimer: this does not seem to be announced officially, so may change afterward. also I only test in mysql. but nature of this solution, I think same way should work as in pg module (it seems to accept domain socket path as host parameter)

EDIT(2017/12/7): google seems to provide official early access, and same method still works.
EDIT(2018/07/04): it seems that there is someone just copy-and-paste my example code and get into trouble. as google says, you should use connection pool to avoid sql connection leak. (it causes ECONNREFUSE) so I change example code a bit. EDIT(2019/04/04): in below example, using $DBNAME as spanner instance name is confusing, I modify example.

in https://issuetracker.google.com/issues/36388165#comment44 google guy says cloud function instance can talk with cloud sql through domain socket in special path '/cloudsql/$PROJECT_ID:$REGION:$DBNAME'.

I actually can connect and operate cloud SQL from below cloud function code. 

const mysql = require('mysql');
const pool = mysql.createPool({
    connectionLimit : 1,
    socketPath: '/cloudsql/' + '$PROJECT_ID:$REGION:$SPANNER_INSTANCE_NAME',
    user: '$USER',
    password: '$PASS',
    database: '$DATABASE'
});
exports.handler = function handler(req, res) {
    //using pool instead of creating connection with function call
    pool.query(`SELECT * FROM table where id = ?`, 
                                req.body.id, function (e, results) {
        //made reply here
    });
};

I hope this would be help for those cannot wait for official announce from google.

Related Topic