Exploiting OpenAPI to go cross-platform

John Yarham
Feb 4, 2019 · 5 min read
Photo by Jannes Glas on Unsplash

Historically, Redgate has been primarily focused on building tools for professionals working on the SQL Server platform, but this is now starting to change. Specifically, the teams working on SQL Change Automation (SCA) are actively exploring the possibility of building out this key database DevOps capability for the Oracle platform alongside our existing Deployment Suite for Oracle.

Inevitably, this is not a straightforward process. Much of the SCA codebase, and indeed the product’s functionality, is heavily coupled to SQL Server. From the way we provision shadow databases to the generation and deployment of migration scripts, there is a lot to untangle, understand and decouple.

We approached this work by performing a series of spikes, covering the Versioning Engine and its REST API, which exposes the core functionality of the product. Our approach to decoupling the SQL Server specific code and replacing it with Oracle-specific or more generic structures hinged around building out a basic SCA-style Oracle project including migration scripts by calling into the API, following the windy path through the codebase and fixing what wasn’t compatible.

Understanding our REST API

Although we had a reasonable idea of which APIs to start working on, the sheer number of them and the complexity of their relationships meant we needed a jump-start in mapping them. For this we turned to OpenAPI and the Swagger UI by adding Swashbuckle to our project.

This allowed us to visualise our APIs, making it much easier to understand how to call them, what data they required and what they would return than digging directly in the source code would have been. As we expected, the APIs we were interested in formed a kind of chain, whereby the results from one API would be passed into subsequent APIs.

Given this knowledge, we were able to set up a Postman project, taking advantage of its ability to parse response bodies and save values for future calls. This streamlined our workflow into a series of manually-triggered, but automatically-populated API calls, letting us spend more time trouble-shooting. However even this eventually proved too manual for us, so we wrote a Powershell script that handled all the API calls for us automatically.

Benefits of using OpenAPI

Using OpenAPI to understand how our REST API worked was invaluable. We found that actually seeing both our APIs and the models behind them in the Swagger UI made understanding them very straightforward. The ability to test them from the browser with minimal effort was also very cool.

Overall, using OpenAPI sped up our investigation dramatically, meaning that within a week the team had proven the technical feasibility of adapting the existing SCA codebase to implement basic functionality (e.g. auto-generation of migration scripts) against an Oracle database. This was a big step for the team as it validated what we were aiming to do moving forwards.

The development of ASP.Net Core is helping increase the value of OpenAPI documentation, making it simpler and quicker for developers to connect disparate API services and document them in a meaningful and useful way. Some of the key features in recent ASP.Net Core releases aimed at improving how OpenAPI can be used are described below.

Return Types

Prior to ASP.Net Core 2.1, Web API controller methods could return either specific (primitive or complex) types or IActionResult. Since the release of ASP.NET Core 2.1 however, a new return type can be used: ActionResult<T>. This has a couple of key benefits over returning IActionResult:

  • You need less code to tell OpenAPI what object you expect to return.

Previously, in order for OpenAPI to understand what object you expected the API to return, the action method would need a [Produces] attribute, and the returned object had to be wrapped in an Ok().

public IActionResult Get(int id)
var customer = GetCustomer(id);
return Ok(customer);

Now however, the API can specify the return type as part of the ActionResult<T> type. Not only will OpenAPI understand the type of the object returned by the API, but in this scenario, it also automatically understands that the expected HTTP response code is 200.

public ActionResult<Customer> Get(int id)
var customer = GetCustomer(id);
return customer;

As with IActionResult methods, other HTTP responses can be returned if necessary (e.g. return NotFound() if no data is returned from the server). For OpenAPI to understand what return types are possible, the necessary attributes (e.g.[ProducesResponseType(404)]) must be added to the method. Unfortunately, if additional return types are specified for a method returning ActionResult<T>, the [ProducesResponseType(200)] becomes necessary.

More details about these return types can be found here.


As part of ASP.NET Core 2.2, a new nuget package Microsoft.AspNetCore.Mvc.Api.Analyzers was released which inspects controllers annotated with the [ApiController] attribute, and finds methods where not all possible returned HTTP response types have been added as attributes.

The return statements get the IntelliSense treatment, with a squiggly warning underline and a quick refactoring option to add the necessary attribute to the method. This makes it easy to spot and fix places where API documentation would be missing from Swagger.


From ASP.NET Core 2.2, controllers that follow common architectural patterns (e.g. CRUD) can have a standard set of return types and status codes applied to them — a so-called ‘Convention’. These conventions can be custom designed, or ASP.NET Core 2.2 provides a set of defaults that work best with ASP.NET Core 2.2 scaffolded controllers. Using conventions eliminates the need to decorate each method with [ProducesResponseType] attributes.

Conventions can be applied to all controllers in an assembly or individual controllers using the [ApiConventionType] attribute. They can also be applied to individual methods using the ApiConventionMethod] attribute. More specific attributes supersede less specific ones, and the analyzers will also identify where your methods are ‘non-conventional’, providing IntelliSense warnings and refactoring options.

More information on conventions can be found here.


OpenAPI is an increasingly powerful tool available to developers wishing to understand and document the APIs that their software depends on. The ability to visualise and test your APIs in the browser is a valuable technique that can greatly speed development and help organise and document critical interfaces in software.

Ingeniously Simple

How Redgate build ingeniously simple products, from inception to delivery.

John Yarham

Written by

Ingeniously Simple

How Redgate build ingeniously simple products, from inception to delivery.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade