var pg = require('pg');

var config = {
  host: 'localhost',
  user: 'v',
  password: 'a',
  database: 'j',
};

var pool = new pg.Pool(config);

pool.connect(function(err, client, done) {
  if(err) {
    return console.error('error fetching client from pool', err);
  }
  client.query('SELECT $1::int AS number', ['1'], function(err, result) {
    done(err);

    if(err) {
      return console.error('error running query', err);
    }
    console.log(result.rows[0].number);
    //output: 1
  });
});

pool.on('error', function (err, client) {
  // if an error is encountered by a client while it sits idle in the pool
  // the pool itself will emit an error event with both the error and
  // the client which emitted the original error
  // this is a rare occurrence but can happen if there is a network partition
  // between your application and the database, the database restarts, etc.
  // and so you might want to handle it and at least log it out
  console.error('idle client error', err.message, err.stack)
});

module.exports = pool;

'use strict';

var express = require('express');
var Promise = require('promise');
var router = express.Router();
var app = express();

var pool = require('./db.js')();
var User = require('./models');

var PORT = 3000;

app.use(function (req, res, next) {
  pool.connect(function(error, client, done) {
    // Handle connection errors
    if (error) {
      done(error);
      console.log(error.message);
      return res.status(500)
          .json({success: false, data: error});
    }
    req.client = client;
    req.done = done;
    next();
  });
});
**index.js**

'use strict';

var express = require('express');
var Promise = require('promise');
var router = express.Router();
var app = express();

var pool = require('./db.js')();
var User = require('./models');

var PORT = 3000;

app.use(function (req, res, next) {
  pool.connect(function(error, client, done) {
    // Handle connection errors
    if (error) {
      done(error);
      console.log(error.message);
      return res.status(500)
          .json({success: false, data: error});
    }
    req.client = client;
    req.done = done;
    next();
  });
});

router.get('/topActiveUsers', (req, res) => {
  topActiveUsers(req, res);
});

router.get('/users', (req, res) => {
  userInfo(req, res);
});

app.use(router);

app.get('*', function (req, res) {
  res.status(400).send('Invalid route');
});

app.listen(PORT, function () {
  console.log('App listening on port ' + PORT);
});

var topActiveUsers = function topActiveUsers(req, res) {
  var ENTRIES_PER_PAGE = 3;
  var startIndex = 0;
  var total = 0;
  req.query.page = +req.query.page || 0;

  var pageNum = req.query.page > 0 ? req.query.page : 0;
  if (pageNum > 0) {
    startIndex = ENTRIES_PER_PAGE * (pageNum - 1);
  }
  total = ENTRIES_PER_PAGE * (pageNum + 1);

  User.topActiveUsers(req)
      .then(function fullfilled(users) {
        if (users.length < startIndex) {
          throw new Error('Invalid pagination offset');
        }
        if (users.length > total) {
          users = users.slice(startIndex, startIndex + ENTRIES_PER_PAGE);
        } else {
          users = users.splice(startIndex);
        }
        return Promise.all(users.map(function (user) {
          return user.applicationListings(req);
        }));
      })
      .then(function fullfilled(users) {
        var result = users.map(function (user) {
          return {
            id: user.id,
            name: user.name,
            count: user._appliedListings.length,
            createdAt: user.createdAt,
            listings: user._appliedListings
          };
        });
        res.json(result);
      })
      .catch(function rejected(error) {
        console.log(error.message);
        throw error;
      })
      .finally(function () {
        res.end();
      });
};

var userInfo = function userInfo(req, res) {
  User.getById(req)
      // run companies/listings/applications in "parallel"
      .then(function fullfilled(user) {
        return Promise.all([
          user.id,
          user.name,
          user.createdAt,
          user.companies(req),
          user.listings(req),
          user.applications(req)
        ]);
      })
      .then(function fullfilled([
            id, name, createdAt, companies, listings, applications]) {
        res.json({
          id: id,
          name: name,
          createdAt: createdAt,
          companies: companies,
          listings: listings,
          applications: applications
        });
      })
      .catch(function rejected(error) {
        console.log('error', error.message);
        throw error;
      })
      .finally(function () {
        res.end();
      });
};

var Promise = require('promise');

module.exports = User;

/**
 * User m2m Company
 * User o2m Listing
 * User m2m applications
 */
function User(opt_data) {
  var data = opt_data || {};

  this.id = data['id'] || null;
  this.name = data['name'] || '';
  this.createdAt = data['created_at'] || new Date();
  this._companies = [];
  this._listings = [];
  this._applications = [];
  this._appliedListings = [];
}
User._RESOURCE_LIMIT = 5;
var UserProto = User.prototype;

