Saturday, July 1, 2017

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



Wednesday, June 28, 2017

Introduction to Postgrest

   Postgrest is third party REST API interface for quering postgres SQL server,which let us access data from postgres tables,views & function.Lets explore it further.

   First we will download postgrest(https://github.com/begriffs/postgrest/releases/tag/v0.3.1.0). For my debian machine I am using ubuntu version  of postgrest        (postgrest-0.3.1.0-ubuntu.tar.xz),Extract it and rename it to just "postgrest",You can preferably move it to "/opt/".

We can run it as follows from Linux Terminal:
    cd /opt
    ./postgrest 'postgres://mypgusername:mypassword@localhost:5432/mypgdb' -a mypgusername -p 2020

    Note: Here first argument is jdbc url to our postgres server."mypgusername" is database role  & 2020 is port on which Postgrest will run.
              role can be "anonymous".

We need some tables to check Postgrest functionality.Lets create below table

        create table t_review
        (
            review_id uuid,
            user_id uuid,
            product_id uuid,
            review_headline varchar,
            verbose_response text,
            review_date timestamp without time zone,
            status record_status
        )

        create table t_review_response
        (
            response_id uuid,
            review_id uuid,
            user_id uuid,
            vote smallint,
            vote_date timestamp without time zone,
            status record_status
        )


    Add some dummy data in both tables please keep in mind "review_id" in "t_review_response" should belong to "t_review"

    You may need to give access right to this table to user through which postgrest is running

        GRANT SELECT ON TABLE t_review TO mypgusername;
        GRANT SELECT,INSERT,UPDATE ON  playground.public.t_review_response  to mypgusername;

    As some columns are declared of type uuid,to generate new uuid in postgres we might need to add corresponding extension if not already installed.

        CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Now we can read data from any table using GET as follows

        API End Point :http://localhost:2020/t_review_response
        API Response:
            [
                {
                "response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
                "vote": 1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                },
                {
                "response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
                "vote": -1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                }
              ]
Where Clause:
    a) Equality:

    API End Point :http://localhost:2020/t_review_response?vote=eq.-1&review_id=eq.8e03e047-7eea-4725-8e5d-5c3dded7c0b9&response_id=eq.75320cf9-e5ca-4844-a2ae-ae6a44466fa9
    API Response:
        [
           {
            "response_id": "75320cf9-e5ca-4844-a2ae-ae6a44466fa9",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "c7de1c3d-0172-44f2-ad92-88bb9eed1918",
            "vote": -1,
            "vote_date": "2017-06-28T14:36:36.206623",
            "status": "active"
           }
        ]
    b) In clause:
    API End Point :http://localhost:2020/t_review_response?response_id=in.cc19414f-449d-447b-a1ae-d741f2f4e05d,e18905d4-5f34-4060-b7fc-fd919849e6ed
    API Response:
        [
            {
            "response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
            "vote": 1,
            "vote_date": "2017-06-28T14:36:36.206623",
            "status": "active"
            },
            {
            "response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
            "vote": -1,
            "vote_date": "2017-06-28T14:36:36.206623",
            "status": "active"
            }
        ]
     c) like Clause:
    API End Point :http://localhost:2020/t_review?review_headline=like.*battery*
    API Response:
        [
            {
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "4ace0309-b355-403f-9777-f046c157a9b0",
            "product_id": "be4b2c18-3afb-41ec-9a6b-157be393eb03",
            "review_headline": "LG V20 battery backup",
            "verbose_response": "Here is my review",
            "review_date": "2017-06-28T14:53:46.974368",
            "status": "active"
            }
        ]

    Postgrest uses * instead of % in like queries unlike sql queries.

     d) not like  clause:
    API End Point : http://localhost:2020/t_review?review_headline=not.like.*battery*
    API Response:
        [
            {
            "review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
            "user_id": "7b326e04-3f12-45a6-9ec2-1015491fccce",
            "product_id": "50514530-e1a7-4904-b4f4-4244d3579658",
            "review_headline": "LG V20 Speaker Sound Quality",
            "verbose_response": "Here is my review",
            "review_date": "2017-06-28T14:54:27.937735",
            "status": "active"
            }
        ]
     e) Not In clause:
    API End Point : http://localhost:2020/t_review_response?response_id=not.in.cc19414f-449d-447b-a1ae-d741f2f4e05d,e18905d4-5f34-4060-b7fc-fd919849e6ed
    API Response:
        [
            {
            "response_id": "d3f5841d-6d38-47a4-a60f-a9cf17653574",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "fb8cfa0d-896a-44aa-9530-7517b9e5259f",
            "vote": -1,
            "vote_date": "2017-06-28T14:36:36.206623",
            "status": "active"
            },
            {
            "response_id": "ab7d479b-36ae-4b03-b23a-f883939e11ec",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "user_id": "40baf601-ab30-4f07-88f2-9b9f51d30c76",
            "vote": -1,
            "vote_date": "2017-06-28T14:36:36.206623",
            "status": "active"
            }
        ]


        Note :    documentation of postgrest list way to use other common operators like  "less than equality","greator than" etc.

