How to read data form cloud SQL in Google Cloud Function in Node JS

Question

I am new in Google Cloud Function, Here am trying to load data from cloud SQL for that I have written a code, which has database connection as well as SQL query string however when am trying to test the function it is throwing either error or Object or sometime it says you are not returning any data. I tried many ways to send data but still doesn't work for me. Thanks in advance.

Code snippets,

exports.welcome = async (req, res) => {
  const tag = req?.body?.fulfillmentInfo?.tag;
  let message = tag || req?.query?.message || req?.body?.message || 'Hello World!';

  const pool = mysql.createPool({
    connectionLimit: 1,
    host: '10.30.48.2',
    user: 'root',
    password: 'TEST@TEST',
    database: 'hospital_assist'
  });
  
  const jsonResponse = {
    fulfillment_response: {
      messages: [
        {
          text: {
            //fulfillment text response to be sent to the agent
            text: [`Welcome entry point ${message}`],
          },
        },
      ],
    },
  };

  let qResult = await pool.query('SELECT * FROM Patients;');
  // pool.query('SELECT * FROM Patints', (error, result) => {
  //   console.log(error);
  //   console.log(result);
  //   console.log('In loop');
  //   res.status(200).send(jsonResponse);  
  // });



  // await pool.query('select * from Patients;', (error, results) => {
  //       if (error) {
  //           console.log(error);
  //           res.status(200).send(jsonResponse);
  //       }
  //       qResult = results;
  //       // res.send(results);
  //   });

  console.log(qResult);
  console.log(`In loop ${qResult}`);
  res.status(200).send(JSON.stringify(qResult)); 

  // res.status(200).send(jsonResponse);
};

Did you add a serverless VPC connector to your Cloud Functions? Do you have a public IP on your Cloud SQL instance? if so, what's your SQL engine (MySQL or postgreSQL)? — guillaume blaquiere
– guillaume blaquiere, Commented Feb 7, 2022 at 14:40
I have both public as well as private IP. SQL engine is MySQL. — Gajanan Kolpuke
– Gajanan Kolpuke, Commented Feb 7, 2022 at 14:47

enocom · Accepted Answer · 2022-02-07 16:56:22Z

1

If you're trying to connect to your instance using public IP, you'll need to specify a Unix socket path, e.g.,

mysql.createPool({
    user: process.env.DB_USER, // e.g. 'my-db-user'
    password: process.env.DB_PASS, // e.g. 'my-db-password'
    database: process.env.DB_NAME, // e.g. 'my-database'
    // If connecting via unix domain socket, specify the path
    socketPath: "/cloudsql/my-project:us-central1:my-instance",
    // Specify additional properties here.
    ...config,
});

See the docs for details.

answered Feb 7, 2022 at 16:56

enocom

1,8861 gold badge18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

guillaume blaquiere · Accepted Answer · 2022-02-07 16:55:57Z

In your case, you have several solutions, 2 are specially recommended:

Use internal IP (your current code). With that pattern, you can remove the public IP and hide totally the database from the internet. To allow CLoud Functions accessing private IP, you have to bridge the serverless world (the server and the network is managed for you by Google Cloud, and therefore you aren't in your VPC) with your project world (especially your VPC where your database is connected). A serverless VPC connector is done for that.
With Cloud Functions you can use a built-in connector with Cloud SQL MySQL (only in version 8). It opens a linux socket and you have to use a driver/library that support that socket communication mode. The advantage here is the encryption of the database communication through the built-in connector.

Note: with a Cloud SQL public IP avoid to authorized network on your Cloud SQL database to keep a good level of security

Collectives™ on Stack Overflow

How to read data form cloud SQL in Google Cloud Function in Node JS

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related