fetching data from postgresql in botpress
# 🤝help
q
I have followed the instructions to add the URL and .env file, but when adding a new server in PostgreSQL, I still encounter the error "Unable to connect to server: connection failed::1), port 5432 failed: FATAL: password authentication failed for user 'botpressuser'." Can you please assist me?

https://cdn.discordapp.com/attachments/1138068471855468554/1138068472027414659/Screenshot_24.png

https://cdn.discordapp.com/attachments/1138068471855468554/1138068472224551033/Screenshot_25.png

a
Hey @quiet-television-21336 if your PG server is on localhost it won't be accessible by services not on your computer. There are two things you can do: 1. Use a managed hosted pg database. I like https://www.elephantsql.com/ or https://neon.tech/ 2. Use a port-forwarding service like ngrok https://ngrok.com/ and set up a port-forwarding web URL that goes to 5432 on your machine
q
It's work! thank you
I used ElementSQL. when i want to access a table in the database it crashes. i have entered the correct password provided by elephentSQL. Can you help me?

https://cdn.discordapp.com/attachments/1138068471855468554/1138419328010698803/Screenshot_26.png

https://cdn.discordapp.com/attachments/1138068471855468554/1138419328253972490/Screenshot_27.png

https://cdn.discordapp.com/attachments/1138068471855468554/1138419328555958332/Screenshot_28.png

a
is ElementSQL limiting the number of simultaneous users? I've never used the service so I'm afraid I'm not too helpful
q
Now I'm using Neon. Botpress has started successfully, and I have also connected to PostgreSQL. I designed a simple bot, but it seems that the data about the bot has not been saved to the database. Can you help me?

https://cdn.discordapp.com/attachments/1138068471855468554/1138688771391504395/Screenshot_29.png

a
Ah! You're using Botpress v12! That makes a big difference. v12 can connect to a locally hosted PG server, you just have to change a few things in the bot.config.json folder. But since you have Neon up and running, why fix something that isn't broken. If you've got firewalls up, you might try making an exception for the BP server, or try running ./bp as a privlidged user. @rich-lamp-98721 so you have any other ideas?
q
if I connect to a locally hosted PG server, what do I edit in the bot.config.json folder
i design a simple bot, where is that data stored?

https://cdn.discordapp.com/attachments/1138068471855468554/1139043524961116180/Screenshot_31.png

https://cdn.discordapp.com/attachments/1138068471855468554/1139043525263114340/Screenshot_32.png

a
We have a guide in our old docs about switching to postgres here http://botpress-docs.s3-website-us-east-1.amazonaws.com/docs/infrastructure/database/
basically you need to change the database_url to
DATABASE_URL=postgres://login:password@your-db-host.com:5432/your-db-name
r
By default the data will be store in the FS. Good practice is to use PG by setting the DATABASE_URL env var and store all the files in the DB using BPFS_STORAGE=database
q
I still don't see in which table the designed data is saved.

https://cdn.discordapp.com/attachments/1138068471855468554/1139512107596005487/Screenshot_33.png

https://cdn.discordapp.com/attachments/1138068471855468554/1139512107830870016/Screenshot_32.png

Can I create a bot from under Postgres or create a bot using the Api without using the Botpress interface?
That's what I'm looking for in data
r
@quiet-television-21336 Bot files are stored in srv_ghost_files
q
Maybe I missed something so I can't get the bot's data yet. Can you help me?

https://cdn.discordapp.com/attachments/1138068471855468554/1139747772560592978/Screenshot_34.png

https://cdn.discordapp.com/attachments/1138068471855468554/1139747772900311161/Screenshot_35.png

hey hey, make some noise
a
hey hey, so what specific bot data are you looking for? There are around 40 or so tables that BP uses in the database
q
I want the data of the bot TH true milk

https://cdn.discordapp.com/attachments/1138068471855468554/1140589584355446834/Screenshot_36.png

