Can I use knex.js onConflict().merge() to add to an integer column's value?

Question

I have a MySQL table something like this:

CREATE TABLE `some_table` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `some_other_id` INT(11) NOT NULL,
    `some_date` DATE NOT NULL,
    `some_total` INT(11) NOT NULL DEFAULT 0,
    `UNIQUE KEY `uq_some_key` (`some_other_id`, `period_date`)
)

I can "upsert" row values, adding to the some_total column, in MySQL with this:

INSERT INTO `some_table` (`some_other_id`, `some_date`, `some_total`)
VALUES (1, '2022-01-01', 1)
ON DUPLICATE KEY UPDATE
    `some_total` = `some_total` + VALUES(`some_total`);

This statement inserts a row if the unique constraint on some_other_id and some_date is not hit, or updates the row if the constraint is hit.

Is it possible to use Knex.js to do this? I see that it has onConflict() and merge() functionality to emulate ON DUPLICATE KEY UPDATE, but I don't know if it will infer the unique constraint for the onConflict(). And I can't figure out the syntax. When I try this, some_total is undefined:

knex('some_table')
    .insert({
        some_other_id: 1,
        some_date: '2022-01-01',
        some_total: 42
    })
    .onConflict()
    .merge({
        some_total: some_total + 42
    });

Is this possible, or do I have to use a raw() query?

Answer 1

Additions are a bit more tricky than directly referencing a column (which could be done with knex.ref()) so I don't think there is a way to avoid raw entirely, however you can use just a small snippet of raw and keep the rest of your query builder.

knex('some_table')
    .insert({
        some_other_id: 1,
        some_date: '2022-01-01',
        some_total: 42
    })
    .onConflict()
    .merge({
        some_total: knex.raw('?? + 42', 'some_total')
    });