How to connect Dialogflow Fulfillment with MySQL DB?

Question

I tried to connect my Dialogflow agent with MySQL DB at my GCP, but it didn't work well. I just wanna see the results in console, but there's no results about that. I can only see the result "Undefined", but since I am not familiar with Node.js, I am not sure what this means. Since there is no content in the result, I cannot proceed to the next step.

Here is my index.js script :

// See https://github.com/dialogflow/dialogflow-fulfillment-nodejs
// for Dialogflow fulfillment library docs, samples, and to report issues
'use strict';
 
const functions = require('firebase-functions');
const {WebhookClient} = require('dialogflow-fulfillment');
const {Card, Suggestion} = require('dialogflow-fulfillment');

const mysql = require('mysql');
 
process.env.DEBUG = 'dialogflow:debug'; // enables lib debugging statements
 
exports.dialogflowFirebaseFulfillment = functions.https.onRequest((request, response) => {
  const agent = new WebhookClient({ request, response });
  console.log('Dialogflow Request headers: ' + JSON.stringify(request.headers));
  console.log('Dialogflow Request body: ' + JSON.stringify(request.body));
 
  function welcome(agent) {
    agent.add(`Welcome to my agent!`);
  }
 
  function fallback(agent) {
    agent.add(`I didn't understand`);
    agent.add(`I'm sorry, can you try again?`);
  }

  function connectToDatabase() {
    const connection = mysql.createConnection({
      host: '34.64.***.***',
      user: 'username',
      password: 'password',
      database: 'dbname'
    });
    return new Promise((resolve,reject) => {
       connection.connect();
       console.log('connection successed.');
       resolve(connection);
    });
  }
  
  function queryDatabase(connection, name){
    return new Promise((resolve, reject) => {
      connection.query('SELECT * FROM tb_user WHERE name = ?', name, (error, results, fields) => {
        console.log('query successed.');
        resolve(results);
      });
    });
  }
  
  function queryDatabase2(connection){
    return new Promise((resolve, reject) => {
      connection.query('SELECT * FROM tb_user', (error, results, fields) => {
        if (error) console.log(error);
        console.log('results', results);
      });
    });
  }
  
  function handleReadFromMysql(agent) {
    const user_name = agent.parameters.name;
    console.log(user_name.name);
    return connectToDatabase()
    .then(connection => {
      console.log('connectToDatabase passed');
      return queryDatabase(connection, user_name.name)
      //return queryDatabase2(connection)
      .then(result => {
        console.log('queryDatabase passed');
        console.log(result);
        agent.add(`User name is ${user_name} .`);
        //result.map(user => {
          //if(user_name === user.name) {
            //agent.add(`UserID is  ${user.id}`);
          //}
        //});
        connection.end();
      });
    });
  }
  
  let intentMap = new Map();
  intentMap.set('Default Welcome Intent', welcome);
  intentMap.set('Default Fallback Intent', fallback);
  intentMap.set('findName', handleReadFromMysql);
  agent.handleRequest(intentMap);
});

and the logs are :

enter image description here

Please somebody help me...

Answer 1

I solved this problem 4 months ago, so I share my code to help another like me.

I use socketPath of DB info. I forgot the reason.

function connectToDatabase() {
    const connection = mysql.createConnection({
        socketPath: '/cloudsql/*************:asia-northeast3:****',
        user: '****',
        password: '****',
        database: '****'
    });
    return new Promise((resolve, reject) => {
        connection.connect();
        console.log('connection successed.');
        resolve(connection);
    });
}

And make query function like :

function findNameCountQuery(connection, name) {
return new Promise((resolve, reject) => {
    var sql = `SELECT * FROM tb_user WHERE name = ?`;
    var execSql = connection.query(sql, [name], (error, results) => {
        if (error) throw error;
        resolve(results);
    });
});

Then I use agent function like :

async function findName(agent) {
    const user_name = agent.parameters.person.name;
    const connection = await connectToDatabase();
    const result = await findNameQuery(connection, user_name);
    result.map(user => {
        message += `사용자 ID seq number : ${user.seq}\n`;
    });

    agent.add(message);
    connection.end();
}

Hope to be helpful.