Connect DialogflowChatbot with PostgreSQL database

Question

I would like to connect a custom chatbot in Dialogflow with a PostgreSQL database. The scenario is that the user submits his/her queries to the bot and in turn it commits SQL queries to the database. I know that Webhook/Fulfillment and Integration should be used but the question is how.

Until now, I tried to code in Fulfillment/Inside Editor by by putting...

const pg = require('pg');
const connectionString = process.env.DATABASE_URL || 'postgres://un:pass@localhost:5432/postgres';
const client = new pg.Client(connectionString);
client.connect();
const query = client.query('CREATE TABLE items(id SERIAL PRIMARY KEY, text VARCHAR(40) not null, complete BOOLEAN)');
    query.on('end', () => { client.end(); });

...into index.js. Also, when trying over Google Assistant I always get a Webhook error without any indicative explanation:

textPayload: "MalformedResponse: Webhook error (206)"

I don't want to connect the bot to an additional intermediate website; the chatbot should be able to do the queries and check the database by itself.

Does somebody have an advice for me? Thank you!

An excerpt of the Log is given below:

7:10:03.013 PM
dialogflowFirebaseFulfillment
Ignoring exception from a finished function
7:10:02.995 PM
dialogflowFirebaseFulfillment
Function execution took 10 ms, finished with status code: 200
7:10:02.986 PM
dialogflowFirebaseFulfillment
Billing account not configured. External network is not accessible and quotas are severely limited. Configure billing account to remove these restrictions
7:10:02.986 PM
dialogflowFirebaseFulfillment
Function execution started
7:09:49.540 PM
dialogflowFirebaseFulfillment
Ignoring exception from a finished function
7:09:48.543 PM
dialogflowFirebaseFulfillment
Function execution took 865 ms, finished with status code: 200
7:09:47.678 PM
dialogflowFirebaseFulfillment
Billing account not configured. External network is not accessible and quotas are severely limited. Configure billing account to remove these restrictions
7:09:47.678 PM
dialogflowFirebaseFulfillment
Function execution started
7:09:12.659 PM
dialogflowFirebaseFulfillment
Warning, estimating Firebase Config based on GCLOUD_PROJECT. Initializing firebase-admin may fail
7:08:41.442 PM
dialogflowFirebaseFulfillment
Warning, estimating Firebase Config based on GCLOUD_PROJECT. Initializing firebase-admin may fail
7:04:51.279 PM
dialogflowFirebaseFulfillment
Ignoring exception from a finished function
7:04:51.238 PM
dialogflowFirebaseFulfillment
Function execution took 896 ms, finished with status code: 200
7:04:50.343 PM
dialogflowFirebaseFulfillment
Billing account not configured. External network is not accessible and quotas are severely limited. Configure billing account to remove these restrictions
7:04:50.343 PM
dialogflowFirebaseFulfillment
Function execution started
7:04:33.195 PM
dialogflowFirebaseFulfillment
Warning, estimating Firebase Config based on GCLOUD_PROJECT. Initializing firebase-admin may fail

