How we built an extension for SQL Operations Studio

Published in

Ingeniously Simple

7 min readMay 9, 2018

We just shipped our first extension for SQL Operations Studio, Redgate SQL Search, in the May release.

SQL Operations Studio is a modern, extensible developer/DBA tool for building and managing your SQL Server databases. SQL Search is a free tool provided by Redgate to search SQL across SQL Server databases.

We would like to share with you what we’ve learned and how we went on to build this extension.

Getting started

When starting to build your first extension there might be a lot of concepts that are new to you.

The first is building Ops Studio extensions. This is very similar to building vscode extensions and there is a lot of literature in this area. We found extending Visual Studio Code walkthrough very helpful.

If you want to know more about how to create an extension you could have a read of Kevin Cunnane’s post Writing a SQL Operations Studio Extension in 15 minutes where he shows how to use yo and generator-sqlops in order to create an extension.

The second is that interactions between the user and extensions are significantly different to Redgate products we’ve built in the past. Now interactions come through the command palette.

SQL Search interaction starts with the Ctrl / Cmd + Shift+ p and followed by SQL Search:

To add commands to an extension you add them to your package.json file.

In the SQL Search extension we expose 2 commands Search and Reindex

Finally SQL Operations Studio is a cross platform database management tool. Extensions are expected to work cross platform. So if you have existing code you want to use in your extension you will need to ensure it runs on all platforms first.

Building a more complex extension

Ops Studio extension skeleton

We created an open source repository to share with everyone how we engineered SQL Search for SQL Operations Studio:

github.com/red-gate/sqlops-extension-skeleton

Is this the way to architect an extension in Ops Studio?

We don’t know, we hope we will get feedback on this and change it to follow the recommended way.

This work was heavily inspired by some existing vscode extension

2. Architecture

In the case of SQL Search and the sqlops-extension-skeleton, we divided the architecture of the extension into 3 components:

Backend
Extension
Frontend

2.1. Backend

The Backend of our SQL Search extension is a .NET console application that wraps the SQL Search core engine.

In the case of the sqlops-extension-skeleton example it looks like this:

├── Backend.sln
└── src
    └── Backend.Console
        ├── Backend.Console.csproj
        └── Program.cs

2.2. Extension

The Extension is the entry point of our extension, it registers the commands that the user can trigger from Ops Studio (search, reindex).

In the case of sqlops-extension-skeleton it looks like this:

├── README.md
├── images
│   └── gatebase.png
├── package-lock.json
├── package.json
├── src
│   ├── extension.ts
│   ├── extensionContentProvider.ts
│   ├── extensionContext.ts
│   ├── extensionFacade.ts
│   ├── extensionUri.ts
│   ├── proxy
│   │   ├── baseUri.ts
│   │   ├── extensionProxy.ts
│   │   └── localWebServer.ts
│   └── versionResult.ts
└── tsconfig.json

It also serves an API using a local express server in order to get and dispatch different actions from the user:

2.3. Frontend

The Frontend is a React / Redux application written in TypeScript and it serves two views, the results view and the object definition view.

Triggering results view and object definition view

In the case of the sqlops-extension-skeleton it is a JavaScript React application:

.
├── package-lock.json
├── package.json
├── public
│   ├── favicon.ico
│   ├── index.html
│   └── manifest.json
├── src
│   ├── App.css
│   ├── App.js
│   ├── http
│   │   └── queryStringParameters.js
│   ├── index.css
│   └── index.js

3. Communication

These 3 components communicate between each other via different mechanisms.

Communication across all the extension parts

3.1 Backend — Extension

3.1.1 Extension side — spawning Backend process

The Backend .NET console application is called by spawning the Backend console using the dotnet command.

Where version() is the following

The runCli function will be the one that spawns the dotnetwith our Backend.Console.dll.

