6

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 ?

Saqib Ali
  • 3,953
  • 10
  • 55
  • 100
divya kriplani
  • 63
  • 5
  • 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. – Saqib Ali Jul 27 '21 at 13:51
  • 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? – divya kriplani Jul 28 '21 at 16:26

2 Answers2

7

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.

lauren
  • 195
  • 4
  • 2
    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. – Doctor Eval May 04 '22 at 23:41
0

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.

Mario
  • 6,572
  • 3
  • 42
  • 74