How to Expose a Relational Database Function as a REST API

Appery.io API Express provides an easy way to expose a SQL query as a REST API. You can as easily expose a relational database function as a REST API. In this blog post you will learn how to do that.

A user-defined function is created with CREATE FUNCTION statement. We will use a simple function, one the increments the entered number by 1.

Here is an example for PostgreSQL database:

CREATE OR REPLACE FUNCTION increment(i INT) RETURNS INT AS $$
BEGIN
  RETURN i + 1;
END;
$$ LANGUAGE plpgsql;

And this is an example for MySQL database:

DELIMITER $$
CREATE FUNCTION increment(i INT)
  RETURNS INT
BEGIN
  RETURN i + 1;
END;
$$
DELIMITER ;

To invoke the increment function use this SQL query:

SELECT increment(10);
// will return 11

To expose this function as a REST API, create the following API Express service:

Using the SQL component

It uses the SQL component and invokes the same SQL query.

Now you can easily test the API Express service. Entering 100 will return 101:

Testing the service

As you can see, invoking and exposing a custom SQL query as a REST API is simple. Now you also know how to expose a database function as a REST API.

To learn more about API Express and how to integrate with external resources (SQL database, REST API, web service), please go to our API Express YouTube playlist where we have many videos to help you build faster.

Blog