Skip to content

Instantly share code, notes, and snippets.

@gregbarcza

gregbarcza/Promise_postgres_transactions.js

Last active September 21, 2015 20:34
Show Gist options
  • Save gregbarcza/a478912be460a8790a62 to your computer and use it in GitHub Desktop.
Save gregbarcza/a478912be460a8790a62 to your computer and use it in GitHub Desktop.
Download ZIP
Using promise based PostgreSQL transactions in sails.js
Raw
Promise_postgres_transactions.js
var Promise = require("bluebird");
var util = require('util');
var moment = require('moment');
var pg = require('pg');
module.exports = {
process: function(req, res) {
//YOUR CONNECTION NAME HERE
var pConfig = sails.config.connections.PostgresqlServer;
var conString = util.format("postgres://%s:%s@localhost:%s/%s", pConfig.user, pConfig.password, pConfig.port, pConfig.database);
var client = new pg.Client(conString);
Promise.promisifyAll(client);
Users.findOne({code:code})
.then(function (_user) {
return Accounts.findOne(data).populateAll();
})
.then(function (_account) {
account = _account;
return client.connectAsync();
})
.then(function(){
return client.queryAsync('BEGIN');
})
.then(function() {
var sql1 = util.format('UPDATE accounts SET balance = balance - %d, "isUnused" = FALSE WHERE id = %d', bonus, account.id);
var u1 = client.queryAsync(sql1);
//var t1 = Transactions.create({type:'redemption', amount: bonus, account: account.id});
var sql2 = util.format('INSERT INTO "transactions" ("type", "amount", "account", "createdAt", "updatedAt") values (\'%s\', \'%s\', \'%s\', \'%s\', \'%s\')', 'redemption', bonus, account.id, postgresNow(), postgresNow());
var t1 = client.queryAsync(sql2);
//var r = Redemptions.create({amount: bonus, paid: amount, account: account.id});
var sql3 = util.format('INSERT INTO "redemptions" ("amount", "paid", "account", "createdAt", "updatedAt") values (\'%s\', \'%s\', \'%s\', \'%s\', \'%s\') ', bonus, amount, account.id, postgresNow(), postgresNow());
var r = client.queryAsync(sql3);
return Promise.all([bonus, u1, t1, r]);
})
.then(function () {
return client.queryAsync('COMMIT');
})
.then(function() {
res.send(data)
})
.catch(function(err) {
client.queryAsync('ROLLBACK');
res.negotiate(err);
})
}
};
function postgresNow(){
return moment().format('YYYY-MM-DD HH:mm:ss ZZ');
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment