Originally posted on atdatabases.org
One of the first things you'll need to do when starting most node projects is to chose a database and database library. You'll normally want to choose your database before you choose the library, but in the case of @databases, we support a few different databases, so you do have some leeway.
SQL vs. NoSQL
In recent years, NoSQL databases have grown, and then somewhat declined in popularity. They often seem much easier to get started with than SQL databases because you don't need to learn any new language, and you don't need to define your schema up-front. They also promised (in theory) some improvements in scalability vs. more traditional SQL databases.
While there are some situations where the scalability part is relevant, you should keep in mind that Facebook stores the vast majority of its data in a few MySQL servers. Unless you are a very big tech company like Google or Facebook, it is likely that Postgres or MySQL will scale to meet your needs with no problems.
The idea that you can get away without thinking as carefully about your schema in NoSQL databases is also often flawed. You normally end up with just as rigid a schema definition, except nobody has written down what that schema is.
The point about learning SQL is valid, but you can get started without needing to learn very much, and it is an extremely useful skill to develop. If you must use a NoSQL database, MongoDB remains one of the most popular, but I hope you will at least try using an SQL database first.
Choosing an SQL Database
SQL Server and Oracle are both used in many busnesses, but their licensing and pricing models make them difficult and expensive to deploy and manage, and they don't offer any real upsides over MySQL or Postgres, so I won't consider them in depth here.
SQLite
SQLite is great if you need to store data in a small project that will run on people's own machines, rather than on a server. It's very portable, very lightweight, and runs within the node.js process itself. This is also great if you are building an Electron app.
It cannot be shared amongst multiple instances of your app, which makes it a poor choice for most server side applications.
If you decide to use SQLite, you should read Getting started with SQLite and Node.js next.
MySQL
MySQL is widely used and deployed. It is easy to get hosted versions from any of the major cloud providers. If you're already using it, there's certainly no reason you should panic about that choice. I wouldn't choose MySQL for new projects though, because:
- It suffers from serious issues when handling time zones & dates/timestamps.
- It doesn't have a propper type for
BOOLEAN, which leads to messy translation between0/1andtrue/false. - It also doesn't have nearly as good support for JSON as Postgres
If decide to use MySQL, you should read Getting started with MySQL and Node.js next.
Postgres
Postgres is one of the most fully featured databases, and has good handling of dates & times, as well as excellent support for storing and querying unstructured JSON when you need to.
If you decide to use Postgres, you should read Getting started with Postgres and Node.js next.
Top comments (7)
Great lineup! You might also want to add HarperDB into the mix - it has hybrid SQL and NoSQL functionality in one tool, and it's even built from the ground up in Node.js. :)
Postgres with Prisma.io generated client access later.
I do agree that there is value in learning both, but when it time to move on something and hit the ground coding, SQL doesn't seem the best fit generally.
Amen. Great article.
My favourite is SQLite.
I very like this article.
Have Sequelize so that when change required the transition will be easy.