Bot connection to SQL

Hello,

can you tell me how to connect to SQL (or basically any kind of database) if its possible?

I would like the bot open database, search for relevant info and retrieve some data…

Thank you for any advice

Hey @kyon84!

So I’m understanding that you would like your bot to connect to an external database.

You could use the Knex library, which is already included in Botpress.

Can you give me more context on why you would like to connect to a database?

Let’s say user provide chatbot First name, chatbot store it as variable,open database, search for Last name and provide it to user…

@kyon84 As @spgin said, you can use the embedded Knex engine to query any type of database.

You can establish your connection inside an action, a hook or a custom module, execute your query, then use the data any way you want. Example:

  const Knex = require('knex')

  const myAction = async (name, value) => {

    const knex = await Knex({
      client: 'pg',
      connection: 'postgres://postgres:qazwsx@localhost:5432/bp8'
    })

    const users = await knex('srv_channel_users').select('*').where({ channel: 'web' })
    console.log(users)
  }

There’s a list of supported clients here: http://knexjs.org

You can also handle your own backend with an API to answer user queries, then use axios inside actions to query it:

const {data} = await axios.post('/my/api/user_info', {firstname: 'someone')}
console.log(data.lastname)

If you want to have better performances with knex (eg not initializing the database connection for every request), you can use the hook on_server_started to set an API route to serve it, or you can create a custom module.

TLDR: there’s a lot of different ways you can achieve that, it always depends on what you need to do

Hello,

I added action with following code

const Knex = require('knex')

  const getNameByEmailAddress = async () => {
    const knex = await Knex({
      client: 'mssql',
      connection: {
        connectionString: 'Initial Catalog=PeopleList;Data Source=localhost\\SQLEXPRESS;Integrated Security=SSPI;'
      }
    })

    const users = await knex('dbo.Person')
      .select('Name')
      .where({ Email: temp.user_email })
    console.log(users)
    temp['user_name_2'] = users
  }

  return getNameByEmailAddress()

Changed client from pg to mssql and when I try to perform action I get this error:

Can you please advice if my code is ok and how to download and install tedious package?

Thank you.

i think using an api is the easiest work around

can you solved?
Share please.
jose