IssueTracker

Connecting to Cloud SQL from Cloud Functions is currently not supported, as the UNIX socket does not exist (causing ENOENT) and there is no defined IP range to whitelist (causing ETIMEDOUT). One possibility is to whitelist 0.0.0.0/0 from the Cloud SQL instance but this is not recommended for security reasons.

I will treat this as a feature request though, as there is interest in having this supported.

Thanks for responding.
Do you have any roadmap for this.
It would be good to point to this issue in the documentation (as a note) as it seems it is a big feature missing - not be able to use Cloud SQL. Most people would expect this to work.

Thanks.

I would love to see this as well

Count me as VERY interested!

Want

+1

+1

Having the same problem.

Here is a tip. We are going to deploy a REST API in front of our Cloud SQL via App Engine or Compute Engine, then let the cloud functions call the REST API instead, we won't need to open up 0.0.0.0/0. In fact all of our traffic is expected never egress as it will be using internal IP's for the REST API instance.

Je...@gochin.com: Remember that the Cloud Functions don't live in the internal IP segments of Compute Engine, but on the internal interface. So your REST API needs to be open to the public for this to work (see https://issuetracker.google.com/issues/36859738)

+100

+1

+1

+1

+1

+1

+1

+1

+1

+1

+1

big plus one for this seems an obvious integration to add

+1

I was shocked to learn after I'd put an application together that it didn't work because of this. Honestly, if I'd known that something this basic was missing from Cloud Functions I'd have gone with one of the competitors. For now I'll have to figure out something, but for my future applications please let Cloud Functions access Cloud SQL!

+1

+1

+1

+1

+1

Please enable this functionality!

+1

+1

+1

+1

+1

+1

+1

+1

We can connect to Cloud Spanner but the pricing is crazy for doing development.

I developed the app and tested in the functions (offline/local) environment. I can successfully do CRUD on my Cloud SQL. But then I deploy to function app and there is no way to auth my function to the database.. I only have a firewall like control.. And I have to blow it open as the IP is not going to be stable.. Oh well.. This shouldn't be like it is! please fix asap.

+1

+1

Hi All, This works fine for me. using the mysql npm package.
 
// index.js
var mysql = require('mysql');
exports.writeToSQL = function writeToSQL(req, res) {
    if (req.body === undefined) {
        res.status(400).send('No message defined!');
    } else {

        var connection = mysql.createConnection({
            host: ‘<IP OF THE CLOUD SQL’>,
            user: ‘<NEW DB USER>’,
            password: '<NEW DB PWD>',
            database: '<DB USER>'
        });

        connection.connect();
        var values = {DT_CREATED: new Date(), MESSAGE: JSON.stringify(req.body), SOURCE: req.ip };

        connection.query('INSERT INTO EVENTS SET ?', values, function(error, results, fields) {
            if (error) console.log(error);
            else console.log("inserted successfully");
       
        });

        connection.end();
        res.status(200).send('Success: ' + req.body.message);
    }
};


// package.json
{
  "name": "sample-http",
  "version": "0.0.1",
   
    "dependencies": {
            "mysql": "^2.13.0"
  }
   
}

This is not an official announcement (stay tuned), but as Pietro noticed access to Cloud SQL started to work recently. We are still performing tests, but you should be able to experiment with the feature by specifying:
socketPath: '/cloudsql/' + CloudSQLInstanceId (<project-id>:<region>:<name>)
in the createConnection method.

Great news!!!!
Checked and its working!

Keep us posted on the official release.
Thank you!

+1

+1

Will it work with Cloud SQL Postgres instances too? It seems it's not currently working, either by IP or socketPath (via host).

+1 for Postgres.  Please support !!

+1 for Postgres

+1 for Postgres! PLEASE

+1

Any chance we can get a rough idea of timing on the Postgres side now that this issue is assigned ?  

Thanks,

Alex

Postgres is coming... but just to be clear; we haven't officially announced this yet and it's not officially supported, so use at your peril!

+1

Will postgres be the same as mysql for connection config ?  config.host: '/cloudsql/' + CloudSQLInstanceId ?

I believe so yes.  I reserve the right to be wrong of course, although I'm eventually consistent ;)

+1 for Postgres

+1 for Postgres support!

+1

+1

+1 for postgres support !

+1 pg support

+1

+1

+1

This is truly needed.

I hope it can be supported officially soon! :P I need it for my project! Thank you!

I need it for my project as well. Hope it gets officially supported soon.

+1

+1

+1

+1

+1

+1

+1 for Postgres

+1 for Postgres

+1

+1

+1 for Postgres

+1

+1

+1

C'mon, Google. This is such an important feature.  Can you please give us a ballpark estimate of when it will be available so we can plan appropriately?

Is it one month or two years?

+1 for pg, one thing that make companies great for developers is that they follow a reasonable logic, pls fix asap this is something everyone expect to be there

+1

+1

+1

after experimenting with
socketPath: '/cloudsql/' + CloudSQLInstanceId (<project-id>:<region>:<name>)   (see prev post)

I run into connection errors when the cloudfunction is called with higher volume.
We need somthing like connection-sharing or connection-reuse otherwise this is not going to fly.

Hi Ronald,

How are you creating the connection?  More specifically.. *where* are you creating the connection? (I.e. where in the code)

+1 for PostgreSQL. ;-)

I connect inside the cloudfunction call, but your question hints me as that I can also connect globally and use this connection. Thanks, I'll try this.

Hi Ronald,
A global connection would be better and will likely solve your problem.

Also double-check that you're closing the connection safely.  Some of the examples I've seen might be treating the "end()" call as synchronous, where in-fact it's async so you should make sure you return/callback from the function _after_ end completes.

Eg. assuming you're using this...

https://github.com/mysqljs/mysql#terminating-connections

Then this...

connection.end(function(err) {
  callback(err);
});

or even.. connection.end(callback)

could you post a more complete example of a best-practices way to do use a mysql socket in the cloudfunction? This would help a lot!  

for instance: is it better to open/close a connection in the handler function or open a global mysql connection and never close it? How many connections can I expect when using a global mysql connection? Is that the max number of started instances for the cloudfunction?  The mysql-socket seems to work but some guidance as how to use them would be useful

The following applies to using relational databases in general. It is not specific to Google products.

There is a cost for establishing a connection to a database. Opening a connection per-request is bad practice. Connections should be reused when possible. Even worse, if you do one connection per request and don't close them, you will leak connections and eventually run into some sort of database connection limit.

If your code will only service one request at a time, you should use a single shared connection. As long as all of your transactions complete successfully, you don't need to worry about flushing/closing the connection as everything is committed and the connection will be closed when the process exits.

If you are going to service multiple requests simultaneously, you should use a shared connection pool. You probably don't need to worry about closing the pool, but it is important to return connections to the pool when you are done with them to avoid leaking connections.

My post and Ian's might be confusing.. let me try to clarify.

Creating the connection in global scope is preferred, because it will be re-used if subsequent requests hit the same instance.  In that case you shouldn't need to worry about calling "end()".  
If you do use global scope, make sure than you wait for any operations to complete before returning.  For example:

pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) {
    callback(error);
    return;
  }
  callback(null, results);
});

Try to avoid doing this...

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function (error, results, fields) {
  if (error) throw error;
  // Neat!
});
callback();

The callback here might be called before the INSERT has actually completed.

If you're sticking with having a per-request connection, make sure you close it before you exit using end() and return from the function in the callback for end() (don't just fire-and-forget the call to end() and return immediately after)

I've tried the socketPath connection, it seemed that it is not reliable sometimes on small number connections as well for me.  I had experiencing connection errors spuriously.   I had to switch to IP connection and allowing IP with 0.0.0.0/0 to come into MySQL instance.  This is really unsafe but I had no choice.  Here is my code via the NPM mysql package:

function _executeSql(sql) {
  return new Promise(function(resolve, reject) {
    // Get a connection from the pool
    pool.getConnection((error, connection) => {
      if(error) {
        console.error(`connection error = `, error);
        reject(error);
      } else {
        connection.query(sql, (error, results, fields) => {
          // And done with the connection.
          connection.release();
          // Handle error after the release.
          if (error) {
            console.error("query error = ", error);
            reject(error);
          } else {
            resolve(results);
          }
          // Don't use the connection here, it has been returned to the pool.
        });
      }
    });
  });
}

