I dabbled with stored procedures when I was dealing with a problem that required one bit of data in the database to be in sync with another bit of data. Basical

I dabbled with stored procedures when I was dealing with a problem that required one bit of data in the database to be in sync with another bit of data. Basically I had a list of records with time values, and I wanted to have another record store the sum of all those time values. There were lots of records, and writes didn’t happen that often, so I thought a stored procedure that was triggered when a records time value was updated would work well. I wanted to be able to have multiple APIs wrapping the database (one GraphQL layer one that would make sql queries directly and be consumed by an iOS and web app, and another traditional REST one designed for integration with third parties that don’t support GraphQL). I could have either duplicated that logic to sum up the values across those layers or made a shared api later underneath the REST and GraphQL layers (or put the rest layer underneath the graphql layer), but those other options seemed less ideal. I wanted the API layer only to be responsible for sending data to and from the database, authentication and permissions. That app was a side project that I lost interest in, so I never got to see the long term implications of that, but I still think stored procedures were the right call for that particular problem. As much for data consistency and layer responsibility reasons as for performance reasons. The main difficulty with stored procedures seems to be that they’re kind of hard to test and isolate. But when used sparingly, and when you test that the effects your expecting happen on writes (even if you can’t test the procedure explicitly), I think they’re a good way to keep purely data consistency related logic inside the database.