ps: I don't mind the downvoting. I am truly looking for answers.
---
A schema-less storage design have some valid uses cases. But even them leak a undeniable truth:
All schema-less design are already a enforced schema, but more general.
Even a KV store is a enforced schema. You have key, and values, and specific operations on them.
Eventually, you will note that that schema-"less" design is too constrained / liberal. For the same reason you note that use all the time hash tables is not as good. You need arrays, and objects, and list, and records, etc.
And you noted that some operations start to repeat themselves. Will be nice if that become abstracted? Right?
And you will noted later that provide reliable results is very hard, specially if things are out-of-order, multi-thread, multi-process, etc.. Will be nice that that become abstracted, right?
And then you can do all that yourself, in your language of choice. And is fine!
But later, you will need to use another language. Now, you need to repeat all that effort AGAIN. Will be nice if you use something like all that about micro-services and stuff, and put the hard-part isolated and caring about that, and you can freely mix-match tech as you see fit?
But then, you do all this, and you MISS THE DAMM SIMPLICITY OF A KV STORE!. How nice will be if exist a model, let's call it relational, that not only can model a KV store, but much MUCH MUCH more!
TADA! You have reinvented a RDBMS!
But, if you think of your database as a single source of truth, then I almost can't imagine any reason to use mongo[1]. Without ACID transactions it is your problem to enforce all those pesky constraints across your whole codebase and data storage. And that work is HARD.
For instance. I once had to implement a simple scheduling app. Basically, if worker A was scheduled to work from 2017-04-30T18:00:00 to 2017-04-30T18:30:00, then there can't be any overlapping schedule item for said worker. I guess you can do something like a two-phase commit with mongo, but I used a tsrange[1] and an exclusion constraint, and got everything almost for free.
[1] Well, AFAIK mongo has better sharding. For now :)
[2] https://www.postgresql.org/docs/9.6/static/rangetypes.html
[3] https://www.postgresql.org/docs/9.0/static/ddl-constraints.h...
No one is making you enforce schemas with PostgreSQL if you work this way.
As for enforcements - there are things that are _impossible_ to enforce at an application level for atomicity reasons - for example - if you have a `users` table and you can't have duplicate email addresses - you'd need the operation "check if a user with the said email exists, if it does - return it, if it doesn't create it and return it" - PostgreSQL can do this easily, which prevents duplicate entries - Mongo - not so easily (see the "two phase commit" docs for how to simulate transactions).
Can't you easily do it with upsert in Mongo?
The strongest reason is that the database will complain loudly if you want to make a change that breaks existing constraints. An application, no matter how simple, will probably change, and you don't want to leave data out there that is unreadable and that will break your application's expectations. The schema minimizes the chances of garbage historical data that have been, in my experience the plague of NoSQL-backed apps. Often you find people that end up writing ad hoc constraint checkers, creating a poor imitation of what a database that understands schemas does well.
Another big reason is that there's rarely just one application, and the applications could (and very often should) be written in different languages. A schema at that point becomes a service which just happens to not use Json or Thrift, but a far more complicated, sturdier one. You could still argue that databases are too complicated to expose very widely, but I'd much rather have all my data sitting in the very schema-heavy redshift to do reporting than to rely on application logic, or use most business insight tools out there.
Also, let's not forget that schemas let databases do a lot of complex operations rather cheaply, because they can have very optimized code of very specific shapes next to the data. In the cases where 'first, I will take 50K rows out of the db and into my application' is just not acceptable, you have to let the DB do the work. In those cases, the interface to use a windowing function in a SQL database is IMO far more convenient than having to go into the classic remote map reduce function in erlang.
I think there were plenty of gains from the NoSQL movement, especially as far as to start writing database where high availability and horizontally scalable loads are concerned. But outside of those realms, where few companies really are, I would not recommend a schemaless database today.
Just to elaborate a little on your third paragraph, this is incredibly important as an app matures. You might start out thinking you're only ever going to have one interface to a datastore, but the reality is that you always have at least two. Someone has the ability to go directly to the store itself and make changes. That's life. Someone at some point in the lifetime of your app or company is going to go in and have to fix things by hand. If you have no schema and no constraints in the datastore itself, the odds of an error make things worse are far, far greater.
We all know that's bad practice, but we all also know that sometimes you just have to do it for some reason. We also know that there will be times when someone is testing something on what they think is the local testing db but are actually connected to prod, and they do something experimental, and it screws things up. That's also a terrible thing to let happen, but we all know that it does.
The second thing I'd like to point out that, much like sending email, any sufficiently useful application will, at some point, provide an API. And you're going to have to re-implement the backend validation again for that.
So you know from the outset that if you are working on something you expect to be successful, you're going to have 3 access points. You're going to have to do the same work 3 times in 3 different places, unless you just decide to take the risk and use a db that doesn't have schema or constraints--which is a big risk. Doesn't make sense to me.
Finally, I'd like to add to this good post that it's pretty easy to work in either direction with many ORMs. If you're comfortable with DBs, you can write out your migration scripts and use the ORM to generate models from it. If you prefer to work with an ORM, you can write the model and manage the migrations in the ORM. Either way, there's really not that much overhead compared to what you gain in safety and security and DRY.
I'm not a DRY nazi, and when I'm prototyping, I'll repeat code anywhere I suspect a later iteration to diverge from the other instances of that code, and then refactor when I get closer to production. But when you're talking about database structure, I don't see how that can diverge, so it makes sense to me that you should never repeat yourself there. That's just asking for trouble.
Do it once in the database, and do it right. Then all you have to do is trap database errors in your backend code and validate on the front end. That will be different for every interface, but it's a lot simpler and cleaner and safer than enforcing schemata and constraints in every codebase that accesses the data store.
Primarily, security.
Good question! Why not have all the logic in the database?
The answer is, you can actually do that. And it’s popular enough that someone founded a company on that concept, although they didn’t use Postgres, but their own self-built database.
You might have heard of it: https://firebase.google.com/ (They were acquired later, and are now Google’s top database offering).
At least two OSS backend stacks, PostgREST and PostGraphQL, are built on exactly this idea. And there's at least one commercial stack with the same idea: SlashDB.
(in general, I'm not a fan of Object-Foo-Mapping, but then I've used dynamic/runtime typed languages on and off since the 80s)
However, by constraints I was more talking about things like "these properties need to be unique", "this column here must reference that column over there", "this column's values must be in this set" and so on.
I know I could enforce these things in the application layer, but I don't think that's feasible.
Edit: Typo
Whether you enforce password complexity in the database is a question of where your team is more comfortable working, and what the business requirements are.
You can enforce complex constrains like this in `>3.0`:
db.createCollection( "contacts",
{ validator: { $or:
[
{ phone: { $type: "string" } },
{ email: { $regex: /@mongodb\.com$/ } },
{ status: { $in: [ "Unknown", "Incomplete" ] } }
]
}
} )
I couldn't make a universal statement about where the constraints you're talking about should go without knowing the business rules for the application.
The migrations don't drive your schema, the schema drives the migrations. Proper tools don't make you have a bunch of .SQL files that organise your database.
pg_dump -s your_db | less
$ psql your_db
your_db=# \dn
your_db=# \dt public.
your_db=# \dt+ your_table
your_db=# \df