When to choose a SQL or NoSQL database
Tags: webdev, database, postgres, firebase
Published: 2023-01-16 17:25:16

Regarding databases, there are plenty of options to choose from. For example, there are relational databases, document storage databases, graph databases, and other types suitable for specialized use cases.

In the context of a SaaS application, you usually need to choose between a relational or a document storage database. In this article, I will explain how to decide which one to use in which scenario. Selecting the type of database is a fundamental decision you must make in the first days of your project. You'll face an effortful data migration if you change your mind later.

The two most common relational databases are Postgres and MySQL. There are some differences between these two technologies, but this is outside of the scope of this article. For most projects, they are equally good choices. That's why I recommend just using the system you're familiar with. Relational databases allow you to model your data structure using typed fields and relations among your entities. This modeling process requires effort and experience but can be adopted by most developers quickly. 

A document storage database is based on key-value pairs. Objects are stored in a structured way, usually represented as a JSON object. These objects are accessed directly by their keys instead of queries. In terms of performance, both approaches are equally good. The document storage database has advantages in very high amounts of data because it does not need to obey the relationship among the objects. Therefore the database can efficiently distribute the data to several nodes.

You may ask yourself, which approach is the right fit for your project? The answer is surprisingly simple. Only a relational database will be the right choice if your domain model has relations. Of course, this is not a law of nature. You can still build your SaaS application on a document storage database. Often this will result in a very high amount of redundancy and even inaccurate data because only your code is responsible for keeping the data consistent. Every bug in your application can easily lead to a mess. The database will not support you at all to keep your data consistent.

We often see that developers use document storage databases for the wrong reason. Sometimes they assume that they are faster (which is not true). Sometimes they think they need unlimited scalability, but this isn't the case for almost all projects. The most popular commercial document storage database is Firestore, a NoSQL cloud database provided by Google's Firebase. Firestore is especially a good fit for mobile apps in case all you need is to persist JSON objects. It's easy to start with, but it does not offer you the comfort of relations. And also, there's no good way to query your data programmatically. If your use case is not trivial, you will see the limit of the approach very soon in your project. For this reason, most projects should consider using a relational database. There are plenty of good solutions on the market to support you in modeling your schema and accessing it from your application via API, making document storage databases unnecessary for most projects. For example, some so-called Backend-as-a-Service systems like Strapi, Directus, Supabase, and others enable you to design your data model and automatically create an API. If you implement your backend yourself, you can use an ORM to do the heavy work. Typical examples are Prisma for Node.js and Doctrine for PHP.

## Conclusion

Document databases are useful for processing high loads of incoming and generally very high amounts of data. For instance, when you build a storage system for log files, chat messages or sensor data, these NoSQL solutions are a good fit.