Strategies for Adapting SQL-Based Data Retrieval to a Newly Introduced REST API in a Short Timeline

Question

I'm in a pickle following a recent executive decision by our parent company. They have elected to abstract away our SQL data warehouse, transitioning to a REST API for data retrieval. The purported aim is to streamline data access, though, as anyone previously accessing the data warehouse will now have to make web requests to obtain data. The decision is final and nowhere close to being in my power to change.

Our team has no experience with REST APIs or their usage. We have approximately 200 programs that currently make calls to the data warehouse, and all these will need to be altered to make API calls post-transition. My initial thoughts have been angled towards the development of an adapter package that could mediate our SQL transactions and queries to this new REST API. However, as some preliminary research has indicated, this wouldn't be a straightforward task. With this adapter approach, queries with joins or anything more complex will make applications significantly slower since they'd need to be processed client-side after getting data from tables.

Additionally, we don't trust the parent company to properly ship this API. It's unclear whether their implementation will maintain A.C.I.D. compliance. There have been related issues in the past.

I've been tasked with the responsibility to ensure a smooth operational transition for all of our internal programs once the REST API change goes live, which is scheduled to go live one month from now. Once the API goes live, all access to the database via SQL connections will be shut down completely.

I'm also not sure if this matters, but all of these programs that need to be adapted are written with either C# or VB .NET.

Given the nuances of this scenario and the short timeline:

1. What would be the most efficient strategy to adapt our existing SQL-based data retrieval systems to work with the new REST API?
2. Are there established patterns or practices for creating an adapter from SQL transactions to REST API calls, especially in a manner that minimizes the client-side processing overhead for complex queries?
3. What considerations should be kept in mind to ensure that the adapter or transitional solution is as maintainable and scalable as possible, given the limited time for implementation?
4. How can I, along with my team, quickly educate us on REST API interactions to mitigate the risks of this transition?
5. Is there a way to ascertain the data consistency guarantees of the new API before it goes live, to pre-empt potential issues?

I apologize if this is a repeat post, and for my ignorance on the subject. I saw this post, which was very informative, however, as I stated above, I nor anyone I can influence has power to change the migration plan. I also saw an idea to implement the Repository Pattern, which seems like a great idea, but I lack the expert foresight to see the pitfalls of using it.

Your insights and advice would be incredibly valuable in navigating this situation. Thank you in advance for your time and expertise.

Answer 1

Definitely write a middle adapter layer, which accepts app requests and retrieves corresponding DB results.

Start doing it right now, on your most important apps. Let the app's needs dictate what you implement. Add to the layer as you work through the apps and discover additional needs. Write automated integration tests which demonstrate that "new" app results are identical to "old" direct DB access results.

Surely you roll new DB credentials every 90 days or so. Roll a new password and deliberately avoid giving it to the first app that you believe is "done" and that is using the new layer. This will cause any direct connection attempts to fail, bringing to your attention the few bits of code you missed on your first pass. Make the new password available to just the new layer's code. Verify that an automated system test of the app produces identical results under both the "new" and "old" access approaches. Using what you've just learned, move on to maintenance of additional apps.

Some weeks from now when one or more API endpoints become available, teach the adapter layer to use them. Verify that automated tests continue to produce identical results. Bug report to the API owner the various discrepancies which you will inevitably notice.

---

When you get a moment to take a breather, instrument the adapter layer to record things like query latency (in seconds) and app-level throughput (in rows per second). Consider measuring two elapsed timings: seconds to obtain first row, and seconds to obtain last row. Compare performance against old and new backends. Decide what sensible performance targets are, and repair any code that does not meet them. Some of that code will be owned by you, and some of it will be owned by the external API team.

Numbers don't lie. A complaint of "it's slow!" doesn't constitute a bug report, but observations that a query went from 1s to 5s will be useful for prioritizing a fix.

---

Create database dumps (SELECT * from all tables) of the source dataset Right Now. Restore those rows in a test environment. Keep performing such dump / restores nightly, so you're confident you have a good backup which your production users and test cases can successfully run against. Retain the last 2 or 3 days worth of dumps.

Continue making dump / restores up until the parent company cuts off access as part of the cutover. Your automated tests will be able to access that frozen snapshot in the coming months.

---

1. most efficient strategy? I outlined an approach above. It lets you show concrete progress immediately, and gives a clear burn-down list of tasks to accomplish.
2. established patterns? If this was a python code base I would advocate turning any raw SELECT queries into sqlalchemy queries. And then either turn those queries into an adapter layer, or perhaps create a new API-based backend for sqlalchemy to call when rendering and executing a query. Given your C# codebase, the alchemy sharp ORM layer may prove useful.
3. maintainable and scalable? I outlined a very incremental approach above, that lets you keep using "old way" in a subset of your code and that lets you easily compare old / new behavior. Timing instrumentation will offer early alerts if a certain implementation causes a performance regression. Some important queries will be a JOIN which extracts < 1 % of rows from table A and < 1 % of rows from table B. Sending lots of ultimately discarded rows for a client-side JOIN makes no sense. So prioritize creating a new API endpoint when you find such a JOIN which is performance-critical. Or maintain a local cache of such tables, if their contents don't have to be absolutely up-to-the-second for useful app results. To cache an append-only table, consider adding a new API endpoint which delivers "all rows since timestamp T".
4. educate? A crash course in REST principles is outside the scope of this question. Google is your friend. There are numerous resources online.
5. ascertain data consistency guarantees? I outlined automated integration and system tests that compare old / new results. We can leverage the fact that old / new results must be identical. During this time of transition it is imperative that you not allow feature drift, where "new" offers some novel bit of functionality which "old" won't implement. Only after you build confidence and then "flip the switch" to "new" can you allow novel features to be built out using just the "new" approach.