The Open Source Tools of Paste The Plan

If you couldn’t tell, we’re big fans of open source software. We like it so much that we recently released our First Responder Kit, including sp_Blitz, under the MIT license. So when we were building Paste The Plan, our free tool to share SQL Server execution plans, there was no question that we were going to use a bunch of open source software. Here’s the rundown of which open source tools we used and why we chose it.

Open Source Tools In the Presentation Layer

HTML Query Plan – This fantastic piece of code allows the XML query plan to be displayed graphically. If this little gem wasn’t around I’d still be figuring out how to draw a line between plan operators. A big thanks to Justin Pealing for making this available to all.

Bootstrap – Ahh Bootstrap, how do I love thee? Created by the folks at Twitter, Bootstrap is a front-end framework that makes it easy to create great looking websites. Paste The Plan’s tabs and messages utilize Bootstrap. Bootstrap also has a great grid system that makes it easy to position UI elements on the page. At this point, I’m not sure I’d build a website without Bootstrap.

jQuery – It’s the most used JavaScript framework on the web for a reason. jQuery makes writing client-side JavaScript drop-dead simple. If we were writing a JavaScript library, I’d think twice about including it, but since Paste The Plan is a web application it’s inclusion is a no brainer.

Clipboard.js – Clipboard.js allows us to put content in the user’s clipboard thus allowing a one-click copy. We used to have to do this with a tiny Flash application. Thankfully, by using Clipboard.js, Flash-based copying is a thing of the past.

Code Prettify – Reading code is a tedious task. In order to help the reader read crazy amounts of XML we’re color coding the XML using Code Prettify. Created by Google, it does the heavy lifting of making text output more readable. It supports a vast amount of languages (including Mumps and Nemerle if you’re into that kind of thing) and easy to get working.

Bluebird.js – We’re using API Gateway in AWS for the Paste The Plan API. In order to communicate with the PasteThePlan API, we are using code that API Gateway makes available (thanks Amazon). This code is using a new-ish feature called promises. Now, every modern browser supports promises, but wouldn’t you know it Internet Explorer doesn’t, so this is where Bluebird comes in. Bluebird allows older browsers like IE to use promises. Instead of rewriting the communication code to API Gateway, we used Bluebird and had an early lunch.

Open Source Tools in the Application Layer

libxmljs/node-libxslt – For our application layer, we’re using AWS Lambda with Node.js. Node is an interesting language in that, unlike Java or C#, most of your base functionality isn’t baked into the language. In order to parse the query plan XML, we need to either write the parser ourselves or find a library to do it. This is where libxmljs and node-libxslt come in. We’re using these tools to not only parse the XML but to format, to traverse, and to transform the query plan into HTML.

shortId – Generating unique ids are a pain. This is how why we get fun stuff like guids in our databases. Our problem was how do we generate a unique readable id. Shortid creates small, non-sequential, url-friendly unique ids which solved our problem perfectly.

ADM-ZIP – We wanted to use zip compression on the JSON files that we’re storing in S3. This not only minimalizes storage size in S3 but it also minimizes transport size too. ADM-ZIP allows us to compress the JSON object in memory before we save it to S3. Best of all is that it doesn’t have any additional dependencies.

dotenv – We have Paste The Plan installed in multiple environments. In order to let the application know which database to connect to or which S3 bucket to use we’re using an environment file. With dotenv you can declare an .env file and have the application read it at run-time with just one line of code. This saves a bunch of time from having to read and parse the file yourself.

Brent says: just reading this reminds me of why I gave up doing development work. My hat is off to Richie for continuing to challenge himself by embracing new technologies, picking up different tools, and figuring out creative ways to get problems solved. Throughout this, I kept asking Richie, “Do you want to keep going with this, or switch back to the Microsoft stack?” He kept right at it, and I think the result is worth it.

Previous Post
How We Architected Paste The Plan
Next Post Winners and Sample Plans

8 Comments. Leave new

  • Did you do load testing? Or at the very least, in-depth reviews of the scalability of the architecture in the event this thing becomes really popular? If so, what and how?

    • Because of the way Lambda and the whole Serverless concept works we didn’t do any load testing. We did get a spike in traffic on Tuesday in which we turned a few “knobs” and got everything smooth again.

      In terms of processing, the app isn’t really doing much so we weren’t focused on scalability. It’s nice though that we are on an architecture that can scale, with very little effort, when traffic gets messy.

  • Very tidy piece of work. And I agree 100+% with Brent’s comment about why he got out of development — just reading the summary of technologies made my head spin a bit.

    • I just took it one step at a time. This is usually my process for finding and evaluating oss tools:

      Determining the problem are we trying to solve.
      Looking for a tool that can solve the problem.
      Looking for a tool that can solve part of the problem.
      Figuring out how the tool works.
      Determining how the tool will work in our solution.
      Create a POC with the tool solving the problem int our solution.

      On second thought…maybe Brent is the smart one here.

  • This site was added to my toolset almost instantly and to read it is open source based makes my heart sing. Keep up the great work folks.

  • Steve Armistead
    September 15, 2016 1:00 pm

    My head is indeed spinning; this crotchety old dba’s eyes glazed over as I read this post. Wow, my hat is off to you guys – all of you! Thanks for coming up with the idea, with the plan, with the development, and especially with the final deliverable. It’s proving quite handy as I show narly plans to our development teams.

  • NeverWorkForOzar
    September 15, 2016 2:00 pm

    Reading this article and trying to focus on SQL Server makes me so glad I didn’t come to work for your company. 🙂 The recent and unfortunate layoffs reinforced that notion too. However, as a consumer, I love your product and am very grateful for being able to freely consume it. Brent, are you a genius? Well, do I need to ask, really?


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.