Welcome to noWHERE: Extending SQL Operations Studio

“Give us the tools and we’ll finish the job!”

Back when SQL Operations Studio went into public preview, I read a lot of people exploring the application and wondering just what (or whom) it was for. Immediately though, a light bulb went off in my head: this probably wasn’t meant to replace SQL Server Management Studio, but rather to complement it. It might not have all the tools you can use to administer a SQL Server, but it does have all the functionality you need to interact with a SQL Server. Think less DBA, more developer and analyst. And what’s there is GREAT: the tools team basically took Visual Studio Code (which I pretty much live in for working with PowerShell) and made a SQL-only version of the shell. It’s not like you couldn’t have SQL-based support in VS Code before, but with support for storing connection information it has a little bit more than your out-of-the-box experience with Code.

There’s three other killer features for these tools though: one, they’re both open source, so you can clone, fork, and contribute to them as development goes forward. Two, both of these editors are built on Electron, which enables them to run cross-platform. Last, but certainly not least (especially in terms of this blog post), both platforms are extendable in the form of extensions. With extension support, you can add in features of different languages (like PowerShell or Python in VSCode) or put new functionality into your shells, like new color schemes or syntax or language parser.

I’m a big fan of these tools, so I wanted to give authoring an extension a shot. I figure I’d start simple and create something that’s actually pretty useful: a simple syntax checker that puts up a warning if you forget to include a WHERE clause with a DML statement.

What’s in an extension?

Extensions in SQL Operations Studio are small, bite-size (ish) packages that add some sort of new feature to your IDE environment. They can be written in JavaScript or TypeScript. Before you scoff and think that you can’t do some really powerful things with it like you can with PowerShell or C#, like, say, load up cool libraries like SMO, fear not: Electron applications are, by design, just Node.js applications deployed via a framework. Extensions in these IDEs are no different: that means you have a huge array of prewritten packages at your disposal via npm (node package manager). That means that there’s a pretty even-money chance that what you’re trying to extend might already have a package you can leverage in your extension (which we’ll see more of in a minute).

The structure of an extension is a folder that lives in your local user data path. Inside that folder are a group of files and folders that make up your extension’s functionality. There’s some boilerplate extensions you can download that give you a basic structure to work with. There’s lots of files that get downloaded as part of the template, but here’s the basic gist:

• The extension.js is a JavaScript (or .ts if you want typescript) file. This is where your code lives. You’ll write your classes in here, and leverage the built-in VS Code node.js modules to interact with the editor. You’ll also define functions to control different hooks, or actions, into the editor’s events, like the user switching tabs, typing in text, or actually executing a command.
• The package.json file is a key:value pair file (hence the .json extension) that ties everything together. It’s essentially a manifest for your extension. It describes what activation events to watch out for and is also used if you publish your extension.
• The node_modules directory contains any node.js modules that you want to use in your extension and you need to redistribute with it. The boilerplate extension you can download comes with a slew (that’s putting it lightly) of node.js modules that you probably won’t end up using in your final extension.
• There are other folders for things like unit testing and folders for editor integration, and some .git files too that you can tailor to your needs. There’s more structure for TypeScript based development, too.

Good artists copy…

So when I sat down to write noWHERE, the concept seemed simple enough: I’ll code some text parsing into extension.js to look at the editor window’s text and look for UPDATE and DELETE statements and then try to see if there’s a missing WHERE clause. Seems reasonable enough, right? Except writing a SQL parser isn’t really something I want to do (or would be ABLE to do, frankly).

Since the module is written in JavaScript, I can leverage node.js modules to do most of the work for me. In this case, I downloaded a module called sqlite-parser. It’s a barebones, no-frills SQL syntax parser. It doesn’t support any dialect-specific stuff for T-SQL, but for this initial project, I only really care about ANSI UPDATE and DELETE statements. This module is great because when you call the function:

var docContent = doc.getText()
var ast = sqlliteParser(docContent)

