Create SQL query with knexJS

Question

I've this sql query:

UPDATE events SET quota_over = quota_over / 100 where quota_over >= 100;

How can I convert it for knexJS, I have tried with:

knex('events')
    .where('quota_over', '>=', 100)
    .update({quota_over: 'quota_over' / 100})

However, the result is not as expected

Answer 1

What you did is dividing "quota_over" string by 100 in JavaScript which would result in NaN. To achieve a similar result based on your SQL query, you can use knex.raw() to compose a raw SQL expression.

Based on this answer and this documentation, you can do something like this:

knex('events')
    .where('quota_over', '>=', 100)
    .update({quota_over: knex.raw('?? / 100', ['quota_over'])})

Where ?? is a way to bind column name reference inside knex.raw().

Hope that helps!