How to Set Up a Slack Bot for SQL Query Generation with tbls-ask-agent-slack

Introduction

A while back, I created a Slack App called tbls-ask-agent-slack. In short, it's a Slack Bot that generates SQL queries based on natural language requests. I introduced it in my company's tech blog (in Japanese), and it gained some attention. However, I didn't provide detailed setup instructions, so I'd like to share a step-by-step guide here.

In this tutorial, we'll set up the bot and try generating queries for a sample database schema right from Slack.

Preparation

First, clone the repository:

ghq get [e-mail]:kromiii/tbls-ask-agent-slack.git

(I use the ghq command, but feel free to use git clone if you prefer.)

Next, create a Slack App:

1. Go to https://api.slack.com/apps
2. Click "Create New App" and choose "From an app manifest"
3. Select your workspace
4. When prompted for the manifest, copy and paste the contents of the manifest.yml file from the cloned repository

Setting Up Environment Variables

You'll need to set three environment variables:

• OPENAI_API_KEY: Your OpenAI API key
• SLACK_APP_TOKEN: Slack App token
• SLACK_OAUTH_TOKEN: Slack OAuth token

Create an .envrc file by copying the sample:

cp .envrc.sample .envrc

Edit .envrc and add your OpenAI API key:

export OPENAI_API_KEY=sk-***

For the Slack tokens:

1. Go to your Slack App's settings
2. Under "OAuth & Permissions", copy the token starting with xoxb- and set it as SLACK_OAUTH_TOKEN
3. Under "Basic Information", copy the App-Level token starting with xapp- and set it as SLACK_APP_TOKEN

Setting Up the Sample Schema

tbls-ask-agent-slack requires a database schema file generated by tbls. For this tutorial, we'll use a sample schema:

cp schemas/config.yml.sample schemas/config.yml

If you need to reference files from a private repository, you can add a GITHUB_TOKEN to your environment variables.

Running the Bot

With everything set up, run the bot:

go run main.go

When you see "Connected to Slack with Socket Mode.", the bot is ready.

Invite the bot to a Slack channel and mention it.

It will prompt you to choose a schema (select "wordpress" for this example).

After a short wait, it will respond with an explanation and the requested SQL query.

Conclusion

That's it! You've successfully set up and run tbls-ask-agent-slack. For those looking to deploy it in a production environment, I've provided Kubernetes manifest files in the repository:

https://github.com/kromiii/tbls-ask-agent-slack/blob/main/manifests/deployment.yml

Feel free to use and modify them as needed. Happy querying!