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:
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:
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.