User.topActiveUsers = function topActiveUsers(req) {
  var queryString = "select * from users u inner join "+
      "(select user_id, count(id) cnt from applications "+
      "where id in (select id from applications where "+
      "created_at > current_date - interval '1 week') "+
      "group by user_id) a on u.id = a.user_id order by a.cnt desc";
  return queryPromise(req, queryString)
      .then(function fullfilled(result) {
        return result.rows.map(function(row) {
          return new User(row);
        });
      });
};

User.getById = function getById(req) {
  var queryString = 'select * from users where id = $1::int';
  return queryPromise(req, queryString, [req.query.id])
      .then(function fullfilled(result) {
        return new User(result.rows[0]);
      });
};

UserProto.companies = function companies(req) {
  var queryString = 'select c.id, c.name, t.contact_user '+
      'from companies c, teams t '+
      'where t.user_id = $1::int and t.company_id = c.id '+
      'limit $2::int';
  return queryPromise(req, queryString, [this.id, User._RESOURCE_LIMIT])
    .then(function fullfilled(result) {
      return result.rows.map(function (data) {
        return new Company(data);
      });
    });
};

UserProto.listings = function listings(req) {
  var queryString = 'select * from listings '+
      'where created_by = $1::int '+
      'limit $2::int';
  return queryPromise(req, queryString, [this.id, User._RESOURCE_LIMIT])
    .then(function fullfilled(result) {
      return result.rows.map(function (data) {
        return new Listing(data);
      });
    });
};

UserProto.applicationListings = function applications(req) {
  var queryString = "select * from listings l inner join "+
      "(select listing_id, user_id, created_at from applications) a "+
      "on a.listing_id = l.id "+
      "where a.user_id = $1::int order by a.created_at desc limit 3";
  var self = this;
  return queryPromise(req, queryString, [this.id])
      .then(function fullfilled(result) {
        self._appliedListings = result.rows.map(function (data) {
          return new Listing(data);
        });
        return self;
      });
};

UserProto.applications = function applications(req) {
  var queryString = 'select a.id as app_id, a.created_at, a.cover_letter, '+
    'l.id as list_id, l.name, l.description '+
    'from applications a, listings l '+
    'where a.user_id = $1::int and a.listing_id = l.id '+
    'limit $2::int';
  return queryPromise(req, queryString, [this.id, User._RESOURCE_LIMIT])
      .then(function fullfilled(result) {
        return result.rows.map(function (data) {
          return new Application(data);
        });
      });
};

function Company(opt_data) {
  var data = opt_data || {};
  this.id = data['id'] || null;
  this.createdAt = data['created_at'] || new Date();
  this.name = data['name'] || '';
  this.isContact = false;
}

function Listing(opt_data) {
  var data = opt_data || {};
  this.id = data['id'] || null;
  this.createdAt = data['created_at'] || new Date();
  this.name = data['name'] || '';
  this.description = data['description'] || '';
}

function Application(opt_data) {
  var data = opt_data || {};
  this.id = data['id'] || null;
  this.createdAt = data['created_at'] || new Date();
  this.listing = data['listing'] || null;
  this.coverLetter = data['cover_letter'] || '';
}

function queryPromise(req, queryString, queryParams, debug) {
  if (debug) {
    console.log(queryString, queryParams);
    req.client.connection.on('message', function(msg) {
      console.log(msg)
    });
  }
  return new Promise(function _promise(resolve, reject) {
    req.client.query(
        queryString,
        queryParams || [],
        function result(error, result) {
      req.done(error);

      if (error) {
        console.log('error ' + error.message);
        return reject(error);
      }
      resolve(result);
    });
  });
};

var pg = require('pg');

module.exports = function() {
  var config = {
    port: 5432,
    max: 10,
    idleTimeoutMillis: 30000
  };
  switch (process.env.NODE_ENV) {
    case 'development':
      config.host = 'localhost';
      config.user = 'xxxx';
      config.password = 'xxxx';
      config.database = 'xxxx';
      break;
    case 'production':
      config.user = 'xxxx';
      config.database = 'xxxx';
      config.password = 'xxxx';
      config.host = 'xxxx'
      break;
    default:
      throw new Error('Invalid enviroment');
  }

  var pool = new pg.Pool(config);

  pool.connect(function(err, client, done) {
    if(err) {
      return console.error('error fetching client from pool', err);
    }
    client.query('SELECT $1::int AS number', ['1'], function(err, result) {
      done(err);

      if(err) {
        return console.error('error running query', err);
      }
      console.log(result.rows[0].number);
      //output: 1
    });
  });

  pool.on('error', function (err, client) {
    // if an error is encountered by a client while it sits idle in the pool
    // the pool itself will emit an error event with both the error and
    // the client which emitted the original error
    // this is a rare occurrence but can happen if there is a network partition
    // between your application and the database, the database restarts, etc.
    // and so you might want to handle it and at least log it out
    console.error('idle client error', err.message, err.stack)
  });

  return pool;
};