You pass it in the content of the current editor’s window. It returns an abstract syntax tree (or AST) as a JSON object, which you can just then check for the presence of a WHERE clause or not.

So our extension then just becomes a wrapper for this function: rather than try and write our own parser, we’ll leverage code that already exists. All we have to do is write the handlers for the different events that occur in our editor window, leverage methods to get the current editor text, and parse results and display it to the user in some kind of meaningful way. That might sound like a lot, but by leveraging already-existing open-source modules, we can greatly speed up our delivery.

I once had a mentor tell me, during a High School internship, that any time you want to accomplish a programming task, someone else has probably already done it, and done it better. No reason that can’t apply here, either. Again, this lets us focus on implementing a extension instead of developing a competent parser and extending our editor.

Installing the extension

Have you ever used VS Code? I’ve made no secret of my love for the product in some of my other posts and presentations, and that’s mostly because it’s oh-so-extendable. Installing extensions is easy: you just pop open the extensions tab, search for an extension, and install it. I spend most of my time using PowerShell (and now Python) so I have extensions for both:

SQL Operations Studio is still in preview, so it doesn’t appear that there are any published extensions yet available for it (yet). That doesn’t mean you CAN’T install extensions manually, though. Extensions are installed per-user as part of your local profile on your computer. On a Windows-based machine, you’ll have specific paths where these extensions get installed to. Take my machine as a for-instance, where I already have some extensions already installed for VS Code, and none for SQL Operations Studio:

Check out those two folders: the top explorer window is my local profile for VS Code, which, like the screen shot before this one, shows folders for PowerShell and Python. The bottom explorer window shows my installed SQL Operations Studio extensions (none). That’s where these extensions get saved. If I wanted to manually install the extension, I could just unpack something into these folders. I put my code out on github, so if I go there and download a .ZIP archive, and expand it in my .sqlops/extensions folder…

Once I restart my session in SQL Operations studio, I can see it shows up as an extension!

Putting it to work

So now it’s time to test out my extension. The code is written to look for specific scenarios before applying a parser to the text in the file:

• The document type in the active editor must be either of tyle “SQL” or “MSSQL.” This is determined by the file extension in the editor. If the file we’re working with doesn’t fit that model, the extension won’t attempt to parse it. This is defined in the “activationEvents” part of the package.json file.
• The meat of the extension code has controllers and code that looks for certain events in the editor, specfiically onDidChangeTextEditorSelection  and onDidChangeActiveTextEditor events. When those occur, we call a function to parse the editor’s text and look for any missing WHERE clause statements.

When the function runs, it currently returns text to specify if there’s a missing where clause. I’m using the editor’s bottom status bar to log this:

So in real-time, here’s how it works. Notice that I get the warning in the status bar once I complete my UPDATE statement, and it persists until I add a WHERE clause. My extension doesn’t validate that the WHERE clause is meaningful, just that one exists:

Pretty heckin’ slick. And since the structure functionality of this extension is designed the same way we can design extensions for VS Code, this extension works both places!

More to come

I’m thrilled about the direction SQL Operations Studio is taking, and that the tools team allows us to extend them. I love the concept of extensions for VS Code and the fact that we have the ability to add extensions easily to Operations Studio is huge. Suddenly, everything is in play for any additional functionality you want to include.

My noWHERE project is just beginning. I’m going to continue to enhance and add testing to this module and eventually hopefully publish it for both VS Code and SQL Operations Studio. Make sure you head over to my github repository and check it out.

There are also some things I wish worked a little better too: for example, I can write to the status bar, and I change the text color… but I can’t change the bar color. I think the errors would stand out more if I could change it, but those colors are controlled by the themes. Whether or not that functionality gets added isn’t really up to me, but I’m hoping the libraries get more methods to do stuff like that too.

So what do you think? I’d love to hear your feedback on the project, or an idea for a module you’d like to try and write (or maybe have me help you write)!s