> If it’s not in your database, you’re likely managing it in the app
“Managed in your app” sounds like a benign state of affairs.
Anything that doesn’t have a home ends up smeared across your entire codebase. It isn’t that it’s in the app, it’s that it’s everywhere in the app, meaning changing it becomes a huge investment of energy that people will try to avoid or put off.
You can have a SQL database and still end up with assumptions about the data smeared across your codebase. I've worked on multi-million line codebases on top of a SQL database where nobody dared change the schema of some (very non-optimal) tables because too much code directly depended on the structure of those tables. Having a clean and DRY data access layer is necessary regardless of the underlying database.
As soon as multiple independent codebases share the same database I would argue you need to put an API on top of that database and turn it into a microservice that owns its database. Otherwise the internal details of how the tables are structured will wrap itself into the codebases and make it very hard to evolve the database's schema.
> Having a clean and DRY data access layer is necessary regardless of the underlying database.
SQL databases (via views and even sprocs) allow you to abstract particular client’s view of the data from the base storage layer inside the database.
> As soon as multiple independent codebases share the same database I would argue you need to put an API on top of that database and turn it into a microservice that owns its database.
An RDBMS is is an integrated service that owns its own datastore with a very-well-defined, extremely battle-tested API designed to support multiple clients with completely different views of and access to the data, all as logically isolated as necessary from the design of the base storage layer.
If you aren't using an RDBMS, sure you may need to wrap something around the datastore that provides a tricky-to-get-right subset of what an RDBMS provides fairly trivial-to-use facilities for out of the box, just like like not using an RDBMS often forces you to do for another subset if you are concerned about integrity.
But if you 'have it in the database' it will still be smeared across your app too.
'Putting stuff in one place', regardless of that place, is hard. Necessary, but hard. And it requires tradeoffs.
If you need a 'sorry this username is taken' friendly error, your app needs to handle constraint errors from your DB. Even if only on the translation layer. At which point you'll have it duplicated on multiple layers, add tight coupling between layers, or need to forego that message and e.g. settle with a generic exception instead.
The difference is that the actual constraint lives in one place and the rest of the locations are UX benefits to help the user. The system doesn't get into a bad state just because you forgot to add the constraint in the 100th location.
> rest of the locations are UX benefits to help the user.
In my experience this is a pipe dream.
Maybe in my simple example, one could parse the Constraint Exception and map that to a field and user-friendly error. Maybe. No framework or ORM that I've ever seen that does this though.
But even when it does: it still requires you to do the parsing and mapping in the application: introducing a tight coupling (e.g. you cannot add a constraint without releasing new locales).
In practice, is my experience, you'll most likely have some constraints in the DB, some validations in your ORM, some of which overlap, some of which are unique to one of both.
Which is arguably worse than having each app that uses the database repeat that. It all depends on the use-case, obviously.
Definitely. You won't have the rich exception messages all over. However, if the rule is a _business rule_ then it must either live _in_ the DB, or (very frequently) live in a dedicated repository layer that all application access goes through. Otherwise its not a rule and you _will_ forget to enforce it at some point.
This is the distinction we make too: business validations go in the database, ux-validations go in the application.
In practice, however, this means the business validations are duplicated all over the place (but always enforced, as last line of defence, in the database).
It also means customers get more frequent 500 errors (exceptions): when a business rule is implemented in DB but not (yet) in an application.
> It isn’t that it’s in the app, it’s that it’s everywhere in the app
That's only if you don't know how to properly code a data access layer in your application. And if you have many apps using the DB, perhaps the data layer should be in a library.
It's also a lot easier to mess up evolving the logical schema and result with unexpected and incoherent database state if your store doesn't enforce the logical. schema. Sure, the more the logical schema is enforced, the more you are forced to do up-front when the logical schema changes, but that work prevents you from:
(1) apply a data migration that fails to result in a state that complies with the logical schema, or
(2) producing a state inconsistent with the logical schema because your application code doesn't correctly observe the schema, as defective code will fail for violating constraints instead.
“Managed in your app” sounds like a benign state of affairs.
Anything that doesn’t have a home ends up smeared across your entire codebase. It isn’t that it’s in the app, it’s that it’s everywhere in the app, meaning changing it becomes a huge investment of energy that people will try to avoid or put off.