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
MySQL sys.users table content

Now we will create our bot’s flow.

Botpress Bot's flow
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
UserDBUtility using get database operation type
UserDBUtility using update database operation type
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.

11 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

Leave a Reply

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