The ability to call a PostgreSQL stored procedure from an API allows for rapid development in a secure way. Rather than writing database queries to interact with your data you can utilize an API that can be rapidly developed and reused many times. The reusability of APIs and using stored procedures means if you are scaling from a single backend application to hundreds your APIs will not change.
Introducing DreamFactory
DreamFactory allows for the instant creation of fully documented and secure APIs within a point and click interface. There is no longer a need to spend previous development time creating APIs that work at scale while also ensuring your data is secure along the way. DreamFactory ensures your APIs are secured at minimum via API keys as well as allows you to customize your APIs on the fly with it’s built in scripting engine. Below we will go through generating a PostgreSQL API and interacting with the stored procedures via the API.
Generating a PostgreSQL API in DreamFactory
DreamFactory can generate REST APIs for not only PostgreSQL, but a multitude of databases, such as MySQL, Microsoft SQL Server, Oracle, and MongoDB. To do so, you’ll login to the DreamFactory administration interface, navigate to Services and then enter the service creation interface by clicking on the Create button located to the left of the screen. From there you’ll select the PostgreSQL service type by navigating to Database > PostgreSQL (see below screenshot).
Next you’ll be prompted to provide a name, label, and description (below screenshot). The latter two are used just for reference purposes within the administration interface, however the name value is particularly important because as you’ll soon see it will comprise part of the API URL.
Finally, click on the Config tab. Here you’ll be prompted to provide the database connection credentials (see below screenshot). This should really be nothing new; you’ll supply a host name, username, password, and database. Additionally, you can optionally specify other configuration characteristics such as driver options, the timezone, and caching preferences. For the purpose of this tutorial we will stick to the required fields and leave the optional features untouched.
With the credentials in place, press the Save button at the bottom of the page. You now have a fully documented and secured API generated!
Calling the PostgreSQL Stored Procedure API Endpoint
DreamFactory not only generates endpoints to interact with your tables but your functions and stored procedures as well. To view and interact with all your endpoints we recommend checking out the API Docs tab.
Using the API Docs Tab
Within the API Docs tab all your endpoints are available. You are even able to click into each to try them out in real time. Please note that these are not example endpoints, if you use the delete endpoint you are deleting records at the database level.
Starting at the top you have your function and stored procedure endpoints. As you scroll down you have your schema endpoints and lastly you can find your table endpoints at the bottom. To retrieve a list of your functions or stored procedures we can perform a GET call on either the /_func or /_proc endpoint.
Now that we see the available procedures and functions we can use the /_func/{function_name} and /_proc/{procedure_name} endpoints. As you can see we can call the /_proc/film_in_stock endpoint to see how many films are currently in stock.
This is simply scratching the surface of the possibilities with stored procedures in DreamFactory. If your stored procedure requires additional parameters then you can utilize the POST endpoint. This allows for passing parameters through the payload body. Alternatively they can be passed through the URL as well. For additional details on calling stored procedures in DreamFactory I recommend checking out our wiki section on everything stored procedures.