If you haven’t heard of Slack, it’s a wonderful team messaging platform. At first glance, it looks a bit like a private Twitter where you can set up “channels” to have conversations with colleagues. But the great thing with Slack is its flexible API and all the marvelous ways in which you can extend its functionality. You can send rich text messages with status updates from your production servers or you can interface with popular web services like Trello, right there in the chat window!

So as a way to kick start an effort at learning node.js and re-discover web development (which I haven’t really done in about 15 years now), I set out to build a Slack API. Here’s what I learned.

The plan

I frequently find myself googling reference documentation on MSDN for the syntax on common T-SQL statements. Not being a DBA proper, I can never ever remember the RESTORE DATABASE syntax for instance, and I can’t really find my way around all the ALTER DATABASE options by heart.

I wanted to build a “slash command” in Slack that will serve up a syntax reference for a T-SQL command, with a link to the full MSDN article on the subject. Slash commands trap messages that start with a slash followed by a keyword, in this case “tsql”, so the following command..

/tsql update statistics

would return something like

UPDATE STATISTICS table_or_indexed_view_name   
    [   
        {   
            { index_or_statistics__name }  
          | ( { index_or_statistics_name } [ ,...n ] )   
                }  
    ]   
    [    WITH   
        [  
            FULLSCAN   
            | SAMPLE number { PERCENT | ROWS }   
            | RESAMPLE   
              [ ON PARTITIONS ( { <partition_number> | <range> } [, &n] ) ]  
            | <update_stats_stream_option> [ ,...n ]  
        ]   
        [ [ , ] [ ALL | COLUMNS | INDEX ]   
        [ [ , ] NORECOMPUTE ]   
        [ [ , ] INCREMENTAL = { ON | OFF } ]  
    ] ;

Programming node.js

node.js, like the name implies, is a light-weight programming language built on the Chrome Javascript V8 engine. Its strength, apart from being really easy to understand and write, lies in the vast collection of “packages” (libraries) that you can download and install with a single command line statement using a package manager like npm. Want to write an e-mail interface? Somebody  already thought of that – it’s probably like three or four lines of code. Web server? Same, there are quite a few of those around. Text parsing? File management? Database queries? Flow charts? There’s literally a package for everything.

And it’s so much more light-weight than, say, .NET or Java development – you really don’t even need an IDE if you don’t want it, although I’ve grown to like Visual Studio Code (which incidentally works beautifully on OS X).

Hosting

I went with an Azure web app. Turns out Azure web apps support node.js nearly out of the box with just a few steps, so no complicated installs necessary. And to deploy my code, I just push to a Git repository (also on Azure) and it automatically deploys to the web server.

The only headache I had was debugging. Somewhere, somehow, there’s a way to debug code on the web server on Azure, but I never really figured out how. And not even being able to see error messages when your app crashes kinda sucks, to be honest.

What the API does

The API has to do two things: perform an OAUTH authentication, and respond to API queries. Slack uses OAUTH to authenticate that a user really does want to add the app to the Slack team. In essence, this happens in three steps. Slack sends a web request to your API, the API server then requests an OAUTH token from the Slack service (using a secret key), and finally returns this token on the original Slack connection.

As for serving up the T-SQL reference documentation, it’s just a matter of opening static text files in the web server directory and passing them with some encoding to the client. That’s it.

All of these exchanges are JSON encoded, which is trivial to work with in node.js.

Slack review

Slack does a review of new apps before they’re published in their directory. There are quite a few criteria you have to meet, including a privacy policy, support page, contact information and a lot of other stuff. Reviewing my app took about two weeks from start to finish. The reviewer was very friendly and professional and I got a few good pointers to improve the app.

So go on, install it

So go ahead! If you use Slack, give the T-SQL Reference app a try and tell me what you think.