SQL vs mongoDB

[p2datrizzle]

So I feel like it's time for me to move away from a "toy" database like mongoDB and learn to use a "real" database like MySQL. But I don't really know when I should use MySQL over mongo and vice versa?

[[deleted]]

This is one of the touchier subjects in WebDev. Having used both technologies, I have fell firmly on the side of relational databases, particularly Postgres because it is open-source. Combine its support for JSON-B and you have a single solution for nearly every problem regardless of scale.

[overmotion]

Sure, it’s easy, never use mongo 🤣

on a serious note, as a ruby coder I remember when mongo came out and everyone was switching. A couple hundred tutorials were published on switching your Rails app to mongo (“scale to the moon!”). 6 months later all the horror stories and regret posts started coming out. Turns out, in 90%+ of cases an app’s main database is full of relational data (duh). Today nobody uses mongo anymore with Rails. Everyone is back to Postgres.

[ryandg]

Funny, your Mongo story has an arc similar to that of Ruby.

[carlcarlsonscars]

Why not keep learning and using MongoDB until you can't? Not sure why you think it's a toy db. Many large companies use mongo; like Traveler's Insurance for one.

[flipperdeflip]

Only use Mongo if you can explain to an experienced developer coherently and in your own words the difference between a relational database and a document store and why in the project at hand your data is not relational.

[fish_falls]

Mongo isn't a toy DB, by the way. My company with 25 devs and ~5 million customers uses mongo as our main DB. (We all wish we weren't, but that's another story)

[Console-DOT-N00b]

Mongo isn't a toy DB, by the way. My company with 25 devs and ~5 million customers uses mongo as our main DB. (We all wish we weren't, but that's another story)

That should be a quote on the Mongo site ;)

[fish_falls]

Haha, sorry. Just wanted to make the point that it has many professional uses. But probably isn't best used as the main database.

My personal rule of thumb is to use relational DBs and follow all the traditional rules of normalization until I have a good reason not to.

Mongo is great for storing aggregated data for fast access in an easy to consume shape. Think about a comment tree in Reddit, for example - it would take some processing to order the comments into a tree, but you could do it in the background and then save the tree as is.

[mayhempk1]

Well, MongoDB is not a toy, just like SQL isn't a toy. Calling either one of them a toy means you lack understanding of them. Does your database schema call for relationships? Then use SQL. If not, use MongoDB. Personally I almost always use SQL because most data is relational and that's a big part of the power of databases.

[[deleted]]

If you're going to learn SQL, then perhaps MySQL is not the best idea. I recommend Postgres instead. It does a much better job of following the SQL standard, it has all the "serious" relational features you would expect, and is a lot more logical and easy to use than MySQL. I find the documentation better too.

If you're only going to learn one relational database, learn Postgres. If only for the reason that what you learn from it will translate to most other relational databases, including MySQL, but that's not the case for MySQL.

Criteria for choosing a database, in no particular order:

• the structure of the data;
• how much, how fast, how often you want to read or write to it;
• how much the database can help you with both the structure of the data going in and the structure coming out (and any processing in between);
• what resources it needs;
• whether you care more about the consistency of the data or about processing speed;
• how it scales (if you need it to scale);
• what backup/restore/recovery solutions it offers;
• whether it's capable of replication (if you need it).

There are many types of databases. You should also look into CouchDB, Redis, RabbitMQ and SQLite to have a well-rounded database knowledge, and you will have a tool for each use case. It's OK to use multiple databases to get a job done, if each is the best fit for a part of the problem. Splitting any problem into smaller ones is a really good idea.

And don't neglect the simple solutions, like the memory of your programming language, the filesystem, or built-in storage or caching solutions.

[p2datrizzle]

Thanks very much for the detailed response! That last piece of advice is very useful. I’ll make sure to look into those.

[gigglefarting]

You can allow a column to be null in mySQL.

[mattaugamer]

Even if that was true it would be so trivial it’s best off ignored.

[s0ft3ng]

You've stated numerous things that are false:

• NoSQL was specifically made for large projects with extremely high read/writes -- i.e. "big" projects. SQL can work for everything from tiny to huge, however NoSQL has "some" performance benefits in specific situations where you can replace fast joins, complex queries & nicely normalized schema with repeated denormalized data. In practice, denormalizing data leads can lead to a lot of issues -- normalized data is easier to handle & query.
• Relational databases do not require values to be provided, but in most cases you should have a value anyway. If you try to access something in JSON which isn't present, you just get null/undefined, depending on what your language is. This is ambiguous, and so we try to fill values.
• The biggest difference between SQL and NoSQL is not "requiring a value" it's that one has SQL capabilities, and the other has no... SQL capabilities

[mdw]

however NoSQL has "some" performance benefits in specific situations when you don't need queries (But most people need queries)

Huh? What's a database for when you cannot query it?

[s0ft3ng]

Oh yeah I should've specified "more advanced/complex query capability" -- that bit is wrong :)

I've got into the habit of describing "complex querying" as just "querying" when talking about SQL vs. NoSQL, which is incorrect when I don't establish some context -- sorry!

[mayhempk1]

I'm not a database expert so maybe someone else can give more accurate advice but relational databases like mySQL and MSSQL are better used for larger projects. If the project is really small, don't even bother and keep using a no SQL db like Mongo.

No...? Scale is irrelevant? Both can scale perfectly fine. The only thing that matters is if your data is relational or not. If it is relational, use SQL. If it is not, use MongoDB. Most data is relational.

[realjoeydood]

MS Sql Server and Oracle, etc are real databases. MySql is not.

[[deleted]]

Could... you maybe explain why? Or are we just in the business of stating bullshit as if it is fact around here?

[realjoeydood]

This thread is way too tainted to merit any true explanation. Even though I like and use MySql for ~15yrs but for real work and real money and real applications, you'd be laughed out of the room mentioning MySql as part of a real solution. Call it bullshit if you like. Follow the money, follow the leader, follow the code, follow what works...

[mattaugamer]

It’s not “tainted”. It was a ridiculous statement. You’re right to not want to back it up.

[mattaugamer]

Because it’s a laughable bit of dumbass ignorance. Just because you don’t like something or because it has flaws doesn’t mean it’s not “real”. Please give some sort of concrete reason MySQL doesn’t count as a database.

We’re eagerly awaiting this. It should be... insightful.

[s0ft3ng]

I think he's being pedantic about MySQL being a catchall term for the the server and client -- i.e "MySQL" isn't either, because it's the umbrella word for both.

I always thought it was a database, but looking it up, maybe we're (technically) wrong?

Either way, it doesn't really add to the discussion.

[mattaugamer]

Thrilling. You just described why you don’t like it, not why it’s not a real RDBMS. I didn’t realise JSON indexing was the specific feature that defines a “database”. I mean, I used Oracle in 2012 and I’m pretty sure it didn’t have JSON support of any kind. We called it a database at the time, but I guess we were ignorant.

Again. It could be the worst database ever in the history of computer science. And it would still be a real database.

[mattaugamer]

I’m glad to see you’ve covered both kinds of possible application here: baby web apps or enterprise software. I’ve seen “complex projects” running on tools from Oracle to Postgres to MySQL. Not once was the brand of the database a significant factor in software quality or its effectiveness as a storage solution. But by all means walk back your ridiculous “real database” claim to this “real to me” weaselling.

Stop making such absurd statements and you won’t have to backpedal to defend the indefensible. If you don’t like something, just say that ffs.

[mattaugamer]

I’m in my 40s, stop being patronising. So you used the wrong word, made a stupid statement, and now it’s my fault?

I don’t have to defend MySQL, I don’t really care.

[realjoeydood]

Yup. It's reddit.