Building a Slack Bot that Writes Custom Queries against your Database

Introduction

Hey everyone! In this one, let's take a look at how we can allow users to query a database, even when they might not know any sql. In this tutorial, we'll build a system that takes a slack message keyword, queries a database using it, and returns a response.

Prerequisites

Some understanding of regular expressions and SQL will be beneficial. Also, here's the documentation that covers the topics we'll use:
Working with Slack
Working with Regex
Working with SQL

Slack Trigger

Our workflow should look something like this:
User submits a slack message containing string that will trigger the desired action.
The string needs to be extracted using regular expressions
The string extracted needs to query our database
The queried value needs to be returned to the user as a slack message.

To start off, we need to look for messages posted into the channel we want these operations to occur in. So we'll need a slack trigger for that.

We're able to configure this trigger how ever we would like. We can choose the workspace and the channel. Additionally, we need to be sure to select the output we want.

In this example, we're selecting the slack message text. This creates a variable below, inside the variables panel, that we can refer to in the next step. This will be what we apply our regular expression to.

Conditional and Regular Expression

For the next step, we only want to return a response if a keyword is found. We'll just use "?" as a trigger to let us know we need to perform an action. We can do this by dragging in an if statement, and configuring it to check if the question mark character is present in the string.

Querying the Database - Regex Pattern

Since we now know when to return a response, let's work on generating that response. The first step is to know what the specific response is that we need. We can extract words from our trigger symbol (?) using regular expressions, and then pass this on to an SQL module.

The regular expression settings in my script look something like this:

The regular expression inserted will just look for the contents following a question mark, and then we can return those contents as a captured group.

Querying the Database - Building the SQL Statement

Now that we've successfully captured the text we want, we can use this to write our SQL statement. We'll drag in an SQL module below our regex module:

In this SQL module, we can write our SQL using that captured group. Your statement might look something like this:

In my example, we're looking for the first instance and returning this value. This is what we'll write back to slack. At this step, there's also the availability to write back multiple outputs from your SQL database.

Writing Back to SQL

Now that we've returned the value back from the database, we can easily write it to Slack using a Slack module.

Questions, Concerns?

If there's any questions feel free to message us on discord. We're happy to help! If you want to see this full script template, you can find it here.