[clavalle]

>As mentioned by the other comment, it's standard to use database triggers to ensure consistency.

shudder

Triggers are almost always the wrong level to handle consistency. It flies in the face of 'fail fast' if you need to actually handle problems of consistency and if you need 'last line' protection, why not bake it into the structure instead?

[chatmasta]

Foregoing triggers in favor of validating data in the application layer can be dangerous. The problem is that you need to re-implement data validation on every path to the database. If you've got a single application server, and it's the only client that ever writes to the database, this might work. But as soon as you add another service with write capability, you need to re-implement the validation logic. What if you forget?

If you put the validation in the database, any application can leverage it. If you want to fail fast, you can still do validation in the application layer while relying on the DB layer as the ultimate validator.

[neuland]

It’s generally not good for services to share write privileges to a database for this very reason.

Also you can end up with services communicating between an implicit database api rather than a well established rest or rpc (or whatever) api.

[scarface74]

But as soon as you add another service with write capability, you need to re-implement the validation logic.

Then Don’t Do That (tm)

It’s hardly ever a good idea to have multiple apps writing to the same set of tables directly.

[chatmasta]

Why not? How is it any different than two web requests causing a multi-threaded application server to send two simultaneous writes to the database?

If you use triggers for validation, you can rely on your database's MVCC to resolve any conflicts between triggers. Note that this is significantly more performant and robust than the application trying to resolve, or even notice, those conflicts.

[scarface74]

Don’t do that, was in response to:

But as soon as you add another service with write capability, you need to re-implement the validation logic. What if you forget?

In context of application validation logic, the logic is only living in one place.

Triggers are basically “spooky action at a distance”.

It’s also much easier to promote, version, upgrade, and rollback application changes.

[jhanschoo]

There are consistency guarantees that it's possible that you can't ensure with just application logic.

Suppose you are running a booking system for ridesharing. When you want to lock-in a passenger's seat, it's possible that consistency can be violated when you do it at the application level: both A and B request a seat after a query returns that there is a seat available, then simultaneously lock-in their reservation after seeing stale data.

It's also naive to think that validation in the application is sufficient. For, say, a webapp, there should be three places where that occurs: at the client where you can provide the most meaningful feedback, at the service to catch most consistency errors and guard against malicious actors while you can provide meaningful feedback, and at the database level for the most perennial and slow-moving constraints so that concurrency and your own bugs don't fuck up your source-of-truth.

edit: come to think if it, it's possible to do it without triggers as long as you can still express the constraint as a query: append a boolean field to each table with a TRUE check constraint, and use a table query mirroring the constraint to populate that field on every mutation.

[scarface74]

Suppose you are running a booking system for ridesharing. When you want to lock-in a passenger's seat, it's possible that consistency can be violated when you do it at the application level: both A and B request a seat after a query returns that there is a seat available, then simultaneously lock-in their reservation after seeing stale data.

This is a solved problem.

  function reserveSeat (customerid, seatid)
  {
   lock
  {
      updatedRows  = update seats set customerid = {customerid} where id= {seatid} and customerid is null 

      return updatedRows == 1
   }
  }

Yes pretend scarfaceScript takes care of sql injection vulnerabilities and “lock” makes sure that only one thread can enter the block at any given time.

[zepolen]

It's hardly a good idea if you have a database that allows invalid state.

Otherwise it's a fantastic idea.

[scarface74]

An “invalid state” can mean a lot of things - including business rules that can’t be expressed by simple relational, constraints rules.

But what happens when you need to change something about the database. Isn’t it a lot easier to have all of the code in one place?

So the usual retort is to put all of the business rules in stores procedures.

Then you have an unholy mess of a database with triggers and stored procedures that are harder to modify, harder to unit test, harder to version and just an unmaintainable mess.

I’ve never heard a single developer say that they love maintaining a system with 100s of large stores procedures, triggers, etc.