Neophobe Plebeian Mumpsimus
Wondering what’s next for npm?Check out our public roadmap! »

@linkfuture/pg-api

1.0.6 • Public • Published

PG-API

NPM version NPM downloads NPM Status License

RESTful API for PostgreSQL
An easier way to query database

Table of Contents

Installation

The easiest way to install @linkfuture/pg-api is with npm.

npm install @linkfuture/pg-api

Usage

//reference: https://github.com/brianc/node-postgres/tree/master/packages/pg-connection-string
const $config = {
    //"connection":"postgres://<user>:<password>@<host>:<port>/<database>?ssl=true&sslmode=no-verify",
    "connection":{
	  "user": "postgres",
      "host": "<hostname>",
      "database": "<database>",
      "password": "<password>",
      "port": 5432,
      "ssl": { rejectUnauthorized: false }
	},
    "tables":{
        "user":{
            delete:false,//disable delete operation on user table, other operation will be available as default. 
         }
    }
}
const $pgConnector = require("@linkfuture/pg-api");
// access through Restful API
const $pgApi = $pgConnector.api($config);
app.use("/api/db/",$pgApi);  
// access through NodeJs
const $pgQuery = $pgConnector.query($config);
let result = await $pgQuery.select("user",{$where:{user_id:1}}); 

Env Support

PGUSER=dbuser
PGHOST=database.server.com
PGPASSWORD=secretpassword
PGDATABASE=mydb
PGPORT=3211

Query

SELECT (GET)

  • Select by Primary Key
    GET http://[host]/api/db/[table-name or view-name]/[id]
    GET http://[host]/api/db/user/1
  • Select by JSON Query
    GET http://[host]/api/db/[table-name or view-name]?$q=[JSON QUERY]
    GET http://[host]/api/db/user?$q={"$where":{"id":{"$any":[1,2,3]}}}
  • Select by Query String
    GET http://[host]/api/db/[table-name or view-name]?[ColumnName]=[ColumnValue]&$limit=10
    GET http://[host]/api/db/user?age={"$gt":5,"$lt":50}&is_active=1&$limit=1
  • Select in Node
    const $pgQuery = $pgConnector.query($config);
    let result = await $pgQuery.select("user",{$where:{user_id:1}});
    let result = await $pgQuery.selectOne("user",{$where:{user_id:1}});
    let result = await $pgQuery.selectById("user",1);
  • JSON Query Example
    Normal Query
{
  "*":true
  ,"unknown_field2": {"$multiply":["age","price","price"]}
  ,"unknown_field3": {"$multiply":["age",{"$divide":["age","price"]}]}
  ,"unknown_field4": {"$divide":["age","price"]}
  ,"unknown_field5": {"$plus":["age","price"]}
  ,"unknown_field6": {"$minus":["age","price"]}
  ,"unknown_field7": {"$module":["age","price"]}
  ,"$where":{
      "display_name": "UNIT TEST",
      "account": {"$similar":"account%"},
      "age":{"$gt":5,"$lt":50},
      "is_active":1,
      "roles":[1,2],
      "price":{"$between":[300,500]},
      "account_id":{"$any": [4,3]},
      "meta":{"$contain":{"b":4}},
      "$or":[
          { "account":"test_1"},
          { "account":"test_2"}
       ]
  }
  ,"$sort":{"data_registered":"DESC","account_id":"ASC"  }
  ,"$limit":10
  ,"$offset":0
}

Group by Query

{
  "gender":true
  ,"sum_operation": {"$sum":"age"}
  ,"sum_multiply_operation": {"$sum":{"$multiply":["age","price"]}}
  ,"count_operation": {"$count":1}
  ,"min_operation": {"$min":"age"}
  ,"max_operation": {"$max":"age"}
  ,"avg_operation": {"$avg":"age"}
  ,"$where":{
    "display_name": {"$like":"% display %"}
    ,"data_registered":{"$gt":"2015-09-30 21:21:31.647424+00"}
    ,"$or":[
        { "account":"account_1"}
      ,{ "account":"account_2"}
      ,{"display_name": {"$similar":"my display name"}}
    ]
  }
  ,"$limit":10
  ,"$offset":0
  ,"$group":["gender","age"]
}

Distinct Query

{
  "$distinct":["price",{"unknown_field2":{"$multiply":["age","price","price"]}}]
  ,"$limit":10
  ,"$offset":0
}

