I maintain a relational database of sorts, and occasionally a feature request requires a schema change (eg. add a new column, etc...).

However, a number of "services" consume this data. It's not obvious what will break if data change happens.

What are some good strategies to test for these changes and gain confidence when modifying the db.

  • 1
    What do you know about those services, and how much control does your organization have over them? Are they a "black box" for you, do you have developers maintaining them and can provide information if they are written with forward compatibility in mind?
    – Doc Brown
    Commented Jul 26, 2023 at 17:17
  • You don't test "for" the changes (as in a one-of test run), you keep a safety net of tests on the services that depend on the data and run them frequently... Commented Jul 27, 2023 at 8:09

3 Answers 3


Let us assume the worst case: your services are "black boxes", written by some third party which isn't available any more, and you don't have access to the source code. Then your best option is to test all of them very thouroughly, each and every functionality, with all the available tools, test data and testers you have.

Still there stays some risk some "funny guy" has implemented something like

 if(currentDate>"01/01/2024" && Checksum(schemaMetadata)!=expectedChecksum)

(maybe unintentionally, or intentionally, who knows). So all your tests pass, the software goes into production shortly before end of 2023, and now guess what happens in 2024!

So my point is: to get confidence by tests, you need to make at least some assumptions of good will by the original developers. And the more you know about the development history, design of the services, and how much the devs cared for forward compatibility, the less effort you can invest into testing.

Ideally, when you add some columns to existing tables with default values on the DB side, or add some tables with no relationships blocking changes or deletions which were formerly possible, you shouldn't be forced to test any of those service beyond the bare minimum. Also, when you know for sure that certain services don't access certain tables, views or stored procedures at all, you may be able to exclude them from testing when those tables, views or stored procedures are not touched.

In reality, there is often a middle ground between "there are no tests needed" and "no amount of testing will prevent the software breaking in production". But where this is depends heavily on the case and cannot be answered sensibly out of context.


Shielding data access

However, a number of "services" consume this data.

Your situation is the gold standard of examples for why this advice exists:

A database is an implementation detail that should be hidden behind a singular service. No more than one service should access the database. All other services, if they need access to this data, should talk to that one service which has access to the database.

For the continuation of this answer, "the service" refers to that one service with access. I will refer to all other services as "consumer".

There are several benefits here:

  • The database schema can be designed by a single service and does not inherently break consumers, as long as the service can still map the new database schema to the old contract.
  • The logic layer that is now inbetween the database and consumer can be used for access control and auditing purposes. While database servers tend to allow for some of this, it's not as flexible. The service's logic layer can run any arbitrary logic that you might want, which is a really good platform for injecting middleware into the data storage/retrieval process.
  • The service's API can more easily be versioned than the database structure. Yes, this could technically be done using views in the database, but there's more limitations on how to map the source data to these versioned views.
  • Versioning the service API means that any breaking changes can be put into a new version, therefore not breaking any existing consumers.
    • If and when necessary, the old version can be phased out and removed eventually; but at least your consumers have a grace period where both versions are available so that they can upgrade their logic on their own (reasonable) time.
    • Otherwise, the old version can live on indefinitely - there is no real cost attached to keeping it around as long as it doesn't hinder future version design.
  • If tomorrow you decide to change your persistence technology (e.g. moving to an Azure blob, or a file server, or a new database stack), the consumers don't all have to learn how to use this new technology. The service is the only codebase that needs to implement the new tech; the consumers can still talk to the service using the same channels that they used before.

I hope you see that this is vastly superior to providing direct database access to everyone.

Note that even if you don't need all of these features, it's trivial to implement a passthrough service that simply provides access to the data. It's not a significant extra effort to do so, and it ensures that if any of these bullet points become relevant for you in the future, you just have to update your service rather than having to redesign it from the ground up.

Knowing if a change is breaking

Your question also implies that breaking changes must invariably be tested for. While I am in favor of testing in general, especially regression testing like this; I do want to point out that I generally expect developers to be able to distinguish a breaking change from a non-breaking one without needing to run the tests.

Non-breaking changes include:

  • Adding an additional endpoint to the API
    • Somewhat obviously, in a way that does not invalidate or conflict with prior endpoints (I'm thinking of route collisions here)
  • Adding an additional optional parameter to an endpoint
    • The default value of this parameter should cause the endpoint to behave the way it did before.
  • Adding an additional property to a model
    • If this is an input model, it needs to be optional and its default value must lead to the endpoint working like it did before.
    • If this is an output model, it generally doesn't matter that you have more properties that the consumer is not aware of.

Breaking changes include:

  • Removing endpoints from the API
  • Removing properties from any models
  • Changing the route for an endpoint
  • If you provide a rerouting option so that the old route still directs to the endpoint, that makes it non-breaking.
  • Changing the type of a property on a model
  • Changing the name of a property on a model
  • If your response model is typed (i.e. the consumer uses the type/class names in their logic), any change made to the type (class) name is breaking. This is usually not the case e.g. for JSON.

This list is not definitive, it's off the top of my head. The general idea of a breaking change always boils down to the same question:

If I make this change, and I tell no one, will my consumers' runtime suddenly break or behave differently?

If yes, it's breaking. If no, it's non-breaking.

  • 1
    It is easy "on paper" to suggest an asker a full architectural and organizational (!) change to solve their problems - however, I guess the OP is more looking for something which they can do now, with their existing architecture.
    – Doc Brown
    Commented Jul 27, 2023 at 8:41

You need an api layer between the consumers and the database.

This could be a program like a REST website, or if the programs are directly connecting, "old skool style", you could limit their access to a set of stored procedures or views.

Once you have this layer in place, you can change the database schema and alter the api layer so that it still returns the same format of data. ie change the sprocs/views so the dataset returned still has the same columns

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.