Connect PostgreSQL to your chatbot

With this PostgreSQL integration, you can easily perform read and write queries on your PostgreSQL database directly from your chatbot.

To get started, you need a publicly-accessible PostgreSQL database. All recent versions of PostgreSQL should be compatible with this integration.

Setup

To configure this integration, provide your database connection settings in the environment variables of this App. You will need the host, port, user, password and database name.

Usage

You can perform both read and write queries: any valid PostgreSQL query (that your user is authorized to perform) can be executed. You can use both prepared and unprepared statements, but we encourage you to prefer prepared statements whenever possible!

// example of unprepared statement
do result = App("postgresql", query="SELECT * FROM users WHERE name = \"John\" AND `age` > 42")

// same example, this time with a prepared statement
do result = App("postgresql", query="SELECT * FROM users WHERE name = $1 AND `age` > $2", values=["John", 42])

// always check that the query is successful!
if (result.success) {

  // the returned data is in `result.data`
  say "There are {{result.data.length()}} rows that match the query"
}

You can also write data to your database as easily:

// add a new row with a prepared statement
do query = 'INSERT INTO user (name, email, age) VALUES ($1, $2, $3) RETURNING *'
do values = ["James", "james@doe.com", 74]

do result = App("postgresql", query=query, values=values)

// remove all data from the `users` table
do App("postgresql", query="DELETE FROM users")