When working on personal projects or large-scale applications, developers will often have to set up a database layer to persist the data. With all of the different databases out there, there is not a one-size-fits-all database solution and what you choose will depend on your project needs and any tradeoffs considered.
Below are the two main types of databases.
-
SQL / relational databases - data is structured and database is accessed with SQL (Structured Query Language)
-
NoSQL / non-relational databases - data is unstructured and database is developed to scale and handle many queries
Here’s a side-by-side breakdown of the two types:
|
SQL |
NoSQL |
| STORAGE |
Data is structured and stored in tables
- Each row contains information about one entity (record)
- Each column contains separate data points (attributes)
|
Data is unstructured, and there are different types of NoSQL databases with different storage models
-
Document Databases: Data is stored in documents and the documents are grouped together in collections. Each document can have a different structure.
-
Key-Value Stores: Data is stored in an array of key-value pairs, with the key acting as an attribute name that is linked to a value
-
Wide-Column Databases: Instead of "tables", columnar databases have column families, which are containers for rows. These databases are best used for analyzing large datasets.
-
Graph Databases: Data is saved in graph structures with nodes (representing entities), properties (information about the entities), and lines (connections between the entities). These are best used to store data whose relations are best represented in a graph.
|
| DATABASES |
-
Open Source: PostgreSQL, MySQL, SQLite, MariaDB
-
Other: Oracle DB, MS SQL Server
|
-
Document Databases: MongoDB
-
Key-Value Stores: Redis, Dynamo, Voldemort
-
Wide-Column Databases: Cassandra, HBase
-
Graph Databases: Neo4J, InfiniteGraph
|
| SCHEMA |
Fixed schema
- Columns must be decided on and set up before you add any entries/rows
- Each row must have data for each column
- Schema can be altered later, but it will modify the entire database and will go offline to update
|
Dynamic schema
- New columns can be added on the fly
Each entry/row equivalent doesn't have to contain data for each column equivalent
|
| QUERYING |
Uses SQL to define and manipulate the data |
Queries are focused on a collection of documents, sometimes called UnQL (Unstructured Query Language) |
| SCALABILITY |
Database can scale vertically
- Vertical scaling is more costly as it needs more horsepower (CPU, RAM) of the hardware to increase capacity
- Horizontal scaling across multiple servers is possible but it's challenging and time consuming
|
Database can scale horizontally
- Horizontal scaling needs more machines (servers) in the NoSQL database infrastructure to increase capacity, which is cheaper because servers/hardware/cloud hosting is cheap
|
| RELIABILITY / ACID COMPLIANCY (Atomicity, Consistency, Isolation, Durability)
|
- Majority of SQL databases are ACID compliant
- Strong data reliability and safe guarantee of performing transactions
|
Majority of NoSQL databases sacrifice ACID compliance for performance and scalability |
| WHEN TO USE |
- When you need to ensure ACID compliance (e-commerce and financial apps, etc)
- The data is structured, unchanging, consistent
- Business is not facing hyper-fast growth that requires more servers
|
- When you don’t want data to become a bottleneck
- Working with big data
- Storing large volumes of data that often have little structure (and can store different types of data together and on the fly)
- Taking advantage of cloud computing and storage because it’s cheaper and can spread out across multiple servers for scalability
- When rapid development is important
|
Resources
Grokking the System Design Interview - SQL vs. NoSQL (paid resource)
mongoDB - NoSQL vs SQL Databases
Top comments (1)
You can have the best of both worlds:
cubicspot.blogspot.com/2012/05/cre...
I wrote that post back in 2012. I still use the same techniques today as documented by that post with a few minor refinements. Since then, SQLite has grown and matured to the point that it is my go-to for personal projects that need a database but won't be deployed to a production environment. SQLite is small, lightweight, ultra-fast, self-contained, zero-dependencies, and comes as pre-compiled packages for several scripting languages. If there is an arbiter TCP/IP service written for it, it is also highly scalable and can even be sharded.