So its Easter Friday and I come across this cool on ProductHunt that turns plain English into SQL queries. So something like and it’ll process that and output something like startup “Show users where city is Melbourne ” 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 how many users signed up this week? /db_bot or find the 5 latest users /db_bot 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 (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 seemed perfect for what I needed. Natural Language Processing 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 to learn more. Wit.ai https://wit.ai/docs 3. Now that I my initial phrases trained let’s jump into some . Wit.ai has a gem great! Install 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. Ruby wit-ruby > 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. = .singularize.camelize.constantize@class.where(@target_attribute.to_sym => @search_query) @class @table 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 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. db_bot
