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.




No comments:

Post a Comment