hi All,
does "socketPath: '/cloudsql/' + CloudSQLInstanceId (<project-id>:<region>:<name>)" works on any type of Firebase pricing package?

I tried many things, but still get ECONNREFUSED
Should I enable some special Authorization to make Cloud SQL Mysql accessible for Firebase Cloud functions?

Error: connect ECONNREFUSED /cloudsql/silent-thunder-177604:us-central1:alextest2
    at Object.exports._errnoException (util.js:1018:11)
    at exports._exceptionWithHostPort (util.js:1041:20)
    at PipeConnectWrap.afterConnect [as oncomplete] (net.js:1086:14)
    --------------------
    at Protocol._enqueue (/user_code/node_modules/mysql/lib/protocol/Protocol.js:145:48)
    at Protocol.handshake (/user_code/node_modules/mysql/lib/protocol/Protocol.js:52:23)
    at Connection.connect (/user_code/node_modules/mysql/lib/Connection.js:130:18)
    at exports.getData.functions.https.onRequest (/user_code/index.js:28:11)
    at cloudFunction (/user_code/node_modules/firebase-functions/lib/providers/https.js:26:47)
    at /var/tmp/worker/worker.js:635:7
    at /var/tmp/worker/worker.js:619:9
    at _combinedTickCallback (internal/process/next_tick.js:73:7)
    at process._tickDomainCallback (internal/process/next_tick.js:128:9)

+1 Cloud functions are useless until this gets added.

+1 AWS Lambda is the most pragmatic (and secure) solution for now if you want to combine an SQL database with a serverless architecture.

+1 I'd love to see this implemented, it'd literally change everything about how I do backend development. (for the much much better).

Went with GAE myself, but would really like to see this one implemented for postgresql.  Since its been several weeks since things unofficially started working on mysql, can we expect anything relatively soon on the postgres side ?

[fingers crossed]

+1 for Postgres with Cloud Functions!!

+1

+1

+1

+1

any updates here team?

+1

+1 any news?

Glad you ask. This week Google introduced a new document-based NoSQL database inside Firebase called Cloud Firestore that works with CF https://firebase.googleblog.com/2017/10/introducing-cloud-firestore.html

Any news on connecting CF to CloudSQL?

+1

+1

+1 Postgres please :)

+1

Guys.... If you don't have any other constructive information to contribute
with please just star the issue. You don't have to make a +1 post. Google
will know from the amount of stars that you think this is important. You
spam all of us with your +1 messages.

And Google please make a filter that blocks +1 messages as they are really
annoying and only make me wanna unstar this issue....

On Oct 12, 2017 17:52, <buganizer-system@google.com> wrote:

Any updates on MySQL and PSQL being officially supported?

It's been 15 or 16 months since the "unofficial" announcement.

Hi folks,

Really sorry for the delay here.  For those interested in accessing Cloud SQL (MySQL & PostgreSQL) from Cloud Functions, please sign up here:

https://goo.gl/forms/CHoiHwsBWMx5GZ3w1

We'll be sending out guidance to those who sign up very soon

Any news about the guidance?

Thanks

An initial round of people who signed up have been added to the early access program.

Hi,
I registered and I hope you send the guidance as soon as possible.

Thanks for your efforts.

I just sent out a new batch of invites.  Post back if you expected one and didn't get it

hey, can I get an invite?

I didn't receive any invite.

Thanks
Fabio

We are begining to look into CloudSQL and this will be a core requirement - signed up for the guidance above.

New batch of updates sent :)

we have a existing firebase/firestore project. but a new feature would require postgres geographical capabilities, being able to use cloud functions would save me from a much painfull redo in express on compute engine. tnx in advance

Very interested as well. We are going to be pushing to Postgres.

+1

+1

+1

For all of you replying +1, what is it that you are +1ing?

^ Cloud Functions support for Cloud SQL (w Postgres)

It is available now. Sign up using the form in #122.

Could you please provide access to beta #122 for the new batch of users waiting for acceptance from 2 weeks.

Thanks for your help.

Kinda assumed that this would work out of the box, luckily it's being tested already. If there are more testers needed, let met know would love to help!

Regards,

Zino Hofmann
Chief Innovation Officer

User Maree Beare (maree.beare@hautla.com) has just submitted a form but did not receive any invite.

