updateOrCreate with a JSON column

[AlekSergeriePasuper]

Prerequisites

Hello fellow developers!

I firstly want to thank the adonisjs team for the amazing framework!
I was trying to use the updateOrCreate on a Model, but I got an unexpected error.

When the code triggers the "create", everything works fine and the data is inserted in the database. However, when it triggers the "update", it fails due to a bad conversion of my JSON column (I think).

I get the error (for my mysql database):

  ERROR (server/18072): update `showed_columns` set `columns` = {"line":true,"part":true,"partDescription":true,"oneYearSales":true,"vastLastYear":true,"vastYearToDate":true,"bestbuyTransbecOneYear":true,"lastSaleDate":true,"orderPoint":false,"perCar":false,
"replacementCost":true,"buyersGuide":true} where `report_id` = '1' - Unknown column 'part' in 'field list'
    err: {
      "type": "Error",
      "message": "update `showed_columns` set `columns` = {\"line\":true,\"part\":true,\"partDescription\":true,\"oneYearSales\":true,\"vastLastYear\":true,\"vastYearToDate\":true,\"bestbuyTransbecOneYear\":true,\"lastSaleDate\":true,\"orderPoint\":false,\"perCar\":false
,\"replacementCost\":true,\"buyersGuide\":true} where `report_id` = '1' - Unknown column 'part' in 'field list'",
      "stack":
          Error: update `showed_columns` set `columns` = {"line":true,"part":true,"partDescription":true,"oneYearSales":true,"vastLastYear":true,"vastYearToDate":true,"bestbuyTransbecOneYear":true,"lastSaleDate":true,"orderPoint":false,"perCar":false,"replacementCost":tr
ue,"buyersGuide":true} where `report_id` = '1' - Unknown column 'part' in 'field list'
              at Packet.asError (C:\Users\Alek\Documents\SuperBuyer\superbuyer\server\node_modules\mysql2\lib\packets\packet.js:728:17)
              at Query.execute (C:\Users\Alek\Documents\SuperBuyer\superbuyer\server\node_modules\mysql2\lib\commands\command.js:29:26)
              at Connection.handlePacket (C:\Users\Alek\Documents\SuperBuyer\superbuyer\server\node_modules\mysql2\lib\connection.js:497:34)
              at PacketParser.onPacket (C:\Users\Alek\Documents\SuperBuyer\superbuyer\server\node_modules\mysql2\lib\connection.js:97:12)
              at PacketParser.executeStart (C:\Users\Alek\Documents\SuperBuyer\superbuyer\server\node_modules\mysql2\lib\packet_parser.js:75:16)
              at Socket.<anonymous> (C:\Users\Alek\Documents\SuperBuyer\superbuyer\server\node_modules\mysql2\lib\connection.js:104:25)
              at Socket.emit (node:events:513:28)
              at addChunk (node:internal/streams/readable:324:12)
              at readableAddChunk (node:internal/streams/readable:297:9)
              at Socket.Readable.push (node:internal/streams/readable:234:10)
              at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
      "code": "ER_BAD_FIELD_ERROR",
      "errno": 1054,
      "sqlState": "42S22",
      "sqlMessage": "Unknown column 'part' in 'field list'",
      "sql": "update `showed_columns` set `columns` = `line` = true, `part` = true, `partDescription` = true, `oneYearSales` = true, `vastLastYear` = true, `vastYearToDate` = true, `bestbuyTransbecOneYear` = true, `lastSaleDate` = true, `orderPoint` = false, `perCar` = f
alse, `replacementCost` = true, `buyersGuide` = true where `report_id` = '1'",
      "status": 500
    }

As you can see, there are some missing parenthesis around the columns value in err.message . From my understanding, it should be '{"line":true,, ... }' ). I will provide you with my code so you can understand.
I also tried the "update" method but got the same error. I was able to work around this error by using the "update" method on the Database instead of the Model, but had to stringify it myself, otherwise it was failing with the same error.
I could not stringify the columns (formattedColumns) before saving them with the Model because the Model ShowedColumn is expecting a JSON with the type Columns.

It is possible that I am missing something that could be causing this issue I have and that I did not see it.
Thank you for your time!
PS: Can't wait to try the v6!

Package version

"dependencies": {
"@adonisjs/auth": "^8.2.3",
"@adonisjs/core": "^5.9.0",
"@adonisjs/lucid": "^18.4.0",
"@adonisjs/repl": "^3.1.11",
"adonis5-scheduler": "^2.0.2",
"axios": "^1.4.0",
"csvtojson": "^2.0.10",
"ftp": "^0.3.10",
"luxon": "^3.3.0",
"mysql2": "^3.5.0",
"objects-to-csv": "^1.3.6",
"proxy-addr": "^2.0.7",
"reflect-metadata": "^0.1.13",
"source-map-support": "^0.5.21",
"uuid": "^9.0.0",
"workerpool": "^6.4.0"
}

Node.js and npm version

node: v18.16.0
npm: 9.5.1

Sample Code (to reproduce the issue)

Here is my model:

