1

I am using TypeORM with MySQL and am setting up automatic auditing of all columns and database tables via MySQL Triggers - not TypeORM's "Logger" feature (unless you have some extra info)...

Without getting bogged down, the MySQL Triggers approach works very well and means no app-side code is required.

The problem: I cannot provide MySQL queries with the logged in app user's ID in a way that does not require we apply it in every query created in this app. We do have a central "CRUD" class, but that is for generic CRUD, so our more "specialist" queries would require special treatment - undesired.

Each of our tables has an int field "editedBy" where we would like to update with the user ID who edited the row (by using our app).

Question: Is there a way to intercept all non-read queries in TypeORM (regardless if its active record or query builder) and be able to update a column in the affected tables ('editedBy' int field)?

This would allow our Triggers solution to be complete.

P.S. I tried out TypeORM's custom logging function:

... typeorm createConnection({ ....
    logger: new MyCustomLogger()
... });

class MyCustomLogger { // 'extend' has issue - works without anyway: extends Logger {

 logQuery(query, parameters, somethingelse) // WORKS
 { ... }

logQuery does appear to fire before the query (I think) is sent to MySQL, but I cannot find a way how to extract the "Json-like" javascript object out of this, to modify each table's "editedBy". It would be great if there was a way to find all tables within this function and adjust editedBy. Happy to try other options... that don't entail updating the many files we have containing database calls.

Thanks

Chooka
  • 35
  • 1
  • 5

2 Answers2

3

IMHO it should not be correct to use the logging feature of TypeOrm to modify your queries, it is very dangerous even if it would work with a bit of effort.

If you want to manage the way the upsert queries are done in TypeOrm, the best practice is to use custom repositories and then always calling it (not spawning vanilla repositories aftewards like in entityManager.getRepository(Specialist), instead use yours with entityManager.getCustomRepository(SpecialistRepository)).

The official documentation on the subject should help you a lot: https://github.com/typeorm/typeorm/blob/master/docs/custom-repository.md

Then in your custom repository you can override the save method and add whatever you want. Your code will be explicit and a good advantage is that it does not apply to every entity so if you have other different cases when you want to save differently, you are not stuck (you can also add custom save methods).

If you want to generalize the processing of the save methods, you can create an abstract repository to extend TypeOrm repository that you can then extend with your custom repository, in it you can add your custom code so that you don't end up copying it in every custom repository.

SpecialistRepository<Specialist> -> CustomSaveRepository<T> -> Repository<T>
zenbeni
  • 7,019
  • 3
  • 29
  • 60
2

I used a combination of https://github.com/skonves/express-http-context node module to pass user ID to TypeORM's Event Subscribers feature to make the update to data about to be submitted to DB: https://github.com/typeorm/typeorm/blob/master/sample/sample5-subscribers/subscriber/EverythingSubscriber.ts

Chooka
  • 35
  • 1
  • 5