One of the most fundamental choices to make when developing an application is whether to use a SQL or NoSQL database to store the data. Conventional SQL (i.e. relational) databases are the product of decades of technology evolution, good practice, and real-world stress testing. They are designed for reliable transactions and ad hoc queries, the staples of line of business applications. But they also come burdened with restrictions—such as rigid schema—that make them less suitable for other kinds of apps.

NoSQL databases arose in response to those limitations. NoSQL systems store and manage data in ways that allow for high operational speed and great flexibility on the part of the developers. Many were developed by companies like Google, Amazon, Yahoo, and Facebook that sought better ways to store content or process data for massive websites. Unlike SQL databases, many NoSQL databases can be scaled horizontally across hundreds or thousands of servers.

The advantages of NoSQL don’t come without a cost, though. NoSQL systems don’t generally provide the same level of data consistency as SQL databases. In fact, while SQL databases have traditionally sacrificed performance and scalability for the behind reliable transactions, NoSQL databases have largely ditched those ACID guarantees for speed and scalability.

In short, SQL and NoSQL databases offer different tradeoffs. While they may compete in the context of a specific project—as in, which to choose for this application or that application—they are complementary in the bigger picture. Each is suited to different use cases. The decision is not so much a case of either/or as it is a question of which tool is right for the job.

), the basics remain the same.

By contrast, each NoSQL database tends to have its own syntax for querying and managing the data. CouchDB, for instance, uses requests in the form of JSON, sent via HTTP, to or from its database. MongoDB sends JSON objects over a binary protocol, by way of a command-line interface or a language library.

Some NoSQL products can use SQL-like syntax to work with data, but only to a limited extent. For example, Apache Cassandra, a column store database, has its own SQL-like language, the . Some of the CQL syntax is straight out of the SQL playbook, like the SELECT or INSERT keywords. But there is no way to perform a JOIN or subquery in Cassandra, and thus the related keywords don’t exist in CQL.

Shared-nothing architecture

A design choice common to NoSQL systems is a “shared-nothing” architecture. In a shared-nothing design, each server node in the cluster operates independently of every other node. The system doesn’t have to get consensus from every single node to return a piece of data to a client. Queries are fast because they can be returned from whichever node is closest or most convenient.

Another advantage of shared-nothing is resiliency and scale-out. Scaling out the cluster is as easy as spinning up new nodes in the cluster and waiting for them to sync with the others. If a NoSQL node goes down, the other servers in the cluster will continue to chug along. All the data remains available, even if fewer nodes are available to serve requests.

Note that a shared-nothing design is not exclusive to NoSQL databases. Many conventional SQL systems can be set up in a shared-nothing fashion, although that typically involves sacrificing consistency across the cluster for performance.

that are reminiscent of those found in conventional SQL.

Eventual consistency

NoSQL systems trade strong or immediate consistency for better availability and performance. Conventional databases ensure that operations are atomic (all parts of a transaction succeed, or none do), consistent (all users have the same view of the data), isolated (transactions don’t compete), and durable (once completed they will survive a server failure).

These four properties, collectively referred to as ACID, are handled differently in most NoSQL systems. Instead of immediate consistency across the cluster, you have eventual consistency, due to the time needed to copy updates to other nodes in the cluster. Data inserted into the cluster is eventually available everywhere, but you can’t guarantee when.

Transaction semantics, which in a SQL system guarantee that all steps in a transaction (e.g. executing a sale and reducing inventory) are either completed or rolled back, aren’t typically available in NoSQL. For any system where there needs to be a “single source of truth,” such as a bank, the NoSQL approach won’t work well. You don’t want your bank balance to be different depending on which ATM you go to; you want it to be reported as the same thing everywhere.

Some NoSQL databases have partial mechanisms for working around this. For instance, MongoDB has consistency guarantees for individual operations, but not for the database as a whole. Microsoft Azure CosmosDB lets you , so you can choose the behavior that fits your use case. But with NoSQL, expect eventual consistency as the default behavior.

NoSQL lock-in

Most NoSQL systems are conceptually similar, but are implemented very differently. Each tends to have its own metaphors and mechanisms for how data is queried and managed.

One side effect of that is a potentially high degree of coupling between the application logic and the database. This isn’t so bad if you pick a NoSQL system and stick with it, but it can become a stumbling block if you change systems down the road.

If you migrate from, say, MongoDB to CouchDB (or vice versa), you must do more than just migrate data. You also must navigate the differences in data access and programmatic metaphors—in other words, you must rewrite the parts of your application that access the database.

NoSQL skills

Another downside to NoSQL is the relative lack of expertise. Where the market for conventional SQL talent is still quite large, the market for NoSQL skills is nascent.

For reference, reports that as of the end of 2017, the volume of job listings for conventional SQL databases—MySQL, Microsoft SQL Server, Oracle Database, and so on—remains higher over the last three years than the volume of jobs for MongoDB, Couchbase, and Cassandra. The demand for NoSQL expertise is growing, but it’s still a fraction of the market for conventional SQL.

Merging SQL and NoSQL

We can expect some of the differences between SQL and NoSQL systems to disappear over time. Already many SQL databases now accept JSON documents as a native data type, and can perform queries against that data. Some even have native ways to impose constraints on JSON data, so that it is handled with the same rigors as conventional row-and-column data.

On the flip side, NoSQL databases are not only adding SQL-like query languages, but other capabilities of traditional SQL databases. For instance, at least two document databases – MarkLogic and RavenDB – promise to be ACID compliant.

Here and there are signs that future generations of databases will straddle the paradigms and offer both NoSQL and SQL functionality. Microsoft’s , for instance, uses a set of primitives under the hood to interchangeably reproduce the behaviors of both kinds of systems. is a SQL database that combines strong consistency with the horizontal scalability of NoSQL systems.

Still, pure SQL and pure NoSQL systems will have their place for many years to come. Look to NoSQL for fast, highly scalable access to free-form data. This comes with a few costs, like consistency of reads and other safeguards common to SQL databases. But for many applications, those safeguards may well be worth trading for what NoSQL offers.