Alternative For Stored Procedures In cockroachDB

Question

I was trying to migrate from Another RDBMS to cockroachDB but I think there is no such functionality like stored procedures in Cockroach. So what is the best alternative to make a stored procedure in cockroachDB ?

Answer 1

CockroachDB does not support stored procedures and the best alternative would depend on the problem you are trying to solve. A few examples:

• If the stored procedure contains business logic, we'd recommend moving that logic to the application.
• Simple stored procedures that contain a single DML statement should be moved into the application's DataAccess logic.
• More complex stored procedures that contain explicit transactions or error code should be moved to the application-level transactions.

EDIT: Stored Procedures as a Litmus Test, an article by Joe Emison, compares Stored Procedures to other solutions. It may be helpful in understanding alternatives.

Answer 2

CockroachDB is distributed SQL and natively suits serverless patterns. As a stored proc is just a way to ensure procedural consistency, you could probably get by using serverless functions (whatever flavor). The idea is the serverless function is a proxy for the stored procedure.

While it is possible for a procs to call other procs, common advice is to avoid having serverless functions call each other. It would be reasonable to develop a cloud library (JavaScript, for example) which models all the DB constraints. Then each serverless function becomes an endpoint (proc) and the library provides the means to reuse/shared logic.