loading...
WayScript

Tutorial: Building a Slack Bot that Writes Custom Queries against your Database

tjmd profile image TJ-MD Originally published at wayscript.com ・3 min read

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:

  1. User submits a slack message containing string that will trigger the desired action.
  2. The string needs to be extracted using regular expressions
  3. The string extracted needs to query our database
  4. 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.

trigger

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.

output

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.

conditional statement

inputs

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.

sql

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

regular expression

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:

regex

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

sql

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.

Slack

Need Help?

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.

Posted on by:

tjmd profile

TJ-MD

@tjmd

Helping WayScript and other developer focused companies launch amazing products

WayScript

A rapid scripting platform for developers. Connect to any database and API and build apps fast in any programming language. Get Superpowers.

Discussion

pic
Editor guide