CODEWITHSUNDEEP
javascriptknex.js
Jujubes
Jujubes

Reputation: 434

knex - chaining statements

I'm curious how knex works because it seems so fluid whereas sql is more structured. Ultimately knex transpiles into sql or whatever database engine.

Questions

  1. Is it better to chain methods to continually resolve the promise? ex:

    knex('table').select('wire').limit(1).then((row)=>{
 if(row){
   knex('table2').update()... (1)
 }
 throw new Error('Nothing')
}).then(r=>{
   knex('tablw')..... (2)
})
....
catch(e=>{
   ....
})

or to create a transaction in knex?

  1. When is knex actually querying the database? Does it only happen once all the promises are resolved? or is the database being called at every knex() statement?

I cannot find any documentation on how it actually calls/queries the database.

From first glance, it looks like each knex() statement would call the database (in my example it would be three separate times?) which would mean that creating a transaction would be 1000% better than all these small queries/calls

thanks a bunch!

Upvotes: 3

Views: 1073

Answers (2)

Shayan Shaikh
Shayan Shaikh

Reputation: 65

The update can be done in batches, i.e 1000 rows in a batch

And as long as it does it in batches, the bluebird map could be used.

For more information on bluebird map: http://bluebirdjs.com/docs/api/promise.map.html

const limit = 1000;
const totalRows = 50000;
const seq = count => Array(Math.ceil(count / limit)).keys();
let noErrors = true;
map(seq(totalRows), page => updateTable(dbTable, page), { concurrency: 1 });

const updateTable = async (dbTable, page) => {
let offset = limit* page;

return knex(dbTable).pluck('id').limit(limit).offset(offset).then(ids => {
    return knex(dbTable)
        .whereIn('id', ids)
        .update({ date: new Date() })
        .then((rows) => {
            console.log(`${page} - Updated rows of the table ${dbTable} from ${offset} to ${offset + batch}: `, rows);
        })
        .catch((err) => {
            noErrors = false;
            console.log({ err });
            rollback();
            throw error; // Both with and without it - failed
        });
})
.then(() => {
    if(noErrors)
        console.log({result:'MyResultsHere'});
    else
        console.log({ err });
})
.catch((err) => {
       console.log({ err });
 });
};

Where pluck() is used to get ids in array form.

You can create a custom rollback() function if you want that if any error is occured during process you want to undo your changes.

Upvotes: 0

felixmosh
felixmosh

Reputation: 35493

Excellent questions, here are some answers:

  1. It depends on your need, transaction is not a query aggregator but a mechanisem which allows you to query the db with multiple queries which will guarantee that they will run as one. For example, when you need to make several changes in several tables, which if one of them fail won't make sense to execute the others. The classic example is a bank app, which you should take money from one user and add it to other. If the first action (taking the money) succeed but the second action fails, your system is in unknown state. To prevent this, you run them in transaction, if anything will fail the DB will rollback the action.

Small tip, make use of async/await syntax, it will make you code more readable.

  1. Knex is a query builder, it follows the Builder design pattern, this means that all the method are "accumulated" inside it and once you call the then method it builds the query and passes it to the DB. You can test it, build a select query and don't call the "then", it won't execute any query on the DB.

Upvotes: 2

Related Questions