external databases
# 👀feature-requests
s
# help Is it possible for the chat bot to query an external databases using API calls
f
Hey, certainly it is possible to do so using an execute card, you have the axios client available ther
t
@freezing-elephant-85019 @straight-yacht-31911 so I can have a mongodb db and botpress to use it ?
l
@thousands-raincoat-37715 yes. You just need your DB to have http-based interface that axios can query. I don't think it comes out of the box with mongodb, but here's an article with a bunch of links on various implementations: https://www.mongodb.com/features/mongodb-rest-api Although a Botpress FR for built-in drivers for the most common DBs sounds like a cool idea.
t
@limited-gpu-21450 so...my botpress can deduct from what user ask him, what query can do ?
l
I don't think I understand your question. Please provide your specific example of the functionality that you need to implement.
t
So…I have in db a collection with cars
And a route for it
Is my api
Botpress can use that route and based on what user asks to generate the write query and pass it to my api
And from there my apt use that query to take the response
Send back to botpres and botpress do smt with it
@limited-gpu-21450
Better now ?
Or instead of having those inputs in fe…car brand color etc etc…user write those criteria like this “I want to see a red car, bmw with 300hp”
Botpress understand this and he takes from it color brand name power and send it to my api
l
(to mods and @straight-yacht-31911: apologies for getting off topic) @thousands-raincoat-37715 1. Feed the customer's query to ChatGPT ("AI Generate Text" node) with instructions explaining your db schema, asking to return query string and inserting user's input. Smth like this:
Copy code
## Introduction:
I need to generate an SQL query for a database that includes information about cars and their manufacturers.

## Database Schema:
- **Manufacturers Table:**
  - `Manufacturer_ID`: INT, Primary Key
  - `Manufacturer_Name`: VARCHAR(200)
  - `Country`: VARCHAR(200)
  
- **Car_Models Table:**
  - `Model_ID`: INT, Primary Key
  - `Manufacturer_ID`: INT, Foreign Key referencing `Manufacturers(Manufacturer_ID)`
  - `Model_Name`: VARCHAR(200)
  - `Price`: DECIMAL(10,2)
  - `Horsepower`: INT
  - `Color`: VARCHAR(50)
  - `Buy_URL`: VARCHAR(200)
  - Relationship: Each car model is linked to a manufacturer through `Manufacturer_ID`.

## Query:
'I want to see a red car, bmw with 300hp'

## Comment:
Please return SQL query without any additional information or comments. If the query cannot be generated because of unclear insturctions or missing information, return 'null'.
2. Send the query to your DB through whatever interface it has. 3. Process the output: handle invalid values, send valid output back to chatgpt to transform into humand-readable format, insert images/links as markdown or whatever you need to do to make it presentable. 4. Display result in a message.
t
You are the God 🍩🍩
Danke
s
@DxD thank you so much ,I would also I like to know if this is possible with querying a relational database built using postgres or MySQL databases
f
@thousands-raincoat-37715 @straight-yacht-31911 Might I suggest tables? https://botpress.com/docs/cloud/studio/tables/
t
??
@thousands-raincoat-37715 is the cars collection you're using live? As in, are you constantly adding/updating rows?
t
Well…I have an api for cars
Admin add cars
Or delete
If a car is gone
f
Does the admin add those cars directly to the database, or is it part of some sort of an inventory system?
t
I have a crud project in next js
My boy just use the api
f
So you have access to the entry to the database when it is added, right?
t
Yep
f
So here's what I'd do - One time - download all data from the DB as CSV, upload it to a botpress table - Using webhook integration, send the row to botpress when you do any crud operation This way your botpress tables will always be in sync with your external db
t
I want to sell it…hard this with csv
Can I create a script to automatic upload it in botpress ?
f
I suggest using tables because of the following reasons - - They're optimized for vector search (natural language) - You can query them with ai filters https://botpress.com/docs/cloud/studio/tables/#using-ai-to-generate-filters - You can use them as KBs
So the first step is optional. If you have a new client who doesn't have any data. You don't need to upload the CSV. I was assuming you have a mongo db table with some data already present. And ofcourse yes, you can also write a script to upload it in botpress
t
Awesome
My mongodb now has some cars…just to test
But when I will sell it, will be empty
f
Great
So you don't need to have the script
t
The client will add his cars and then my bot must help him to sell the cars
Yes
Cuz I will call my api
Based on what user wants
f
They'll have to add the cars manually? Your client might have a db of their own, right?
t
Yes
Manually..depends
My project knows to read csv
Or excel
And get data from there
f
Great. So you can write a script that consumes a CSV that your client uploads. And you can upload the data from the csv to both your mongodb instance and botpress tables
t
Ok
Danke
Do you have a lot of experience with botpress ?
f
All the best with your project
What do you mean?
t
Thx mate
Like you did many bots
f
Well, I work at botpress. We're all bots here
t
Wow
Very cool
I’d like to work there
Must be very nice
f
Unfortunately we only hiring bots at the moment
By the way, you can also use tables instead of MongoDB. This will make sure that you have a single source of truth for cars. Using the botpress client. https://botpress.com/docs/api-documentation/#tables https://www.npmjs.com/package/@botpress/client