0

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

3 Answers 3

4

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.

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.

2
  • 1
    Calculating the number in a table with millions of rows took a lot of time, especially if the request is sent to the server 2000 times per minute, if I don't know something, please suggest a source for me to learn!
    – PersianMan
    Aug 20, 2022 at 7:40
  • @PersianMan Is the table indexed appropriately? Millions of rows isn't really that much anymore when it comes to DBs. It's hard to know exactly what your needs are but if you are getting 2000 request a minute are adding they additional rows to the data or are they for the aggregated results? If it's the results, a cache might be useful.
    – JimmyJames
    Aug 22, 2022 at 16:29
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.