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 ?

Multistage

can you please describe the use case you are trying to achieve with Stored Procedures? Many a time Stored Procedures create a lock-in into a DB vendor. – Acclivity

For now just the basic sample stored procedures I m trying to see what are limitations and capabilities of cockroach... that's it ....So if I want to use it for a transaction purpose whether i will be able to do that or not? – Multistage

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.

Dependency

I'm gonna say I found that "stored procedures as a litmus test" to be a pretty terrible read. The expressiveness of stored procedures has enormous benefits in some use cases and I've been promoting them ever since I discovered that I could get 100x performance and better reliability with 1/10th LOC relative to the equivalent application in Java or Go. YMMV, but there are plenty of benefits to stored procedures and I for one would love to find something as expressive, hosted or not. – Interactive

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.

Linders