transactions broken with postgres

[knex]

transactions broken with postgres

[tgriesser]

Hey @kubino - you're right, I did some major refactoring of a few pieces before shipping the initial release and didn't realize I didn't have any tests for transactions. I believe I just fixed it up and added some tests, please take a look and let me know if you see any issues now.

[kubino]

Hi perfect, transactions are now working. Your library looks amazing. I am pretty new to promises, so it's hard to get full picture of your internals for me. However I have different problem now. In Postgres also Schema changes can be run inside the transaction. This is also a good way how to control order of sql command - I want to ensure delete table is called before create table. I am passing now transaction object to dropTable and createTable methods and inside I am instructing to use same connection. Maybe it would be usefull to support it out of the box.

[tgriesser]

@kubino - If you want to ensure dropTable is called before createTable, you should be able to achieve that with a promise, no transaction necessary.

Knex.Schema.dropTableIfExists('users').then(function () {

   return Knex.Schema.createTable('users', function (t) {
      t.increments('id');
      t.string('first_name');
      t.string('last_name');
   });

}).then(function() {
   console.log('users has been dropped and created');
})

[kubino]

Hi I did tried this, sorry I forgot to mention it before.
It works with mysql but NOT with postgres. However when runned within transaction and same connection is used, everything is just fine. Not sure where is the difference between these two databases. I guess both are opening new connection, and we are just relying here there everything will get processed by database as it was issued by node server. But this was not my case with Postgres. I believe it can work on other machines...

[tgriesser]

@kubino Can you paste some example code where it didn't work.

[kubino]

sorry forget it it was my fault, just called a method instead of passing a function reference in then() !!!

Your library is awesome , I was looking hard for some simple but powerful API. Great Job. Thank you for sharing it

[tgriesser]

Glad to hear it! Also, you don't need to create individual when.defer() instances or individual error handlers for each a promise, just return a promise to the promise and the first promise becomes the second... and if any of them fail the last promise gets that error... i.e.:

var promises = [];
_.each(Object.keys(tablesDefinition), function(tblName) {
   promises.push(Knex.Schema.createTable(tblName, tablesDefinition[tblName]).then(function() {
      console.log('Created %s!', tblName);
   }));
});
return when.all(promises).then(null, function(e) { 
   console.log('createTableError: ' + e.toString()) 
});

rather than what you had posted before.