Using Views in Postgrest:
         Postgrest can also RESTIFY views.Lets create a sample view like below
   

    CREATE VIEW vw_review_response AS
    SELECT
        t_review.review_headline,
        t_review_response.review_id,
        count(*) totalResponses,
        sum(t_review_response.vote) as votes ,
        sum(case when t_review_response.vote > 0 then 1 else 0 end) as totalUpVotes,
        sum(case when t_review_response.vote < 0 then 1 else 0 end) as totalDownVotes
    FROM
        t_review
        LEFT OUTER JOIN t_review_response on t_review.review_id = t_review_response.review_id   
    GROUP BY
        t_review_response.review_id,
        t_review.review_headline;

        GRANT SELECT ON TABLE vw_review_response TO mypgusername;

    API End Point:http://localhost:2020/vw_review_response
    API Response:
        [
            {
            "review_headline": "LG V20 battery backup",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "totalresponses": 128,
            "votes": 2,
            "totalupvotes": 65,
            "totaldownvotes": 63
            },
            {
            "review_headline": "LG V20 Speaker Sound Quality",
            "review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
            "totalresponses": 64,
            "votes": 128,
            "totalupvotes": 64,
            "totaldownvotes": 0
            }
        ]

Select Specific Columns:
    We can also select only specific columns from table.In below request we will read 3 columns only, namely review_headline,totalupvotes,totaldownvotes.

    API End Point:http://localhost:2020/vw_review_response?select=review_headline,totalupvotes,totaldownvotes
    API Response:
        [
            {
            "review_headline": "LG V20 battery backup",
            "totalupvotes": 65,
            "totaldownvotes": 63
            },
            {
            "review_headline": "LG V20 Speaker Sound Quality",
            "totalupvotes": 64,
            "totaldownvotes": 0
            }
        ]

Ordering Rows:
        we can order the result set using order param,Here our sort order is  totalupvotes ascending & totaldownvotes descending.
    API End Point:http://localhost:2020/vw_review_response?order=totalupvotes.asc,totaldownvotes.desc
    API Response:
        [
            {
            "review_headline": "LG V20 Speaker Sound Quality",
            "review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
            "totalresponses": 64,
            "votes": 128,
            "totalupvotes": 64,
            "totaldownvotes": 0
            },
            {
            "review_headline": "LG V20 battery backup",
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "totalresponses": 128,
            "votes": 2,
            "totalupvotes": 65,
            "totaldownvotes": 63
            }
        ]

