Using orm events or database triggers

Question

In orms, we can easily perform our desired operations in the save() method (for example, when a record is added in the likes table, the count column in the post table will increase.)

This is great, but

• it prohibits direct manipulation of the database!

• No one has the right to execute a raw query (insert or update) on this table

• if this database is used by different clients with different programming languages, they should all use orm and implement the same logic in save(changed several codes for one change)

Why does the database not perform this operation?

Even if these operations are not related to the database (for example, connecting to Kafka), the database is still able to do it.

I never worry that if I execute raw query on db, the data will be corrupted

Answer 1

It is an architectural choice.

Either you do it in the ORM. But the you should make sure that the relevant database objects are only accessed outside of your code via a service that also uses the ORM.

Or you do it using database triggers, and rely on your database, with the risk of less portability, database lock-in, scalability bottlenecks (e.g. if a trigger needs to access a lot of rows, which might create locks or performance constraints depending on isolation level.

It is highly recommended to be consistent in the approach. Otherwise you might end up with the inconveniences of both approaches without fully enjoying any of their respective benefits.

Answer 2

I'm going to pose a "frame challenge" here. In other words, I am going to propose that you shouldn't do either.

Instead of keeping a count in an separate table, you should count rows. You are likely optimizing on something that isn't a problem.

Answer 3

If you want your data to be absolutely consistent (and you probably do), then your database should take care of things like this for itself.
Just because one team chooses to use an ORM doesn't mean that anybody else will, or even that that same team will continue to use the [same] ORM forever more.

This is software. Things change.

it prohibits direct manipulation of the database!

If you ever allow your Developers or Testers to change data directly, then this approach falls flat on its face.
Of course, whether or not you do is a HUGE discussion in itself (but, in most cases, I strongly suspect they'll find a "reason" to get "in there" eventually!)

No one has the right to execute a raw query (insert or update) on this table

Not even your DBAs?

if this database is used by different clients with different programming languages, they should all use orm and implement the same logic in save(changed several codes for one change)

So now you're duplicating this code in multiple ORMs, which may do things differently, just because ORM suppliers can.

If it really matters, then get the database to take care of it on behalf of any client and any system.