3 minutes read

MySQL is a free open source RDBMS solution, which makes it very popular among users. I covered How to use values from the database in Botpress? where you can connect to an Oracle database and retrieve values. In this tutorial we will see how we can use Knex and a database utility custom action to connect to MySQL database to retrieve and update a record using Botpress.

We will create a simple bot which asks for an userid, fetches data from MySQL database against this userid (using SELECT operation). If it finds a record, then it updates the isActive status of the entered userid to true (using UPDATE operation).

Create a custom action (out\bp\data\global\actions\UserDBUtility.js) in Botpress code editor

/**
   * This utility can Save/Retrieve/Update data to MySQL DB
   * @title User MySQL DB Utility
   * @category Utility
   * @author Abhishek Raj Simon
   * @param {string} name - Only supports get/set/update operations
   * @param {string} table - Table name
   * @param {string} key - Can contain any key against which value needs to be pushed to DB
   * @param {string} value - Can contain a any value that needs to be pushed to DB   
   */
const userDBUtility = async (name, table, key, value) => {
  const userId = event.target
  const botId = event.botId
  user.data = undefined;
  const knex = require('knex')({
    client: 'mysql',
    connection: {
      host: 'localhost',
      user: 'root',
      password: 'lamepassword',
      database: 'sys'
    },
    useNullAsDefault: false,
    log: {
      warn(message) {
        console.log(message);
      },
      error(message) {
        console.error(message);
      },
      deprecate(message) {
        console.log(message);
      },
      debug(message) {
        console.log(message);
      },
    }
  });

  if (knex) {
    temp.param = value;

    let query = "", response = false;
    if ('get' === name) {
      query = "select name from " + table + " where userid='" + value + "'";
    } else if ('set' === name) {
      query = "insert into " + table + "(userid, name) values('" + key + "',' + value + ')";
    } else if ('update' === name) {
      query = "update " + table + " set " + key + " = " + value + " where userid = '" + event.payload.text + "'";
    }
    await knex.raw(query).on('query', function (data) {
      //A query event is fired just before a query takes place. Useful for logging all queries throughout your application.
      console.log("Executing: " + data.sql)
    }).then(function (data) {
      if (data.length == 2 && name === 'get') {
        user.data = data[0][0][key]
      } else if (data.length == 2 && name === 'set') {
        user.data = undefined;
      } else if (data.length == 2 && name === 'update') {
        console.log(data)
      } else {
        user.data = undefined;

      }
    }).catch(err => console.log(err));

  } else {
    console.log("knex is not initialized");
  }
}

return userDBUtility(args.name, args.table, args.key, args.value)

In our custom action userDBUtility we are initialiing a MySQL connection using knex and then we will use get and update to perform SELECT and UPDATE database operation on our MySQL database.

Note: Please pass connection parameters like host,user, password and database.

Knex is an SQL query builder for PostgresMSSQLMySQLMariaDBSQLite3Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use. Knex is available out of the box with Botpress. To use any of the above databases, we also need to install the respective database library. For our case we will use yarn add mysql. If you are using any other database, please use an appropriate command from the below list.

yarn add pg
yarn add sqlite3
yarn add mysql
yarn add mysql2
yarn add oracledb
yarn add mssql

Once we get the response we are storing it inside user, one of the memory variables supported by Botpress.

Here is thesys.users table content from MySQL database. This table contains data for a user like userid, user’s name, user’s active status and the last login date.

MySQL sys.users table content how to connect to mysql database using botpress How to connect to MySQL database using Botpress? image
MySQL sys.users table content

Now we will create our bot’s flow.

Botpress Bot's flow how to connect to mysql database using botpress How to connect to MySQL database using Botpress? image 1
Botpress Bot’s flow

