SQL(Structured Query Language) is a language for interacting and performing operations on databases. A wide array of SQL supported database systems exist (MYSQL, PostgreSQL, MsSQL, etc).
Since it’s inception over forty years ago, it has been the go-to choice for web storage. But, it’s been forty years. There are alternatives. And, unsurprisingly, a good number of users have gradually migrated away from SQL and on to new platforms.
But should they have? Isn’t SQL still pretty great? Let’s find out together.
In this article, we will discuss a few reasons you shouldn’t switch to NoSQL databases and why an SQL database might serve you better. We will also look at why SQL should be your preferred choice for your next web application or database learning path.
Why not SQL?
As you know, a lot of people and companies have come up with reasons to migrate from using SQL supported databases to other forms of data storage for one reason or another.
Most of the time, these reasons make sense for certain use cases. But, those same use cases could be solved just as well with a proper SQL Database that uses a correctly normalized schema and proper indexes.
In this Google research, engineers at Google highlighted potential problems with databases that don’t use SQL.
Let’s look at some of them:
Schemaless vs Structure
When data becomes enormous (or is exploding), managing it becomes a concern. Since NoSQL databases don’t have a defined schema, it’s somewhat easier to use this as the storage method because it can be scaled really quickly and new data formats won’t affect the already existing structure.
From afar, the features are superb and can play a major role in development speed. But considering that more members can be added to a team and data can be stored for a long time, this creates a few headaches.
Since no defined structure exists, developers have to make choices on how things can be stored in the database. For instance, what keys would store certain data? It’s easy to imagine a variety of representations of “birthday,” with each developer choosing his or her own representation as a key when adding a user’s birthday to an entry. A team of developers might imagine almost anything: “bday,” “b-day,” “birthday”.
This level of inconsistency among developers and naming conventions is trouble waiting to happen and can be mitigated by avoiding NoSQL solutions. The NoSQL structure offers no support to limit this problem because that would mean reimagining the schema. It doesn’t want to harsh your mellow. A schema would get in the way.
SQL handles this because it forces developers to predefine the structure of their database. It also enforces the data type ahead of time so it knows what to expect. No matter the team everyone already knows how the engine will work regardless of the particular product. You also have far fewer choices and they are both mature AND proven.
The chances of you making poor choices are much lower with a SQL database.
NoSQL databases are rapidly gaining popularity because it tries to solve the scalability problem of SQL databases. It’s not based on Schema and it stores data in documents and JSON format, which makes it easy to scale and interact with any web client.
It’s loosely coupled and makes a lot of things easier, especially when you don’t have your whole design planned out already. SQL is very structured and requires a lot of planning and time — both of which are expensive commodities. Let’s compare scalability between the two.
Scalability can be broken down into:
- Read scaling = handle higher volumes of read operations
- Write scaling = handle higher volumes of write operations
ACID-compliant databases (like traditional SQL databases) can scale reads. They are not inherently less efficient than NoSQL databases because the (possible) performance bottlenecks are introduced by things many popular NoSQL lacks (like joins and where restrictions) which you can opt not to use. Clustered SQL RDBMSs can scale reads by introducing additional nodes in the cluster.
There are constraints to how far read operations can be scaled, but these are imposed by the difficulty of scaling up writes as you introduce more nodes into the cluster. Write scaling is where things get hairy. There are various constraints imposed by the ACID principle which you do not see in eventually-consistent (BASE) architectures:
- Atomicity means that transactions must complete or fail, so a lot of bookkeeping must be done behind the scenes.
- Consistency constraints mean that all nodes in the cluster must be identical. If you write to one node, this write must be copied to all other nodes before returning a response to the client. This makes a traditional RDBMS cluster hard to scale.
- Durability constraints mean that, in order to never lose a write, you ensure that before a response is returned to the client the write has been flushed to disk.
To scale up write operations or the number of nodes in a cluster beyond a certain point you have to relax some of the ACID requirements. Dropping atomicity lets you shorten the duration for which it locks tables (sets of data).
- MongoDB or CouchDB. Dropping consistency lets you scale up writes across cluster nodes.
- Riak, Cassandra. Dropping durability lets you respond to write commands without flushing to disk. Examples: Memcache, Redis. NoSQL databases follow the BASE model instead of the ACID model. They give up the A, C, and/or D requirements, and in return, they improve scalability. Some, like Cassandra, let you opt into ACID’s guarantees when you need them.
However, not all NoSQL databases are more scalable all the time. Before settling for NoSQL databases as a storage engine first ensure it’s the best use case because of the risks NoSQL databases face with their data.
Most times you don’t want to retrieve all the data in a column. You want to perform simple operations on the dataset and get the result instead of the data itself.
For instance, you want the sum of a single column. SQL users can execute a query with the SUM operation and send one — just one — number back to you. WIth NoSQL databases such isn’t possible. You have to first get all of the data then do the addition yourself. The addition isn’t the problem because it takes about the same time to add up the numbers on any machine.
However, shipping the data around is slow, and the bandwidth required to ship all that data can be expensive. There are few extras in NoSQL databases. If you want to do anything besides store and retrieve data, you will probably do it yourself. Most times, you will do it on a different machine with a complete copy of the data.
The real problem is that it can often be useful to do all of the computation on the machine holding the data because shipping the data takes time and resources. SQL databases solved these problems with things like stored procedures and other inbuilt functions.
More reasons you should use a SQL database
As the name implies Structured Query Language is “structured” and follows a certain pattern to perform its operations. This has its advantages.
- Because of its structure, a lot of planning has to be done ahead of time which will ultimately result in a properly structured system. Any new developer joining the team doesn’t have to think about structure because they have already been defined therefore making a contribution to the system easier and faster.
- Most SQL databases are table-based. This makes relational SQL databases a better option for applications that require multi-row transactions — such as an accounting system — or for legacy systems that were built for a relational structure.
- Because of its structure migrating databases becomes easier and less complex than databases that do not have a defined structure.
SQL is the most popular approach for interacting with databases. It powers many of the established databases like MYSQL, PostgreSQL etc and has been used by most of the web biggest companies, this means that there are a huge community, extensive testing, and more stability.
Most SQL clients, like MySQL, are available for all major platforms, including Linux, Windows, Mac, BSD, and Solaris. It also has connectors to languages like Node.js, Ruby, C#, C++, Java, Perl, Python, and PHP, meaning it’s not limited to SQL query language.
SQL is more secure compared to other forms of data storage. Security features come out of the box with some SQL clients, while others require the use of third-party software to add a layer of security. Because of its massive community, legacy systems and high tech companies that rely on the infrastructure a lot of effort is being made to ensure more security and efficiency for SQL systems.
In reality, SQL is a programming language used for interacting with databases. Because its a programming language it can be programmed to perform certain functions automatically based on events. Popular SQL clients like MySQL have inbuilt support for this through functions like stored procedures/Routines, indexes, triggers etc. This reduces the amount of code a developer has to write to access certain features, and since this inbuilt functions exist on the server any programming language that supports SQL can query it and it would still work the same way.
Strong industry demand
Most organizations need someone with SQL knowledge. According to data online, nearly 20,000 such jobs are advertised monthly, and the median salary for a position that requires SQL knowledge is about $84,000, as of 2018.
And positions span across developers, in-demand skills like database administrator, data analyst, and data scientists are also needed. There plenty of resources out there to learn SQL, probably more than ever. It’s a nice bonus that it’s typically easier for beginners to learn SQL than it is for them to pick up other programming languages.
In this article, we looked at why people are switching from SQL databases and the common misconception around it. We looked at several reasons an SQL database will be a great choice. Ultimately before you settle for a particular database you should consider the points above and then decide which works best for you.
Plug: LogRocket, a DVR for web apps
LogRocket is a frontend logging tool that lets you replay problems as if they happened in your own browser. Instead of guessing why errors happen, or asking users for screenshots and log dumps, LogRocket lets you replay the session to quickly understand what went wrong. It works perfectly with any app, regardless of framework, and has plugins to log additional context from Redux, Vuex, and @ngrx/store.