Installation

npm install @linkfuture/pg-api

Usage

//reference: https://github.com/brianc/node-postgres/tree/master/packages/pg-connection-stringconst$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 APIconst$pgApi=$pgConnector.api($config);app.use("/api/db/",$pgApi);// access through NodeJsconst$pgQuery=$pgConnector.query($config);letresult=await$pgQuery.select("user",{$where:{user_id:1}});

Env Support

PGUSER=dbuserPGHOST=database.server.comPGPASSWORD=secretpasswordPGDATABASE=mydbPGPORT=3211

Query

SELECT (GET)

GET http://[host]/api/db/[table-name or view-name]/[id]GET http://[host]/api/db/user/1

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]}}}

GET http://[host]/api/db/[table-name or view-name]?[ColumnName]=[ColumnValue]&$limit=10GET http://[host]/api/db/user?age={"$gt":5,"$lt":50}&is_active=1&$limit=1

const$pgQuery=$pgConnector.query($config);letresult=await$pgQuery.select("user",{$where:{user_id:1}});letresult=await$pgQuery.selectOne("user",{$where:{user_id:1}});letresult=await$pgQuery.selectById("user",1);

{"*":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}

{"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":["price",{"unknown_field2":{"$multiply":["age","price","price"]}}],"$limit":10,"$offset":0}

INSERT (POST)

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      }

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"        }      }    ]

const$pgQuery=$pgConnector.query($config);letresult=await$pgQuery.insert("user",[{"account":"my_account_1",,"password":"my passowrd"}}]);

UPSERT (PUT)

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    }

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      }    ]

const$pgQuery=$pgConnector.query($config);letresult=await$pgQuery.upsert("user",[{"account":"my_account_1",,"password":"my passowrd"}}],"city_pkey");

PARTIALLY UPDATES (PATCH)

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]}        }    }

const$pgQuery=$pgConnector.query($config);letresult=await$pgQuery.update("user",{"display_name":"new name","$where":{"id":{"$any":[1,2,3]}}}});

DELETE (DELETE)

DELETE http://[host]/api/db/[table-name or view-name]/[id]DELETE http://[host]/api/db/user/1

DELETE http://[host]/api/db/[table-name]?$q=[JSON QUERY]DELETE http://[host]/api/db/user?$q={"id":{"$any":[1,2,3]}}

DELETE http://[host]/api/db/[table-name]?[ColumnName]=[ColumnValue]DELETE http://[host]/api/db/user?age={"$gt":5,"$lt":50}&is_active=1

const$pgQuery=$pgConnector.query($config);letresult=await$pgQuery.delete("user",{"id":{"$any":[1,2,3]}});letresult=await$pgQuery.deleteById("user",1);

Composite

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

Enum

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

Function (GET or POST)

GET http://[host]/api/func/[func name]?$params=<parameters>GET http://[host]/api/func/f_table?$params=1&$params=999GET http://[host]/api/func/f_table?_user_id=1&_company_id=999//auto apply "_" on begin for better user experienceGET http://[host]/api/func/f_table?user_id=1&company_id=999//auto add default value on function when specific on the codeGET http://[host]/api/func/f_table?user_id=1POST 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

const$config={"connection":"postgres://<user>:<password>@<host>:<port>/<dbname>","tables":{"user":{select:true,delete:false,//disable delete operation on user tableupdate:false,//disable update operation on user tableinsert:false,//disable insert operation on user tablemax_limit:5000,//set default select max results, by default is 1000limit: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":{asynconRequest:function(){$logger.info("onRequest =>",JSON.stringify(arguments));},asyncon_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*frompublic.userwhereaccount_id=${id};",                    "select*frompublic.citywhereid=${cityId}",                    "insertintopublic.user(account,display_name)VALUES(${name1},${display_name1}),(${name2},${display_name2})returningaccount_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":{asynconRequest:function(){$logger.info("onRequest =>",JSON.stringify(arguments));},asyncon_city_request:function(){$logger.info("on_city_request =>",JSON.stringify(arguments));},asyncon_select_city_request:function(){$logger.info("on_select_city_request =>",JSON.stringify(arguments));},asynconBuild:function(){$logger.info("onBuild =>",JSON.stringify(arguments));},asyncon_city_build:function(){$logger.info("on_city_build =>",JSON.stringify(arguments));},asyncon_select_city_build:function(){$logger.info("on_select_city_build =>",JSON.stringify(arguments));},asynconQuery:function(){$logger.info("onQuery =>",JSON.stringify(arguments));},asyncon_city_query:function(){$logger.info("on_city_query =>",JSON.stringify(arguments));},asyncon_select_city_query:function(){$logger.info("on_select_city_query =>",JSON.stringify(arguments));},asynconComplete:function(){$logger.info("onComplete =>",JSON.stringify(arguments));},asyncon_city_complete:function(){$logger.info("on_select_city_complete =>",JSON.stringify(arguments));},asyncon_select_city_complete:function(){$logger.info("on_select_city_complete =>",JSON.stringify(arguments));},asyncon_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(<jsonquery>);$repository.tables.user.insert(<jsonquery>);//if we have type_struct composite$repository.composites.type_struct//if we have type_gender composite$repository.enums.type_gender

KeyWords

Notice