In order to save anything to Postgres DB, you need to first switch from SQLite to Postgres in Botpress.

Let us create a simple bot that takes a message and depending on the parameter passed the custom action either saves or retrieves data from the Postgres DB.

Create a Custom action (out\bp\data\global\actions\calldb.js)

/**
   * Saves or Retrieves data from Postgres DB
   * @title Postgres DB Utility
   * @category Utility
   * @author Abhishek Raj Simon
   * @param {string} name - Either get/set
   * @param {string} value - Can contain a any value that needs to be pushed to DB
   */
  const myAction = async (name, value) => {
    const userId = event.target
    const botId = event.botId
    const message = value
    if ('get' === name) {
      temp.message = await bp.kvs.get(botId, userId + 'message')
    } else if ('set' === name) {
      await bp.kvs.set(botId, userId + 'message', message)
      temp.message = 'Saved!'
    } else {
      temp.message = 'Invalid entry'
    }
  }
  return myAction(args.name, args.value)

Call calldb custom action inside “On Receive” Node properties

Next, add a text content type to display the status. If you get Saved! then you are finally saved 😉 (Data got saved into Postgres table srv_kvs)

If you want to save in a custom table then you can use Knex.

Please check the below video which demonstrates both saving and retrieving the entered data from Postgres.


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.

14 Comments

hiii bay · January 31, 2020 at 1:16 pm

I want to get the type of method(get/set) from the user….
Could you say me how to get the both the values at same time like
Bot: name
usr :get
Bot: value
usr: abcxyz

    Simon · February 7, 2020 at 11:22 pm

    @hii bay: If I understand you correctly you need to have 2 custom actions 1 for

    get

    another for

    set

    . Then you can use both getter and setter at the same time.

Luiz Cesar Leite · May 20, 2020 at 7:12 am

Simon,

I first switched from SQLite to Postgres in Botpress following your instructions and everything was fine. I have a Botpress_1291 as my BotPress DB in Postgres, working flawlesslly.

Now I want to save my business infos in another database in the same server.

So, I won’t use srv_kvs as my table in the main DB (Botpress_1291). Instead, I wanna use another table located in another database in the same server.

How can I do it?

Thanks a lot!

Luiz Cesar Leite · May 20, 2020 at 6:33 pm

Hi Simon!

I followed the tutorial about leave SQLite and embrace Postgres and it’s working perfectlly 🙂

But now I wanna use a different DB. Following your tutorial I’m using Botpress_1291 as my Postgres DB for my Botpress v 12.9.1. So far so good.

But I wanna use another DB for my business logic information, like Botpress_lists.

Botpress v 12.9.1 and Botpress_lists are in the same server and uses the same .env file credentials.

How to do it?

Luiz Cesar Leite · May 20, 2020 at 6:34 pm

Simon,

I’m sorry for the duplicated comment: when I get here to see if had writed back I didn’t saw my previous comment.

    Simon · May 20, 2020 at 7:34 pm

    Hi Luiz, when you add a comment it needs approval, because I get a lot of spam on my website. At the moment I am occupied with my other project commitments, that is why I am unable to write new tutorials.

    What do you mean by `Botpress_lists`? You can check this tutorial where a totally different DB similarly you can use it to store your business specific data.

Luiz Cesar Leite · May 21, 2020 at 4:40 am

“What do you mean by `Botpress_lists`?”

I followed you tutorial on how to switch to Postgress and it worked. Botpress is in it’s own database (I named it `Botpress_1291`).

I want to use another database on the same server to store my business infos. Just like:

Server
— Botpress_1291 (where all the Botpress tables lives)
— Botpress_lists (let’s assume that I’ll use a table inside it called doctors_list)

So, in the code
await bp.kvs.get(botId, userId + ‘message’)

I wanna know how to access “Botpress_list” and “doctors_list”, like
await Bopress_lists.doctors_list.get(botId, userId + ‘message’).

Is it possible? Or I must to put the tables inside Botpress primary DB in order to your tutorial?

Hope to read from you son, and hopping you achieve everything in your new project! Success!

    Luiz Cesar Leite · May 21, 2020 at 10:54 pm

    Just for the record: that’s the content of my .env file 🙂

    DATABASE=postgres
    DATABASE_URL=postgres://postgres:mypasswd@127.0.0.1:5432/Botpress_1291

      Simon · May 22, 2020 at 12:06 am

      Yes this is correct.

        Luiz Cesar Leite · May 22, 2020 at 4:41 am

        Thanks for your patience, Simon. You helped a lot!

    Simon · May 22, 2020 at 8:45 am

    @Luiz I do not think you can access Botpress_lists using await Bopress_lists.doctors_list.get(botId, userId + ‘message’) as Botpress KVS is a high level abstraction over the configured Botpress database.

    If you still want to use it using the same technique I think it would be possible to create your custom tables inside Botpress_1291 and instead of kvs here await bp.kvs.get pass your table name. Check the code here, it takes kvs or another table name.

    Incase if you want to keep using a separate database Botpress_lists then use knex to create connection and then perform your DB operations, which is demonstrated here.

    Hope this answers all your question. Let me know if this helps or not, otherwise shoot another question.

Saúl · June 2, 2020 at 6:41 am

Hello Simon, first of all thank you for the great blog, it’s been helping me since I started migrating from FlowXO to Botpress, there’s not that much info out there about it, or at least this clear. I have a question, i’ve been working on this Bot and I hope I can get your assistance since I’m working by myself and I’m super new to botpress and or this working enviroment. My bot does this, it’s primary function it’s to make or record appointments, I have the entire thing running but I need to save the info in a Postgres DB, as you can imagine I have to store a lot of variables like dates, places and emails, okay not that much of them but they’re quite a few, Is this code that you gave us able to retrieve each piece of information that I store?

L. Geyer · July 29, 2020 at 5:50 pm

Hello Simon,

your suggestions really helped me a lot in building my chatbot. 🙂 I wanted to find out what I did wrong. If I apply the code the variables temp and botid are unknown. Also, I get the feeback that the return function has to be inside a function body. Did I miss something somewhere?

    Simon · August 1, 2020 at 12:24 pm

    @Geyer Can you share an export of your bot, I will have a look at it.

Leave a Reply

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