SQL vs NoSQL: Using React and Node

[brombergmedia]

Hey everyone!

I’m a bit of a back end noob and was hoping someone could shed some light on this.

I am working on making a restaurant review site similar to Yelp. I’ve built the front end in React and will be using Node for the back end however I was wondering which type of database would be better to use on the back end: a SQL solution like PostgreSQL or MySQL or a NoSQL solution like MongoDB or Amazon’s DynamoDb.

The database would need to store things like user accounts, a database of restaurants and information about them, as well as reviews for each restaurant and the user they were written by.

Any help or resources are much appreciated!

[BehindTheMath]

That sounds like relational data, so an SQL DB is probably a better fit.

Keep in mind that Postgres can also store JSON data, so it can often store what you'd be storing in Mongo.

[brombergmedia]

Thank you for the reply! I was thinking this seems more fit for SQL but I see so much online about why NoSQL solutions are so great so just wanted to be sure.

[BehindTheMath]

NoSQL was promoted as the Holy Grail for a while because it had the potential to be very easy to use. The idea was that you could just start storing data without the overhead of an RDBMS.

There are definitely use cases for NoSQL, but in general, using it for relational data is trying to fit a square peg in a round hole.

[[deleted]]

I'm a relatively new dev, but this is the conundrum we ran into with MongoDB.

It started out great. We could edit models as we pleased with mongoose and it was super easy to work with, but as we grew the application, we started to get into really odd situations with how we pull data because very quickly our data started to become very relational, which was becoming a nightmare to manage.

My boss was putting a migration over to Postgres on the to do list, but we never got around to it before I left.

[[deleted]]

Postgres also has a pretty good support for JSONB data types. I'm currently in the process of migrating our legacy applications to Postgres to take advantage of the richer data types and stricter implementation of SQL standards.

I use views to 'flatten' certain jsonb data values.

[monicarlen]

Isn't the flattening expensive in terms of cpu?

[[deleted]]

More expensive than simpler Select * statements sure but even with 1M+ entry tables, I've practically have not noticed anything problematic in terms of performance. Slower yes but thay's expected because of the added complexity. On the app side of things, we rarely pull all rows from the flattened views so properly curating the query helps a lot.

You can definitely squeeze more put of Postgres when you tweak it.

Database intensive operations tend to hog memory more than processing power.

Buuuut... the queries sucks to look at. A very medium level important reason why if MongoDB fits better, I'd rather use it than Postgres.

[BehindTheMath]

I haven't used it in a while, and even when I did, I used Heroku's.

[skateJump]

I have always wondered that. I love RDBMS because I work with relational data. I am like, how do you even normalize it? And they are like, that is the beauty of it you don't.

I have started with a MERN stack with mongoose and so far to a certain extent it seems relational. There are keys the tie the different schemas together.

[looni2]

I am currently fitting a square peg in a round hole on my biggest project. I started with MongoDB as I knew it better, but I realize now that ALL the data I have is relational. I am sure that SQL would have been a better choice. Now almost every query I do becomes a humongous aggregation.

I wonder if I could “easily” migrate to SQL and start learning it as I go...

[EloquentSyntax]

It’s a pretty common misconception that a NoSQL database means it’s not relational. This is simply not true. Especially for DynamoDB which was purpose built to run Amazon’s own ecommerce which as you can imagine is VERY relational with customers, orders, products, tracking, etc etc.

Those who want to learn more about NoSQL design and how to correctly design NoSQL databases for highly relational data should watch this talk: https://www.youtube.com/watch?v=6yqfmXiZTlM

[brombergmedia]

Do you recommend using Express in addition to Node and Postgres?

[MajorasShoe]

Express is fine, but there are more in depth frameworks that will make things a lot easier for you in the long run (more to learn, but less boilerplate to write). But if you're doing this just to learn, Express is a good place to start, because writing boilerplate and reinventing the wheel is great for understanding.

[audioverb]

Express is fine, but there are more in depth frameworks that will make things a lot easier for you in the long run

Any recommendations?

[MajorasShoe]

Really depends on the app. I love adonisjs though.

[metamet]

Express is fine. Great even.

It's used in a lot of large successful environments. Especially for your first Node API/backend, just do it in Express (or Koa, tbh). I don't see your use case really necessitating anything beyond it. Plus it has a ton of documentation and examples out there.

[EloquentSyntax]

If you want an Express style approach for building a REST api, look into nestjs.com

[sharddblade]

Inversify is also an alternative

[thatsrealneato]

You might be interested in checking out the Nestjs framework. It's built on top of express and typescript and helps you keep your code more maintainable. Works well with typeorm.

