0

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.

  • 1
    I have not seen yet an universal SQL client purely RESTyfied in the way exposed here. The cons won't outweigth the pros. Not even in the long rub. The overhead will make the development of the clients to be more constrained and complex. In consequence more expensive. You will miss concepts as important as transactionality (among boundaries within the same application) and ACID transactions. Have you considered an RPC approach instead of REST? XmlRPC, JsonRPC, gRPC,... – Laiv Jan 30 '18 at 19:18
  • Have a look a OData.org, which sits on top of REST and allows joins in queries, and multiple atomic updates (i.e. transactions). (Also you might look into GraphQL.) – Erik Eidt Jan 30 '18 at 19:51
  • The idea of the Batch in OData.org may looks like it allows us to implement transactionality, but I'm unsure about that. The operations are unrelated each other. There could be cases where the result of a command has to be taken into account for the next command. And so on. Anyways, after checking out the features, I have to say that looks Interesting. Worth a nothing taking a look. – Laiv Jan 30 '18 at 21:07
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.