INSERT (POST)

  • INSERT by JSON Query
    POST http://[host]/api/db/[table-name]
    POST http://[host]/api/db/user
    {
         "account":"my_account_1"
        ,"password":"my passowrd"
        ,"display_name":"my display name"
        ,"gender":"male"
        ,"date_registered":"2015-10-30 14:21:31.647424 -07:00:00"
        ,"struct":{"name":"full update","supplier_id":[10,50],"price":1.99} 
        ,"age":10
        ,"price":50
        ,"roles":[1,2]
        ,"is_active":true
        ,"struct":null
        ,"meta":null
      }
  • BULK INSERT by JSON Query
    POST http://[host]/api/db/[table-name]
    POST http://[host]/api/db/user
    [
      {
         "account":"my_account_1"
        ,"password":"my passowrd"
        ,"display_name":"my display name"
        ,"gender":"male"
        ,"date_registered":"2015-10-30 14:21:31.647424 -07:00:00"
        ,"age":10
        ,"price":50
        ,"roles":[1,2]
        ,"is_active":true
        ,"struct":null
        ,"meta":null
      }
      ,{
         "account":"my_account_2"
        ,"password":"my passowrd"
        ,"gender":"female"
        ,"price":50
        ,"age":10
        ,"display_name":"my display name"
        ,"date_registered":"2015-10-30 14:21:31.647424 -07:00:00"
        ,"meta":
        {
          "img":"https://scontent-ord1-1.xx.fbcdn.net/v/t1.0-1/c9.0.40.40/p40x40/1618502_10203352692842640_430525865_n.jpg?oh=10b7e45293509d2b667a27f21985891f&oe=582C74C9"
        ,"gender":"male"
        ,"languages":"english"
        }
      }
    ]
  • Insert in Node
    const $pgQuery = $pgConnector.query($config);
    let result = await $pgQuery.insert("user",[{"account":"my_account_1",,"password":"my passowrd"}}]);

UPSERT (PUT)

The UPDATE action to be performed in case of a conflict,otherwise do insert.
Reference https://www.postgresql.org/docs/9.5/static/sql-insert.html for more

  • UPSERT by JSON Query
    PUT http://[host]/api/db/[table-name]/[constraint_name]
    PUT http://[host]/api/db/city/city_pkey
    {
        "id":1  //update if id is exist
        ,"name":"my_account_2"
        ,"district":"11213"
        ,"countrycode":"my passowrd"
        ,"population":6000
    }
  • BULK INSERT by JSON Query
    PUT http://[host]/api/db/[table-name]/[constraint_name]
    PUT http://[host]/api/db/city/city_pkey
    [
      {
        "id":1 //update if id is exist
        ,"name":"my_account_2" 
        ,"countrycode":"my passowrd"
        ,"district":"11213"
        ,"population":6000
      },
      {
        "id":2 //update if id is exist
        ,"name":"my_account_1"
        ,"countrycode":"my passowrd"
        ,"district":"22222"
        ,"population":7000
      }
    ]
  • Upsert in Node
    const $pgQuery = $pgConnector.query($config);
    let result = await $pgQuery.upsert("user",[{"account":"my_account_1",,"password":"my passowrd"}}],"city_pkey");

PARTIALLY UPDATES (PATCH)

  • Update by JSON Query
    PATCH http://[host]/api/db/[table-name]
    PATCH http://[host]/api/db/user
    {
        "display_name":"new name",
        "age":10,
        "$where":{
            "id":{"$any":[1,2,3]}
        }
    }
  • Update in Node
    const $pgQuery = $pgConnector.query($config);
    let result = await $pgQuery.update("user",{"display_name":"new name","$where":{"id":{"$any":[1,2,3]}}}});

DELETE (DELETE)

  • Delete by Primary Key
    DELETE http://[host]/api/db/[table-name or view-name]/[id]
    DELETE http://[host]/api/db/user/1
  • Delete by JSON Query (no need [$where])
    DELETE http://[host]/api/db/[table-name]?$q=[JSON QUERY]
    DELETE http://[host]/api/db/user?$q={"id":{"$any":[1,2,3]}}
  • Delete by Query String
    DELETE http://[host]/api/db/[table-name]?[ColumnName]=[ColumnValue]
    DELETE http://[host]/api/db/user?age={"$gt":5,"$lt":50}&is_active=1
  • Delete in Node
    const $pgQuery = $pgConnector.query($config);
    let result = await $pgQuery.delete("user",{"id":{"$any":[1,2,3]}});
    let result = await $pgQuery.deleteById("user",1);

Composite

Query composite

    GET http://[host]/api/db/composite/[composite name]
    GET http://[host]/api/db/composite/type_struct

Enum

Query enum

    GET http://[host]/api/db/enum/[enum name]
    GET http://[host]/api/db/enum/type_gender

Function (GET or POST)

Query Function, you can pass params with specific arguments sequence or pass with object

    GET http://[host]/api/func/[func name]?$params=<parameters>
    GET http://[host]/api/func/f_table?$params=1&$params=999
    GET http://[host]/api/func/f_table?_user_id=1&_company_id=999
    //auto apply "_" on begin for better user experience 
    GET http://[host]/api/func/f_table?user_id=1&company_id=999
    //auto add default value on function when specific on the code
    GET http://[host]/api/func/f_table?user_id=1
    POST http://[host]/api/func/f_table
    {
         "_company_id":999
        ,"_user_id":1
    }
    POST http://[host]/api/func/f_table
    {
         "company_id":999
        ,"user_id":1
    }
    POST http://[host]/api/func/f_table
    {
        "user_id":1
    }
    SELECT * from f_table(1,999)
    CREATE FUNCTION f_table (
        _user_id int,
        _company_id int DEFAULT 1
    )