import { DateTime } from "luxon";
import {
  BaseModel,
  BelongsTo,
  beforeCreate,
  beforeSave,
  belongsTo,
  column,
} from "@ioc:Adonis/Lucid/Orm";
import { v4 as uuid } from "uuid";
import Report from "./Report";

interface Columns {
  line: boolean;
  part: boolean;
  partDescription: boolean;
  oneYearSales: boolean;
  vastLastYear: boolean;
  vastYearToDate: boolean;
  bestbuyTransbecOneYear: boolean;
  lastSaleDate: boolean;
  orderPoint: boolean;
  perCar: boolean;
  replacementCost: boolean;
  buyersGuide: boolean;
}

export default class ShowedColumn extends BaseModel {
  public static table = "showed_columns";
  @column({ isPrimary: true })
  public id: string;

  @column()
  public reportId: number;

  @column()
  public columns: Columns;

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime;

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime;

  @beforeCreate()
  public static async generateUuid(showedColumn: ShowedColumn) {
    showedColumn.id = uuid();
  }

  @belongsTo(() => Report, {
    localKey: "id",
    foreignKey: "reportId",
  })
  public report: BelongsTo<typeof Report>;
}

Here is my migration:

import BaseSchema from "@ioc:Adonis/Lucid/Schema";

export default class extends BaseSchema {
  protected tableName = "showed_columns";

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.uuid("id").primary();
      table
        .integer("report_id")
        .index()
        .notNullable()
        .references("id")
        .inTable("reports")
        .unique("")
        .onDelete("CASCADE");
      table.json("columns");
      table.timestamp("created_at", { useTz: true });
      table.timestamp("updated_at", { useTz: true });
    });
  }

  public async down() {
    this.schema.dropTable(this.tableName);
  }
}

And here is where the error occurs:

import type { HttpContextContract } from "@ioc:Adonis/Core/HttpContext";
import Database from "@ioc:Adonis/Lucid/Database";
import ShowedColumn from "App/Models/ShowedColumn";

type Columns = {
  line: boolean;
  part: boolean;
  partDescription: boolean;
  oneYearSales: boolean;
  vastLastYear: boolean;
  vastYearToDate: boolean;
  bestbuyTransbecOneYear: boolean;
  lastSaleDate: boolean;
  orderPoint: boolean;
  perCar: boolean;
  replacementCost: boolean;
  buyersGuide: boolean;
};

export default class ReportsParametersController {
 public async updateShowedColumns({ request }: HttpContextContract) {
    const { columns, reportId } = request.all();

    function convertArrayToObject(arrayData) {
      const objData: Columns = {
        line: true,
        part: true,
        partDescription: true,
        oneYearSales: true,
        vastLastYear: true,
        vastYearToDate: true,
        bestbuyTransbecOneYear: true,
        lastSaleDate: true,
        orderPoint: true,
        perCar: true,
        replacementCost: true,
        buyersGuide: true,
      };

      return arrayData.reduce((acc, item) => {
        acc[item.label] = item.visible;
        return acc;
      }, objData);
    }

    const formattedColumns: Columns = convertArrayToObject(columns);

    const payload = { columns: formattedColumns, reportId };

    const showed_columns = await ShowedColumn.query()
      .where("reportId", reportId)
      .first();
    if (showed_columns) {
      //the commented lines did not work the JSON is not stringified correctly
      /*showed_columns.columns = formattedColumns;
        await showed_columns.save();*/

      /*await ShowedColumn.query()
        .where("reportId", reportId)
        .update({ columns: formattedColumns });*/
      
     //this works
      await Database.from("showed_columns")
        .where("report_id", reportId)
        .update({ columns: JSON.stringify(formattedColumns) });
    } else {
      await ShowedColumn.create(payload);
    }

    /*
    //this gave an error when it triggers the update (the JSON is not stringified correctly)
    const showed_columns = await ShowedColumn.updateOrCreate(
      { reportId: reportId },
      payload
    );*/
    return showed_columns;
  }
}

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[kilobyte2007]

Is there a fix for this? The issue persists.

[markgidman-rad]

similar issue - model.merge().save() with a json column is treating the json property like a nested column

[kilobyte2007]

I figured out the issue. It turns out you have to prepare the JSON yourself.

Use this in your model (let's say you have a settings json column):

@column({
  prepare: value => JSON.stringify(value)
})
declare settings: object

If you are using mysql2 you don't need to serialize it as mysql2 will do it for you.

[ketan]

If this is the recommended way to serialize JSON, this information ought to go into the documentation. Happy to submit documentation MR, if the maintainers can confirm this is the right way to serialize/deserialize json columns

[RomainLanz]

If this is the recommended way to serialize JSON, this information ought to go into the documentation. Happy to submit documentation MR, if the maintainers can confirm this is the right way to serialize/deserialize json columns

The serialization depends on the driver you are using and on the DBMS. For example, nothing has to be done if you are using PSQL.

If you want to add something to the documentation, I recommend making it generic and asking people to check how it is done per their DBMS/driver.