DEV Community

Cover image for What database should i use?
JDOG787
JDOG787

Posted on

What database should i use?

Hi, so I'm wanting to make a pretty big app but I'm not sure what database to use...

I know there's Mongodb, MySQL, PostgreSQL, and lots more. My question is: Which one should I use? What are the pros, and cons of each? Should I use a SQL or nonSQL database? Why would I use one over then other?

Could someone point me in the right direction?

BTW I do already know mognodb :)

Discussion (55)

Collapse
leob profile image
leob • Edited on

For 95% of the use cases I'd recommend an RDBMS (SQL) such as MySQL or PostgreSQL rather than MongoDB - easier to use, more powerful, and just a better choice. Whether it should be MySQL or PostgreSQL is in my opinion a toss-up, I mostly used MySQL but PostgreSQL is also fine.

MongoDB is really for niche uses cases, those where you need to stash away vast amounts of fairly simple data - think IoT, weather data, any sort of large scale real time data collection - that's where MongoDB shines, not for your average web app. And for apps/systems that need huge scalability beyond the capacity of RDBMS, but then you're really talking huge scale.

But for your typical run-of-the-mill web/business apps, MongoDB would in general be a poor choice. The lack of a standardized and powerful query language like SQL, with its joins, aggregations and so on, the lack of a schema, and the lack of transactions (ACID), makes MongoDB much harder to use. With MongoDB (NoSQL) you will constantly be reaching for tricks and hacks which will make your app harder to develop, and maintain.

Collapse
sarneeh profile image
Jakub Sarnowski

This! People mindlessly go for MongoDB because of NoSQL hype, not understanding the real use case for these kind of databases. I was one of them. Maintaining "a typical app" was a real pain in the ass. Moved to PostgreSQL fixed all the issues and removed a lot of code hacks from the codebase.

Collapse
patarapolw profile image
Pacharapol Withayasakpunt • Edited on

No, I was there because of the lower cost of entry.

  • Generousness of free tier.
  • Easier to configure.

Of course, I know that RDBMS's TRIGGER and FOREIGN KEYs can be convenient.

Also, I don't hate RDBMS, but the language (SQL) itself.

Maintaining "a typical app" was a real pain in the ass. Moved to PostgreSQL fixed all the issues and removed a lot of code hacks from the codebase.

Indeed, if you can explain more...

Thread Thread
sarneeh profile image
Jakub Sarnowski

Mostly, with NoSQL you can't assure any kind of data integrity - everything needs to be based on your app's code. There are no relationships in NoSQL unless you code it yourself - I think that's the main pain for me and it's very, very fragile.

Thread Thread
patarapolw profile image
Pacharapol Withayasakpunt

Indeed, most RDBMS enforces integrity very well, and it would be counter-intuitive to store denormalized / JSON data. Actually, graph-type NoSQL can do this as well (as well as being ACID compliant).

BTW, how do you balance between ORM features vs true SQL features?

Collapse
tomburgs profile image
Toms Burgmanis

I agree.

I tried mongo out when I heard all the hype around it. It’s definitely a step-down from SQL for most and it’s meant for really niche use cases, as you said.

Though I do understand it’s popularity among beginners as it doesn’t require you to learn an entirely new language and concepts just to store some basic data.

It’s also worth noting that it does have aggregations, and while it doesn’t exactly have such advanced joins as in SQL you can use $lookup which uses the same concept as SQLs foreign keys, so it’s not completely featureless, but still lacks behind.

Collapse
leob profile image
leob

I've used Mongo in earnest for one app, and soon I regretted it. But I have to confess that for that app Mongo was simply a poor choice, I should have known better (it was more like "let's try out this fancy new thing").

Collapse
patarapolw profile image
Pacharapol Withayasakpunt

MongoDB supports database that stores container data structure, i.e. not normalized. Not only storing, but indexing as well (arrays, deep json's).

What it lacks. from what I can see, are foreign keys and triggers; as well as user-defined functions. (Maybe because it doesn't have complex SQL variant.)

Collapse
tajeddine profile image
Tajeddine Alaoui

PS: $lookup have huge performance drawbacks, Use ref instead.

Thread Thread
patarapolw profile image
Pacharapol Withayasakpunt

Yes, I admit that $lookup is slow, in my experience.

I expect better with SQL joins.

Thread Thread
tajeddine profile image
Tajeddine Alaoui

I suggest Mongo since it is a general porpuse DB.

Thread Thread
leob profile image
leob

What exactly is a "general purpose DB" ? If Mongo is a "general purpose DB" then so are MySQL and PostgreSQL, you can use all three to store data.

Thread Thread
tajeddine profile image
Tajeddine Alaoui

Mongodb have a wide variety of tools, It's huge ecosysteme provide more general purpose products, If we talk about performance Mongo have the superiority from reading, creating updating.

Collapse
jdog787 profile image
JDOG787 Author

Hmm, ok. I agree that Mongodb is great for some cases, but I've heard that SQL is good for certain cases as well. But they also scale better than monogdb(so I've heard), which is what I'm looking for. I'm going to make a social media app, but I want it to be able to scale easily if needed.

So for this case would you say to use a SQL database?

Collapse
leob profile image
leob

"... they also scale better than mongodb ... which is what I'm looking for"

Eh no, it's the other way around actually - NoSQL databases (like Mongo) potentially scale better than SQL databases (RDBMS) ... with NoSQL you can scale out "horizontally" (using 'shards' on different machines), with SQL databases there are limitations in that regard - conventionally a SQL database has to run on one big fat machine.

So if it's really all about extreme scalability for you, then MongoDB would be your choice. I'd say SQL databases are better/easier than NoSQL in almost all respects (ease of use, powerful query language with joins, normalized data model, defined schema, ACID), except for scalability - that's where NoSQL shines - at least in theory :-)

But, we're talking about giga scale here ... will your social app really be that huge? You're not going to build a new Facebook or are you :-)

