Get Knex.js transactions working with ES7 async / wait

Question

I am trying to connect ES7 async / await using transaction knex.js.

Although I can easily dispense with transactional code, I'm struggling to get transactions to work correctly using the above async / wait structure.

I use this module to simulate async / wait

Here I have:

Non transactional version:

works fine but not transactional

app.js

// assume `db` is a knex instance app.post("/user", async((req, res) => { const data = { idUser: 1, name: "FooBar" } try { const result = await(user.insert(db, data)); res.json(result); } catch (err) { res.status(500).json(err); } })); 

user.js

 insert: async (function(db, data) { // there no need for this extra call but I'm including it // to see example of deeper call stacks if this is answered const idUser = await(this.insertData(db, data)); return { idUser: idUser } }), insertData: async(function(db, data) { // if any of the following 2 fails I should be rolling back const id = await(this.setId(db, idCustomer, data)); const idCustomer = await(this.setData(db, id, data)); return { idCustomer: idCustomer } }), // DB Functions (wrapped in Promises) setId: function(db, data) { return new Promise(function (resolve, reject) { db.insert(data) .into("ids") .then((result) => resolve(result) .catch((err) => reject(err)); }); }, setData: function(db, id, data) { data.id = id; return new Promise(function (resolve, reject) { db.insert(data) .into("customers") .then((result) => resolve(result) .catch((err) => reject(err)); }); } 

Attempt to make a transaction

user.js

 // Start transaction from this call insert: async (function(db, data) { const trx = await(knex.transaction()); const idCustomer = await(user.insertData(trx, data)); return { idCustomer: idCustomer } }), 

it seems that await(knex.transaction()) returns this error:

[TypeError: container is not a function]

Answer 1

Async / await is based on promises, so it looks like you just need to wrap all the knex methods in order to return promise compatible objects.

Here is a description of how you can convert arbitrary functions to work with promises so that they can work with async / await:

Trying to understand how BlueBird works.

Essentially, you want to do this:

 var transaction = knex.transaction; knex.transaction = function(callback){ return knex.transaction(callback); } 

This is because "async / await requires either a function with a single callback argument or a promise," whereas knex.transaction looks like this:

 function transaction(container, config) { return client.transaction(container, config); } 

Alternatively, you can create a new async function and use it as follows:

 async function transaction() { return new Promise(function(resolve, reject){ knex.transaction(function(error, result){ if (error) { reject(error); } else { resolve(result); } }); }); } // Start transaction from this call insert: async (function(db, data) { const trx = await(transaction()); const idCustomer = await(person.insertData(trx, authUser, data)); return { idCustomer: idCustomer } }) 

It may also be useful: Knex Transaction with Promises

(Also note: I'm not familiar with the knex API, so I'm not sure which parameters are passed to knex.transaction , given above, for example).

Answer 2

I could not find a solid answer for this anywhere (with rollbacks and commits), so here is my solution.

First you need the "Promisify" function knex.transaction . There are libraries for this, but for a quick example, I did this:

 const promisify = (fn) => new Promise((resolve, reject) => fn(resolve)); 

This example creates a blog post and comment, and it goes back if there is an error.

 const trx = await promisify(db.transaction); try { const postId = await trx('blog_posts') .insert({ title, body }) .returning('id'); // returns an array of ids const commentId = await trx('comments') .insert({ post_id: postId[0], message }) .returning('id'); await trx.commit(); } catch (e) { await trx.rollback(); } 

Answer 3

I think I found a more elegant solution to the problem.

Borrowing from the knex Transaction documentation, I will match their promise style with the async / await style that worked for me.

Promise style

 var Promise = require('bluebird'); // Using trx as a transaction object: knex.transaction(function(trx) { var books = [ {title: 'Canterbury Tales'}, {title: 'Moby Dick'}, {title: 'Hamlet'} ]; knex.insert({name: 'Old Books'}, 'id') .into('catalogues') .transacting(trx) .then(function(ids) { return Promise.map(books, function(book) { book.catalogue_id = ids[0]; // Some validation could take place here. return knex.insert(book).into('books').transacting(trx); }); }) .then(trx.commit) .catch(trx.rollback); }) .then(function(inserts) { console.log(inserts.length + ' new books saved.'); }) .catch(function(error) { // If we get here, that means that neither the 'Old Books' catalogues insert, // nor any of the books inserts will have taken place. console.error(error); }); 

asynchronous / pending style

 var Promise = require('bluebird'); // import Promise.map() // assuming knex.transaction() is being called within an async function const inserts = await knex.transaction(async function(trx) { var books = [ {title: 'Canterbury Tales'}, {title: 'Moby Dick'}, {title: 'Hamlet'} ]; const ids = await knex.insert({name: 'Old Books'}, 'id') .into('catalogues') .transacting(trx); const inserts = await Promise.map(books, function(book) { book.catalogue_id = ids[0]; // Some validation could take place here. return knex.insert(book).into('books').transacting(trx); }); }) await trx.commit(inserts); // whatever gets passed to trx.commit() is what the knex.transaction() promise resolves to. }) 

Documents state:

Issuing an error directly from a transaction handler function automatically rolls back the transaction, as does returning a rejected promise.

It appears that the transaction callback function is not expected to return anything or Promise. Declaring a callback as an asynchronous function means that it returns a Promise.

One of the advantages of this style is that you do not need to trigger a rollback manually. Returning a declined promise will automatically result in a rollback.

Be sure to pass all the results you want to use elsewhere for the final call to trx.commit ().

I checked this template in my own work and it works as expected.

Answer 4

Adding an excellent answer to sf77, I implemented this template in TypeScript to add a new user, for which you need to perform the following in one transaction:

1. creating a custom record in the USER table
2. creating an entry to enter the LOGIN table

 public async addUser(user: User, hash: string): Promise<User> { //transform knex transaction such that can be used with async-await const promisify = (fn: any) => new Promise((resolve, reject) => fn(resolve)); const trx: knex.Transaction = <knex.Transaction> await promisify(db.transaction); try { let users: User [] = await trx .insert({ name: user.name, email: user.email, joined: new Date()}) .into(config.DB_TABLE_USER) .returning("*") await trx .insert({ email: user.email, hash }).into(config.DB_TABLE_LOGIN) .returning("email") await trx.commit(); return Promise.resolve(users[0]); } catch(error) { await trx.rollback; return Promise.reject("Error adding user: " + error) } } 

Answer 5

For those who come in 2019.

After I upgraded Knex to version 0.16.5. Sf77's answer no longer works due to a change in transaction Knex function:

 transaction(container, config) { const trx = this.client.transaction(container, config); trx.userParams = this.userParams; return trx; } 

Decision

Save promisify promise promisify :

 const promisify = (fn) => new Promise((resolve, reject) => fn(resolve)); 

trx update

from

 const trx = await promisify(db.transaction); 

in

 const trx = await promisify(db.transaction.bind(db)); 

Answer 6

Peter Vu's solution didn’t catch errors correctly for me, so I created a new thread in which I got a working solution with ES7 syntax: I could not catch the transaction rejection knex