Hacker News Reader

Comment by jeffdn | Hacker News Reader

Yep, that's fair enough. In my experience, however, in the vast, vast majority of cases, folks either don't actually care about the length, or they should be using business logic in code to do the validation, rather than waiting for the database to yell at them, invalidate their transaction, etc.

View on HN

>they should be using business logic in code to do the validation

I trust myself to do this, I don't trust my coworkers and future devs writing applications for the same database to follow the same constraints in their code though. Constraints at the DB level keep your data clean(er) even if some other devs tries to shit it up.

If I had a dollar for every time I saw someone doing application-layer-only validation of data and end up being surprised that some data that wasn't supposed to be valid ended up in the database only to later blow up in their face, I'd be a fairly well-off man.

In practice, declaratively creating data constraints at schema-creation time has a much much higher success rate than trying to imperatively enforce data constraints at write time, in my experience. The missed cases and bugs always come back to haunt you.

I'm not talking about all validation, I'm talking about a length constraint on a string (that isn't something like a US state, which should be a two-character field) -- already a pretty uncommon thing. At work, our codebase has 975 instances of a column storing a text value, and the length is material only in a very small handful of those.

How do you handle user input in your case? Frontend-only length limits? Or just allow them to take up more storage if thats what they submit?

The benefit is that you can treat the database schema as the single source of truth as to what data is valid. Otherwise what tends to happen is that different code components will have different ideas of what exactly is valid. This is also important because databases tend to be longer-lived than the code that accesses them.

It's good practice to have a length constraint on all text fields, just to put some cap on field sizes. You don't want somebody uploading 100MB of garbage into the `first_name` field—A 1000-char max is more than enough.

Constraints generally should be enforced both application- and database-side wherever possible. Application constraints can be more robust and have better UX, but only database constraints can offer you guarantees about what is actually in your database.

Reply on news.ycombinator.com