NestJs, TypeORM and MySQL

NestJs, TypeORM and MySQL

I have been using Nest with MySQL. As ORM I used TypeORM for every project. But definetly I've hit some road blocks on TypeORM and MySQL.

I think TypeORM is the best choice for ORM in node as world as of now , but apparently if you use JSON column for your entity its not that great.

You better show some love for handling raw queries or try to keep JSON column as small as possible, because it can become a source of a headaches in the long run.

this.repo.query('SELECT some-column->"$.email_verification.token" as `token`  FROM `user` WHERE some-column->"$.email_verification.token" = "some-token";');
TypeORM raw query

I couldn't figure out a nice way of writing query in TypeORM query builder.
You can see it's a nasty read and  a response from database you'd get a object with properties where you named them in SQL query AS part. Which is good. I even raised Github issue as question, maybe someone knows a better way. Nobody at the moment nobody responded (as of writing this post). As soon as I will come up with better way I'll update Github and this post.

So if you plan to access your entity's json column consistenly and it would be a keypart of your application I'd suggest moving that data to separate table. But if the of data is for one time use only, like email verification tokens etc. which you eventually are not going to store in the long term after email is verified, then I think it's a good idea to keep that it into JSON column.

Edit

I got back from a TypeORM GitHub repo, and I had a suggestion.
I think it works ok. Here it is:

repo.findOne({where: {options: Raw(() => `options->"$.email_verification.token" = '${token}'`)}});

Not great. Not terrible.


Have a nice day,
Karolis

Show Comments