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:
- What would be the most efficient strategy to adapt our existing SQL-based data retrieval systems to work with the new REST API?
- 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?
- 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?
- How can I, along with my team, quickly educate us on REST API interactions to mitigate the risks of this transition?
- 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.