Developing SQL <-> REST Adapter

Question

This is a very conceptual question and looking for advice or examples you may know about. I'm just getting back into development after a very long hiatus in a sales career, so please excuse me if my thoughts or questions seem simplistic or outdated.

The data for a very large number of implementations is moving off-site from an SQL server to be accessed by a very few specific RESTful APIs. Rather than spending the man hours redeveloping each iteration of the data access to REST GET requests, I wonder if there's a better way. My idea is to develop (or find) an SQL layer that (invisible to the client) adapts the SQL queries into REST requests and returns the data in SQL format. All then, the client would need to do is switch it's SQL source to the new "SQL service" and automatically retrieve the data from the REST API formatted to suit the application.

This may not be possible to create a universal REST API translator because the data schemas are so different between JSON/XML and SQL generally speaking, but I wonder if because the APIs I am accessing use the same data schema if something couldn't be developed for my specific implementation. Perhaps I would need to tweak this adapter layer application for each client's nuances in their SQL queries, but maybe this would save development time for each client not wanting to rush to total redevelopment of their platform.

I wonder if anything like this has been accomplished before, is even possible, and what issues lie ahead. Is this too much of a band-aid that will break down with data changes? Could this be lightweight enough to work without significant slowdowns? Any advice or thoughts are greatly appreciated.

Answer 1

This is not generally possible. REST and SQL have completely different concepts.

• REST is about resources and state transfer.
• SQL is about queries on relational data.

There is some overlap though: both deal with data and can deal well with CRUD (create–read–update–delete) workflows.

The core difference is that SQL allows you to specify queries in an ad-hoc manner, and can join multiple tables. In contrast, REST doesn't allows you to perform complex queries on the datamodel. If you create a SQL-to-REST translator the results will probably be very inefficient (e.g. joins would have to be performed client-side, or be provided by a dedicated resource).

A sensible migration plan will therefore get rid of this difference first, by restricting all clients to a fixed set of prepared queries. These should be encapsulated in some library, e.g. using the Repository Pattern. This step will also help you gather correct requirements for the REST API.

Once the clients have moved to that library, you are free to change the internals. You can then develop a replacement with the same interface that doesn't use SQL queries but accesses the data through a REST API. This library is then a drop-in replacement. The API server may continue to use the SQL-based repository for some time.

Note that such a migration is really hard unless you control all API clients. If other teams or other organizations built these clients, they will have little incentive to upgrade – can't you just keep the SQL server running for them? This is therefore less of a software engineering challenge, and more of a political problem. The easiest solution (if you have the necessary political capital) is to present a long-term migration plan where the SQL access will be switched off at some fixed date, with enough prior time to migrate. But there's probably some business-critical legacy client that no one understands and can't be updated so you'll be forced to keep the SQL access running forever, which calls this whole migration into question. So, maybe check for that first.