3 ms·

As mentioned by the other comment, it's standard to use database triggers to ensure consistency. There are two major problems with dealing with them in the app

As mentioned by the other comment, it's standard to use database triggers to ensure consistency. There are two major problems with dealing with them in the application side 1. You can no longer guarantee consistency. 2. In the case where you need to do some things procedurally so that your application can no longer make a yes/no query to determine if a constraint is violated but need to make use of intermediate results, then this means that you will need to transfer intermediate results over the wire, which is a performance penalty over just doing it in the database server. edit: of course, when you need to ensure consistency with some data source outside the database, there's no choice but to do it application-side.

clavalle 7y ago
>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 7y ago
  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 7y ago
    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 7y ago
    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 7y ago
      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 7y ago
        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 7y ago
        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.
6nf 7y ago
Yea the 'correct' approach is often to to both: - You check for consistency issues on the client-side UI level, and give the user instant feedback when something is not right. - You also create a db trigger to enforce the rule in the database itself. This way you, as a developer, can write your code with the assumption that the data is gonna be consistent when it comes from the database, even if something went wrong on the client UI level at some point.

clavalle 7y ago

>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 7y ago
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 7y ago
  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 7y ago
  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 7y ago
    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 7y ago
      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 7y ago
        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.

6nf 7y ago

Yea the 'correct' approach is often to to both: - You check for consistency issues on the client-side UI level, and give the user instant feedback when something is not right. - You also create a db trigger to enforce the rule in the database itself. This way you, as a developer, can write your code with the assumption that the data is gonna be consistent when it comes from the database, even if something went wrong on the client UI level at some point.