What database to use? SQL or NoSQL?

Selecting the proper database in a development project is a decision that has to be taken carefully. Developers should not just pick a database and go without properly weighing in the benefits each type of database brings to the table.

SQL Databases

SQL is the elder sibling of the database that has supported the entire world up to now.

ACID and BASE are two transaction models that prioritizes different characteristics of databases.

ACID focuses on consistency over availability. In ACID, a transaction is either complete or it does not exist.

BASE focuses on availabilty over consistency. In BASE, a transaction may be incomplete, users might experience inconsistent data temporarily and the data will be consistent eventually.

Modern databases are distributed data stores that replicate data across multiple nodes connected by a network. Also they allow users to perform multiple data manipulations in a single transactions (reads and writes together). Users expect the data to be consistent among all the nodes after a transaction. But CAP Theorem introduces a limitation on these claims.

CAP Theorem explains how only two of the following guarantees can be provided by any distributed data store.

Consistency: Every read receives the most recently updated data or an error.

Availability: Every database request gets a successful response without a guarantee that the data store now contains most recent data.

Partition tolerance: The system is able to work even few messages are dropped or delayed between the nodes.