When building a new app, database selection is one of the most critical steps. You have to consider implementation, setup, maintenance, ease of use, and the features of a database. This article overviews the pros and cons of several popular databases. Hopefully, it offers some help in choosing a suitable database for your needs.

Relational vs. Non-relational databases

Relational databases, also referred to as relational database management systems (RDBMS), retain data in tables. Usually, relationships can be formed between the tables by shared information, as the name relation database indicates.

Suppose you have an online store, then you need to save information including products, customers, and orders. In a relational database, you can use a table for each main category and use shared information (e.g., product ID in both table products and orders) to join the tables when necessary.

After storing information in relational tables, you can use SQL (Stuctured Query Language) to create relationships between the tables and perform CRUD (create, read, update, delete) operations.

By contrast, non-relational databases, or NoSQL (Not only SQL), keep data in individual documents. Usually, the data is stored using key-value pairs and in JSON-like structures. For the same online store example, you can collect all information connected to one product in a file, including the details of the product, the orders of this product, and in each order you add the details of the customer that placed the order. Similarly, you can store all information related to a customer or an order in one file.

Let's take a look at several popular databases you might need to know before building your apps.

Relational Databases

MySQL

MySQL is an open-source database management system based on SQL, developed by Oracle and written in C and C++. It is one of the eariliest launched RDBMS and has many variants. As one of the most popular databases, MySQL has been broadly implemented in various fields, either on a small or large scale. MySQL features ease of use, high reliability, great simplicity, and fast performance. Besides, it guarantees data security by using a data security layer and encrypting passwords.

Pros

• Free version available.

• Paid versions with full support for commercial use.

• Frequently updated.

• Rich functionality.

• Capable of handling both structured data and semi-structured data.

• Fast performance and high security in processing large volumes of data and huge numbers of read and write operations.

Cons

• Support for the free version is not free.

• Resource-intensive, requiring significant hardware and memory when working with large datasets.

• Limited advanced SQL features.

• Users may need to implement additional security measures to protect data.

Microsoft SQL Server

Microsoft SQL Server, sometimes MS SQL Server or SQL Server, is a relational database management system developed by Microsoft and written in C and C++ programming languages. It is designed for data storage, featuring high performance, reliability, and security. Additionally, SQL Server has an elegant mechanism to promote data restoration and recovery. In improper shutting down or power loss conditions, SQL Server restores your database using the log files, caches, and backups.

Pros

• Easy to use.

• High reliability and performance.

• Free for development or educational purposes.

• Excellent integration with Microsoft ecosystem.

• Dynamic resource allocation.

• Outstanding data recovery support.

• Support advanced SQL features, including subqueries, transactions, and table partitioning.

Cons

• Limited open-source support.

• Enterprise-level support and maintenance can be pricey.

• Setup and maintenance can be complex, especially for large-scale applications.

• Resource-intensive, requiring significant hardware and memory when working with large datasets.

• Restricted compatibility with other databases.

Oracle Database

Oracle Database, also referred to as Oracle DBMS or Oracle, is a multi-model database. Released in 1970s, designed to improve error-prone manual database management. It supports running on various operating systems. As a multi-model database, it works well with structured data, semistructured data, spatial data, and RDF storage. It is a robust system. You can use it to store and retrieve data quickly and securely. It is also scalable and distributed, a great option for organizations that deal with enormous databases and require rich features.

Pros

• Enterprise-grade capability and reliability.

• Professional support and maintenance services.

• Support advanced SQL features, including subqueries, transacrions, and table partitioning.

• High performance to handle large amounts of data and huge numbers of read and write operations.

• Horizontally scalable to deal with high-traffic applications.

Cons

• Limited open-source support.

• Enterpriss-level usage can be expensive.

• Set up and maintenance can be complex, and specialized knowledge might be necessary for large-scale applications.

• Restriced compatibility with other databases.

Non-relational databases

MongoDB

MongoDB is a non-relational and schema-free document-based database. It stores data in key-value pairs, thus is better used for unstructured data. It is an open-source platform, developed in C++, Go, JavaScript, and Python languages. MongoDB retains most of the data in the RAM to ensure performing queries at a high speed. In addition, simplicity is a major feature of MongoDB regarding the query syntax and execution. Without a predefined schema, MongoDB offers high flexibility for keeping all kinds of business data. MongoDB is also extremely scalable. You can scale it vertically by adding more resources or horizontally by data sharding.

