SQL databases are called relational databases. SQL databases represent data in the form of tables where each table consists of n number of rows. Each row in a SQL database is an entity and each column is an attribute that describes the entity. There is a distinct, structured relationship between rows and columns in a table.
Why would someone want to use SQL?
SQL databases use structured query language (SQL) for defining and manipulating data. SQL is extremely powerful, versatile and has widely used options available making it a safe and great choice for complex queries. You can maintain referential integrity and improve performance by indexing fields and caching records.
Acidity — Each transaction either completes successfully or is fully rolled back
Consistency — Data written to a database must be valid according to all defined rules
Isolation — When transactions are run concurrently, they do not contend with each other, and act as if they were being run sequentially
Durability — Once a transaction is committed to the database, it is considered permanent, even in the event of a system failure.
ACID is a set of properties of database transactions intended to guarantee validity even in the event of errors, disastrous events, etc. SQL databases are ACID compliant and this prevents database tables from becoming out of sync, thus protecting the integrity of your data.
Due to its maturity, SQL has a much stronger and more developed community. Excellent support is available for all SQL databases from their vendors.
- Query Efficiency
With SQL, data is structured and organized. Hence, it is very efficient to query data with the SQL database.
- Transactional Systems
SQL database is a great and stable fit for heavy-duty, transaction-oriented systems.
- Ease of Use
SQL is very lightweight and declarative. Hence, it can be used by less technical staff such as business analysts and marketers.
Why would someone NOT want to use SQL?
SQL requires that you use pre-defined schemas to determine the structure of your data before you work with it. This can be restrictive. Also, all your data must follow the same structure. This requires significant upfront preparation and careful execution. A change in the structure will disrupt the entire system.
- Hard to Scale
SQL databases are designed to run on a single server to maintain the integrity of the data, so they are not easy to scale. They are vertically scalable. You can manage the load by increasing the CPU, RAM, SSD, etc. of a single server.
NoSQL databases are called non-relational or distributed databases. NoSQL databases are a collection of documents, key-value pairs, graph databases or wide column stores, etc. with no standard schema definition that it needs to adhere to.
Why would someone want to use NoSQL?
NoSQL databases are horizontally scalable. You can handle more load by sharding or by adding more servers. In NoSQL, each object is pretty much self-contained and independent. Thus, objects can be stored on multiple servers without having to be linked. NoSQL databases have the ability to become larger and powerful making them the preferred choice for large or constantly evolving systems.
NoSQL databases have dynamic schemas for unstructured data and data is stored in many ways: document, column, graph, key-value, etc. Due to this flexibility, you can create documents without having to first define their structure. Each document can have its own structure. The syntax can vary from database to database. You can add fields as you go.
Basic Availability — This means while the database guarantees the availability of the data, the database may fail to obtain the requested data or the data may be in a changing or inconsistent state
Soft State — The state of the system can change over time i.e. stores don’t have to be write-consistent, nor do different replicas have to be mutually consistent all the time
Eventual Consistency — The system will become consistent over time i.e. stores exhibit consistency at some later point (e.g., lazily at read-time)
NoSQL databases are BASE compliant. A BASE system gives up on consistency, which in turn improves scalability.
Why would someone NOT want to use NoSQL?
- Query Inefficiency
NoSQL database provides flexibility in the type of data that you can store. Hence, querying isn’t as efficient as in SQL due to the potentially large difference in data structures. They are not stable enough for high load and complex transactional applications.
- Adolescent Community
NoSQL community is not as well defined as SQL since it is relatively new. NoSQL databases have to rely on community support.
- Hard to Use
Each NoSQL database offers its own unique querying language, which means more languages to learn, increased difficulty in connecting these databases to applications. Querying NoSQL database typically requires developers or data scientists.
The lack of JOINs leads to denormalization, which leads to data bloat and rigidity.
The structure of your data is the most important factor in determining whether you want to use a SQL or No SQL database.
SQL database is good if you need predefined structure, set schema, and multi-row transactions. It provides great benefit for transactional data whose structure doesn’t change frequently, and where data integrity is paramount. It is also best for fast analytical queries.
NoSQL databases are the preferred choice for large or ever-changing datasets. With NoSQL, the focus is on scalability and flexibility and not on query efficiency. Hence, great for rapid development and iteration.