Error handling in the SQL Change Automation SSMS extension

SQL Change Automation has seen a lot of change over the past year or so. It started out as a Visual Studio extension called ReadyRoll, which was then merged with DLM Automation. The resulting product was then rebranded into what we now call SQL Change Automation.

The Versioning teams are now developing a SQL Change Automation extension for SQL Server Management Studio, and there are plans for another extension in Oracle. This means that the core SQL Change Automation technology is going to be exposed in four completely different places — a Visual Studio extension, PowerShell commandlets, SQL Server Management Studio, and Oracle!

Considering our APIs

Needless to say, this has caused us to think carefully about our APIs. At the core of SQL Change Automation is what we call the ‘Versioning Engine’. The Versioning Engine is responsible for the core functionality of the product, such as creating migration scripts, and deploying databases. We wanted a way of cleanly decoupling the Versioning Engine from client code, starting with the SSMS extension.

To achieve this decoupling, the Versioning Engine runs in a separate process on a HTTP web server. The web server exposes a REST API which calls down to the Versioning Engine. The SSMS client can then communicate with the Versioning Engine by proxy via the REST API exposed by the web server.

This means we can create a clean API for clients to use the without having to refactor the API of the Versioning Engine itself. It also means we can create a client for SQL Change Automation in many new contexts.

Handling errors

This approach has not been without its challenges. One such challenge is how we communicate errors from the Versioning Engine to clients that are accessing it via the web server.

In the Visual Studio Extension, error handling was straightforward. We accessed the Versioning Engine library directly, and just caught exceptions it threw in the normal C# way.

However, with our new web server architecture, the SSMS extension has no direct access to the Versioning Engine library. Any exceptions thrown by the Versioning Engine are therefore lost in the bowels of the web server. If an exception is thrown, the server may return some reasonable error code in its HTTP response (e.g. 500 Internal Server Error). Yet that doesn’t really give the SSMS client any useful information to display to the user.

Sending errors from the web server

The first step to fixing this problem was to decide on how errors ought to be communicated from the web server. We had a couple of options:

• Serialize the C# exception objects and send them with the HTTP response
• Create a bespoke error object, defined as part of the REST API

We settled on the second option since it allows us to make our API easier to consume. The obvious format was JSON — but what format should a JSON error object take?

It turns out Microsoft have also thought hard about this, so we decided to follow their advice as laid out on their REST API guidelines document (https://github.com/Microsoft/api-guidelines/blob/vNext/Guidelines.md#7102-error-condition-responses).

Following the Microsoft guidelines, we decided that an error object must have two mandatory properties:

• Error code
• Message

The error object can also provide other properties if necessary. The last step on the web server was to convert exceptions thrown by the Versioning Engine into JSON objects that abide this format, and send them with the HTTP response.

Consuming errors in the client

Next we had to figure out a way of turning these error objects back into something useful in the SSMS client. We decided that when the client receives an error object on a HTTP response, it should throw a new exception. We use the data in the error object to construct the exception, and the exception can then be handled in the normal C# way (i.e. with try/catch).

With this approach clients have no idea about the original Versioning Engine exception. As far as the client is concerned, the web server has just included an error object as part of the HTTP response body.

We like this approach for the following reasons:

• The JSON error object is simple, yet flexible enough to communicate plenty of error information
• JSON objects are easily mapped to plain domain objects (POCOs, POJOS, etc). which means clients can easily consume the errors
• We use ASP.Net Core for our HTTP communication, which makes serialization/deserialization of JSON simple
• Errors defined by the REST API are decoupled from specific exceptions produced by the Versioning Engine
• This means we can freely refactor the Versioning Engine’s error API without making any changes to the way clients handle errors

The work is ongoing, and we’re just getting to the point when we’re surfacing errors in the UI using these error objects. We anticipate there will still be a few things to iron out when as we put this into practice. Nonetheless we’re confident about this approach, so we’re planning to submit an engineering-wide Architectural Decision Record to document it.

If you’re interested in trying out the SQL Change Automation SSMS extension, we’re looking for participants in our Early Access Program. Find out more at www.red-gate.com/sca-eap