js mysql json字符串,使用knex插入MySQL JSON列时自动对对象进行字符串化

Let's jump straight to an example code:

create table test_json_table

(

data json not null

);

I can insert to the table like this:

const columns = { data: "{ some_json: 123 }" }; // notice that the data column is passed as string

await knex('test_json_table').insert(columns);

And get data from the table like this:

await knex('test_json_table').select();

// returns:

// [

// { data: { some_json: 123 } } // notice that the data is returned as parsed JavaScript object (not a string)

// ]

When inserting a row the JSON column needs to be passed as a serialised string. When retrieving the row, an already parsed object is returned.

This is creating quite a mess in the project. We are using TypeScript and would like to have the same type for inserts as for selects, but this makes it impossible. It'd be fine to either always have string or always object.

I found this topic being discussed at other places, so it looks like I am not alone in this (link, link). It seems like there is no way to convert the object to string automatically. Or I am missing something?

It'd be nice if knex provided a hook where we could manually serialise the object into string when inserting.

What would be the easiest way to achieve that? Is there any lightweight ORM with support for that? Or any other option?

解决方案

You could try objection.js that allows you to declare certain columns to be marked as json attributes and those should be stringified automatically when inserting / updating their values https://vincit.github.io/objection.js/api/model/static-properties.html#static-jsonattributes

I haven't tried if it works with mysql though. I don't see any reason why it wouldn't.