Let’s look at each node in the above screenshot

  1. The 1st node is a greeting node.
  2. The 2nd node waits for user input, calls our UserDBUtility custom action to retrieve data from MySQL database, later depending on the response received from database flow transitions to either Node 3 or Node 4. Please check the below screenshot for more information.
  3. The 3rd node is a success node, where the record is found for the given user id and updates isActive status of the provided user into sys.users table before ending the flow.
  4. In case the user record is not found, flow is transitioned to the 4th node that is a failure node. The flow is then transitioned to a retry node.
  5. The 5th node is the retry node, which always transitions to Node 2 to repeat tasks from step 2.
UserDBUtility using get database operation type how to connect to mysql database using botpress How to connect to MySQL database using Botpress? image 2
UserDBUtility using get database operation type
UserDBUtility using update database operation type how to connect to mysql database using botpress How to connect to MySQL database using Botpress? image 3
UserDBUtility using update database operation type

That’s it. This is how we can connect to MySQL database using Botpress bot. Let me know in the comment section if you have questions.


Simon

I am a Fullstack developer and Consultant with an experience of 9+ years in the industry. I mainly work on Java, React, Javascript, NodeJs, Elasticsearch and Botpress.

24 Comments

Luiz Cesar Leite · May 13, 2020 at 1:47 am

Simon,

Thanks for the tip 🙂

A question: a hook could be used instead an action? I think that this way we don’t need to make a connection everytime, in each action.

Am I right?

Thanks

    Simon · May 14, 2020 at 3:41 pm

    Hi Luiz

    You are welcome and yes you are right. My intention was just to show an approach using Botpress actions where users can connect to MySQL database and perform DB operations (a popular ask), but creating a connection using hooks and then using that instance should be the way to go.

      Luiz Cesar Leite · May 15, 2020 at 1:21 am

      Hi Simon!

      Thanks for your kind answer.

      Hope you made a tutorial on how to use hooks and actions together 🙂

joe · June 24, 2020 at 5:08 pm

hi. i can run “yarn install mysql -save” or “npm install mysql -save” and i recive “coomand not foud” i install botpress from executable.
can you helpme please?
thans.
joe.

    Simon · June 25, 2020 at 10:56 am

    Please use Botpress source for your development work. Use executable when you are planning to deploy in production.

joe · June 24, 2020 at 5:41 pm

hi.
Can help me… “yarn o npn install mysql -save” command not found.
I install from botpress exe, how can add module?
Tanks.
Joe.

    Simon · June 25, 2020 at 10:01 am

    @joe Is yarn installed on your system? Try running yarn -vcommand to verify if yarn is available on your system or not (it should give yarn version), otherwise follow this tutorial. You need to download Botpress source from github location and then you should try running yarn && yarn build

Santiago · July 3, 2020 at 6:57 pm

Hi! Excellent work! I’m starting with botpress and it seems really great! I was wandering, what would be the case if I just wanna read information from a table?

Jeffrey Bennett · July 16, 2020 at 6:09 pm

Hello, I’m interested in using this library, but I am concerned about the password field being exposed in plain text over JavaScript. How do you secure this so it’s never delivered to the browser? Thank you!

Said · October 12, 2020 at 10:47 pm

Thanks for your tutorial. I have followed the same steps but in the emulator I get the following error message:
“Error: Knex: run
$ npm install mysql –save
Cannot find module ‘mysql'”

What can be the issue?
I really appreciate your help.

    Simon · November 2, 2020 at 6:52 pm

    @Said: Delete node_modules and try again.

    Note: Use node version 10.11.0

Leonardo Bascuñan · December 7, 2020 at 6:59 pm

Hello, I have a query in which of the following folders should I save UserDB Utility.js? in analitycs I don’t think so, in basic-skills or builtin or channel web, thanks in advance.

    Simon · December 8, 2020 at 12:22 pm

    Use this path out\bp\data\global\actions\UserDBUtility.js

ankit kumar · March 15, 2021 at 12:53 pm

Hi Simon,

