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.

  • 3
    Prepare for the inevitable disaster caused by this stupid executive decision. Switching to a new technology on this scale requires more than a month of time, period. Apart from the sheer amount of code changes you need thorough tests before releasing. Not doable in 30 days. Sep 23, 2023 at 4:58
  • 1
    "The decision is final and nowhere close to being in my power to change." - you will be astonished how quick such decisions can be changed when the production in your company stops and half of your employees cannot work anymore. Just make sure you document you did your best to fulfill your superiors wishes, by leaving a trace of emails, giving early feedback about progress or non-progress (or in short, cover your ass).
    – Doc Brown
    Sep 23, 2023 at 6:42

1 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.
  • Hi, thanks very much for the detailed answer. Just to clarify -- so, you think I should use the repository pattern to write the adapter layer? Also, how could I do integration tests for the adapter? Are you saying to simply write the integration tests now, and then only use them when the API becomes available? The only problem I see is this (and I should have been more specific in the post, editing now): once the API goes live, access through normal SQL is shut down. Perhaps I could write the tests, so it only grabs data between certain dates?
    – javery
    Sep 22, 2023 at 19:29
  • 1
    Yes. And, I don't understand how you could be asking about integration tests. I mean, doesn't the existing codebase already have integration tests, which verify there's N rows or a JOIN produces a certain result? Your existing codebase is unmaintainable if it lacks an adequate test suite. Fix that first, before considering any API interactions. You will definitely need to have some period of overlap where old + new backends are available to your automated tests. Production can have a "flip the switch!" flag day once confidence is built.
    – J_H
    Sep 22, 2023 at 19:33
  • We unfortunately don't have any integration tests, no. I work at a small company where our two software engineers write and maintain code for products. I'm a (junior) data analyst that got caught into maintaining (less, but still important) tools we use to run parts of our production process, so I've never written (or seen) integration tests because most of these tools are poorly put together. It is a mess. The worst part is I have no influence on whether they "flip the switch" or not. I agree with you. Thank you for your advice.
    – javery
    Sep 22, 2023 at 19:43
  • Overall a good answer but I would warn about getting too deep into 'REST' at this point. It means different things to different people. If the team is unfamiliar with using HTTP-based services, that's where I would start. Having a few people on the team who are comfortable with HTTPS/TLS is not going to hurt either.
    – JimmyJames
    Sep 22, 2023 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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