In Defence of Stored Procedures

I wrote 25+ DB2 stored procedures supporting Letter of Credit and Amendments workflows for enterprise banking deployments. By the standards of most modern backend advice, that sentence describes a mistake. Business logic belongs in the application layer; the database stores rows and stays out of the way.

I think that advice is correct far more often than not, and I think it was wrong here. The interesting part is why.

'Keep logic in the app layer' quietly assumes your application is the only thing talking to the database, and that you get to choose the database. Neither held. The system was DB2, chosen years before I arrived, with Java middleware in front of it and other consumers besides the service I was working on. The database wasn't an implementation detail of one application — it was the integration point between several.

When multiple consumers need the same Letter of Credit logic, you get one of two outcomes. Either the logic lives once, next to the data, and everyone calls it — or it gets reimplemented per consumer, and they drift. In a domain where the consequence of drift is two systems disagreeing about the state of a financial instrument, that's not a stylistic preference.

An LC amendment touches a lot of tables. Done in the application layer, that's a sequence of queries, each a network round trip, with the app assembling results it mostly doesn't need in order to produce a small answer. Done as a procedure, the work happens next to the data and one result comes back.

This is where most of the performance work landed — optimising multi-table joins and query execution plans, which brought average API response times down by roughly 35%. That gain was available precisely because the logic was close enough to the data to be optimised as a unit. Spread the same work across a dozen application-level calls and there's no execution plan to tune; there's just a lot of network.

Banking systems get audited. A stored procedure is a database object with controlled deployment, defined permissions, and a change process that a DBA team already owns and an auditor already understands. That institutional fit isn't a technical argument, and it's the kind of thing greenfield advice never accounts for — but on an enterprise banking deployment it carries genuine weight, and pretending otherwise means proposing architectures that will not survive review.

I'd rather be honest about the trade than pretend it's free. Stored procedures are meaningfully worse to test — there's no fast in-memory harness, and the tooling is a long way behind what you'd expect for application code. Version control is a convention you impose rather than something the platform gives you. And the logic is now tied to DB2 specifically, so 'we'll migrate the database later' stops being a cheap sentence.

The thing that made those costs manageable was treating the boundary as a contract. Mapping database schemas to REST responses and Java middleware contracts explicitly — and maintaining the data-mapping documentation alongside — is what stopped procedures becoming an undocumented layer only one person understood. That documentation cut schema-mismatch errors during releases, and it's the part I'd insist on if I did it again.

The general rule I'd draw out: put logic where the constraints are, not where the convention is. On a greenfield service with one consumer and a database you control, keep it in the app layer — that advice is good and I follow it. On an enterprise system where the database is the integration point, the consumers are plural, and the auditors are real, the convention is answering a question you weren't asked.