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.
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.
'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.
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.
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 is messy.
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.
People who haven’t even learned about Chesterton’s fence have no idea how much they don’t know about robust software.
(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.