Introduction
This article is NestJS
a record of the know-how I realized while developing through the framework. There may be a better way than the one I suggested. Suggestions are always welcome :)
NestJS
- The framework TypeORM
manages the DB through the library. This article TypeORM
deals with how to store JSON arrays in MySQL DB.
When we save 'array' in DB column?
Storing an array type in a DB column appears more often than you think.
Foreign Key Relation (OneToMany/ManyToOne)
Person Email Consider
a case in which Entity and Entity exist for. At this time, since one person can have multiple emails, the relationship between the two is 'OneToMany/ManyToOne'. The MySQL table is stored as follows.
+----+--------------------+--------------+
| id | person | email_id(fk) |
+----+--------------------+--------------+
| 1 | Tom | 1 |
| 2 | Evans | 2, 3 |
+----+--------------------+--------------+
+----+------------------+----------------+
| id | email | person_id(fk) |
+----+------------------+----------------+
| 1 | tom@mail.com | 1 |
| 2 | evans@mail.com | 2 |
| 3 | evans@univ.ac.kr | 2 |
+----+------------------+----------------+
Looking at the two tables, each has a foreign key called email_id
and person_id
. Person
in the Evans
case of , it has two emails in particular, so it has an email_id
array of.
That is, you can find fk_id
Array under the OneToMany/ManyToOne relationship.
simple-array
Even if it is not a foreign key, you can store arrays in MySQL columns. TypeORM
helps simple-array
you to store simple arrays in MySQL through.
@Column('simple-array')
num_arr: number[];
@Column('simple-array')
str_arr: string[];
However , if you store it in a simple-array
way string[]
, some problems arise. This is dealt with in more detail later.
How to save JSON array?
So far, we have looked at the case where an array is stored in MySQL. Now let's see how we can store JSON array in MySQL.
simple-array
Let's try it!
@Column('simple-array')
json_arr: JSON[];
@Column
, simple-array
and declared in the form of an array as before. Can this be saved? NOPE! In this case, an error occurs. Let's look at the error message.
DataTypeNotSupportedError: Data type "Array" in "example.json_arr" is not supported by "mysql" database.
?? Earlier we looked at the case where MySQL uses arrays. Array but now the error message says that the type is not supported by MySQL .
The error message is correct. MySQL Array does not support types. It's just giving us the feeling of supporting arrays in some way!!
simple-array
Let's try it! - string[]
version
Let's see how to circumvent the current problem. Our purpose is still to store JSON arrays in MySQL. But the JSON[]
form is impossible, so string[]
let's bypass the method we looked at earlier.
@Column('simple-array')
json_arr: string[];
In javascript, it can be serialized in the form of through JSON.stringify()
a function called . Our plan is as follows-
- The API receives input as a JSON array.
- Iterates through the received JSON array and applies to all JSON
JSON.stringify()
to serialize -
string[]
Save as MySQL -
Get
Wheneverstring[]
a request comes in, the string in the string array stored asJSON.parse()
is parsed and returned as
If you do this, you can convert the JSON array to a string array and store it in the DB without an error message.
But…
string[] version issue
For those of us who store JSON, this method also has problems.
Let's say you have a JSON array like this:
[{name: 'Baker', job: 'musician'}, {name: 'Euler', job: 'mathematician'}]
JSON.stringify()
If we change this to
["{name: 'Baker', job: 'musician'}", "{name: 'Euler', job: 'mathematician'}"]
becomes
Saving to DB works fine. However Get, if you check what is stored in the DB with ...
["{name: 'Baker'",
"job: 'musician'}",
"{name: 'Euler'",
"job: 'mathematician'}"]
It has the following bizarre form... !!
The reason is that…
MySQL pseudo-Array
We thought that MySQL supports number arrays or string arrays, even if JSON arrays cannot be stored. But… That was TypeORM
a trick of !!
Actually MySQL Array
doesn't support types at all!! (Already confirmed with an error message.) So simple-array
, how did we save it using properties?
simple-array
stores all arrays as string. If it is a number array, "1, 2, 3"
, if it is a string array, "Bill, John, Baker"
. So, if even a comma (,) is inserted in the middle of the string, it is stored in the DB like this.
"'In other words, please be true', 'In other words, I love you'"
That is, even if it is a string array, only one string is stored in the DB. And parses the information of the column declared as TypeORM
, based on the comma (,). simple-array
therefore, even if you originally saved 2 strings, you will get 4 strings if you read the DB value.
Actually MySQL there is nothing wrong with it. TypeORM
it is possible that the simple-array
method of MySQL, especially in , is causing this problem. In other DBs, it may not be stored as a single string like this. 2 In MySQL, this is… ㅠㅠ
In the case of JSON, commas (,) must appear to list objects, but in general strings, commas are rare. So TypeORM
the developer may not have noticed. However, even such a small error can become a big obstacle or cause an error in development. (I lost half a day because of this...)
[Solution] Just save it as a string!
Through several trials and errors, simple-array
I confirmed how TypeORM stores arrays in MySQL...
So let's use this to reorganize our code.
@Column()
json_arr: string;
Column is simply string
set to .
Each JSON in the JSON array JSON.stringify()
is serialized as a function. Then, they JSON.parse()
are transformed into a possible form and stored.
// save JSON array into MySQL DB
saveJSONArray(json_arr: JSON[]) {
let jsonArr_string = "[";
for(let i=0; i < json_arr.lenght; i++){
jsonArr_string += JSON.stringify(json_arr[i]);
if(i < json_arr.lenght-1){
jsonArr_string += ",";
}
}
jsonArr_string += "]";
this.yourRepository.save({ json_arr: jsonArr_string });
}
// load JSON array from MySQL DB
async findJSONArray(): Promise<YourEntry[]> {
const entry_arr = await this.yourRepository.find();
for(let i=0; i < entry_arr.lenght; i++){
entry_arr[i].json_arr = JSON.parse(entry_arr[i].json_arr);
}
return entry_arr;
}
I think the best way is json_arr: string
to set it to , parse the DB value to , and return it as JSON. JSON.parse()
(Of course, someone may have found a better way than this and put it to good use!)
Top comments (0)