I am a newbie to botpress and have little knowledge of javascript, and i want to execute sql query
botpress version:
Botpress Server
Version 12.17.2
OS linux red-hat
I have two questions:
1. How can i fire sqlite query from botpress which it supports default ?where is sqlite db can be found ?
2. As per above mention js code userDBUtility.js , i had placed this code in data/global/actions/UserDBUtility.js but after that when i triedto restart botpress i see below error
Launcher Unhandled Rejection [TypeError, Cannot read property ‘split’ of undefined]

Any help would be appreciated, i am stuck here not able to proceed further.

ankit kumar · March 15, 2021 at 12:57 pm

Hi Simon,

I am a newbie to botpress and have little knowledge of javascript, and i want to execute sql query
botpress version:
Botpress Server
Version 12.17.2
OS linux red-hat
I have mysql and knex installed in linux.

I have two questions:
1. How can i fire sqlite query from botpress which it supports default ?where is sqlite db can be found ?
2. As per above mention js code userDBUtility.js , i had placed this code in data/global/actions/UserDBUtility.js but after that when i triedto restart botpress i see below error
Launcher Unhandled Rejection [TypeError, Cannot read property ‘split’ of undefined]

Any help would be appreciated, i am stuck here not able to proceed further.

    Simon · March 19, 2021 at 12:25 pm

    @Ankit kumar: Please look here for userDBUtilityand also the official documentation on kvs on how you can query sqlite from Botpress. You can find the sqlite db here /data/storage/core.sqlite.
    The error “‘split’ of undefined” is something related to your response, I assume your response is empty, that is why you cannot perform split. Please use console logs and other debug techniques on your code to find the root cause.

    Let me know if you need further help.

Yuvilma · May 14, 2021 at 3:16 am

Hello, doing the step by step of your tutorial keeps giving an error, and I can’t find information that can help me.

This is the error:
“Knex: run
$ npm install mysql –save
Cannot find module ‘mysql’
Require stack:
– C:\snapshot\build-windows\node_modules\knex\lib\dialects\mysql\index.js
– C:\snapshot\build-windows\node_modules\knex\lib\knex.js …..”
…”1) If you want to compile the package/file into executable, please pay attention to compilation warnings and specify a literal in ‘require’ call. 2) If you don’t want to compile the package/file into executable and want to ‘require’ it from filesystem (likely plugin), specify an absolute path in ‘require’ call using process.cwd() or process.execPath…”

Please can you help me

    Simon · May 15, 2021 at 10:24 pm

    @Yuvilma: Which version of nodejs are you using? Also, did you tried yarn add mysql (clean up node_modules folder)?

      Yuvilma · May 16, 2021 at 11:09 pm

      Hi @Simon, the version is Node.js v14.15.1. and if i already try to clean the node_modules folder and use yarn add mysql i am frustrated

        Simon · May 17, 2021 at 3:32 pm

        @Yuvilma: The last time I heard they only support nodejs version 10.11.0 and they were planning to upgrade to v12. It’s best to downgrade your node version to 10.11.0 (You can use nvm to manage different node versions in your system)

        I understand it is time taking, but there is always an option to hire somebody to do the job for you. Let me know if you need professional services.

          Yuvilma · May 19, 2021 at 1:44 am

          ok, i got it thanks for the information. And thanks for your tutorials

Gennaro · October 29, 2021 at 1:44 pm

Hi Simon! Thanks for the tutorial.

Despite following it step by step I get the following error:
> error: password authentication failed for user “username”
Do you have any idea on how to solve this?

Thanks

    Simon · January 23, 2022 at 1:35 pm

    Hi Gennaro

    It looks like you successfully connected to your database. The error is w.r.t your credential. Please verify that you have entered proper username and password for database connection and it should work.

    Regards
    Abhishek

Kenny · March 19, 2024 at 3:20 am

Hello Simon, greetings! I am trying to make a simple chatbot for WhatsApp that allows users to make queries about their current status through their identification number, and that can connect to the database through PHP and MySQL and return the information to me. But I haven’t figured out how to connect with botpress, could you help me a little?

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.