a
Like this kind of data?

https://cdn.discordapp.com/attachments/1138068471855468554/1140594082931032144/Screenshot_2023-08-14_at_6.33.03_AM.png

q
yes, can that data be seen under postgresql? And what table is it on?
a
I'm not sure if that kind of data is also saved to the databse. @rich-lamp-98721?
r
The data is in srv_ghost_files are you sure you are connected to the same DB that your Botpress is connected to
q
Now, i see

https://cdn.discordapp.com/attachments/1138068471855468554/1140910323851866113/Screenshot_37.png

can botpress v12 version create bot using API?
can i create bot from under postgresql?
hey hey
hey hey, make some noise
I tried opening a file to edit the content, but I still haven't seen the content change on the botpress interface
a
Yes, the interface doesn't watch for API events, so you would need to unmount/remount the bot to see the changes reflected.
There is an API way to access those files. You'll need to authenticate with a JWT, but once you have that you can do something like this:
Copy code
curl --location --request POST '{ENDPOINT}/api/v1/bots/{BOTID}/mod/code-editor/readFile' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer {AUTH JWT TOKEN}' \
--data-raw '{
    "name":"bot.config.json",
    "botId":"{BOTID}", 
    "type":"action_legacy",
    "location":"bot.config.json"
}'
To read files
and then something like this
Copy code
curl --location --request POST '<ENDPOINT>/api/v1/bots/<BOT ID>/mod/code-editor/save' \
--header 'Accept: application/json, text/plain, */*' \
--header 'Accept-Language: en-US,en;q=0.9' \
--header 'X-BP-Workspace: default' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer <YOUR JWT TOKEN>' \
--data-raw '{ 
    "name":"bot.config.json",
    "location":"bot.config.json",
    "content":"<FULL-BOT.CONFIG.JSON WITH ALL SPECIAL CHARS ESCAPED>",
    "type":"action_legacy",
    "botId":"<BOT ID>" 
}'
To overwrite files
q
Do you have the full api documentation so I can research on other issues as well? Like what above you gave me
a
nope, this API is not really an official API, it's used by internal processes to get things done. We haven't documented it because it is not robust enough for production use- if you send 100 calls per second to these endpoints, it will crash the server. But at smaller doses and for dev work, it should be fine.
You can reverse-engineer a lot of these APIs by looking at the network panel of your browser console while doing things in the Botpress v12 UI. That's how I learned about the above two
q
why every time i change the value of a node the authorization code changes?
a
it shouldn't. The JWT should be valid for at least 30 min
q
Now, I perform node renaming on botpress interface but why postgres side hasn't changed even though I ran command ./bp again
a
not sure what you mean. You renamed a node via API, but don't see the updated results in the database?
q
I edited the token's validity period but why does it change every hour

https://cdn.discordapp.com/attachments/1138068471855468554/1142356172842733628/Screenshot_38.png

https://cdn.discordapp.com/attachments/1138068471855468554/1142356173102784522/Screenshot_39.png

a
interesting. @rich-lamp-98721 got any ideas?
r
@quiet-television-21336 , what you want is edit session.maxAge and not the jwtToken object.
q
I have hand design to create bot using API, every value I enter randomly. So there will be some errors, where can I find this?🧐

https://cdn.discordapp.com/attachments/1138068471855468554/1144473168086569021/Screenshot_40.png

https://cdn.discordapp.com/attachments/1138068471855468554/1144473168321458186/Screenshot_41.png

https://cdn.discordapp.com/attachments/1138068471855468554/1144473168585687050/Screenshot_42.png

Is the "flow" property generated when a node is created on the UI. So with this can I create my own flow? I need the value of flow to be able to create the bot using the API.
a
I'm not sure. It might be easier for you to make the bot manually, then modify its JSON via API for future bots sort of like using a template.
q
creating bot manually is creating on UI right?
a
yes
316 Views