create table users (
    id serial primary key,
    created_at timestamp default current_timestamp,
    name character varying(64)
);

create table companies (
    id serial primary key,
    created_at timestamp default current_timestamp,
    name character varying(64)
);

create table teams (
    id serial primary key,
    company_id integer references companies (id),
    user_id integer references users (id),
    contact_user boolean default false
);

create table listings (
    id serial primary key,
    created_at timestamp default current_timestamp,
    created_by integer references users (id),
    name character varying(64),
    description text
);

create table applications (
    id serial primary key,
    created_at timestamp default current_timestamp,
    user_id integer references users (id),
    listing_id integer references listings (id),
    cover_letter text
);
router.get('/topActiveUsers', (req, res) => {
  topActiveUsers(req, res);
});

router.get('/users', (req, res) => {
  userInfo(req, res);
});

app.use(router);

app.get('*', function (req, res) {
  res.status(400).send('Invalid route');
});

app.listen(PORT, function () {
  console.log('App listening on port ' + PORT);
});

var topActiveUsers = function topActiveUsers(req, res) {
  var ENTRIES_PER_PAGE = 3;
  var startIndex = 0;
  var total = 0;
  req.query.page = +req.query.page || 0;

  var pageNum = req.query.page > 0 ? req.query.page : 0;
  if (pageNum > 0) {
    startIndex = ENTRIES_PER_PAGE * (pageNum - 1);
  }
  total = ENTRIES_PER_PAGE * (pageNum + 1);

  User.topActiveUsers(req)
      .then(function fullfilled(users) {
        if (users.length < startIndex) {
          throw new Error('Invalid pagination offset');
        }
        if (users.length > total) {
          users = users.slice(startIndex, startIndex + ENTRIES_PER_PAGE);
        } else {
          users = users.splice(startIndex);
        }
        return Promise.all(users.map(function (user) {
          return user.applicationListings(req);
        }));
      })
      .then(function fullfilled(users) {
        var result = users.map(function (user) {
          return {
            id: user.id,
            name: user.name,
            count: user._appliedListings.length,
            createdAt: user.createdAt,
            listings: user._appliedListings
          };
        });
        res.json(result);
      })
      .catch(function rejected(error) {
        console.log(error.message);
        throw error;
      })
      .finally(function () {
        res.end();
      });
};

var userInfo = function userInfo(req, res) {
  User.getById(req)
      // run companies/listings/applications in "parallel"
      .then(function fullfilled(user) {
        return Promise.all([
          user.id,
          user.name,
          user.createdAt,
          user.companies(req),
          user.listings(req),
          user.applications(req)
        ]);
      })
      .then(function fullfilled([
            id, name, createdAt, companies, listings, applications]) {
        res.json({
          id: id,
          name: name,
          createdAt: createdAt,
          companies: companies,
          listings: listings,
          applications: applications
        });
      })
      .catch(function rejected(error) {
        console.log('error', error.message);
        throw error;
      })
      .finally(function () {
        res.end();
      });
};

insert into users (id, created_at, name) values
    (1, '2015-01-13 15:30', 'Mark'),
    (2, '2015-01-13 15:30', 'John'),
    (3, '2016-01-01 10:30', 'Melinda'),
    (4, '2016-01-17 23:30', 'Carl'),
    (5, '2016-02-02 16:30', 'Tim'),
    (6, '2016-02-02 16:30', 'Jessica')
;

insert into companies (id, created_at, name) values
    (1, '2015-01-13 15:00', 'Facewall'),
    (2, '2015-01-17 15:00', 'Carl & Co')
;

insert into teams (company_id, user_id, contact_user) values
    (1, 1, TRUE),
    (2, 3, FALSE),
    (2, 4, TRUE)
;

insert into listings (id, created_at, created_by, name, description) values
    (1, '2015-01-15 11:00', 1, 'Join us conquering the world!', 'This is your best chance to be on the right side of the equation...')
;

insert into applications (created_at, user_id, listing_id, cover_letter) values
    ('2015-01-16 12:00', 2, 1, 'Hello, ...')
;