Call a PostgreSQL Stored Procedure from an API | Dreamfactory

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.

Table of Contents:

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.

Benefits of Using Stored Procedures

Utilizing stored procedures in PostgreSQL offers a range of advantages for developers and database administrators.

Stored procedures can significantly enhance the performance of database operations. By precompiling and optimizing SQL queries, they reduce the overhead associated with parsing and query planning. This results in faster and more efficient data retrieval and manipulation.

They also provide an additional layer of security for your database. They allow you to define precise access controls and permissions, ensuring that only authorized users can execute specific procedures.

Stored procedures also promote code reusability across multiple parts of your application. Instead of duplicating SQL queries in various sections of your codebase, you can encapsulate them within stored procedures. This not only simplifies maintenance but also ensures consistency in query execution.

When your application requires updates or modifications to database interactions, you can make changes to the stored procedures without altering the application code. This separation of concerns streamlines maintenance efforts and reduces the risk of introducing errors. Stored procedures enable you to manage database transactions more effectively. You can encapsulate a series of SQL statements within a single procedure, ensuring that either all changes succeed or none at all. This helps maintain data integrity and consistency.

Incorporating stored procedures into your PostgreSQL database not only optimizes performance but also strengthens security and simplifies application maintenance. These benefits make stored procedures a valuable tool for developers aiming to create efficient, scalable, and secure database-driven applications.

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.

DreamFactory Scripting: Tailoring APIs to Your Needs

DreamFactory offers a versatile scripting engine that allows users to customize APIs according to their specific requirements. This feature enables advanced data manipulation, custom authentication and authorization processes, workflow automation, integration with external services, dynamic responses, real-time data updates, custom error handling, and the ability to implement A/B testing and feature toggles. With this scripting engine, users can fine-tune their APIs to match their application's unique functionality and business logic.

Check out the Scripting Library here, or start your free trial now!