SQL vs NoSQL

If you’re a software engineer, I guess you’re familiar with both SQL and NoSQL. However, sometimes it’s hard to decide which one to go with in our project since each has their own strengths and use cases. In this blog, let’s summarize their differences.

1. Structured Query language (SQL)

Structured Query language is a standard language for processing relational databases. Relational databases save data with clear structure. For example, a bank transaction will always have info of sender, receiver and amount of money.

SQL database stores data in tables (rows and columns). Each row is each data and each column is a property of that data. Tables can be connected through foreign key. Some popular relational database management systems are MySQL, Oracle, MS SQL Server, SQLite, PostgresQL and MariaDB.

2. Non Structured Query language (NoSQL)

NoSQL is the opposite of SQL. It saves databases that have a wide and scattered structure instead of a strict one. For example a database that saves a document about somebody’s activity on facebook. These data can be saved under key-value pairs, and the documents in these can be created without a predefined structure.

NoSQL doesn’t have a strict structure so it can store data in different ways. However, there are 4 main ways:

• Key-Value: Data will be stored under a pair of key-value. Each key has its own value. Some systems to manage key-value databases are Redis and Amazon DynamoDB.

• Document Database: Data will be stored as documents. Documents with the same purpose can be put into a collection. The difference between documents and rows/columns is that documents don’t have to be in the same structure. For example, we want to save account data. For SQL, in an Account table that has id, name, isActive, dob, if name is a string, then all data must have name as a string. However in a document database, the name of a data can be anything. Document databases can be implemented in MongoDB and CouchDB.

• Wide-Column Database: Or Big-Table is a type of columnar data. It stores data in rows and columns, but different from SQL tables, each column of a row is a pair of key-value and numbers of columns are dynamic per row. Popular examples of columnar databases include: Amazon Redshift, Google BigQuery.

• Graph Database: Data will be stored under graph structure in order to demonstrate the relationship between data. Each data is called node (entity) and each node’s property will link to that of other nodes through lines. Neo4J and InfiniteGraph are of this type.

3. What are the differences

a) Storage:

– SQL saves data in tables, each row represents an entity, each column represents a property of that entity. All rows in the same table have the same number of properties.

– NoSQL can save data with different structures depending on what we need.

b) Schema:

– In SQL, schema is fixed. This means, every column in a table must be defined before the table is created and every row of data must have value (even nullable) for all columns. Schema can be altered later, but all data created before that would be affected.

– In NoSQL, schema is dynamic. This means that we don’t really need to define anything in advance, the schema can refer to the records we add.

c) Querying:

– SQL retrieves data using its language.

– NoSQL will retrieve data differently depending on what structure the schema is.

d) Scalability:

– In SQL, it’s vertically scalable such as increasing CPU or Ram.

– In NoSQL, it’s horizontally scalable, just add more nodes/collections

e) Reliability:

– Most RDBMS are ACID-compliant (atomicity, consistency, isolation, and durability) so that it ensures any database transactions are processed in a reliable way, resulting in correctness

– Whilst, NoSQL trade ACID for performance and scalability

Both SQL and NoSQL have strengths and weaknesses. Based on these differences between SQL and NoSQL, you can make decision on when and what to use.