Hi, please include me as well in the invites you send out next. I would like to connect to CloudSQL (Mysql or Postgres either would work) via cloud functions.

Hi all

does the experimental mentioned in post #44 feature still work or am i doing something wrong?
  var CloudSQLInstanceId = 'project_id:proj_region:proj_name';
  var connection = mysql.createConnection({
    // host: 'HOST_IP',
    user: 'root',
    password: 'PASSWORD',
    database: 'DATABASE_NAME',
    socketPath: '/cloudsql/' + CloudSQLInstanceId
  });

  connection.connect(function(err) {
    if (err) {
      // console.error('error connecting: ' + err.stack);
      response.json({err: err.stack});
      return;
    }

the above code keeps on throwing Error: connect ENOENT /cloudsql/project_id:proj_region:proj_name


UPDATE*

The did not clarify. the above works once the Cloud Function has been deployed
it does not work which trying to test LOCALLY on Cloud Functions Local Emulator

UPDATE*
To test Cloud Functions locally using the Local Emulator. use the cloud sql proxy and set the port to whatever your test environment is running on


Regards
Ameer

For anyone stumbling upon this issue, I can confirm that using the socket method works with node-postgres client (pg on npm) for postgreSQL.

The trick is to set the 'host' not 'socketPath' (hosts starting with / are sockets for pg: https://www.postgresql.org/docs/9.1/static/libpq-connect.html#LIBPQ-CONNECT-HOST ).

Another tip, you can copy the long socket path directly from the google cloud console->SQL->Instance page. In the box titled "Connect to this instance", click to copy the "Instance connection name". Then prepend it with "/cloudsql/" for the host connection parameter.

Hi, is the early access program still accessible ? I want to use Cloud SQL (postgreSQL) in my cloud functions for the project I'm working on.

Thank you in advance.

+1, I want to use Cloud SQL function for MySQL

+1

+1

+1

+1

+1

+1

+1

+1

+1

+1

Instead of +1ing you should star the issue at the top left.

Any update on this from Google as to when we should expect access to Cloud SQL?

My use case is to write a small script that will run once a day to purge old logs in the SQL database. Cloud Functions also needs time based trigger functionality for this to work.

Cloud SQL sign up: https://docs.google.com/forms/d/1Yhs7Jz5d16MglHO_ucJB2hgI_wKEAmYrYQd0xSkHVys/edit (#122)
Scheduling is coming, for now you can wire up an App Engine cron to Pubsub and trigger I function that way.  Yes I know it's ugly

I might as well just write the code in App Engine as well then.

Yep.. that also works.

+1 would love some sort of official support for communicating with my cloud postgres instance via a cloud function

+1

+1

More than one year and still no official support? I started an application last year, I saw this and I said: when my APP gets production surely this issue will be resolved, my APP is in production now, I don't have official support and Cloud Functions are randomly getting ECONNREFUSED from MySQL instance, I'm paying for first class sql service that doesn't feel like that

I know you never told me: "it's official, that works" but guys, one year with this?

UPDATE:

Support contacted and helped us to fix the issue, we had lot of functions that weren't using pool connections, once we implemented the connection pool on all of our cloud functions, the ECONNREFUSED error has gone, 7 days without any error since the update, thanks.

Use global variables to reuse objects in future invocations
https://cloud.google.com/functions/docs/bestpractices/tips#use_global_variables_to_reuse_objects_in_future_invocations

+1

lol +1

+1

+1

+1

May i know how to connect the Google functions to Cloud SQl mysql database .please will you suggest me any sample code for connecting functions to mysql.

Because Firebase Functions is a modified NodeJS server, you're going to need to:
 - Install any NodeJS MySQL library and learn how to use it.
 - Then, you should add `0.0.0.0/0` to Authorization.

Here's one of the MySQL libraries:
https://www.npmjs.com/package/mysql

+1 I don't want to add 0.0.0.0/0 for security reason.

IMO: Every MySQL database can connect from any external IP address.

However, there does not seem to be a VPN connection... (maybe not, yet)

If you are using Compute Engine however, you can lookup the range of IP address using this method.
https://cloud.google.com/compute/docs/faq#where_can_i_find_product_name_short_ip_ranges

I'm not sure if it is applicable with Google functions.