Easy bloggin' w/ Node

db.js

var pg = require('pg');

var dcs = process.env['DATABASE_URL'];

function exec(sql, binds) {
  return new Promise(function (resolve, reject) {
    pg.connect(dcs, function connect(err, db, release) {
      if (err) {
        reject(err);
      }

      db.query(sql, binds, function query(err, result) {
        if (err) {
          reject(err);
        }

        release();  // Release back to pool
        resolve(result);
      });
    });

    pg.end();
  });
}

pg.on('error', function (err) {
  throw err;
});

module.exports = {
  pg: pg,
  exec: exec,
};

index.js

var express = require('express');
var bodyParser = require('body-parser');

var app = express();
var db = require('./db')
var Posts = require('./posts')(express, db);

/*
 * Middleware
 */
app.use(bodyParser.json());
app.use('/posts', Posts);

app.listen(8080);

package.json

{
  "name": "blagh",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.10.2",
    "express": "^4.11.1",
    "pg": "^4.2.0"
  }
}

posts.js

function Posts(express, db) {
  var router = express.Router();

  function all(req, res, next) {
    var sql = 'SELECT * FROM posts';

    db.exec(sql).then(function success(result) {
      res.json(result.rows);
    }, function fail(err) {
      res.status(500).json(err.message);
    });
  }

  function one(req, res, next) {
    var id = req.params.id;
    var sql = 'SELECT * FROM posts WHERE id=$1::int LIMIT 1';

    db.exec(sql, [id]).then(function success(result) {
      if (result.rowCount === 0) {
        return statusNotFound(res, 'No post found w/ ID: ' + id);
      }

      res.json(result.rows[0]);
    }, function fail(err) {
      res.status(500).json(err);
    });
  }

  function create(req, res, next) {
    var title = req.body.title;
    var body = req.body.body;
    var sql = 'INSERT INTO posts (title, body) VALUES ($1, $2) ' +
      'RETURNING id, title, body';

    db.exec(sql, [title, body]).then(function success(result) {
      var post = result.rows[0];
      res.location('/posts/' + post.id).status(201).json(post);
    }, function fail(err) {
      res.status(500).json(err);
    });
  }

  function update(req, res, next) {
    var title = req.body.title;
    var body = req.body.body;
    var id = req.params.id;
    var sql = 'UPDATE posts SET title=$1, body=$2 WHERE id=$3::int';

    db.exec(sql, [title, body, id]).then (function success(result) {
      if (result.rowCount === 0) {  // Not found
        return statusNotFound(res, 'No post found w/ ID: ' + id);
      }

      res.json({ id: id, title: title, body: body });
    }, function fail(err) {
      res.status(500).json(err);
    });
  }

  function destroy(req, res, next) {
    var id = req.params.id;
    var sql = 'DELETE from POSTS WHERE id=$1';

    db.exec(sql, [id]).then(function success(result) {
      if (result.rowCount === 0) {
        return statusNotFound(res, 'No post found w/ ID: ' + id);
      }

      res.status(204).end();
    }, function fail(err) {
      res.status(500).json(err);
    });
  }

  router.get('/', all);
  router.get('/:id', one);
  router.patch('/:id', update);
  router.post('/', create);
  router.delete('/:id', destroy);

  /*
   * Helpers.
   */
  function statusNotFound(res, desc) {
    console.error(desc);
    res.status(404).json({
      error: {
        code: 404,
        message: 'Not found',
        description: desc
      }
    });
  }

  return router;
}

module.exports = Posts;