Search code examples
node.jssqliteknex.js

Access column value from within knex query

I'd like to update the value of column A by applying a function to column B.

Is there a simple solution of the form:

knex('table')
    .update({
      colA: func(${colB})
    })
Solution

  • Yes, there is a way to do this within Knex.

    For SQL functions which don’t have explicit support in Knex you use knex.raw(SQLstring, parmArray) to encapsulate a SQL snippet or knex.schema.raw(...) to produce an entire SQL statement. And you use single question marks ? for value replacements, and double question marks ?? for field identifier replacements. (see link)

    So the SQL: UPDATE table SET colA = func(colB)

    ... can be produced by including a SQL snippet: (you were close)

    knex('table')
    .update({
       colA: knex.raw( 'func(??)', ['colB'] ) 
     })

    ... or as full raw SQL:

    knex.schema.raw( 'UPDATE table SET ?? = func(??)', ['colA', 'colB'] )

    Cheers, Gary.