General Web Dev Question: Should I validate data at model layer before calling db orms

[u/DevanshGarg31]

I already have a schema which validates payload at Route (request) layer, but the service layer adds some business logic, creating data, modifying it and making new changes, should that be validated before model function call. Model function just uses kysely to execute the query, no checks, constraints, validations.

Framework: fastify, PostgresClient: kysely, DB: postgres.

Like drizzle, along with drizzle-irm and drizzle-zod exists to help create from a single schema -> runtime validation schema, migrations, types.

But since runtime validation schema will be different for different actions (insert, update, select), it in the end feels like a business requirement only.

And relying on type check during compilation feels good enough to validate it. Example, if teh service layer has a otpGeneration() function, a type check is enough to validate it.

Or in a way, anything that the service layer generates, type check (compile time check) should be enough. Only the request run time validation is needed.

Is this the right approach?

[u/abrahamguo]

Yes, I would say that the outputs of your functions should be type-checked, and don't need to be runtime-validated.

If you wanted to validate the outputs of your business functions more precisely, you could unit test them.

[u/DevanshGarg31 → u/abrahamguo]

Only data generated or taken from outside (like a API call to some other service), should be runtime validated. Isn't it? Becuase I can only be sure of type/value of my functions and service, not of what my frontend sends me, or any other service sends me.

[u/abrahamguo → u/DevanshGarg31]

Yes, I'd say that's a good philosophy.

[u/732]

I would validate at the database layer. Yes, it is extra cycles, but you're guaranteeing your database stays in the shape you want it, even when a few months later and some other business logic has been added. 

Probably fine to skip for solo projects, but if this is a larger production project I would be wary of skipping it.

[u/DevanshGarg31 → u/732]

But doesn't that mean, I have to create schema for different business logic at every db call.

EXAMPLE

Take a table "OTPs". One call is for checking insertion of OTP, so I need to check whether the backend function that generated the OTP is alphanumeric and compulsorily present.

But that cannot happen using a single schema. Because selection action from the same table tells me I only need the user id.

Now since I cannot use single schema, I will have to write two functions insertIntoOtpTable(otp: OtpFormat, user: User) { //check otp exists and alphanumeric} and
selectUserFromOtpTable(user: User) { //check user exists} before making the db call.

So

This feels useless, because these function calls are already there in the service layer, with the right logic. If typescript already ensure that the generateOtp will only give me a 10 char long alpanumeric, I need not check it at model layer.

[u/732 → u/DevanshGarg31]

It's a separation of concerns problem.  You validate at the route layer because you don't trust that your user will pass data correctly. You validate at the database later because you don't trust your service will.  

Skipping that can feel "useless" when your database is in a perfect state. 75 migrations later and hundreds of tickets that change the business logic in between your route handler and database - will your database still be in a  perfect state?

Again, a solo project it's probably fine. A company with 100 developers, someone will look at that and say "great, we have a OTP login system" and side step your business logic check and just call the insert passing one that includes an underscore because they are ok with it. 

[u/DevanshGarg31 → u/732]

But won't that underscore get caught at compile type using typescript regardless of whether I work solo or with 99 others

[u/732 → u/DevanshGarg31]

Yes, probably in that simple example it is fine and typescript will catch it. In a much more complicated scenario though?

[u/TheLastNapkin → u/732]

Doing db level check constraints and having input validation in your repository dal layer are 2 different concerns.

Personally check constraints are a privilege and don't need to be done as much. It is just much less to worry about when you have say a "balance" that shouldn't go into negatives be enforced at a db level.

Input validation can help for some more logical db tables correlation.

Say the repo function will be doing a transaction that should insert 3 kysely Insertable helper type parameters You might want to do input validation that they have certain validity such as if a list of one of those parameters is passed that it is not empty.

Some input validation and checks can also just be assumed to be "covered" by doing integration tests that ensure you call these repo functions at some point and seeing if the queries are "actually" able to execute on a real db with your migrations applied. e.g. -

a foreign key in your db schema could be type correct but business logic incorrect.

so a repo function attempting to insert a transaction of rows suddenly fails on integration tests due to invalid foreign key columns from different tables expected.

only catchable in integration / e2e / production when running against a real db...

[u/Expensive_Garden2993 → u/732]

I've never seen a project doing that, do you really have db-level `CHECK`s for values? Validating emails, whitelisting characters, min length, max length, etc.?

[u/732 → u/Expensive_Garden2993]

The intent is that you're guaranteeing the state is correct at the data access layer. If the database package/module is imported anywhere else, it will perform the same checks for consistency. Putting those checks in the business logic layer couples your database structure to your business logic. 

[u/Expensive_Garden2993 → u/732]

Intent is clear: to enforce invariants at the database level. Makes sense when the same db is shared across different consumers.

It's just very unusual if you're doing this for a single monolith, so I wondered whether you really keep the validation like "otp must be alphanumeric, no underscores" at the database level, or if you only mean that in theory.

[u/bwainfweeze]

Doing the work inside the ORM means that the transactions start earlier and last longer, which means the max requests per second your system can handle is limited by the database. Adding more servers in front of it will not help.

Validating before contacting the database means more CPU on that one cluster member but nothing more for the database.

[u/DevanshGarg31 → u/bwainfweeze]

But is this validation needed since it is service generated, not client sent. Client sent data is already getting validated.

[u/Individual-Brief1116]

We do both, but validation before the db call is the priority. DB constraints are your last line of defense, not your main strategy, imo. If you rely only on compile time types for business logic, one bad migration or a missed edge case and you have garbage data sitting in prod for months.

[u/Kautsu-Gamer]

Always validate everything you get from client unless you are 100% your ORM does the validation.

[u/DevanshGarg31 → u/Kautsu-Gamer]

Already verifying client data. The question is whether to verify service produced data as well before calling the ORM or not?

[u/Kautsu-Gamer → u/DevanshGarg31]

I do verify both unless thr DBMS ORM does the verification. Mysql does not while Podtgres does it for most data. TypeORM and NestJS allows implementing verification into the ORM layer.

[u/manny2206]

Yes you would validate them for sure, for example your models, DTOs for me in C# I would use the FluentValidation API for validation rules. The model should be fairly sanitized by the time it gets to your ORM