Writing a SQL Operations Studio Extension in 15 minutes

SQL Operations Studio is a modern, extensible developer/DBA tool for building and managing your SQL Server databases. Every month brings new features, and with the March release came a particularly exciting one — extensions! Writing your own is easier than you might think, so I set the goal of building one in under 15 minutes.

The extension: SSMS Keymaps

I’d seen a lot of tweets and other feedback from users wanting to move from SSMS to Ops Studio but struggling with key bindings:

Happily this is something Visual Studio Code ran into and fixed already — they have Sublime, Notepad++ and other Keymap extensions that give users the keyboard shortcuts they expect to “just work”. Since Ops Studio is build on VSCode we can build an SSMS Keymaps extension to do the same thing!

TL;DR — where’s this extension? Can I add shortcuts? How did you build it?

Where is it: You can download the extension here on its Github releases page.

Can I add shortcuts: Contributions are very much welcome — please read the Readme and add your own shortcuts!

How did you build it — here’s the short version:

• I installed some stuff (one time only),
• Ran a template generator that creates an extension project,
• Picked some keyboard shorctuts you want to use in Ops Studio,
• Added them to a special file called package.json,
• Used a tool to package it into a myextension.vsix file,
• Then shared it with the world!

The rest of this post is these steps, just with a lot more details.

Creating a new extension project

We’ll build our extension in Visual Studio Code. If you run into trouble you can read their extension guide — as Ops Studio is built on the same framework, most things should “just work”. To get started, you need a few things:

• Visual Studio Code
• Node.js installed and added to your path
• SQL Operations Studio Debug extension (optional). This lets you test your extension without packaging it and installing into Ops Studio

It’ll take about 10 minutes to install all of the above, so we only have 5 minutes left to get our extension going. Luckily it’s pretty easy!

Install the extension generator

We’ve built an extension generator using Yeoman. To install it, run the following from the command prompt:

npm install -g yo generator-sqlops

This walks you through the steps to create your extension.

Create your extension

Run the following command to launch the extension generator:

yo sqlops

then choose New Keymap from the list of extension types.

Follow the steps to fill in the extension name (in our case, ssmskeymap) and a description and it’ll create a new folder.

Open the folder in Visual Studio Code and you’re ready to create your own keybindings!

Adding a keyboard shortcut

Step 1: Find the shortcuts to replace

Now that we have our extension ready to go, I want to add some keyboard shortcuts (or keybindings) used in SSMS into Ops Studio. I used Andy Mallon’s Cheatsheet and RedGate’s keyboard shortcuts list for inspiration. The top things I saw were missing were:

• Run a query with the actual execution plan enabled. This is Ctrl+M in SSMS and doesn’t have a binding in Ops Studio.
• Having Ctrl+Shift+E as a 2nd way of running a query. Some people complained this was missing
• Having Alt+F1 run sp_help. We added this in Ops Studio but since that binding was in use we put it as Alt+F2 instead
• Toggle full screen (shift+alt+enter)
• F8 to show your Object Explorer / Servers view

It’s easy to find and replace these — run Open Keyboard Shortcuts to show the Keyboard Shortcuts tab in Ops Studio, search for query and then choose to Change Keybinding. Once you’ve done so you can see it in the keybindings.json file (run Open Keyboard Shortcuts File to see it).

Step 2: Add shortcuts to the extension

This one’s easy — I opened up the package.json file in the extension and replaced the contributes section with the following:

"contributes": {
  "keybindings": [
    {
      "key": "shift+cmd+e",
      "command": "runQueryKeyboardAction"
    },
    {
      "key": "ctrl+cmd+e",
      "command": "workbench.view.explorer"
    },
    {
      "key": "alt+f1",
      "command": "workbench.action.query.shortcut1"
    },
    {
      "key": "shift+alt+enter",
      "command": "workbench.action.toggleFullScreen"
    },
    {
      "key": "f8",
      "command": "workbench.view.connections"
    },
    {
      "key": "ctrl+m",
      "command": "runCurrentQueryWithActualPlanKeyboardAction"
    }
  ]
}

Step 3: Test

To test, I made sure sqlops was in my PATH by running the Install 'sqlops'command in PATH command in Ops Studio, and had the SQL Operations Studio Debug extension installed in VSCode. Then I hit F5 to launch Ops Studio in debug mode with my extension running

I opened up a new SQL query, typed in select * from sys.tables and hit Ctrl+M to run it and see the execution plan. And…. tada! It ran as expected!

Success! It’s all working and ready to share. Keymaps are one of the quickest extensions so feel free to make your own!

Packaging the extension

To share with others I needed to package the extension into a single file. This can be published in an extension marketplace or just shared among your team / community. To do this, I first needed to install another npm package from the command line:

npm install -g vsce

Then I ran vsce package from the base directory of my extension. I did have to add in a couple of extra lines to stop the vsce tool complaining:

"repository": {
    "type": "git",
    "url": "https://github.com/kevcunnane/ssmskeymap.git"
},
"bugs": {
    "url": "https://github.com/kevcunnane/ssmskeymap/issues"
},

Once this was done, my ssmskeymap-0.1.0.vsix file was created and ready to install / share with the world!

Publishing your extension to a marketplace

As this is early days, SQL Operations Studio is just rolling out the process for publishing to the Extensions list inside Ops Studio. We’ll be documenting the exact steps in our wiki, and the basic process will be to host the extension VSIX somewhere (a Github Release page is a good idea) then submit a PR changing this JSON file to add you extension info.

I’ll be following this process myself to get the SSMS Keymaps extension published and update this page once I’ve completed it!

Next Steps — contributing to SQL Operations Studio

I hope after reading this you’ll be inspired to build your own extension for SQL Operations Studio. We have support for Dashboard Insights (pretty graphs that run against your SQL Server), a number of SQL-specific APIs, and a huge existing set of extension points inherited from Visual Studio Code.

If you have an idea but are not sure how to get started please open an issue or tweet at the team (sqlopsstudio, me, Alan, Abbie).

Help and documentation are getting written on our wiki, and you can always refer to the VSCode extension guide since it covers all the existing APIs and patterns.