CODEWITHSUNDEEP
javascriptmysqljsontypescriptknex.js
jakubka
jakubka

Reputation: 736

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
);

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?

Upvotes: 1

Views: 2001

Answers (3)

Alon Bar David
Alon Bar David

Reputation: 1727

Well you could call your own function before inserting that converts all objects to string and call it every time before you insert.

You can probably wrap knex to do it automatically as well.

Upvotes: 0

Gökhan Ayhan
Gökhan Ayhan

Reputation: 1299

I think the easiest way using jsonb data type. mysql json type

We prefer postgresql for this kind of problem at office, easier and solid database for your problem.

Upvotes: 1

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

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.

Upvotes: 2

Related Questions