Also, you wont be able to connect to a local instance of your postgres running on localhost:5432. You have to use a hosted postgres service. — Sairaj Sawant, Dec 06 '18 at 16:01
I did add pg to the dependencies, still the same. And yes, I do use it at localhost:5432. :( Thank you. You think that using some other database would be better/can be set locally? — Herbert Schulz, Dec 06 '18 at 16:10
You can setup postgres on ElephantSQL or MongoDB on mlab and use it as you use globally! — Sairaj Sawant, Dec 06 '18 at 16:13
Can you post log screenshot by clicking **View Execution Logs** on Fulfilment page near deploy if you are using inline editor — Sairaj Sawant, Dec 06 '18 at 17:33
I don't know if you have access to it: https://console.firebase.google.com/project/marysbikeshop-2234f/functions/logs?search=&severity=DEBUG — Herbert Schulz, Dec 06 '18 at 18:30
But one issue may be that they want restrict you to use Firebase as the default DB... — Herbert Schulz, Dec 06 '18 at 18:31
No there is no such restriction. Can you update the post to include screenshot of the above URL i.e the log — Sairaj Sawant, Dec 07 '18 at 00:00
Done. I tried to connect to MySQL as well (also localhost:8080). — Herbert Schulz, Dec 07 '18 at 13:49
You need to use a Billing Account, for example, a Blaze Plan of firebase to access external networks that are databases hosted as a service. Refer [this](https://stackoverflow.com/questions/42784135/cloud-functions-for-firebase-billing-account-not-configured) — Sairaj Sawant, Dec 07 '18 at 13:54
Can you mark the answer as accepted so that it would help others facing the same problem? — Sairaj Sawant, Jun 04 '20 at 12:07

Sairaj Sawant · Answer 1 · 2019-01-30T10:23:07.167

I recently wrote about connecting MongoDB with Dialogflow here.

As I have stated before you won't be able to connect to a local instance of your Postgres running on localhost:5432 (neither MySQL on localhost:8080). You have to use a hosted Postgres service like ElephantSQL (for other databases as well except Firebase/Firestore.)

Now to moving toward your answer, First of all, it is important to take this log message seriously:

Billing account not configured. External network is not accessible and quotas are severely limited. Configure billing account to remove these restrictions

To address this it is extremely important you use a Billing Account, for example, a Blaze Plan of firebase to access external networks (that are databases hosted as a service in your case) Refer this.

Code

'use strict';

const functions = require('firebase-functions');
const {WebhookClient} = require('dialogflow-fulfillment');
const {Card, Suggestion} = require('dialogflow-fulfillment');

const pg = require('pg');
const connectionString = process.env.DATABASE_URL || 'postgres://fsdgubow:K4R2HEcfFeYHPY1iLYvwums3oWezZFJy@stampy.db.elephantsql.com:5432/fsdgubow';
const client = new pg.Client(connectionString);
client.connect();
//const query = client.query('CREATE TABLE items(id SERIAL PRIMARY KEY, text VARCHAR(40) not null, complete BOOLEAN)');

});

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) {

     const text = 'INSERT INTO items(text, complete) VALUES($1, $2) RETURNING *'
     const values = ['some item text', '1' ]

    // insert demo in your items database
    client.query(text, values)
      .then(res => {
        console.log(res.rows[0])
        //see log for output
      })
     .catch(e => console.error(e.stack))

    // sample query returning current time...
     return client.query('SELECT NOW() as now')
           .then((res) => { 
               console.log(res.rows[0]);
               agent.add(`Welcome to my agent! Time is  ${res.rows[0].now}!`);
           })
           .catch(e => console.error(e.stack))

  }

  function fallback(agent) {
    agent.add(`I didn't understand`);
    agent.add(`I'm sorry, can you try again?`);
}

  // Run the proper function handler based on the matched Dialogflow intent name
  let intentMap = new Map();
  intentMap.set('welcome', welcome);
  intentMap.set('Default Fallback Intent', fallback);
  // intentMap.set('your intent name here', yourFunctionHandler);
  // intentMap.set('your intent name here', googleAssistantHandler);
  agent.handleRequest(intentMap);

});

ElephantSQL Console

Firebase Logs

Google Assistant

I have used ElephantSQL for demo here. Look for more queries. Make sure you place return before them to execute successfully and avoid "MalformedResponse: Webhook error (206)"!

Hope that answers all your queries. Good Luck!

dear sai.raj. i will make it so as soon as i try it out. right now i have a looot of work to do. thanks. — Herbert Schulz, Dec 20 '18 at 18:54
Hi when I deploy on dialogflow ,it's give me some errors,like: Detailed stack trace: Error: Cannot find module 'pg' — blueman010112, Feb 01 '19 at 03:24
I can't install this function via inline editor in dialogflow , could you give me more direction? — blueman010112, Feb 11 '19 at 01:33
@blueman010112 add pg in package.json file of your inline editor [ref](https://stackoverflow.com/a/48428692/6763544) — Sairaj Sawant, Feb 11 '19 at 05:03
@Herbert, Can you mark the answer as accepted so that it would help others facing the same problem? — Sairaj Sawant, Jun 04 '20 at 12:07

Connect DialogflowChatbot with PostgreSQL database

1 Answers1