So its Easter Friday and I come across this cool startup on ProductHunt that turns plain English into SQL queries. So something like “Show users where city is Melbourne” and it’ll process that and output something like
SELECT * FROM users WHERE city = ‘Melbourne’;
I thought this was just the greatest and had to find how all this worked. Non-technical people at my organization could really use something like this. Imagine going into Slack and going
/db_bot how many users signed up this week?
or
/db_bot find the 5 latest users
So my goal by the end of the day turned into How to make a bot that talks to my Rails database.
- The first thing I would need to do was research Natural Language Processing (NPL). You can basically apply Machine Learning to a phrase and extract meaning. It’s all the craze at the moment so I thought surely there has to be a nice free library out there. After weighing up a few options Wit.ai seemed perfect for what I needed.
- Wit.ai is an interface where you can train your bot with the statements and phrases you’d like your bot to understand. It was actually incredibly easy to get started. Have a look below at what I did but essentially I assigned relevant words in my phrase meaning, meaning that will later be returned as attributes. Have a look at their docs https://wit.ai/docs to learn more.
3. Now that I my initial phrases trained let’s jump into some Ruby. Wit.ai has a gem great! Install wit-ruby and jump right in. It’s really easy to use. Send a message and get back a hash of all the entities you setup in the previous step. Using all this data is enough for us to construct our database queries.
> Wit.message('Find users where name is Allison')=> {"verb" => [{"confidence" => 0.9961181593747102,"type" => "value","value" => "return"}],"table" => [{"confidence" => 0.9972328497408993,"type" => "value","value" => "users"}],"target_attribute" => [{"confidence" => 0.9919267848022522,"type" => "value","value" => "name"}],"search_query" => [{"confidence" => 0.9543589657183698,"type" => "value","value" => "allison","suggested" => true}]}
4. The rest is fairly straight forward. You assign the entities to variables, apply a little magic and viola you have your first query.
@class = @table.singularize.camelize.constantize@class.where(@target_attribute.to_sym => @search_query)
Here’s example of one I prepared earlier. You can find the source code for it here on Github
> bot = DbBot.message('How many users signed up this week?')> bot.response=> "5 users"> bot = DbBot.message('Find the last 10 users')> bot.response=> "There you go"> bot.collection=> #<ActiveRecord::Relation [#<User id: 1...
There’s still a lot that needs to go into db_bot but it’s the foundation for what I’d eventually like to power a Slack bot that our team can use to extract data out of our databases.