title: Express with pug, Postgres and proper MVC
body: <p>I recently started using Node.js + Express.js (generated with pug) + pg-promise for handling db.</p>
  
  <p>My first target is to obtain data from Postgres (already set up) and display it pretty using render and pug. Let's say it is user list from <em>Users</em> table.</p>
  
  <p>On <a href="https://mherman.org/blog/2016/03/13/designing-a-restful-api-with-node-and-postgres/" rel="nofollow noreferrer">this</a> restful tutorial  I have learned how to get data and return it as JSON - <strong>it worked</strong>. </p>
  
  <p>Based on <a href="https://developer.mozilla.org/en-US/docs/Learn/Server-side/Express_Nodejs/routes" rel="nofollow noreferrer">Mozilla's tutorial</a> I seperated my code:</p>
  
  <ul>
  <li><em>routes/users.js</em>: where for '/' I call <em>user_controller.user_list</em> method (using <em>router.get</em>)</li>
  <li><em>controllers/userController.js</em> I have exported <em>user_list</em> where I would like to ask <strong>model</strong> for data and call <strong>render</strong> if I have results</li>
  <li><em>queries.js</em> which is kinda my model? But I'm not sure. It has API: connection to db with promises and one function for every query I am going to use in Controllers. I believe I should have like one Model file per table (or any logical entity) but where to store pgp connections? 
  This file is based on first tutorial I mentioned</li>
  </ul>
  
  <pre>
  
      // queries.js (connectionString is set properly to my postgres)
      var pgp = require('pg-promise')(options);
      var db = pgp(connectionString);
      function getUsers(req, res, next) {
          db.any('SELECT (user_id, username) FROM public.users ORDER BY user_id ASC LIMIT 1000')
          .then(function (data) {
              res.json({ data: data });
          })
          .catch(function (err) {
              return next(err);
          });    
      }
      module.exports = {
          getUsers: getUsers
      };
  
  </pre>
  
  <p>Here starts my problem as most tutorials uses mongoose which is very model-db-schema-friendly and what I have is simple 'SELECT ...' string I pass to pg-promise's any() function. 
  Therefore I have no model class like User.</p>
  
  <p>In <em>userControllers.js</em> I don't know how to call <em>getUsers()</em> to handle its data. Returning JS object from getUsers() would be nice.</p>
  
  <p>Also: where should I call render? In controller or only in </p>
  
  <p><code>db.any(...).then(function (data) { &lt;--here--&gt; })</code></p>
  
  <p>Before, I also tried to embed whole Postgres handling into Controller but from db.any() I got this array for handling:</p>
  
  <p><code>[{ row: '(1,John)' },{ row: '(2,Amy)' },{ row: '(50,Peter)' } ]</code></p>
  
  <p>Didn't know how go from there as I probably lost my API functionality as well ;-)</p>
  
  <p>I am browsing through multiple tutorials how to handle MVC but usually they handle MongoDB and 
  satisfy readers with res.send() not render().</p>