How to execute a database stored procedure

Questions and Answers
1
System Information
  • Strapi Version: 3.6.3
  • Operating System: Suse linux
  • Database: Postgres 12.3
  • Node Version: 14.16.0
  • NPM Version: 7.18.1
  • Yarn Version: 1.22.10

I have created several collections and standard API methods using the strapi admin console. All good so far. But I need to run some functionality database-side. The kind of functionality I want is for example when a strapi-generated POST call populates a database table, that an additional object that is in a table that is not generated by a strapi collection is generated. I also need for example to POST a transaction, but then run the data through an algorithm and manipulate it and update balances in several database tables. But I need to crawl before I can walk.

I have read through the strapi documentation and can see how to make a custom controller/route/handler. But what is not clear to me from trawling the forums and googling is how to make that controller execute a database stored procedure rather than use the standard HTTP requests.

As a simple example to get me started, how would I execute this SP that generates x default records in a database table?

CALL public.databasename_add_records(x)

I am not looking for advice as to how to achieve the same result using a standard POST request. This example is merely an attempt at providing a specific and simple use case as I have seen other questions similar to my own closed down for being too vague in terms of what the SP functionality might be.

2 Likes