NS

Build a Bot using Node and MySQL

There is no excuse for forgetting important dates in your life. Well. we don’t choose to forget, we just do. For people like myself, I spend so much time simultaneously multitasking on my computer, I am easily distracted. We can’t just write everything on that blackboard in the kitchen. However, a simple gesture of kindness can make someone’s day. Let’s see what we can do about solving this problem.

Wouldn’t it be awesome to have a bot in your Slack channel or your Group Me or Messenger or any messaging platform you use, to remind you of things you care about? In this article, we will build a Bot, let’s call it CareBot because it cares for everyone and wishes people ‘Happy Birthday’ on their birthdays or Congratulate them on their work anniversaries or anything to everything else.

Care-Bot

A bot is nothing but an automated group member that can post a message on our behalf to our group, by sending them an HTTP Post request. We will automate the process of doing a post request on the day of a group member’s birthday or work anniversary.

Ingredients

  1. List of important dates
  2. IDE (or vim/e-macs if you are feeling ambitious)
  3. Node.js installed (‘node -v’ should return something in terminal)
  4. BYOS (Bring your own Storage) — MySQL, Dynamo, Textfile etc.
  5. BYOP (Bring your own Platform) — GroupMe / Whatsapp / Messenger / Slack etc.

Recipe

To cover as much as possible and build a scalable system, we will use MySQL as Storage and Promises for async requests. You are welcome to use a Textfile to keep things simple. I will add the code towards to end if you would like to keep it simple and use a Text file.

The first thing we do is create a MySQL database and create a table called birthday.

CREATE TABLE \`birthday\` (  
    \`id\` int(6) NOT NULL AUTO\_INCREMENT,  
    \`name\` varchar(50) NOT NULL,  
    \`birthday\` date DEFAULT NULL  
     PRIMARY KEY (\`id\`)  
)

Add sample record in the table

Bonus: You could also add a new column “post_hook” so you can choose what group or channel you want to send the message to.

INSERT INTO \`birthday\` VALUES('Test', '0000-06-20')

Query the table to fetch all names where the birth date and month match today’s date. That means it is their birthday.

SELECT name from birthday where DATE\_FORMAT(birthday, '%m-%d') = DATE\_FORMAT(now(), '%m-%d')

Alright!! Our back-end is completed. Let’s move on to the Service now that talks to the database.

Create a config.json file to store your database credentials

{  
   "dbhost" : "database-host-name"  
   "dbname" : "database-name"  
   "dbuser" : "user-name"  
   "dbpassword" : "user-password"  
}

How to get Platform APIs to Post Messages to?

Slack: https://api.slack.com/methods/chat.postMessage

GroupMe: https://dev.groupme.com/tutorials/bots

Whatsapp: https://developers.facebook.com/docs/whatsapp/api/messages/

Messenger: https://developers.facebook.com/docs/messenger-platform/reference/send-api/

Now, Let us write a node program that does the following:

  1. Connect to the database
  2. Create a select query and fetch the response
  3. If the query returns something (that tells us today is someone’s birthday)
  4. Map over all the returned records and do an HTTP post to send a greeting in the channel/group.
const XMLHttpRequest = require("xmlhttprequest").XMLHttpRequest();
const mysql = require('mysql');
const config = require('./config');
const pool = mysql.createPool({
  connectionLimit: 10,
  host: config.dbhost,
  user: config.dbuser,
  password: config.dbpass,
  database: config.dbname
});

pool.getConnection( ( err, connection ) => {
  //Use the connection
  connection.query('SELECT name from birthday where DATE_FORMAT(birthday, '%m-%d') = DATE_FORMAT(now(), '%m-%d')', ( error, results, fields ) => {
    //When done with the connection, release it
    connection.release();
    //Handle error after the release
    if ( error ) throw error;
    //Get the list of promises (async requests) for the HTTP requests
    let promises = postMessage(results);
    //Once all the promises have resolved, kill the process
    Promise.all(promises).then((results) => {
      process.exit();
    });
  });
});

postMessage = (results) => {
  //Return a Promise object that resolves when the HTTP request is done
  return results.map( (item ) => {
    return new Promise ( (resolve, reject) => {
      let http = new XMLHttpRequest();
      let postHook = < PLATFORM_API_TO_POST_MESSAGE_TO >
      content = { Content: "![birthday](bday-gif-link) \n" + "Happy Birthday " + firstName + "!!" }

      http.open('POST', postHook, true);

      //This is the callback method that occurs after the HTTP request finishes
      http.onLoad = () => {
        //once we finish the HTTP request, resolve the promise
        resolve(http.responseText);
      }

      http.setRequestHeader('Content-Type', 'application/json;charset=UTF-8');
      http.setRequestHeader('X-Auth-Token', '<You may not need this>')
      http.send(JSON.stringify(content))
    });
  });
}

That’s it! Now our service is ready. All we have to do now is to automate the execution of this function.

Automatic Scheduling using Cron:

Cron is a time-based job scheduler in Unix-like computer operating systems. Users can use cron to schedule jobs to run periodically at fixed time, dates or intervals.

Setting up the Cron Job:

crontab -e //This will open the vi editor  
0 4 \* \* \* //Everyday day at 4 ([https://crontab.guru/)](https://crontab.guru/#0_4_*_*_*)  
Save and Close (Esc and :wq!)

Our Cron Job is ready that will be executed every day. You can use a Cron syntax generator to set whatever cadence you would like. That’s all, your Carebot is ready.

Bonus: Use a Text File to store birthdays and get rid of Promises and db connections

birthdays.txtTom, 4/5  
Harry, 12/4  
....  
....
const XMLHttpRequest = require("xmlhttprequest").XMLHttpRequest();
var fs = require('fs')
fs.readFile('birthdays', 'UTF-8', function ( err, contents ) {
  var birthdays = contents.toString().split('\n');
  const bdayPeople = birthdayToday(birthdays);
  greetBirthday(bdayPeople);
})

function birthdayToday(birthdays)  {
  var results = []
  birthdays.map( (item) => {
    const birthday = item.split(',')
    const name = birthdays[0]
    const date = birthdays[1]
    var newDate = new Date();
    const todayDate = (newDate.getMonth()+1) + '/' + newDate.getDate();
    if (date === todayDate) {
      results.push(name);
    }
  })
  return results;
}

function greetBirthday(bdayPeople) {
  postMessage(bdayPeople)
}

postMessage = (results) => {
  //Return a Promise object that resolves when the HTTP request is done
  return results.map( (item ) => {
    return new Promise ( (resolve, reject) => {
      let http = new XMLHttpRequest();
      let postHook = < PLATFORM_API_TO_POST_MESSAGE_TO >
      content = { Content: "![birthday](bday-gif-link) \n" + "Happy Birthday " + firstName + "!!" }

      http.open('POST', postHook, true);

      //This is the callback method that occurs after the HTTP request finishes
      http.onLoad = () => {
        //once we finish the HTTP request, resolve the promise
        resolve(http.responseText);
      }

      http.setRequestHeader('Content-Type', 'application/json;charset=UTF-8');
      http.setRequestHeader('X-Auth-Token', '<You may not need this>')
      http.send(JSON.stringify(content))
    })
  }
}

That’s all Folks! Now you would never miss anyone’s birthday ever again.