I'll see if I can dig up any resources

I don’t think we get asked about it very often, especially as changes to our stored procs in databases, were covered with a large suite of unit tests for psql which covered migration paths, but it would still be possibly to say remove a field from a SP that a consumer relied on

It’s an anti-pattern to have multiple systems talk to the same database (because that’s what leads to breaking changes - a schema change). So usually, only one system “manages” the database. It’s an implementation detail abstracted by an API (one over a network boundary or otherwise) In that case, you can test using other means (unit tests and integration tests of the system)

I’ve seen this a few times, where business logic is applications is directly tied to database schemas, and it gets painful, quickly

So in a previous org, we have stored procs in a postgres database, we used PGTap to test all of our migrations would apply, and that our stored procs would work as expected. These were tested in isolation. We would use logical replication, which would cause json messages to be spat out, whenever the DB was touched (app updates, inserts etc) These messages would be picked up by Kinesis -> Lamda (Transforms) -> S3 (Store) They were later ingested by other systems. For the PG -> Kinesis, we would use a test database, with the logical replication setup, and trigger inserts/updates into the db, and check the kinesis handler could process them, in component integration tests. We then separately tested our transform layer, that it could handle the expected kinesis payloads, and convert to the expected format consumers required, who would pick up the transformed data. That is why I wrote the logical replication stuff with Pact, as I could see an opportunity to capture those contracts between each hop, just to help increase the confidence, which retaining the speed of testing these in insolation. We still had integration tests that tested both subcutaneously (tested via API calls) and via the UI, that exercised all the systems moving parts. (We had some external deps, both in the org and third party to deal with as well)

Some of the effort we went through to test locally, didn’t pay off, as some of the for example serverless-offline and localstack, were close but not quite compared to the real infra, so there were some compromises in the confidence, which increased as the project grew in scale. We then switched to considering more ephemeral environments on PR’s so that we could test our changed bit of an application, with all the real infra (things like aurora, serverless and configurable/modular apps meant we could point them to different apps/services ) Separately there was a-lot of work done to consider atomic transactions, and journals of system activities, especially with EDA, as there was alot of failure points and we didn’t want our system transactions in a half enacted state