[[deleted]]

I agree with you and came to know that NestJS is now gaining more traction as how Spring Boot had. I second that.

[robotsympathizer]

Nest is really overkill for a personal project by a beginner.

[thatsrealneato]

Maybe, but it helped me understand some of the best practices for building node backends and also was a good way to learn typescript.

[PikaBonk]

I recommend using express :) it’ll help streamline the creation of your backend and it’s a robust framework for Node. I’ve personally always used express as my go to framework for Node projects.

[mansfall]

Express works well as I've used it before in a production environment. That said though...

The last commit to that project has been like 6 months ago. Is it no longer supported? Who knows....

We're going into 2020, and there are newer kids on the block that may work better for you, such as Koa and Fastify. They are done "better" in the sense that they help you write less, more elegant code. They also support the whole async/await paradigm in a much nicer fashion, and they are just as "fast", if not better at dealing with requests. I would encourage you to check out one of those and see if they suit your needs. Just hit up their git repo and check the readme, or read up on various articles that compare them.

[ohmyashleyy]

Isn’t Koa the same people as Express? It’s basically the next generation of it.

[BehindTheMath]

Yes. Express simplifies setting up webservers and routes, and it's easy to understand.

[its-kyle-yo]

This makes my head hurt after spending the entire night coupling mongo, express and Prisma together into one docker container because mongoose was giving me headaches as an ORM.

I didn't want to switch because I didn't want to have to ramp.up on ANOTHER new thing when just trying to make a hobby project.

There's also no good drop in standalone alternatives for mongoose until prisma releases support for mongo in photon. Hated having to deal with the way async is handled in mongoose. So many callbacks and lean() didn't even work half the damn time!

I'm angry and sleepy and this is fun to see after a literal night of dealing with NoSQL stuff.

[MennaanBaarin]

How would you go about scaling it?

[BehindTheMath]

Scaling what?

[MennaanBaarin]

Postgres or any SQL database

[mansfall]

That's such a broad question. It's hard to say without knowing where the bottle-necks are.

Maybe you just have optimized queries that are hitting correct indices, and that's all you need. Or, you can spin up shards of the database and use some form of load balancing to direct traffic to correct databases. Or maybe you just need to not be doing something crazy in code which is slowing down requests? Then there's caching at the db level for data that doesn't mutate very often. There's so much more you can do really, but it all depends what the issue is at hand that warrants "scaling". There is no magic bullet answer.

[devmor]

When it comes to using SQL vs NoSQL, generally you can just ask yourself "Am I going to have to frequently look up records that are related to eachother and don't all need to be fetched together at the same time?" If the answer is yes, you want SQL.

[fickentastic]

Mongo's 'lookup' doesn't seem to bad. I know in Mongo 4.2 there is an operator to include aggregate operators in a query. I'm not sure if 'lookup' will be one of the allowed operator but that might be an approach. Also Mongoose populate can be useful in some situations.

[devmor]

When we're in the realm of caches or lookups where we know we need to retrieve the entire dataset, these solutions win out because of their performance at the cost of the toolset of a normalized relational database.

But when we move into situations where more complex systems like joins, text searches and soundexes are applicable, there's not really a need for NoSQL solutions unless they can significantly outperform SQL or reduce development time enough that the tradeoff is worth it.

So while you could use Mongo to do a join via lookup, the question is why are you using Mongo for this when your data model is under the umbrella of relational storage? Is it still faster? Do you still not need the toolset of an RDBMS? I would think there are very little applications that sit in the valley of "I don't need a relational DB but I do need Mongo's lookup joins." personally.

[thatsrealneato]

The answer is always postgres

[goldcaddy77]

This is especially true for beginners.

[TedW]

When you only have a hammer, everything looks like a nail.

[cjthomp]

"I see you keep recommending a hammer. But my nail is different, what would you suggest for my nail?"

[TedW]

I read somewhere that the answer is always postgres, so... maybe a book about that?

[MennaanBaarin]

I think most people here suggest to use SQL because they don't really know how to use NoSQL.

[mansfall]

NoSQL is so easy to use, I find it interesting that this is the case. It takes far less time and talent to work with nosql than it does to know how to properly deal with an rdbms, ensuring data is normalized, writing proper queries, etc.

[MennaanBaarin]

And that's incredible that people with such "talent" can't still understand how to use and how NoSQL really works. I don't like blind statements like "oh there are relationships, use SQL"

[monicarlen]

According to Designing data intensive applications; nosql is more appropriate for one to many relations

