Create on-the-fly REST API using Postgres database functions

In this tutorial I would like to demonstrate the ease of creating a REST API using postgres functions. The library that I will be using is @thrinz/pgapi .

Introduction

pgAPI is a “Database as a service” framework that automatically creates the REST API endpoint for the given URL. The endpoint is designed to call a Postgres Database Function configured at the time of API endpoint creation. This application automates the URL routing while the developers must have to just focus on the database method creation.

Prerequisites

1. Must have node installed. https://nodejs.org/en/download/
2. Must have Postgres Database installed.

• You can install Postgres locally . https://www.postgresql.org/download/
• Or AWS Cloud à https://aws.amazon.com/rds/postgresql/
• Or Azure Cloud à https://azure.microsoft.com/en-us/services/postgresql/
• Or GCP — https://cloud.google.com/sql/docs/postgres/quickstart

Start the Application

$ git clone https://github.com/thrinz/pgapi-starter-template
$ cd pgapi-starter-template
$ vi config.env from the terminal (linux or Mac) or open the config.env in a text editor. Modify the config.env with the postgres database Information
   a.     DB_HOST=Postgres Hostname
   b.     DB_USER=Postgres username
   c.     DB_PASSWORD=Postgres Database Password
   d.     DB_NAME=Postgres Database Name
   e.     DB_PORT=Port Number
$ npm install
$ node index.js
Open the link in the Browser. http://localhost:5001/admin
username: admin
password: admin

Home Page

Getting Started

Step 1: Create a Database Connection

You must already have a “default-connection” Connection entry. This connection can be used for the next steps. However, if you decide to use a different database connection, then follow the below instructions

1. Navigate to the Connection menu
2. Click on the + icon, a popup will appear
3. Fill the connection postgres connection information.
4. Click on Save. This action will validate the database connection information for connectivity. If there is an issue with connecting to the postgres database, you will see an error message. Otherwise you will see the new connection entry added.

Step 2: Create a Database Function

This step is not performed in this application. You would need a database client like pgAdmin to start developing the database function

Note : the database function must have only one input parameter with datatype JSON and the return type must be a JSON datatype . See sample database function.

Sample Database Function

CREATE OR REPLACE FUNCTION create_task ( p_data json)
RETURNS json AS
$BODY$
DECLARE
l_out json;
l_message_text text;
l_exception_detail text;
l_exception_hint text;
--
l_id uuid;
l_name text;
l_description text;
l_start_date timestamp;
l_due_date timestamp;
l_priority integer;
BEGIN
l_id := md5(random()::text || clock_timestamp()::text)::uuid;
l_name := (p_data->>'name')::text;
l_description := (p_data->>'description')::text;
l_start_date := NOW();
l_due_date := (p_data->>'due_date')::timestamp;
l_priority := (p_data->>'priority')::integer;
INSERT INTO tasks
(
id,
name,
description,
start_date,
due_date,
priority,
created,
updated
)
VALUES
(
l_id,
l_name,
l_description,
l_start_date,
l_due_date,
l_priority,
NOW(),
NOW()
);
l_out :=  '{"status" : "S" , "message" : "OK" , "id" : "' || l_id || '"}';
RETURN l_out;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS l_message_text = MESSAGE_TEXT,
l_exception_detail = PG_EXCEPTION_DETAIL,
l_exception_hint = PG_EXCEPTION_HINT;
l_out := '{ "status" : "E" , "message" : "' || REPLACE(l_message_text, '"', E'\\"') || '" }';
return l_out;
END
$BODY$
LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS tasks (
id uuid NOT NULL,
name text NOT NULL,
description text NOT NULL,
start_date timestamp with time zone NOT NULL,
due_date   timestamp with time zone NOT NULL,
priority   integer NOT NULL,
created timestamp with time zone NOT NULL,
updated timestamp with time zone NOT NULL
);

Step 3: Create a Function

1. Navigate to the Functions menu
2. Click on the + icon, a popup will appear
3. Fill the Function information

Function Name — Unique name

Connection Name — Dropdown with the list of Connections created in Step 1.

DB Method — Database Function created in Step 2

4. Click on Save. This action will validate the DB Method. If there is an issue with validation then the error message will appear as a popup. Otherwise a new function entry is added.

Step 4: Create a Route

1. Navigate to the Routes menu
2. Click on the + icon, a popup will appear
3. Fill the Routes information

Route Name — Unique Route Name

Description(optional) — Description of the Route

Route Method — URL Route Method [GET,POST]

Route URL — Route URL [Ex. /api/tasks , /api/tasks/:id]

Function Name — Name of the function created in Step 3

Sample Request (tab)(optional) — Sample input JSON value

Sample Response(tab)(optional) — Sample response expected in JSON

4. Click on Save. This action will validate the input values to the route form. If there is an issue with validation then the error message will appear as a popup. Otherwise a new route entry is added.

Step 5: Testing the Route

For POST Routes

curl — header “Content-Type: application/json” — request POST — data ‘<JSON Data>’ <server url>/<route url created in Step 4>

Example

curl — header “Content-Type: application/json” — request POST — data ‘{“name”:”Task1" ,”description”:”Task Description 1", “priority”: 1, “start_date”:”2018–12–08 02:41:17",”due_date”:”2018–12–12 01:31:10"}’ http://localhost:5001/api/task/create

For GET Routes

curl <server url>/<route url created in Step 4>

Example

curl http://localhost:5001/api/tasks

Conclusion

You should have noticed that you did not write a single line of server side code (node js) to create API . All of which has been taken care by the framework. This framework motivates the users to write business logic in the database functions. Although it may not be a suitable fit for all the audience , if you have a use case that has different requirements that is not database driven (like file handling , integration ) , you can still fork the pgapi-starter-template and write your custom code that fits your requirements while you still have the ability to create the on-the-fly REST API.