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);
}
});
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.