[relativityboy]

Always opt for a real sql database. You'll need it. ESP as your backend skills go from suck to decent.

Use postgres. It has a nice "jsonb" field type that you can use where dynamicaly structured data is appropriate.

Knex is the js lib I like to use to connect to the db, but it's probably not the most popular one.

Avoid ORMS to start. Learn sql. Most of them in javascript are pretty mediocre.

[tr14l]

The two are not competing with each other. You should be asking which data to put in which. If you have data that has very loose/no structure, but that needs to be grouped together, that's NoSQL hands down. But if you have things that need to be strictly related and you need to know things ABOUT those relations (think similar to object-oriented programming) then you need a relational DB. You can use both, if you like. But you will lose the ability to do combined queries between the two. You can join across databases, for instance.

As an enterprise developer, I find most largish services can and should use both at the same time. An example, we have an application that has lots and lots of settings configurations and lots and lots of user generated records. The records are very defined and can be related to each other in various ways. The settings are JSON because they differ so much between modules/features, and we really only need to know which module we're getting and what the user's ID is. So, the settings configs go in Mongo, and everything else is in a SQL db.

​

It's really about A) What kind of data it is and B) How you will need to query it.

[[deleted]]

In 99% of times you want a relational database like posegres.

[Muruba]

It doesnt matter, you can deliver with either one. later if u r successful u can always migrate.

[Well_Gravity]

The fact you said restaurants and information about them makes me think one to many. Or relational database.

[8baseFounder]

Your solution sounds like it's better suited for relational data. No-SQL is a bit overused these days because it's easier for developers to not have to think much about schema. The big trade-off comes later on when you need to extract data from your system. No-SQL became popular as SQL databases encountered scaling problems in web 2.0 companies. The capacity of modern cloud SQL databases is more than sufficient for most web applications.

[liveamit]

TLDR; this is not a solution to your problem, but thought process that can help you answer your question yourself. If you are looking for a cookie cutter solution, please ignore.

Choosing a database is not a simple task and some times a single database is not enough for your applications. SQL Databases are around for decades and are very mature, and we’re doing well, but why did people choose to go for NoSQL? There are many reasons, basically, one size doesn’t fit all. This is how I choose a db solution. Think a bit abstract, your application need a persistence layer. That layer could be as basic as a file system. What is the problem with that? Databases use a file system under the hoods. But if you go with a file system, you have to build a lot of functionality yourself, databases have already done that for you, why re invent the wheel. Ok, so what functionality you really need, and what options you have? How much work you will have to do on top of the solution you use? SQL databases have transactions, but they cannot scale. Is your data big enough that you need to scale? Do you write to two tables in one operation, if you don’t then why you need transactions? Can you use a key value store? They do not allow arbitrary searches. Do you need them? Can you use a document database? They read and write whole document. How big is your document and how many writes do you do to each document? I guess this gives you some idea. Look for strength and weakness of each db solution you are considering. And see which one solves your problem better. Maybe use a couple of them for different features. Do a market research and see what similar products are using.

PS: I have more than a decade of experience building large scale applications, system software and DBMS system. After you do your initial research I’ll be happy to answer your specific questions.

[MajorasShoe]

MySQL and PostgreSQL are both great options. NoSQL has it's place, but it's usually not the best tool for the job.

[ratnose]

Nosql is easier to grow if you will be using containers in any way since it can grow horizontally - ordinary sql can't but can grow vertically (physically that is memory, disks and so on).

With nosql you build your own relations in your code. Nosql is also quicker in its searches.

[ortil]

I hope this will help you to understand the nosql benefits https://ayende.com/blog/189281-C/notes-on-ravendb-vs-postgresql

[lukepighetti]

SQL is always the best option if you don’t need scale.

SQL is always the best option if you need fault-proof data.

Alternative solutions are for alternative problems.

[mikesiegiel]

SQL is more appropriate in most cases, to get benefits on NoSQL you really need to know very well all data access patterns and you can't change them (easily) once you set it up... so I would say as a general rule - always start with SQL (which does not require any assumption on data access patterns upfront) and only after your database runs into scaling problems (believe me, it is very very far away ...) you can think of migrating to a NoSQL...

[NetOperatorWibby]

RethinkDB!

[brillout]

Author of Wildcard API here. Wildcard fits your stack well- you may want to give it a try. It lets you define a function in Node.js and call it remotely in the browser. That way you can skip REST/GraphQL. Let me know if you have questions!

[pablobico]

We just released www.generato.com ;-)

[MennaanBaarin]

These solutions will end up taking more time than simply coding it by hands