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

78
July 22, 2022, at 02:30 AM

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

Answer 2

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

Rent Charter Buses Company
READ ALSO
How to view all new resource provisions in AWS

How to view all new resource provisions in AWS

I am trying create a automation based on the new resource provisions in AWSbut i can't find any service/api which helps to view new provisions

77
Jest TestEnvironment - TypeError: Cannot add property next, object is not extensible

Jest TestEnvironment - TypeError: Cannot add property next, object is not extensible

I want to test a node API using JestI am testing the routes and websockets

39
AES 256 ciphertext has question mark?

AES 256 ciphertext has question mark?

im using library called "simple-encryptor" to encrypt data in the urlwhich use AES-256 algorithem to encrypt the data

61
Custom Error Handler not working in NodeJs Express

Custom Error Handler not working in NodeJs Express

I was studying about error handeling in express and I defined oneIt works fine for non async functions

74