Thread Thread
jdog787 profile image
JDOG787 Author

Oh, I guess I got them mixed up lol.

will your social app really be that huge?

No, I don't think so, but I'm partly wondering which kind of database is better, and partly planning ahead.

You're not going to build a new Facebook or are you :-)

Probably not, but that would be cool :D

So you think mongdb is a good choice?

Thread Thread
leob profile image
leob

What stack (backend) are you gonna use, node/express or something else? Social media app, you need streams/channels for realtime ... I'm thinking GraphQL and all that ... Prisma? prisma.io which supports PostgreSQL and MySQL. But yeah "social media" = "big amounts of non structured data" (text, images, audio/video)" means MongoDB does sound like a good fit.

Thread Thread
jdog787 profile image
JDOG787 Author

Yeah, I'm thinking node/express, and graphql. Never heard of prisma though. And yeah, I guess mongdb is good, so I'll go with that! Thanks for all the info and help >:)

Thread Thread
leob profile image
leob

Mongo is a good choice if you have an app where (a) you don't have an extensive data model with lots of relations/joins (like an accounting system, or other "line of business" apps), and (b) you need to store lots of unstructured data like text, images, video/audio and so on.

So your social media app fits the bill, while I'd never ever try to develop an accounting app with Mongo as the data store :-)

Thread Thread
jdog787 profile image
JDOG787 Author

Ok, good to know. Thanks!

Collapse
chiubaca profile image
Alex Chiu

100% agree with this. I'm loving working with postgres and I was a firestore user for a long time.

BUT just so the facts are right, mongodb does support ACID transactions. Also when u pair mongoDB with mongoose you can manage your collections with schemas in an OKish way.

Collapse
leob profile image
leob

You're right, more recent versions of MongoDB do have ACID, they've added that. And I've used mongoose as well, yes that's an improvement because you can declare schemas.

Anyway, I think how it often goes is that a dev starts with a simple app, with a very simple data model, and then MongoDB looks great, but later on when they expand it and the data model becomes more complex they will often think "I wish I'd had chosen an RDBMS" ;-)

But interestingly it's not per se an "either or" choice - it is possible to combine SQL and NoSQL (two databases in one app), for instance you could manage your users and stuff like that in a SQL database, while you store large amounts of "unstructured" data (text, images, video etcetera) in MongoDB. Perfectly possible and I think there are use cases where it might make sense.

Collapse
cullophid profile image
Andreas Møller

95% is a bit low IMO

Collapse
leob profile image
leob

Haha yes probably more like 98-99% ;-)

Collapse
danwalsh profile image
Dan Walsh

I’d highly recommend looking at Cloud Firestore — it’s scalable, accessible via REST API and has generous amounts of usage on their free Spark plans.

That said, apps and databases are “horses for courses”. It all really depends on your requirements and your tech stack/platform.

Collapse
patarapolw profile image
Pacharapol Withayasakpunt
Collapse
jdog787 profile image
JDOG787 Author

If I had to choose between firestore and mongodb I'd go with mongo db :P

Collapse
danwalsh profile image
Dan Walsh

If you’re also going to be managing the infrastructure supporting MongoDB, then yeah, that makes sense.

Like I said, choosing your DB entirely depends on your technical requirements. I build a lot of smaller, single-purpose web apps, and I’d rather not have to manage any more infrastructure than necessary, so Firestore suits my needs. Of course, that’s not going to be true for everyone.

Hope the comment threads on your post have helped you to pick your DB! ☺️

Thread Thread
jdog787 profile image
JDOG787 Author

I agree. Yes, I did figure out what DB to use. I'm going to use mongdb :D

Collapse
ahzu profile image
Ahzu Direct

Absolutely

Collapse
ahzu profile image
Ahzu Direct

IE. Mongo is needed in the MERN stack

Collapse
andreidascalu profile image
Andrei Dascalu

