Our team is tasked with querying an external 3rd Party database to pull information. We don't own the database, so it could change without notice and our queries would stop working. We are currently storing the DB queries in SQL files in our C# project as embedded resources, and running them as SqlCommands.

This works when it works, but are there any better solutions out there? It was suggested to store the SQL in our DB, so if we ever need to change it, it can be done without a release, but that doesn't seem like the best solution either.

  • "It was suggested to store the SQL in our DB, so if we ever need to change it, it can be done without a release" - you mean without a new release of your client software, but what you need then is a new release of "your DB" (whatever that is, since you did not tell us) and its stored queries. Is that really so much simpler in your situation? – Doc Brown Jan 4 '18 at 19:30
  • @DocBrown - yeah, we would't release the whole app, just update the record in our DB with the fixed SQL. My con against that is that it's harder to debug and maintain the script (just a string stored in the DB) as a column in the database vs an actual SQL file. – Martin Jan 4 '18 at 19:34
  • Maybe you should work on making deployment of new releases of your client software less painful? Then it probably would not matter if you isolate the queries in your application, or in "your DB" (whatever that is, since you still did not tell us). – Doc Brown Jan 4 '18 at 19:48
  • @DocBrown - you're not wrong, but I don't control deployments. Sorry, "my DB" is just a SQL Server database that our team owns. What I mean is just storing the query to execute in a Settings table as a string in a VARCHAR column. – Martin Jan 4 '18 at 19:56

The robustness of an database application system can be increased when the application does only rely on the database schema, but not (or at least not too much) on its content.

SQL needs to be treated exactly as any other piece of source code - it has to be written by a developer, it needs some tests (ideally automatic tests), it has to be versioned, it has to be maintained, and when it breaks is has to be debugged.

Now when you have SQL queries which are tightly coupled to your applications code, so they are designed for working with a specific version of your application, it does not matter if those queries are stored in the database or directly in the application - they are effectively part of the application in both situations. And when you need to deploy a new version of those queries - if they are stored in the database, in a config file or elsewhere - it is effectively as if you deploy a new release of your software: the whole system (applications + SQL) needs a new version number, the system as a whole needs to be tested, maintained and sometimes debugged.

If, for some strange reason, you have in your environment better control over deploying new SQL queries as database content than over deploying new releases of your application, then you might prefer to use this for your advantage. But that is actually not a "smart technical solution" or a "best practice" - that is simply a backdoor for deploying new code without proper release management for something which deserves the latter. And you have to make sure 100% no user, power user or admin starts to fiddle around with those queries. Not necessarily intentional, but what if a db admin restores a backup of the database from last week, with the old queries, whilst your application's version is the one from this week, and both don't match?

If this comes to the worst, you might get a strange support call from your customer why the application is not working as intended, but the version number of the application will tell you nothing about the SQLs your customer has in his database and which might be the cause of the problems.

So my recommendation here is: don't "outsource" parts of the application which are effectively tightly coupled to it to another place, just because that "other place" allows a different release cycle. Better work on making the deployment of new releases of your application (including updates, hot fixes etc) less painful. You will surely have tons of other requirements or issues for which quick deployment of the application in less than, lets me guess, 24h hours, may be benefical. If you solved that problem, there will be no need to create any workarounds like storing SQLs outside of your application any more.

EDIT: Some note on the suggestion of using a web service as an "interface between your application and the 3rd party DB": this gives you indeed better control over the release management, since it allows one to manage releases of the application, the db and the service independently. The service then will not just take responsibilities over SQL queries, but also over the way the system connects to the 3rd party db. If the 3rd party might change the whole DB system or the connection policies, you can solve this without redeploying the application (but by redeploying the service, of course).

However, this does not come for free: you add an additional layer of complexity to your system. Moreover, the whole communication between you application and the 3rd party DB then does not run directly between the the application and that DB any more, but over the web service, which might become a bottleneck. So you need to check carefully if this step from a simple two-tier application to a more complex 3-tier application is really worth it.

  • I agree on the 'not-free' aspect. While it's possible the service can become a bottleneck, you can scale pretty easily as long as your are not concerned with state. Also techniques like caching and pre-fetching in the service layer can be implemented without complicating the core application logic. – JimmyJames Jan 5 '18 at 15:58

I would recommend building a web service or other abstracted API for what you are getting out of this database designed in your own terms. For example, if they have something called an Entity that represents a Person in your design, your API calls it a Person.

Once you have this service or services in place you treat them as the source of the data. The 3rd party DB should be invisible to the rest of your application. Then when the 3rd party changes something, you modify the service so that it continues to function as it was.

As mentioned in the comments on other answers, there are some things you will want to evaluate such as whether you have leverage to get the 3rd-party to implement a stable service. This is an improvement over your situation but you won't control that interface. If you build your own service abstraction, you can more easily replace the 3rd-party. Whether that is likely is a calculation your team needs to make. It's also irrelevant if you don't can't push the vendor to do this.

And to the points that doc Brown makes, I agree in general. Performance can become a problem but if all you are doing is pulling data, scaling should be simple. There are also opportunities that come from implementing the facade to use caching and/or pre-fetching to improve performance.

Further reading:

  • Added some note about this in my answer. – Doc Brown Jan 5 '18 at 6:46

JimmyJames's answer of having the third party expose an abstraction around the raw relational data (i.e. a RESTful API) is definitely the option you should advocate to your team and push forward strongly.

However, if you must query a 3d party database because management has decided it will happen so, the very least you should ask for is that the third party provides dedicated views, stored procedures, table-valued functions and / or a combination of such database mechanisms for this purpose.

There are multiple reasons:

  • Maintainability. As you already mentioned, their actual persistent data structures (tables) may change at any moment. If doing so forces them to change the implementation but not shape of a view that you query, it shifts the burden to where it should be and it will never break your application in unforseen ways.

  • Security. If they take this seriously at all, you will connect to their DB under a user/role that only has read permissions on those particular views.

  • Performance. If they maintain a view properly and, if possible, add indexes to it, you will be able to filter and sort the data by leveraging the RDBMS's powers effectively.

Again, I stress that the preferred solution remains transferring the data in some other way, http(s) being the obvious choice here.

  • To clarify, the service facade to the 3rd party could be built by the OP's team. It would be optimal if the 3rd party would implement a stable interface but often easier to do things yourself than try to push some independent to do something they don't understand. The other advantage is that if they do this internally, they can replace the 3rd party with another 3rd party or their own implementation more easily. – JimmyJames Jan 5 '18 at 15:54

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.