Getting error when using objection JS to create query: NJS-012: encountered invalid bind data type in parameter 2

Question

I have been spending hours trying to debug this error. I am using Node, Joi and Oracledb. When I attempted to make a POST request to insert my data from the payload of the request to my table, it gives me the error: NJS-012: encountered invalid bind data type in parameter 2. Nothing I did has been productive to fix the issue. What did I do wrong to cause this issue? The code I use to update my table is in this manager: manager.js

async function create(payload) {
  try {
    return await MassRepublishJob.query().insertAndFetch(payload)
  } catch (error) {
    log.error(`Error while creating mass republish jobs with payload: ${JSON.stringify(payload)}`, error)
    }
}

This manager code is called from controller code: Controller.js

async save(request, reply) {
  const instance = await massRepublishJobManager.create(request.payload)
  reply(instance)
}

This controller is the handler of my POST route:

method: 'POST',
path: `/${root}`,
config: {
  tags: ['api'],
  handler: controller.save,
  description: 'Create new mass republish job',
  notes: 'You can create a mass republish job by either sending a list of content ids or send an object of search querys',
  validate: {
    payload: {
      counts: Joi.object().example(Joi.object({
        total: Joi.number().example(1),
        completed: Joi.number().example(1),
        failed: Joi.number().example(0),
        queued: Joi.number().example(0),
      })),
      type: Joi.string().example('manual'),
      content_ids: Joi.array().items(Joi.number().example(11111)),
      search_query: Joi.object().example(Joi.object({
        query: Joi.string().example('string'),
      })),
      republishing_reason: Joi.string().example('string'),
      duration: Joi.number().example(0),
    }
  }
}

And finally, I set up the table as described in this sql file, the fields "creation_date", "last_update_date", "created_by", "last_updated_by" are the field of the base model. This model extends that base models with extra fields: "counts", "type", "content_ids", "search_query", "republishing_reason", "duration"

CREATE TABLE wwt_atc_api.mass_republish_jobs (
  id                     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  counts                 CLOB DEFAULT('{}') CHECK(counts IS JSON), -- JSON object of 
  counts: each has completed, failed, total, queued
  type                   VARCHAR(255) NOT NULL, -- right now only support 'manual' but can be more in the future
  content_ids            CLOB DEFAULT('[]'),
  search_query           CLOB DEFAULT('{}') CHECK(search_query IS JSON), -- JSON object of query: each has field, operator, value
  republishing_reason    VARCHAR(255),
  duration               NUMBER NOT NULL,
  creation_date          DATE   NOT NULL,
  last_update_date       DATE   NOT NULL,
  created_by             NUMBER NOT NULL,
  last_updated_by        NUMBER NOT NULL
);
GRANT SELECT, INSERT, UPDATE, DELETE ON WWT_ATC_API.mass_republish_jobs TO wwt_cf_atc_api;

Knex instance also prints out this debugging message:

method: 'insert',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [
  [ { id: 11111 } ],
  '{"total":1,"completed":1,"failed":0,"queued":0}',
  310242,
  2022-06-23T18:53:28.463Z,
  0,
  310242,
  2022-06-23T18:53:28.463Z,
  'string',
  '{"query":"string"}',
  'manual',
  ReturningHelper { columnName: 'ID' }
],
__.knexQueryUid: 'c5885af0-f325-11ec-a4bd-05b5ac65699c',
sql: 'insert into "WWT_ATC_API"."MASS_REPUBLISH_JOBS" ("CONTENT_IDS", "COUNTS", 
"CREATED_BY", "CREATION_DATE", "DURATION", "LAST_UPDATED_BY", "LAST_UPDATE_DATE", 
"REPUBLISHING_REASON", "SEARCH_QUERY", "TYPE") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
returning "ID" into ?',
outBinding: [ [ 'ID' ] ],
returning: [ 'ID' ]

I would really appreciate any helps to get me debug this error.

Answer 1

The issue here is that CONTENT_IDS is not being stringified by Knex because the value is an array. This is a well known issue with Knex that occurs because its QueryBuilder does not have any metadata to determine what format it needs to convert a given binding value into in order for that value to be acceptable to underlying driver/database. Typically objects are stringified but arrays are left as is -hence the inconsistent behavior you are seeing in the bindings. The developers of Knex thus recommend stringifying all JSON values before passing them to the QueryBuilder.

In order to ensure that stringification always properly occurs on JSON values, you should define jsonAttributes on your MassRepublishJob Objection Model.

class MassRepublishJob extends Model {
  ...
  static get jsonAttributes() {
    return ["counts", "content_ids","search_query"];
  }
}

This will ensure both that your model stringifies these values before binding and that it parses the stringified JSON when mapping rows from the database.

In addition, you can use the $parseJson or $toDatabaseJson lifecycle methods to manually modify how the Objection Model will bind arguments before any queries are run. You can further utilize objection helpers like val,raw and fn in $parseJson for fine grained control on how values for your model properties are bound.

import {fn, ...} from "objection"
...
class MassRepublishJobs extends Model {
  ...

  $parseJson(json: Pojo, opt: ModelOptions) {
    const superJson = super.$parseJson(json, opt);
    superJson.job_id = randomUUID();
    // will format sql with: to_date(?,?) and safely bind the arguments
    superJson.creation_date = fn('to_date',new Date().toIsoString(),dateFormat);
    return superJson;
  }