Well, as a general direction, you have SQL, noSql and blends.
SQL: you have mySql (though Mariadb as a drop-in MySQL replacement is significantly better and provides noSql models as well as climb stores) and postgresql as main options. I generally go with Mariadb because I know it better and how to optimize it. Don't disregard online sources.

NoSql: for all intents and purposes Mongo is ahead.

Column store: I strongly recommend Clickhouse as a high performance column store with SQL compatible syntax.

Collapse
patarapolw profile image
Pacharapol Withayasakpunt

BTW, I don't really understand why database choice matters, unless you aim for specific features.

Yes, you can put normalized data in MongoDB, and denormalized in Postgres.

But MongoDB supports deep JSON indexing and Array indexing, as well as compound indexes. Not sure about any traditional RDBMS.

Collapse
leob profile image
leob

MySQL does support JSON lookups and indexes. Never used it, but I've heard that it's there.

Collapse
tajeddine profile image
Tajeddine Alaoui

I think devs underestimate the power of Mongo, If you aren't planning to make a db model that rely on huge amount of joins then Mongo is the best choice.

Collapse
leob profile image
leob • Edited on

Whether Mongo is the best choice or not totally depends on your use case - what does your app do ... without knowing the use case it's impossible to say if Mongo is the best choice. In my experience, in most cases it isn't, but for a certain class of cases it might be. So the answer is "it depends". But well, "huge amount of joins" - even when your data model has just a dozen joins/relations then an RDBMS may already have the edge, from an ease of use point of view.

Collapse
imthedeveloper profile image
ImTheDeveloper

Completely depends on your app.

Tell us more about it.

What is it for, who is it for, how do people or a person use it. When do they use it etc.

What is relevant to be stored by this app?

Collapse
Sloan, the sloth mascot
Comment deleted
Collapse
dillonheadley profile image
Dillon Headley

Actually mongodb is a NoSQL database meaning it is also non-relational. Also fun fact: The no in NoSQL stands for “not only” SQL.

Collapse
tajeddine profile image
Tajeddine Alaoui

That's my boy, I wish if devs apperciate Mongo more.

Collapse
margo_hdb profile image
Margo McCabe

This Database Architectures & Use Cases article may be really helpful in understanding which databases to use and why, including pros and cons of each type.

Collapse
jdog787 profile image
JDOG787 Author

I think I figured out which kind of database I'm going to use for this project, but I'll have to remember to check out that article if I'm ever in this position again for another project. Thanks :)

Collapse
margo_hdb profile image
Margo McCabe

No problem! Next time you'll have to check out HarperDB as well :)

Thread Thread
jdog787 profile image
JDOG787 Author

👍

Collapse
ahzu profile image
Ahzu Direct

I think a little strange sometimes so pay no attention to this comment. But seriously, I would use Docker. This way, you can use as many databases as you want in the same app. One section of your app could be for media files and the other for text. You may want to use Redis as well. Use an API and load balancer to tie the whole thing together as you desire. --------->>>> But check out MERN----> I feel it the future of app dev and of coarse you will be using Mongo.

Collapse
codemonkey51 profile image
Codemonkey51
repldb.

Hmmm i dont really know, haven't used that many db's. Someone else could prob giv a better answer than me here

Collapse
jdog787 profile image
JDOG787 Author

yeah looks like it ;)

Collapse
zazapeta profile image
Ghazouane

I like starting with sqlite. Easy and fast until you need more - then postgres db is good.

Collapse
patarapolw profile image
Pacharapol Withayasakpunt

SQLite is in a different league from online database, where there need a lot of admin jobs and housekeeping.

Collapse
loiclefevre profile image
Loïc

Definitely recommend the latest Autonomous JSON Database - you can get 2 for free forever with 20 GB of data (choose Always Free ATP).

Advantages:

  • optimized for JSON: blogs.oracle.com/jsondb/osonformat
  • multi-model: you get SQL and NoSQL specialized APIs, as well as relational powers as well plus spatial and graph processing plus machine learning models building and REST API access...
  • multi-workloads: oltp, anlytics, IoT, websites, mobile app...
  • scalable, secure, highly automated...
  • converged database explained in 3 minutes: youtu.be/yBWgb_oh39U

More info: docs.cloud.oracle.com/en-us/iaas/C...

Collapse
tamerlang profile image
Tamerlan Gudabayev

We need to know the context more to figure out which database is the right one for your project, but in the most common use case scenario a typical relational database would work. I recommend postgresql.

Collapse
naveens16 profile image
Naveen.S

Hfor better recommendation i would like to know your use case. For my most of the projects I always prefer MongoDB over any SQL database.

Collapse
arunmurugan78 profile image
Arun Murugan

Depends on the Application
Check this out!
youtu.be/W2Z7fbCLSTw

Collapse
w6 profile image
w6

If you want a free and simple database for beginners, I recommend you start off with firebase.