[gwbas1c]

> validating the format of an email address

That's input validation. If your database offers an email type, use it. Otherwise, this kind of validation happens mostly in the UI (or API layer) and only lightly elsewhere.

If you're using a strongly-typed language, consider an "email" class instead of a string. You can safely pass around an "email" object and know that it's a valid email address.

> No DB validating the price of a product can't be negative

Does your database have an unsigned int type? Does it have a currency type? Use it if you can, otherwise, you'll need to enforce it in code.

Remember that you still need to validate your input! If you're relying on your database to validate your input, you'll either send arcane and cryptic errors to the user, or misinterpret unrelated database errors as user errors.

> How would you define the line between them

It's all based on what's reasonable. If I have to invent weirdo gymnastics in my database to enforce something, it's probably best to do in code.

What schemas do is provide guarantees about data that can be enforced, especially through upgrades. IE: This value isn't null, and never will be null. This object (row) always has values in columns A, B, and C. This relationship between these rows is always valid because there's a foreign key relationship. These values then get enforced through schema changes. It's harder for edge cases to sneak in. (Compared with schemaless databases, where your code will need to handle data in outdated formats, or will need lots of edge cases for missing values.)

This allows your data to be predictable, so you don't have to resort to lots of heuristics in your business logic about edge cases.

> What are your favorite readings/resoucres on this topic?

Experience. Hopefully you can discuss this with a more experienced team member.

[ranguna]

> If I have to invent weirdo gymnastics in my database to enforce something, it's probably best to do in code.

What if you had to do weird gynmastcis in the code instead ?

Setting up a trigger that checks if the row being inserted has a foreign key that points to a row of another table that has a column with a value that you were expecting.

Easily done with a trigger that requires no round trips, but many see it as an overhead because you now need to maintain a function with DB migrations.

If you do it in the code, you need to make sure you do a second call to your DB to do this validation everyone you insert in a specific table, something that can be missed when witting code.

[gwbas1c]

I would argue that's a good use of the trigger!

The complication is error reporting. Will your application interpret the error that comes from the database correctly? Will your UI (or API) return a useful error?

[ranguna]

Yep agreed, so sometimes it's better to write business logic on the DB side because other wise you'd have to do code gymnastics and multiple round trips in your application code.

As for error handling, you can raise an error with a specific format in your plsql and have your application interpret it like any other DB error, the the application does whatever it wants with it, including sending it to the FE if needed.

Ex :

FE clicks a button to append a payment to an order.

BE creates the payment entity assigned to a wallet of a specific user and currency. BE attempts to assign the new payment to the existing order (the order was created for a different currency).

DB runs a trigger on payment creation that checks that the associated wallet's currency is the same as the order's currency, if it fails, the DB raises "payment_wallet_currency_mismatch"

BE catches the error and matches the error code, throwing back an error to the FE saying that the currency in the payment is invalid for the order.

With this example, a single call was performed to the DB and all the necessary checks were performed on DB side with no extra round-trips.

One downside is version controlling all this, it's a bit of a mess doing code review for DB function changes because you have to write the whole function every time in a new migration file just to change and if statement, for example.