使用knex插入MySQL JSON列时自动对对象进行字符串化 [英] Automatically stringifying object when inserting to a MySQL JSON column with knex

问题描述

让我们直接跳至示例代码:

Let's jump straight to an example code:

create table test_json_table
(
    data json not null
);

我可以这样插入表格:

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)
// ]

插入行时，JSON列需要作为序列化字符串传递.检索行时，将返回一个已经解析的对象.

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.

这在项目中造成了很大的混乱.我们正在使用TypeScript，并且希望插入的类型与选择的类型相同，但这使它成为不可能.总是有字符串或总是对象是可以的.

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?

如果knex提供了一个钩子，插入时我们可以手动将对象序列化为字符串，那就太好了.

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

最简单的方法是什么?是否有任何轻量级 ORM对此提供支持?还是其他选择?

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

推荐答案

您可以尝试objection.js，该方法允许您声明某些要标记为json属性的列，并且在插入/更新它们的值时应自动将其字符串化 https://vincit.github.io/objection. js/api/model/static-properties.html#static-json属性

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

我还没有尝试过它是否可以与mysql一起使用.我看不出为什么不会.

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

这篇关于使用knex插入MySQL JSON列时自动对对象进行字符串化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！