SQL vs NoSQL


SQL vs NoSQL CRUD Syntax

Creating, reading updating and deleting data is the basis of all database systems. In essence —

  • SQL is a lightweight declarative language. It’s deceptively powerful, and has become an international standard, although most systems implement subtly different syntaxes.
  • NoSQL databases use JavaScripty-looking queries with JSON-like arguments! Basic operations are simple, but nested JSON can become increasingly convoluted for more complex queries.

A quick comparison:

SQL NoSQL
insert a new book record
INSERT INTO book (
  `ISBN`, `title`, `author`
)
VALUES (
  '9780992461256', 
  'Full Stack JavaScript', 
  'Colin Ihrig & Adam Bretz'
);
db.book.insert({
  ISBN: "9780992461256",
  title: "Full Stack JavaScript",
  author: "Colin Ihrig & Adam Bretz"
});
update a book record
UPDATE book
SET price = 19.99
WHERE ISBN = '9780992461256'
db.book.update(
  { ISBN: '9780992461256' },
  { $set: { price: 19.99 } }
);
return all book titles over $10
SELECT title FROM book
WHERE price > 10;
db.book.find(
  { price: { >: 10 } },
  { _id: 0, title: 1 }
);
The second JSON object is known as a projection: it sets which fields are returned (_id is returned by default so it needs to be unset).

count the number of SitePoint books
SELECT COUNT(1) FROM book
WHERE publisher_id = 'SP001';
db.book.count({
  "publisher.name": "SitePoint"
});
This presumes denormalized documents are used.

return the number of book format types
SELECT format, COUNT(1) AS `total`
FROM book
GROUP BY format;
db.book.aggregate([
  { $group:
    { 
      _id: "$format", 
      total: { $sum: 1 } 
    }
  }
]);
This is known as aggregation: a new set of documents is computed from an original set.

delete all SitePoint books
DELETE FROM book
WHERE publisher_id = 'SP001';
Alternatively, it’s possible to delete the publisher record and have this cascade to associated book records if foreign keys are specified appropriately.

db.book.remove({
  "publisher.name": "SitePoint"
});

SQL vs NoSQL Performance

Perhaps the most controversial comparison, NoSQL is regularly quoted as being faster than SQL. This isn’t surprising; NoSQL’s simpler denormalized store allows you to retrieve all information about a specific item in a single request. There’s no need for related JOINs or complex SQL queries.

That said, your project design and data requirements will have most impact. A well-designed SQL database will almost certainly perform better than a badly designed NoSQL equivalent and vice versa.

SQL vs NoSQL Scaling

As your data grows, you may find it necessary to distribute the load among multiple servers. This can be tricky for SQL-based systems. How do you allocate related data? Clustering is possibly the simplest option; multiple servers access the same central store — but even this has challenges.

NoSQL’s simpler data models can make the process easier, and many have been built with scaling functionality from the start. That is a generalization, so seek expert advice if you encounter this situation.

SQL vs NoSQL Practicalities

Finally, let’s consider security and system problems. The most popular NoSQL databases have been around a few years; they are more likely to exhibit issues than more mature SQL products. beenreported, but most boil down to a single issue: knowledge.

Developers and sysadmins have less experience with newer database systems, so mistakes are made. Opting for NoSQL because it feels fresher, or because you want to avoid schema design inevitably, leads to problems later.

SQL vs NoSQL Summary

SQL and NoSQL databases do the same thing in different ways. It’s possible choose one option and switch to another later, but a little planning can save time and money.

Projects where SQL is ideal:

  • logical related discrete data requirements which can be identified up-front
  • data integrity is essential
  • standards-based proven technology with good developer experience and support.

Projects where NoSQL is ideal:

  • unrelated, indeterminate or evolving data requirements
  • simpler or looser project objectives, able to start coding immediately
  • speed and scalability is imperative.

In the case of our book store, an SQL database appears the most practical option — especially when we introduce ecommerce facilities requiring robust transaction support. In the next article, we’ll discuss further project scenarios, and determine whether an SQL or NoSQL database would be the best solution.