Limiting Numbers of rows in Result set:
    In Postgrest you can add header in http request to limit number of rows as follows

    API End point:http://localhost:2020/t_review_response
        Request Header:
             {
                Range : 0-4
             }
       API Response:
            [
                {
                "response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
                "vote": 1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                },
                {
                "response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
                "vote": -1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                },
                {
                "response_id": "d3f5841d-6d38-47a4-a60f-a9cf17653574",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "fb8cfa0d-896a-44aa-9530-7517b9e5259f",
                "vote": -1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                },
                {
                "response_id": "ab7d479b-36ae-4b03-b23a-f883939e11ec",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "40baf601-ab30-4f07-88f2-9b9f51d30c76",
                "vote": -1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                },
                {
                "response_id": "b772deb7-5b5b-4db8-966a-d7ec8b14aceb",
                "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
                "user_id": "1900cbb5-6b1f-4cf4-b7d8-ecbc9bce3e42",
                "vote": -1,
                "vote_date": "2017-06-28T14:36:36.206623",
                "status": "active"
                }
            ]
   
    Note:The Range:0-4 gives five rows the counting begins at zero.

    To know total number of record along with effect of server side pagination. we need to have additional header
        {
            Prefer:count=exact
        }

    we are using CURL tool on linux terminal to test the functionality as below.

    Request: curl -i -H "Range:0-4" -H "Prefer:count=exact" http://localhost:2020/t_review_response
    Response:
        HTTP/1.1 206 Partial Content
        Transfer-Encoding: chunked
        Date: Wed, 28 Jun 2017 13:40:21 GMT
        Server: postgrest/0.3.1.0
        Content-Type: application/json
        Content-Range: 0-4/192
        Content-Location: /t_review_response

        [
           {
              "response_id":"cc19414f-449d-447b-a1ae-d741f2f4e05d",
              "review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
              "user_id":"9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
              "vote":1,
              "vote_date":"2017-06-28T14:36:36.206623",
              "status":"active"
           },
           {
              "response_id":"e18905d4-5f34-4060-b7fc-fd919849e6ed",
              "review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
              "user_id":"9dd246a6-cf01-4df5-b259-68ca153bdda9",
              "vote":-1,
              "vote_date":"2017-06-28T14:36:36.206623",
              "status":"active"
           },
           {
              "response_id":"d3f5841d-6d38-47a4-a60f-a9cf17653574",
              "review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
              "user_id":"fb8cfa0d-896a-44aa-9530-7517b9e5259f",
              "vote":-1,
              "vote_date":"2017-06-28T14:36:36.206623",
              "status":"active"
           },
           {
              "response_id":"ab7d479b-36ae-4b03-b23a-f883939e11ec",
              "review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
              "user_id":"40baf601-ab30-4f07-88f2-9b9f51d30c76",
              "vote":-1,
              "vote_date":"2017-06-28T14:36:36.206623",
              "status":"active"
           },
           {
              "response_id":"b772deb7-5b5b-4db8-966a-d7ec8b14aceb",
              "review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
              "user_id":"1900cbb5-6b1f-4cf4-b7d8-ecbc9bce3e42",
              "vote":-1,
              "vote_date":"2017-06-28T14:36:36.206623",
              "status":"active"
           }
        ]

     In response header we got "Content-Range" as "0-4/192" that says that there are in all 192 record out of which we are selecting 5 records from 0th till 4th.

    samething can be done using postman to see response headers beside body & cookie tabs.
   

Calling Stored Procedure:
To demonstrate how we can call stored procedure through postgrest we will create a sample Stored procedure as follows

    CREATE OR REPLACE FUNCTION fn_get_review_response(p_review_id uuid)
    RETURNS  TABLE (headline varchar, review_id uuid,totalresponses bigint,votes bigint,totalupvotes bigint,totaldownvotes bigint) 
    AS $$
    BEGIN
        RETURN QUERY
        SELECT
            t_review.review_headline,
            t_review_response.review_id,
            count(*) totalResponses,
            sum(t_review_response.vote) as votes ,
            sum(case when t_review_response.vote > 0 then 1 else 0 end) as totalUpVotes,
            sum(case when t_review_response.vote < 0 then 1 else 0 end) as totalDownVotes
        FROM
            t_review
            LEFT OUTER JOIN t_review_response on t_review.review_id = t_review_response.review_id   
        WHERE
           t_review_response.review_id = p_review_id
        GROUP BY
            t_review_response.review_id,
            t_review.review_headline;
    END
    $$ LANGUAGE plpgsql;

may need to grant access to user through which postgrest is being run

    GRANT EXECUTE ON  FUNCTION fn_get_review_response(uuid) TO mypgusername

Now we can call our stored procedure,stored procedures are exposed in postgrest under "/rpc".
You can use "Postman" extension to test POST API.

    API End Point  :
        http://localhost:2020/rpc/fn_get_review_response

    Header:
        Content-Type : application/json

    Payload:
        {
            "p_review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9"
        }

        Note: Here I observed that JSON key ,need to be put into quotes else end up in error.
       
    API Response:
        [
            {
            "totalupvotes": 65,
            "votes": 2,
            "headline": "LG V20 battery backup",
            "totalresponses": 128,
            "review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
            "totaldownvotes": 63
            }
        ]

We need to explore built in security aspect of Postgrest further.