Procedures are very useful to enforce business logic in the database, rather than in the application. It’s like putting a higher level application-specific API on top of your database schema.
For Cockroach, it could help substantially with latency for multi-statement transactions, since the procedure could be executed on a database node closest to the leaseholder for the data. Executing a multi-statement transaction on the client means incurring per-statement latency between the client and gateway node, and between the gateway node and the leaseholder.
The H2 embedded database has compatibility modes for other databases, which seems helpful for testing, but ends up being incompatible in various ways for anything non-trivial. You end up fighting the compatibility and skipping useful native features of the target database, while still not having confidence that the code works on the target database.
We now use the awesome Testcontainers library (available for multiple languages) that makes it easy to launch the real database in a Docker container. Testing applications against multiple databases is now trivial.
> During street sweeping hours, you may not park until the street has been physically swept.