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.
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.
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:
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: " \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.
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: " \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.