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
- The 1st node is a greeting node.
- 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. - 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 intosys.users
table before ending the flow. - 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.
- 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.
13 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 -v
command 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 runningyarn && 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