How we built an extension for SQL Operations Studio
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.
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
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
p and followed by
To add commands to an extension you add them to your
In the SQL Search extension we expose 2 commands
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:
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
In the case of SQL Search and the sqlops-extension-skeleton, we divided the architecture of the extension into 3 components:
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:
The Extension is the entry point of our extension, it registers the commands that the user can trigger from Ops Studio (
In the case of sqlops-extension-skeleton it looks like this:
│ └── gatebase.png
│ ├── extension.ts
│ ├── extensionContentProvider.ts
│ ├── extensionContext.ts
│ ├── extensionFacade.ts
│ ├── extensionUri.ts
│ ├── proxy
│ │ ├── baseUri.ts
│ │ ├── extensionProxy.ts
│ │ └── localWebServer.ts
│ └── versionResult.ts
It also serves an API using a local express server in order to get and dispatch different actions from the user:
The Frontend is a
Redux application written in
TypeScript and it serves two views, the
results view and the
object definition view.
In the case of the sqlops-extension-skeleton it is a
│ ├── favicon.ico
│ ├── index.html
│ └── manifest.json
│ ├── App.css
│ ├── App.js
│ ├── http
│ │ └── queryStringParameters.js
│ ├── index.css
│ └── index.js
These 3 components communicate between each other via different mechanisms.
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
version() is the following
runCli function will be the one that spawns the
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
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 is then requested when the extension
sendMessage is triggered.
p and followed by
In this case
uri is equal to
rg://my-extension/myextension?message%3Dhello which matches the scheme that was used to register the
But how does this serve the Frontend?
We use a local Express server to serve our Frontend application.
this.app is an express application defined as:
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
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
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:
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
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 Toolsin Ops Studio command palette
- Ops Studio should share a URL to open the
chrome inspectorin Chrome
- Go to sources
.sqlops/extensions/yourextension/outand 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.
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 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 email@example.com