NestJS, TypeORM and MySQL

Karolis Ščerbiakas • May 3, 2020

nestjs typeorm mysql

I have been using Nest with MySQL. As ORM I used TypeORM for every project. Definitely I've hit some road blocks on TypeORM and MySQL. I think TypeORM is the best choice for ORM in node 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 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";');

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 an 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 consistently, and it would be a key part of your application I'd suggest moving data to separate table. 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:

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

Not great. Not terrible.
Have a nice day,
Karolis