How to connect to MySQL database using Botpress?

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 Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, 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.

Now we will create our 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.

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.