Running into a Knex.js & MySql Error when adding json Column

Question

I'm not sure what I'm doing wrong here, but I've added a jsonb column in my migrations and I keep running into an error:

I've also tried the regular .json column as well as knex described here. But that doesn't seem to work either. There is a part in the docs that says:

For PostgreSQL, due to incompatibility between native array and json types, when setting an array (or a value that could be an array) as the value of a json or jsonb column, you should use JSON.stringify() to convert your value to a string prior to passing it to the query builder, e.g.

I figured since I'm not using PostgreSQl, it shouldn't matter if I stringify it or not, however I did try to stringify the data, alas to no avail in my case.

Error I'm running into:

Error: ER_WRONG_VALUE_COUNT_ON_ROW: Column count doesn't match value count at row 1

I've added in the many columns in this example to keep it as close as possible to my code to help debug. Here's my code below:

exports.up = (knex) => {
  return knex.schema.createTable("foo", (table) => {
    table.increments("id").primary();
    table.string("bar").notNullable();
    table.decimal("fiz", 14, 2).notNullable().defaultTo(20);
    table.decimal("buzz", 14, 2).defaultTo(0);
    table.decimal("biz", 14, 2).defaultTo(0);
    table.string("foobar").notNullable();
    table.jsonb("error").notNullable();
  });
};

My seed_data looks like :

 {
    id: 1,
    bar: "example",
    fiz: 1234,
    buzz: 1234,
    biz: 1234,
    foobar: "example",
    error: {
      data1: "data",
      data2: "data",
    },
  },

Versions of dependencies:

"mysql": "^2.18.1",
"knex": "^0.21.12",
"bookshelf": "^1.2.0",

Anywhere where someone may spot something wrong with my code?

felixmosh · Accepted Answer · 2021-01-15 15:21:34Z

3

Knex doesn't know the types of columns when it inserts to them, therefore, It doesn't know to convert them to string before sending it to the DB.

Mysql json columns expects to get the JSON as a string, try to call JSON.stringify(error) before passing it to insert.

Knex('table').insert(
 {
    id: 1,
    bar: "example",
    fiz: 1234,
    buzz: 1234,
    biz: 1234,
    foobar: "example",
    error: JSON.stringify({
      data1: "data",
      data2: "data",
    }),
  },
);

answered Jan 15, 2021 at 15:21

felixmosh

35.9k10 gold badges81 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Running into a Knex.js & MySql Error when adding json Column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related