Configuration

For security reason, sometimes you may want to disable the operation on specific table, like disable delete operation on user table. You can leverage following configuration to reslove this issue.
By default, the API will enable all operations(select,delete,insert,update) on all tables and views

const $config = {
    "connection":"postgres://<user>:<password>@<host>:<port>/<dbname>",
    "tables":{
        "user":{ 
            select:true,
            delete:false,//disable delete operation on user table
            update:false, //disable update operation on user table
            insert:false, //disable insert operation on user table
            max_limit:5000, //set default select max results, by default is 1000
            limit:12 //set default select limit, by default is 10
         }
    },
    "composites":{
        "type_struct":false //disable type_struct composite query, 
    },
    "enum":{
        "type_gender":false //disable type_gender enum query, 
    },
    "functions":{
        "f_check_error":false //disable f_check_error function query, 
    },
    "events":{
        onRequest:function () {
            $logger.info("onRequest =>",JSON.stringify(arguments));
        },
        on_select_city_request:function () {
            $logger.info("on_select_city_request =>",JSON.stringify(arguments));
        },
    }
    "custom":{ //custom query, you can define your own script with transaction 
            "find-user":{
                "query":[
                    "select * from public.user where account_id=${id};",
                    "select * from public.city where id=${cityId}",
                    "insert into public.user(account,display_name) VALUES(${name1},${display_name1}),(${name2},${display_name2}) returning account_id",
                    "update public.user set display_name = ${updated_display_name} where account=${name1}",
                    "delete from public.user where account=${deletename}",
                ],
                "method":["GET","post"]
            }
     }
}

Events

Events life cycle, you can catch on either global level (i.e onRequest) or specific action level(i.e on_select_city_request).
Request => Build => Query => Complete

  • Request: when api load
  • Build: before build TSQL and verify column and parameters
  • Query: before db operation
  • Complete: after DB operation
        "events":{
            onRequest:function () {
                $logger.info("onRequest =>",JSON.stringify(arguments));
            },
            on_city_request:function () {
                $logger.info("on_city_request =>",JSON.stringify(arguments));
            },
            on_select_city_request:function () {
                $logger.info("on_select_city_request =>",JSON.stringify(arguments));
            },
            onBuild:function () {
                $logger.info("onBuild =>",JSON.stringify(arguments));
            },
            on_city_build:function () {
                $logger.info("on_city_build =>",JSON.stringify(arguments));
            },
            on_select_city_build:function () {
                $logger.info("on_select_city_build =>",JSON.stringify(arguments));
            },
            onQuery:function () {
                $logger.info("onQuery =>",JSON.stringify(arguments));
            },
            on_city_query:function () {
                $logger.info("on_city_query =>",JSON.stringify(arguments));
            },            
            on_select_city_query:function () {
                $logger.info("on_select_city_query =>",JSON.stringify(arguments));
            },
            onComplete:function () {
                $logger.info("onComplete =>",JSON.stringify(arguments));
            },
            on_city_complete:function () {
                $logger.info("on_select_city_complete =>",JSON.stringify(arguments));
            },            
            on_select_city_complete:function () {
                $logger.info("on_select_city_complete =>",JSON.stringify(arguments));
            },
            on_delete_city_complete:function () {
                $logger.info("on_delete_city_complete =>",JSON.stringify(arguments));
            },
        },

pg repositories

const $pgConnector = require("@linkfuture/pg-api");
const $repository = await $pgConnector.repository.build(config);

//if we have user table or view
$repository.tables.user.select(<json query>);
$repository.tables.user.insert(<json query>);

//if we have type_struct composite
$repository.composites.type_struct

//if we have type_gender composite
$repository.enums.type_gender

KeyWords

  • $q
  • $or
  • $where
  • $sort
  • $limit
  • $offset
  • $group
  • $gt
  • $gte
  • $lt
  • $lte
  • $ne
  • $like
  • $ilike
  • $similar
  • $contain
  • $any
  • $between
  • $in
  • $multiply
  • $divide
  • $plus
  • $minus
  • $module
  • $sum
  • $count
  • $min
  • $max
  • $avg
  • $distinct
  • $disableCount
    disable select count for pagination in order to improve select performance, default is false.

Notice

  • Column name it will be ignore if the column name does not exist in current table, but the query will be continue.

  • Bit Bool type will auto convert to bit if the column type is bit.

  • Node 7+ Only The library is writen on Node 7+, heavily use await/async.

Install

npm i @linkfuture/pg-api

DownloadsWeekly Downloads

21

Version

1.0.6

License

MIT

Unpacked Size

63.1 kB

Total Files

17

Last publish

Collaborators

  • avatar