sending SQL results periodically to slack

Sometimes we want to notify ourselves or our teammates about an important update based on our business database(s) without a large setup, so we start searching about a very small tool that does the required job with no hassle, as well we want it to be flexible so we can customize the final message and send it to slack easily.

Today I'm happy to introduce sql2slack, it is a very tiny and portable software that just does the job, it allows you to define your SQL query as a job to be executed at a configured schedule as well it works with the major databases out there.

At first, we need to get a slack webhook url,

1. Go here.
2. Click on Create New App.
3. Choose Incoming Webhooks and activate it.
4. Scroll down to Add New Webhook to Workspace and follow the instructions.
5. Scroll down to the webhooks table, and copy the generated webhook URL.

Let's see how we can send the newly registered (within the last 2 hours) users in our MySQL database to a slack channel.

job new_users {
    // set the slack webhook url here
    channel = "https://service.slack.com/xxxxxxx"

    // we're using mysql
    driver = "mysql"

    // the connection string
    dsn = "root:root@tcp(127.0.0.1:3306)/dbname"

    // define the sql query here!
    query = <<SQL
        SELECT users.* FROM users where created_at > DATE_SUB(NOW(),INTERVAL 2 HOUR); 
    SQL

    // schedule it each 2 hours
    schedule = "* */2 * * *"

    // here we recieve the rows from the above query and pass the message to slack with each user name.
    message = <<JS
        if ( $rows.length < 1 ) {
            return
        }

        say("there are (", $rows.length, ") new users!")
        say("users list is:")

        _.chain($rows).pluck('name').each(function(name){
            say("- ", name, " .")
        })
    JS
}

To learn more about sql2slack, you can go to its github repo, and don't forget to star it.