When you should be using SQL or NoSQL

Most of us use a lot of different technologies for our websites and apps these days, but sometimes we build ineffective designs, or perhaps even choose the wrong technologies when we build it.

What is SQL or NoSQL?

SQL or (Structured Query Language) is the primary language used with Relational Database Management System (RDBMS), some examples include Oracle PL/SQL, PostgresSQL.

NoSQL (or non-relational SQL) databases, as the name suggests, don’t have relationships defined and therefore don’t follow ACID (Atomicity, Consistency, Isolation, Durability) properties found in traditional databases.

But both, have it’s benefits. While NoSQL scales horizontally, we also have traditional SQL databases which scale vertically. While SQL databases can have JOINs and Relationships, most* NoSQL databases don’t have these relationships defined, noted that I have said that ‘most’ here, while it is true for more older NoSQL systems, we have seen the rise of newer systems, which actually support this.

NoSQL, really what good is a Database when there is no relationships?

Lets start off with the elephant in the room, like literally. While NoSQL has been around since the late 1960s, it has appeared in the modern Web (Web 2.0) stack due to the need of horizontally scalability.

You may be asking, what is horizontally scalability. Say for instance, when I need more of the same resource, I just add more RAM, CPU power to the server, that is vertically scaling. But what is horizontally scaling, it’s basically adding more ‘nodes’ of the same server across when we need more resources, this is effecitevely more cost effective then traditional vertically scalable systems.

As we progress more and more into Single Paged Web Applications, we see systems being developed using Frontend/Backend. The rise of ReactJS, AngularJS and VueJS has impacted directly on this transition, our thirst for faster web applications and more device responsive has transitioned us to design systems which are both scalable to the amount of traffic (with the power of load balancers) as well as systems which are easily manipulated with JSON.

Goodbye XML SOAP APIs (even though you still exist)

Designing for Complex Systems

As a Software/Solutions Engineer, as well as having the knowledge of OOP (Object-orientated programming), my main goal is to design which usually has the following requirements:

• The system must be easily maintainable, for any future developer who comes onto my team and takes over my role.
• The system must be scalable (scaling to the amount of systems)

While it is easier, to build, run a NoSQL system such as MongoDB it is very, very hard to design a system that matches both of this requirements — especially for modern web apps.

So lets, walk through an example

Designing a System

Lets design a backend system for a cafe which sells delicious food and coffee.

Firstly we need a table or ‘collection’ of products, which has a unique id (for indexing purposes, price and float).

var Schema = mongoose.Schema,
    ObjectId = Schema.ObjectId;
var Products = new Schema ({
  productId   : ObjectID,
  productName : String,
  price       : Float
})

Well, we may need some sizes of coffee

var Schema = mongoose.Schema,
    ObjectId = Schema.ObjectId;
var Products = new Schema ({
  productId   : ObjectID,
  productName : String,
  price       : Float,
  size        : Integer
})
var Sizes = new Schema({
  size            : Integer, //this can be the uuid
  sizeDescription : String
})

Now our ‘relationships’ looks like:

var Products = new Schema ({
  productId   : ObjectID,
  productName : String,
  price       : Float
})

The Rise of a Newer SQL (the NewSQL)

NewSQL is a class of modern relational database management systems that seek to provide the same scalable performance of NoSQL systems for online transaction processing (OLTP) read-write workloads while still maintaining the ACID guarantees of a traditional database system.

Source: Wikipedia: https://en.wikipedia.org/wiki/NewSQL

NewSQL allows horizontally scalable Relational Databases, but also allows us to perform transactions which still maintaining ACID properties.

Something that I really recommend is playing with Google Cloud’s Cloud Spanner — check it out at http://cloud.google.com/spanner.