In addition, we need to retrieve the output from the process in order to return the response. Notice how we track data with process.stdout.on('data' and resolve the Promise on close process.stdout.on('close'.

3.1.2 Backend side

Our console application expects a ‘version’ command and writes the version in the console as a JSON object.

3.2 Extension — Frontend

There are 2 ways the Extension and the Frontend communicate. The first one is to serve the views. The second one is to expose a local API for the Frontend to request and trigger actions.

3.2.1 Serve views from Frontend

In the case of our skeleton example this is done by registering a TextDocumentContentProvider

This TextDocumentContentProvider is then requested when the extension sendMessage is triggered.

That is Ctrl / Cmd+ Shift + p and followed by Hello World.

In this case uri is equal to rg://my-extension/myextension?message%3Dhello which matches the scheme that was used to register the TextDocumentContentProvider.

TheExtensionContentProviderimplementsvscode.TextDocumentContentProvider.

But how does this serve the Frontend?

We use a local Express server to serve our Frontend application.

Where this.app is an express application defined as:

And extensionPath is path-to-repo/sqlops-extension-skeleton/extension/out.

We serve the out/frontend as a static folder in our Express application.

Later, when the document is loaded we set the URL to be http://localhost:50364/myextension?message=hello.

3.2.2 Serve API for communication between Frontend and Extension

In order to allow communication between the Frontend and the Extension we also expose a local API for the Frontend to request and trigger actions on this extension.

This is done by using the same Express application we were using in the step above.

In the sqlops-extension-skeleton we define 3 API routes that can be used by our Frontend application.

This is defined in our ExtensionProxy.ts

This will allow the Frontend application to use this REST API how it see’s fit:

Here is an example that fetch the active connections.

Ops Studio extensibility API

We have talked a lot about the extension and how to architect it but we have not talked about what comes with Ops Studio extensibility.

The place to go for information about Ops Studio extensibility would be the sqlops github wiki page — Getting Started with Extensibility

At the moment it exposes a way to interact with two parts of the Ops Studio application, the connection management and the object explorer both of which are used by SQL Search extension.

Here is an example of how to retrieve the active connections in our skeleton example:

Debugging experience

It took us some time to figure out the best way to debug extensions in Ops Studio.

Debugging the Backend

The Backend can be debug with vscode or visual studio there is nothing new here.

2. Debugging the Extension

With launch.json

For debugging the Extension we followed these instructions: Debugging an Extension with VS Code.

You can check out vscode/launch.json in the sqlops-extension-skeleton to see how we modified this to be able to debug the extension.

With Chrome dev tools

Another way to debug the Extension part is with the chrome dev tools:

Install Debugger for Chrome extension in vscode
Open Ops Studio
Open developer tools by writing Developer: Toggle Developer Tools in Ops Studio command palette
Ops Studio should share a URL to open the chrome inspector in Chrome
Go to sources
Expand .sqlops/extensions/yourextension/out and put a breakpoint
You are ready to debug your extension

3. Debugging the Frontend

Because the Frontend is served as a static app in an Express application and it is injected in a frame by Ops Studio we cannot have access to the DOM of the application inside Ops Studio.

You cannot debug extension views in Ops Studio

We also don’t have access to the source map so we were not able to attach to the frontend code. Add to this the fact that Ops Studio (which inherits this behaviour from vscode) does not do a great job at displaying logs in the developer tools console.

We found a work around by exposing in the console the URL where the Express application is served and navigating it with your favorite web browser.

[Extension Host] serving at: http://localhost:55736/myextension?message=hello

We use Chrome to inspect and debug the extensions view instead

What’s next?

We will continue to make the SQL Search extension better, listen to your feedback and find the best way to bring Redgate knowledge into Microsoft SQL Operations Studio.

This article is brought to you by Foundry, Redgate’s R&D division. We’re looking into the feasibility of making our software available in SQL Operations Studio. If you’re considering a switch to SQL Operations Studio or would like to use data tools across different platforms (Windows, MacOS, Linux), we’d like to talk to you — reach out directly to the development team at foundry@red-gate.com