How to loop through multi-line sql query and use them in Knex Transactions?

user_5191953 0
Viewed 2061

Newbie in Node and Knex here coming from an RoR background where I need to convert a specific function to Node.js

This is my RoR code:

  def self.save!
    update_queries = self.fetch.map{|currency, rate|
      "UPDATE currency_exchange_rates SET rate='#{rate}' WHERE currency='#{currency}'"
    }.join(';')

    ActiveRecord::Base.connection.execute(update_queries)
  end

So far here's what I have in Nodejs:

static save(new_rates){
      return new Promise(async (resolve, reject) => {
        var query = Object.keys(rate).map(key => 'UPDATE currency_exchange_rates SET rate=' + rate[key] + ' WHERE currency = "' + key + '"').join('; ');
    })
  }

Here's what the input data looks like:

NEW RATES = 

'UPDATE currency_exchange_rates SET rate=32102793.12 WHERE currency= "IDR"; 

UPDATE currency_exchange_rates SET rate=0.7822 WHERE currency= "USDGBP"; 

UPDATE currency_exchange_rates SET rate=3189.756317 WHERE currency= "CAD"; 

UPDATE currency_exchange_rates SET rate=152.8 WHERE currency= "USDLKR"; 

UPDATE currency_exchange_rates SET rate=110.818 WHERE currency= "USDJPY"; 

UPDATE currency_exchange_rates SET rate=1.3103 WHERE currency= "USDAUD"'

I'm using Knex.js to connect to my pg database. How can I execute this all of these using knex? Can I just pass all of the queries in knex.raw()? How about knex.transaction()? What are their differences?

Thanks in advance!

knex.js node.js
2 Answers
// [Sequential Update] Commit transaction after each successfully executed query
await knex.transaction(async trx => {
    return Promise.all(Object.keys(rate).map(key => trx('currency_exchange_rates')
            .update({ rate: rate[key] })
            .where({ currency: key })
    ));
});

// [Bulk Update] Commit transaction after all queries have been executed successfully
await knex.transaction(trx => {
    return Promise.all(Object.keys(rate).map(key => knex('currency_exchange_rates')
            .update({ rate: rate[key] })
            .where({ currency: key })
        ))
        .then(trx.commit)
        .catch(trx.rollback);
});
user_70269280

Something like this is knexy way to do it:

let retVal = await knex.transaction(async (trx) => {
  for (let key of Object.keys(rate)) {
    let value = rate[key];
    await trx('currency_exchange_rates')
      .update({ rate: value }).where('currency', key);
  }
  return "woo done"; // this value is returned from transaction 
});

When dealing with transactions, usually you want to create and run queries one by one to avoid unnecessary buffering of queries before running them through the transaction's single DB connection.

user_3600600
Related