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

Question

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!

Answer 1

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.