Pros

• Flexible and scalable data management.

• Fast and easy to use.

• Access data quickly and easily.

• Easy to scale it up or down to serve different use scenarios.

• Using a rich query language to perform complex data filtering and aggregation.

• High performance to handle large amounts of data and huge numbers of read and write operations.

Cons

• Environment setup can be complex.

• Limited SQL support.

• Limited secondary indexing.

• Commercial support might be costly for large-scale applications.

PostgreSQL

PostgreSQL is also an open-source solution. Different from MongoDB, it is an object-relational database (ORD), a hybrid of SQL and NoSQL databases. Aiming at providing high efficiency and rich integration options, PostgreSQL saves data as structured objects. Its operations are catalog-driven, so that you can write queries in multiple programming languages with no need to recompile the database or edit the data types.

Pros

• Freely-available and can be adapted or modified according to specific needs.

• Robut, reliable, and scalable.

• Plenty of predefined functions.

• Able to deal with structured data, semi-structured data, key-value data, and spatial data.

• Support advanced SQL features.

• Active community support.

Cons

• Maybe not as fast as other databases for certain applications.

• Setup and maintenance can be complex.

• Resource-intensive, requiring significant hardware and memory when working with large datasets.

• Restricted support for mobile platforms.

Elasticsearch

Elasticsearch is an open-source document-oriented search solution. Written in Java, Elasticsearch has high compatibility and can be implemented on most platforms. It is also distributed, which allows it to be scaled up and down to handle different volumes of data elegantly. Furthermore, the speed of performing queries is high, making it a real-time search engine. Thus, you can use it as an easy and fast tool for data analysis, processing and visualization.

Pros

• Easy to use.

• High scalability.

• Fast and powerful full-text search.

• High availability to ensure data access in case of improper shutdowns.

• Rich integration options with other tools.

Cons

• Setup and maintenance can be complex.

• Search latency might occur when working with large datasets.

• Enterprice-level usage can be expensive.

• Resource-intensive, requiring significant hardware and memory when working with large datasets.

Redis

Redis (Remote Dictionary Server) is an in-memory key-value database, written in ANSI and C languages. It avoids using pre-defined schema and stores all data in memory. Redis is also referred to as a data structure server because keys can retain data in multiple types, such as string, list, hash, as well as uniques ones including set and zset. Each of them has specialized and shared commands to query and update data. With high speed and high scalability, Redis is an awesome solution for use cases in real time.

Pros

• Easy to use.

• In-memory data storage.

• Support for multiple data structures.

• High performance to handle millions of requests per second.

• Pub/Sub model available to receive real-time updates by subscribing to certain channels.

Cons

• May not be suitable for applications that require strong durability guarantees.

• Data loss might happen when the single node in Redis fails.

• Limited scalability for large-scale applications due to the lack of built-in support for sharding or partitioning.

• Additional security measures might be necessary for sensitive applications.

Now, you should have a better understanding of the advantages and shortcomings of each data source. Here are some more stats from Stack Overflow, one of the most popular knowledge sharing platforms, for your reference.

From Stack Overflow 2022 Developer Survey, professional developers have a strong preference for PostgreSQL and MySQL. Other popular databases include SQLite, MS SQL Server, MongoDB, and Redis. By contrast, new database learners tend to start with MySQL. Although MongoDB, SQLite, and PostgreSQL are also favoured options, the percentages are only around half of that of MySQL.

The figure below, generated on Openblocks, compares the number of questions with the tags correpsonding to the data sources that this article discusses. As of January 2023, over 657,000 questions have been posted with the tag "mysql", ranking 1st among all other data sources mentioned in this article. "sql-server" ranks 2nd, with a number lower than half of that tagged with "mysql".

What's next?

After selecting a well-suited database for your use case, let's get connected to it and put it into use. Openblocks is a platform where you can easily connect to any of the above-listed data sources. Then, you can start getting your hands dirty with designing and crafting apps by dragging and dropping components. Apart from connecting to databases, Openblocks also allows you to use APIs in your apps. On Openblocks, you can also share your apps with colleagues and customers, as well as publish and update them any time.

Give it a try and feel free to reach out to our community on Discord if you encounter any issue or would like to share an idea.