pg-promise basic usage

const pgp = require('pg-promise')({
  promiseLib: promise
  // Overrides the default (ES6 Promise) promise library for its internal use.
);

// Database connection details
// more details : https://github.com/vitaly-t/pg-promise/wiki/Connection-Syntax
const cn = {
  host: 'localhost', 
  port: 5432,
  database: 'products',
  user: 'user-name',    
  password: 'user-password'
};

const db = pgp(cn);
// const db = pgp('postgres://john:pass123@localhost:5432/products'

db.any('SELECT * FROM users')
  .then(function(data){
    console.log('>>>>', data);
    //[ { name : "aaa", email : "aaa@email.address" }, 
    //  { name : "bbb", email : "bbb@email.address" }, ... ]
  })
  .catch(function(e){
    console.log('>>>>', e);
  });

let index = 1;
db.each('SELECT * FROM users', [], row => {
  row.name = row.name + index++;
 })
 .then(data => {
   console.log(data);
   // [{ name : "aaa1", ... }, { name : "bbb2", ... }, ... ]
 })
 .catch(error => {
   console.log(error);
 });

// function defined in postgresql
create or replace function foo()
returns Date as $cur$
  begin
    return now();
  end;
$cur$ language plpgsql;

// node
db.func('foo', [])
  .then(data => {
    console.log(data);
    // [ { "foo": "2019-10-03T15:00:00.000Z" } ]
  })
  .catch(error => {
    console.log(error);
  })

db.each('SELECT * FROM users', [], row => return row.name )
  .then(data => {
    console.log(data);
    // expect
    //    [ "aaa", "bbb", ... ]
    // result
    //    [ { name : "aaa", email : "aaa@email.address" }, 
    //      { name : "bbb", email : "bbb@email.address" }, ... ]
  })
  .catch(error => {
    console.log(error);
  });

db.multi('SELECT * FROM users;SELECT * FROM class')
  .then(([users, class]) => {
    console.log('>>>>> users');
    console.log(users);
    console.log('>>>>> class');
    console.log(class);
    // >>>>> users
    // [ { name : "aaa", email : "aaa@email.address" }, 
    //   { name : "bbb", email : "bbb@email.address" }, ... ]
    // >>>>> class
    // [ { name : "c1", room : "1" }, 
    //   { name : "c2", room : "2" }, ... ]
  })
  .catch(error => {
    // error
  });

db.none("UPDATE users SET last_locations=$1 WHERE email=$2", [value, email])
  .then(function(){
    console.log('>>>> success');
  })
  .catch(function(e){
    console.log('>>>>', e);
  });

db.one('SELECT count(*) FROM Users', [], c => c.count)
  .then(count => {
    console.log(count); // integer
   });

db.query('SELECT name FROM Users where name=\'xxx\'', [], pgp.queryResult.one + pgp.queryResult.none)
  .then(data => {
    console.log(data)
  })
  .catch(error => {
    console.log(error)
  });

db.result('DELETE FROM users WHERE name=\'kkk\'')
  .then(data => {
    console.log(data);
  });
// Result {
//   command: 'DELETE',
//   rowCount: 1,
//   oid: null,
//   rows: [],
//   fields: [],
//   _parsers: [],
//   _types:
//   TypeOverrides {
//     _types:
//     { getTypeParser: [Function: getTypeParser],
//       setTypeParser: [Function: setTypeParser],
//       arrayParser: [Object],
//       builtins: [Object] },
//     text: {},
//     binary: {} },
//   RowCtor: null,
//   rowAsArray: false,
//   duration: 5 }

db.task(t => {
    return t.map('SELECT email FROM congress_participants WHERE congress_id=\'C1\' and role = \'visitor\'', [], row => row.email)
            .then(rows => {
              let str =  '(\'' + rows.join('\', \'') + '\')';
              return t.any('SELECT * from users where email IN ' + str)
            });
  })
  .then(data => {
      console.log(data);
  })
  .catch(error => {
    console.log(error);
  })

db.tx(t => {
  return t.one('INSERT INTO users(email, name) VALUES($1, $2) RETURNING email', ['nnn@email.address', 'nnn'])
          .then(email => {
            return t.
          })

        return t.one('INSERT INTO Users(name, age) VALUES($1, $2) RETURNING id', ['Mike', 25])
            .then(user => {
                return t.none('INSERT INTO congress_participants(email, congress_id, role) VALUES($1, $2, $3)', [user.eamil, 'C1', 'visitor'])
            });
    })
    .then(function(){
      console.log('>>>> success');
    })
    .catch(function(e){
      console.log('>>>>', e);
    });

db.one('SELECT name FROM Users where name=\'xxx\'', [], c => +c.count)
  .then(count => {})
  .catch(error => { console.log(error); });
  
// QueryResultError {
//    code: queryResultErrorCode.noData
//    message: "No data returned from the query."
//    received: 0
//    query: "SELECT name FROM Users where name='xxx'"
// }

const data_single = { email: 'aaa@email.address', last_location: 'S1'};
pgp.helpers.update(data_single, ['last_location'], 'users') + ' WHERE email = ' + data_single.email;
// UPDATE 'data_single' SET 'last_location' = 'S1' WHERE email = 'aaa@email.address'

const data_multi = [{ email: 'aaa@email.address', last_location: 'S1'},
			        { email: 'bbb@email.address', last_location: 'S2'}]
pgp.helpers.update(data, ['?email', 'last_location'], 'users') + ' WHERE v.email = t.email';
// UPDATE 'data_multi' AS t SET 'last_location' = v.'last_location' 
//   FROM (VALUES('aaa@email.address','S1'),('bbb@email.address','S2'))
//   AS v('email', 'last_location')
//   WHERE v.email = t.email

const dataSingle = {val: 123, msg: 'hello'};
const dataMulti = [{val: 123, msg: 'hello'}, {val: 456, msg: 'world!'}];

pgp.helpers.values(dataSingle);
//(123,'hello')

pgp.helpers.values(dataMulti, ['val', 'msg']);
//(123,'hello'),(456,'world!')