Insert ,Update & Delete using POSTGREST REST API

Continuing further from where we left previously today we will explore how we can do insertion ,updation & deletion of records in a table using REST API exposed by "POSTGREST".

Ground Up:
    Lets start postgrest first as below
 
            ./postgrest 'postgres://mypgusername:mypassword@localhost:5432/mypgdb' -a mypgusername -p 2020

       I am using Pgadmin tool to connect to postgres sql server with user other than "mypgusername" but running postgrest through "mypgusername" user
    we might to grant access to postgres user "mypgusername" on SQL operations time to time.This will also help to verify REST API will not allow unauthorized access.

    Now we will create a dummy table

            create table t_name
            (
                 id integer,
                 fname varchar,
                 mname varchar,
                 lname varchar
            )

    for testing purpose we will add a row to the table.

        insert into t_name values(1,'sandip','suresh','raju')

    we will open  "Postman" a Google Chrome extension and call below API.Being new table we need to grant select right to our postgrest user.

        grant select on t_name to mypgusername;

        API End Point: http://localhost:2020/t_name
        Method: GET
        API Response:
            [
                {
                    "id": 1,
                    "fname": "sandip",
                    "mname": "suresh",
                    "lname": "raju"
                }
            ]

INSERT through REST API:
    First grant our postgres user insert right through which we are running postgrest also give update right we may need it latter.

        grant insert on t_name to mypgusername;
        grant update on t_name to mypgusername;

    Postgrest support INSERT through REST out of box.

        API End Point:http://localhost:2020/t_name   
        Payload:
            {
                "id":"2",
                "fname":"shiv",
                "mname":"shankar",
                "lname":"sharma"
            }
        HTTP Method :POST
        Request Headers:
            Content-Type:application/json
        Response:
            You should get HTTP status code 201 saying record is created.
            Any Http status code between 200 to 299 stand for successful completion of http request.

    Note:Now to verify if record inserted we will call previous API.

UPDATE using REST API:
        API End Point: http://localhost:2020/t_name?id=eq.1
        HTTP Method: PATCH
        Payload:
            {
                "fname": "sandeeep",
                "mname": "sureshhh"
            }
        Request Header:Content-Type: application/json
        Response:Http Response code 204 (The server has successfully fulfilled the request and that there is no additional content to send in the response payload body)

    Here in our API end point we narrowed down records to update to single one by using horizontal filtering with "id=eq.1".

        Note: Again one can verify if record updated     properly or not by calling first api list here.

Deleting a record using REST API:
    we may need to give delete right on our table just like we have given it for insert & update

        grant delete on t_name to mypgusername;
   
        API End Point:http://localhost:2020/t_name?id=eq.1&fname=eq.sandeeep
        HTTP Method: DELETE
        Response:Http status code 204.

        Here again we used horizontal filtering to select single row,if no filtering was used  REST API deletes every record from table "t_name".