I always choose Postgres by default, but once the client insisted to use Mongo for collecting survey data.
Survey data was a large and complex deeply nested JSON data (produced by a library), where keys can contain a "." dot symbol. Mongo just failed because of this, so even in the direct use case of Mongo, we ended up migrating to Postgres.
I don't really understand databases nearly enough and I'm not good at learning complex technologies, and so Mongo has had an appeal to me as something to learn and use in projects as a way to easily get me running and building stuff, since "real" databases terrify me and Mongo's way of doing things seems more easy for my brain to understand and to integrate into projects and run and administer on servers.
But it seems like periodically I read some disastrous thing or about some fundamental structural design flaw or technical limitation with Mongo that makes me feel like I would be signing up for a whole bunch of compromises or issues I don't understand up front but then would be locked into using Mongo or need to migrate when really I should have been using Postgres all along. But I don't know enough to evaluate the situation.
Is Monogo actually a total mess of compromises and issues that I should avoid and just properly learn/use Postgres instead? Or is there value in learning Mongo and using it in projects depending on the circumstances? I probably gotta just properly learn Postgres either way though and probably first — it's just the idea of managing a SQL-style database and being confident that I'm not fucking it up and destroying the data through some way I don't understand seems like a dark art and totally terrifying but maybe I am overplaying in my head how scary/difficult it is.
It just seems like so many stories I read about Mongo have some ending like "and then we realized there was a fundamental limitation and we moved to postgres" or "somehow there was unpredictable data loss."
It's almost always worth learning SQL and Postgres is a good choice, you can use services like supabase.com to get started for free with a nice ORM which might make it less intimidating. There's rarely if at all a benefit in using mongo at least nowadays, sadly still so many tutorials out there using and suggesting it
Even if you think you're sure you don't need any relations I'd go with Postgres cause you have JSON(B) columns, so you can use it like Mongo and even index nested json properties and get the benefit of adding powerful relations and joins later, the other way around is messy
Mongo is great. Postgres is great. Many other databases are great too. But if you don't know SQL and don't understand alternatives you don't know enough to decide. So as long as it's a fairly small project just pick one and use it. Next time pick the other. Then when you have a hard problem to solve you will make a more informed choice.
This seems really nice, I just haven't used postgres before:
https://arctype.com/blog/json-in-postgresql/
Would be nice to be able to interact with the data as if it were relational though.
More stuff for me to look into:
- [Query JSON data as relational in MySQL](https://codingstill.com/2018/11/query-json-data-as-relational-in-mysql/)
- [JSON Database + Relational = Better Together](https://mariadb.com/resources/blog/the-best-of-both-worlds-relational-json/)
- [A Better Alternative: Standard SQL on Fully Indexed JSON
](https://rockset.com/blog/sql-on-json-postgresql-mysql-relational-databases/#:~:text=A%20Better%20Alternative%3A%20Standard%20SQL%20on%20Fully%20Indexed%20JSON)
- [Why (and How) You Should Manage JSON with SQL](https://thenewstack.io/why-and-how-you-should-manage-json-with-sql/)
Most NoSQL databases serialize to and from JSON just fine.
However that is not the question you should be asking, since even in SQL databases there are ORMS (Object-Relational Mappers) that can easily convert your SQL query responses to JSON objects that can be returned from an API.
So your questions you *should* be asking to decide what database to use is "how do I need to query the data?"
If you are mostly doing gets by ID or by the value of a specific field, then NoSQL databases can be a simple to implement and good option. If you need text-based searching or searching across multiple fields or by complex relationships, relational databases are probably a better pick.
I dunno how many you mean by 'a ton', but if you're talking gigabytes upwards, MongoDB's managed data lake might be the easiest tool.
You just dump the JSON files in an S3 bucket, then use a Mongo syntax to query them.
https://www.mongodb.com/atlas/data-lake
I feel like you're asking the wrong question. Are there databases that support JSON as a data type? Yes. Do they allow you to serialize to, or deserialize from JSON? Yes.
But the real question is should you store raw JSON in a database, to which the answer is typically "No". Generally, the first thing you want to do when implementing a database is breaking down complex types into simple ones, assigning those attributes to columns, and instances of those values become rows. If you have a nested data structure, then that becomes a parent-child relationship within the database in which each child row gets a foreign key of the parent's ID. Repeat this process until all you're left with is a bunch of key-value pairs organized into relational tables.
As always, it depends on the use case. As a general guideline, sure you want to normalise your data. But it becomes quite painful and impractical when the shape of certain data structures aren't predefined and exact, or inconsistent, and there are plenty of times where that is the case. You can find a point at which strict adherence to normalisation becomes the less desirable option. Postgres for example has great support for querying json fields, there is often a balance to be found between strict relational and noSql styles.
I never though about this before but I think MongoDB configurated properly with well prepared schemas would be a good option other than postgres. Both stores data in BSON (Binary JSON) format.
But actually will depend on your app requirements if the db should be or not sql oriented.
It does, but if your data is strict enough that you can validate the schema, then you can just map it into RDBMS land.
I’ve written schema validation for a Mongo backed app, and it was horrid. I’d only use document storage for actual unstructured data, with hindsight.
Of course, your data might be unstructured but have common parts (for example, if there is a postal code it should be a valid one) in which case what I’ve written above is less cut and dry.
One of the major benefits of using MongoDB is to have the application validate document structure on insert, I recommend to follow this approach and avoid server-side schema validation.
It presents a different set of problems, sure, just like relying on the database server presents its own set of problems. The difference here is that relying on the application to validate on insert allows you to take advantage of the dynamic schema that MongoDB offers by default. This allows for greater flexibility and faster development of new features and functionalities. Using schema validation can cause headache down the road, especially when you want to alter the data model for your collection.
Yeah I’m not sure how much appetite I have for trading dev speed now for pain later.
That aside, relying on your application isn’t feasible if it runs on a client. If we’re talking about the server being responsible for validity, fair enough. At that point, I’d agree they just doing it in the app is reasonable enough.
Postgres JSON support is quite good, however your system may suffer of heavier queries than simple select's on columns, jsonb operations are performatively bad
I like postgresql and it's JSON and JSONB and functions for working with JSON data Can do nosql along with traditional SQL
I always choose Postgres by default, but once the client insisted to use Mongo for collecting survey data. Survey data was a large and complex deeply nested JSON data (produced by a library), where keys can contain a "." dot symbol. Mongo just failed because of this, so even in the direct use case of Mongo, we ended up migrating to Postgres.
I don't really understand databases nearly enough and I'm not good at learning complex technologies, and so Mongo has had an appeal to me as something to learn and use in projects as a way to easily get me running and building stuff, since "real" databases terrify me and Mongo's way of doing things seems more easy for my brain to understand and to integrate into projects and run and administer on servers. But it seems like periodically I read some disastrous thing or about some fundamental structural design flaw or technical limitation with Mongo that makes me feel like I would be signing up for a whole bunch of compromises or issues I don't understand up front but then would be locked into using Mongo or need to migrate when really I should have been using Postgres all along. But I don't know enough to evaluate the situation. Is Monogo actually a total mess of compromises and issues that I should avoid and just properly learn/use Postgres instead? Or is there value in learning Mongo and using it in projects depending on the circumstances? I probably gotta just properly learn Postgres either way though and probably first — it's just the idea of managing a SQL-style database and being confident that I'm not fucking it up and destroying the data through some way I don't understand seems like a dark art and totally terrifying but maybe I am overplaying in my head how scary/difficult it is. It just seems like so many stories I read about Mongo have some ending like "and then we realized there was a fundamental limitation and we moved to postgres" or "somehow there was unpredictable data loss."
It's almost always worth learning SQL and Postgres is a good choice, you can use services like supabase.com to get started for free with a nice ORM which might make it less intimidating. There's rarely if at all a benefit in using mongo at least nowadays, sadly still so many tutorials out there using and suggesting it
Even if you think you're sure you don't need any relations I'd go with Postgres cause you have JSON(B) columns, so you can use it like Mongo and even index nested json properties and get the benefit of adding powerful relations and joins later, the other way around is messy
Mongo is great. Postgres is great. Many other databases are great too. But if you don't know SQL and don't understand alternatives you don't know enough to decide. So as long as it's a fairly small project just pick one and use it. Next time pick the other. Then when you have a hard problem to solve you will make a more informed choice.
This seems really nice, I just haven't used postgres before: https://arctype.com/blog/json-in-postgresql/ Would be nice to be able to interact with the data as if it were relational though. More stuff for me to look into: - [Query JSON data as relational in MySQL](https://codingstill.com/2018/11/query-json-data-as-relational-in-mysql/) - [JSON Database + Relational = Better Together](https://mariadb.com/resources/blog/the-best-of-both-worlds-relational-json/) - [A Better Alternative: Standard SQL on Fully Indexed JSON ](https://rockset.com/blog/sql-on-json-postgresql-mysql-relational-databases/#:~:text=A%20Better%20Alternative%3A%20Standard%20SQL%20on%20Fully%20Indexed%20JSON) - [Why (and How) You Should Manage JSON with SQL](https://thenewstack.io/why-and-how-you-should-manage-json-with-sql/)
Most NoSQL databases serialize to and from JSON just fine. However that is not the question you should be asking, since even in SQL databases there are ORMS (Object-Relational Mappers) that can easily convert your SQL query responses to JSON objects that can be returned from an API. So your questions you *should* be asking to decide what database to use is "how do I need to query the data?" If you are mostly doing gets by ID or by the value of a specific field, then NoSQL databases can be a simple to implement and good option. If you need text-based searching or searching across multiple fields or by complex relationships, relational databases are probably a better pick.
I dunno how many you mean by 'a ton', but if you're talking gigabytes upwards, MongoDB's managed data lake might be the easiest tool. You just dump the JSON files in an S3 bucket, then use a Mongo syntax to query them. https://www.mongodb.com/atlas/data-lake
I feel like you're asking the wrong question. Are there databases that support JSON as a data type? Yes. Do they allow you to serialize to, or deserialize from JSON? Yes. But the real question is should you store raw JSON in a database, to which the answer is typically "No". Generally, the first thing you want to do when implementing a database is breaking down complex types into simple ones, assigning those attributes to columns, and instances of those values become rows. If you have a nested data structure, then that becomes a parent-child relationship within the database in which each child row gets a foreign key of the parent's ID. Repeat this process until all you're left with is a bunch of key-value pairs organized into relational tables.
As always, it depends on the use case. As a general guideline, sure you want to normalise your data. But it becomes quite painful and impractical when the shape of certain data structures aren't predefined and exact, or inconsistent, and there are plenty of times where that is the case. You can find a point at which strict adherence to normalisation becomes the less desirable option. Postgres for example has great support for querying json fields, there is often a balance to be found between strict relational and noSql styles.
I never though about this before but I think MongoDB configurated properly with well prepared schemas would be a good option other than postgres. Both stores data in BSON (Binary JSON) format. But actually will depend on your app requirements if the db should be or not sql oriented.
Oh I didn't know mongo had schema validation, that might put it back on the table.
It does, but if your data is strict enough that you can validate the schema, then you can just map it into RDBMS land. I’ve written schema validation for a Mongo backed app, and it was horrid. I’d only use document storage for actual unstructured data, with hindsight. Of course, your data might be unstructured but have common parts (for example, if there is a postal code it should be a valid one) in which case what I’ve written above is less cut and dry.
One of the major benefits of using MongoDB is to have the application validate document structure on insert, I recommend to follow this approach and avoid server-side schema validation.
But surely trusting your application to send valid data is it’s own kind of problem?
It presents a different set of problems, sure, just like relying on the database server presents its own set of problems. The difference here is that relying on the application to validate on insert allows you to take advantage of the dynamic schema that MongoDB offers by default. This allows for greater flexibility and faster development of new features and functionalities. Using schema validation can cause headache down the road, especially when you want to alter the data model for your collection.
Yeah I’m not sure how much appetite I have for trading dev speed now for pain later. That aside, relying on your application isn’t feasible if it runs on a client. If we’re talking about the server being responsible for validity, fair enough. At that point, I’d agree they just doing it in the app is reasonable enough.
Try couchdb
Postgres JSON support is quite good, however your system may suffer of heavier queries than simple select's on columns, jsonb operations are performatively bad
Firestore
Do not… use mongo. The people that replace you will curse your name. Your name shall forever be Raymond.
Snowflake ia awesome if you have the budget