Web
Article

SQL vs NoSQL: The Differences

By Craig Buckler

SQL vs NoSQL

SQL (Structured Query Language) databases have been a primary data storage mechanism for more than four decades. Usage exploded in the late 1990s with the rise of web applications and open-source options such as MySQL, PostgreSQL and SQLite.

NoSQL databases have existed since the 1960s, but have been recently gaining traction with popular options such as MongoDB, CouchDB, Redis and Apache Cassandra.

You’ll find many tutorials explaining how to use a particular flavor of SQL or NoSQL, but few discuss why you should choose one in preference to the other. I hope to fill that gap. In this article, we’ll cover the fundamental differences. In a later follow-up article, we’ll look at typical scenarios and determine the optimal choice.

Most examples apply to the popular MySQL SQL and MongoDB NoSQL database systems. Other SQL/NoSQL databases are similar, but there will be minor differences in features and syntax.

The SQL vs NoSQL Holy War

Before we go further, let’s dispel a number of myths …

MYTH: NoSQL supersedes SQL
That would be like saying boats were superseded by cars because they’re a newer technology. SQL and NoSQL do the same thing: store data. They take different approaches, which may help or hinder your project. Despite feeling newer and grabbing recent headlines, NoSQL is not a replacement for SQL — it’s an alternative.

MYTH: NoSQL is better / worse than SQL
Some projects are better suited to using an SQL database. Some are better suited to NoSQL. Some could use either interchangeably. This article could never be a SitePoint Smackdown, because you cannot apply the same blanket assumptions everywhere.

MYTH: SQL vs NoSQL is a clear distinction
This is not necessarily true. Some SQL databases are adopting NoSQL features and vice versa. The choices are likely to become increasingly blurred, and NewSQL hybrid databases could provide some interesting options in the future.

MYTH: the language/framework determines the database
We’ve grown accustom to technology stacks, such as —

  • LAMP: Linux, Apache, MySQL (SQL), PHP
  • MEAN: MongoDB (NoSQL), Express, Angular, Node.js
  • .NET, IIS and SQL Server
  • Java, Apache and Oracle.

There are practical, historical and commercial reasons why these stacks evolved — but don’t presume they are rules. You can use a MongoDB NoSQL database in your PHP or .NET project. You can connect to MySQL or SQL Server in Node.js. You may not find as many tutorials and resources, but your requirements should determine the database type — not the language.

(That said, don’t make life purposely difficult for yourself! Choosing an unusual technology combination or a mix of SQL and NoSQL is possible, but you’ll find it tougher to find support and employ experienced developers.)

With that in mind, let’s look at the primary differences …

SQL Tables vs NoSQL Documents

SQL databases provide a store of related data tables. For example, if you run an online book store, book information can be added to a table named book:

ISBN title author format price
9780992461225 JavaScript: Novice to Ninja Darren Jones ebook 29.00
9780994182654 Jump Start Git Shaumik Daityari ebook 29.00

Every row is a different book record. The design is rigid; you cannot use the same table to store different information or insert a string where a number is expected.

NoSQL databases store JSON-like field-value pair documents, e.g.

{
  ISBN: 9780992461225,
  title: "JavaScript: Novice to Ninja",
  author: "Darren Jones",
  format: "ebook",
  price: 29.00
}

Similar documents can be stored in a collection, which is analogous to an SQL table. However, you can store any data you like in any document; the NoSQL database won’t complain. For example:


{
  ISBN: 9780992461225,
  title: "JavaScript: Novice to Ninja",
  author: "Darren Jones",
  year: 2014,
  format: "ebook",
  price: 29.00,
  description: "Learn JavaScript from scratch!",
  rating: "5/5",
  review: [
    { name: "A Reader", text: "The best JavaScript book I've ever read." },
    { name: "JS Expert", text: "Recommended to novice and expert developers alike." }
  ]
}

SQL tables create a strict data template, so it’s difficult to make mistakes. NoSQL is more flexible and forgiving, but being able to store any data anywhere can lead to consistency issues.

SQL Schema vs NoSQL Schemaless

In an SQL database, it’s impossible to add data until you define tables and field types in what’s referred to as a schema. The schema optionally contains other information, such as —

  • primary keys — unique identifiers such as the ISBN which apply to a single record
  • indexes — commonly queried fields indexed to aid quick searching
  • relationships — logical links between data fields
  • functionality such as triggers and stored procedures.

Your data schema must be designed and implemented before any business logic can be developed to manipulate data. It’s possible to make updates later, but large changes can be complicated.

In a NoSQL database, data can be added anywhere, at any time. There’s no need to specify a document design or even a collection up-front. For example, in MongoDB the following statement will create a new document in a new book collection if it’s not been previously created:

db.book.insert(
  ISBN: 9780994182654,
  title: "Jump Start Git",
  author: "Shaumik Daityari",
  format: "ebook",
  price: 29.00
);

(MongoDB will automatically add a unique _id value to each document in a collection. You may still want to define indexes, but that can be done later if necessary.)

A NoSQL database may be more suited to projects where the initial data requirements are difficult to ascertain. That said, don’t mistake difficulty for laziness: neglecting to design a good data store at project commencement will lead to problems later.

SQL Normalization vs NoSQL Denormalization

Presume we want to add publisher information to our book store database. A single publisher could offer more than one title so, in an SQL database, we create a new publisher table:

id name country email
SP001 SitePoint Australia feedback@sitepoint.com

We can then add a publisher_id field to our book table, which references records by publisher.id:

ISBN title author format price publisher_id
9780992461225 JavaScript: Novice to Ninja Darren Jones ebook 29.00 SP001
9780994182654 Jump Start Git Shaumik Daityari ebook 29.00 SP001

This minimizes data redundancy; we’re not repeating the publisher information for every book — only the reference to it. This technique is known as normalization, and has practical benefits. We can update a single publisher without changing book data.

We can use normalization techniques in NoSQL. Documents in the book collection —

{
  ISBN: 9780992461225,
  title: "JavaScript: Novice to Ninja",
  author: "Darren Jones",
  format: "ebook",
  price: 29.00,
  publisher_id: "SP001"
}

— reference a document in a publisher collection:

{
  id: "SP001"
  name: "SitePoint",
  country: "Australia",
  email: "feedback@sitepoint.com"
}

However, this is not always practical, for reasons that will become evident below. We may opt to denormalize our document and repeat publisher information for every book:

{
  ISBN: 9780992461225,
  title: "JavaScript: Novice to Ninja",
  author: "Darren Jones",
  format: "ebook",
  price: 29.00,
  publisher: {
    name: "SitePoint",
    country: "Australia",
    email: "feedback@sitepoint.com"
  }
}

This leads to faster queries, but updating the publisher information in multiple records will be significantly slower.

SQL Relational JOIN vs NoSQL

SQL queries offer a powerful JOIN clause. We can obtain related data in multiple tables using a single SQL statement. For example:

SELECT book.title, book.author, publisher.name
FROM book
LEFT JOIN book.publisher_id ON publisher.id;

This returns all book titles, authors and associated publisher names (presuming one has been set).

NoSQL has no equivalent of JOIN, and this can shock those with SQL experience. If we used normalized collections as described above, we would need to fetch all book documents, retrieve all associated publisher documents, and manually link the two in our program logic. This is one reason denormalization is often essential.

SQL vs NoSQL Data Integrity

Most SQL databases allow you to enforce data integrity rules using foreign key constraints (unless you’re still using the older, defunct MyISAM storage engine in MySQL). Our book store could —

  • ensure all books have a valid publisher_id code that matches one entry in the publisher table, and
  • not permit publishers to be removed if one or more books are assigned to them.

The schema enforces these rules for the database to follow. It’s impossible for developers or users to add, edit or remove records, which could result in invalid data or orphan records.

The same data integrity options are not available in NoSQL databases; you can store what you want regardless of any other documents. Ideally, a single document will be the sole source of all information about an item.

SQL vs NoSQL Transactions

In SQL databases, two or more updates can be executed in a transaction — an all-or-nothing wrapper that guarantees success or failure. For example, presume our book store contained order and stock tables. When a book is ordered, we add a record to the order table and decrement the stock count in the stock table. If we execute those two updates individually, one could succeed and the other fail — thus leaving our figures out of sync. Placing the same updates within a transaction ensures either both succeed or both fail.

In a NoSQL database, modification of a single document is atomic. In other words, if you’re updating three values within a document, either all three are updated successfully or it remains unchanged. However, there’s no transaction equivalent for updates to multiple documents. There are transaction-like options, but, at the time of writing, these must be manually processed in your code.

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. Many problems have been reported, 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.

More:
  • http://ngumii.com Paul Ngumii

    Interesting read, I have been trying to wrap my head around NoSQL, this helps a great deal. Thanks.

    • Craig Buckler

      Thanks Paul – glad it helped. NoSQL is generally simpler to comprehend but can be confusing at first because it’s difficult to undo the SQL way of thinking. You naturally want to normalize data and, if you’re doing it a lot, NoSQL possibly isn’t the right choice for that project.

      • grh

        “You naturally want to normalize data and, if you’re doing it a lot, NoSQL possibly isn’t the right choice for that project.”

        This has been my experience as well. If I start a NoSQL project, and then shortly after find myself manually incorporating many features that are already present in a SQL database system, then I generally figure out pretty quickly that maybe I should just switch to a SQL database instead.

        Looking forward to the next article!

  • http://www.zacksdomain.com/ Zack Wallace

    It’s always interesting when I read an SQL vs. NoSQL article, because usually at some point you read that one benefit of SQL is “data integrity is essential”.

    My first thought becomes, “so with NoSQL you don’t have data integrity?” What person in their right mind would want to build a database where you can’t trust in data integrity? I hope my bank doesn’t use NoSQL!

    But seriously, I’ve yet to find good examples of where the use of NoSQL shines. What types of systems DON’T have a lot of relational data?? At the least, most data storage needs tend to be related to account records and users.
    What are some good examples of data where you don’t need integrity, nor any relational connections with other data? I don’t know any offhand.

    • Craig Buckler

      I would hope no bank uses NoSQL! It could be done but it’d be more hassle than it’s worth.

      The follow-up article (coming soon) discusses specific projects where NoSQL could be useful. The main point is that SQL databases can validate your data and updates but that makes them very strict. NoSQL databases are more flexible but won’t validate data – you can add checks in the program logic but the database itself won’t prevent any mistakes.

      • http://careersreport.com Julie Franke

        Allow me@ to show you a fantastic ways to earn a lot of extra dollars by finishing basic tasks from your house for few short hours a day — See more info by visiting >MY_DISQUS_PROFILE

      • http://careersreport.com Jennifer Sullivan

        Check~ out a great way how you can earn a lot of extra $ by finishing basic jobs online from home for few hrs /daily VISIT MY-DISQUS-PROFILE to find out more

      • http://careersreport.com Cynthia Pullman

        Allow me to_ show you a fantastic ways to earn a lot of extra money by finishing basic tasks from your house for few short hours a day — See more info by visiting >MY___{DISQUS}___ID::

      • Carl

        It’s my understanding that NoSQL such as MongoDB are usually useful as document storage mechanisms.. i.e. If you have a lot of docs that may or may not be related… such as if you look at Google.. They definitely have a need for NoSQL .. They store a gazillion records coming from a gazillion different sources… that are all mostly not related. .. That’s why they use the https://cloud.google.com/bigtable/ it was designed for just that purpose.

        I really believe the main benefits of NoSQL are the ways that data are indexed.. In order for NoSQL Data to be relavent and useful… it must be indexed in such a way that when a user searches for data, .. the relavent “KEYS” of that data are returned “EXTREMELY” fast.

        That is the main problem and actually the solution that was needed to manage this massive amount of data.

        With relational data, if you search 25 billion records without proper indexing.. your search is really crawling…

        So really the “Key/Value” pairing is the main advantage of NoSQL …. If you are familiar with SPHINX that is basically what NoSQL is doing.

        We have a definition of a Key.. that points to values… Now it can be a combination of values to a key.. or one single value to a key.

        The main point is the way data is indexed and retrieved….

    • Mr B

      How about a document archive where you store different types of documents? That is a perfect example of where a NoSQL DB would shine.

      We currently have one defined in Oracle that is kind designed with a structure that reminds me of a tuple table where each record has an ID, a keyword and a value. All the IDs of a document are the same but each keyword represents the different “columns” and the values are the data.

      While this could have been built using a relational design this is more flexible in our architecture. However, I am looking into a redesign for this process using a NoSQL DB.

      • Craig Buckler

        Sounds reasonable. NoSQL could be ideal for a document store or a CMS if you require some flexibility (who doesn’t?!) That said, you can use a SQL DB to emulate a NoSQL DB like you’ve done. The reverse option isn’t necessarily possible…

    • Brant

      MongoDB has the same data integrity as your network/disk when updating a single collection, same as any other DB. What it doesn’t have is transaction integrity (or transactions at all) to allow updating multiple collections at once. It also requires checking in the client to ensure FK relation like integrity, altho it can indeed be handled.

      If your use case is simple, as many apps and web services are, and does not have to make updates to multiple collections at once as a bank would need to do to transfer money from one account balance to another, then you will be fine.

      When designing an app using mongo, the data layer should assume that integrity can only be maintained when updating 1 collection at a time. I think most apps can safely make this assumption.

      If for example you have a User collection referencing an Address collection, but your UI allows you to update both user name and address in 1 api call, it could happen that the address will not be updated but the user will. If you realize this, you can design a system where that is not a serious integrity issue (what harm is it if the name is updated but the address is not, as the user will see an error for the partially unsuccessful address update, if the UI is designed well). But in a case where this would be harmful, you can denormalize the data due to MongoDB being able to have nested data structures … by combining the Address as a subkey of User collection instead of a separate collection, you can now update both atomically, and maintain integrity of the username and the address fields.

      An app where single collection level data integrity is enough: a blog, stats/monitoring systems, photo sharing apps, game servers, document management systems.
      Apps where you need transaction integrity: financial focused applications, complex inventory systems, apps like maybe a shared family tree (ancestry, etc) with highly relational data.

      You can have highly relational data in Mongo, it is just less efficient to query/update many related collections, but many apps often only update 1 related model at a time, or can be better represented as nested data structures instead of relations.

      Denormalization of non repeated records, such as the Address example above, can be a good thing in mongo, and reduce the use of relations significantly. Since it is schemaless, you can even denormalize polymorphic collections just as easily, where in SQL, you might have several tables representing the different types that can be in the collection, and another table specifically to join those tables into a single list for the reference.

      One other easy to solve Mongo integrity issue is schema integrity, you don’t want multiple versions of schemas in a collection if you can help it. This is solved in the same way it is for SQL, by having schema models in app code, and creating migrations when schemas need to change.

  • http://www.zacksdomain.com/ Zack Wallace

    It’s always interesting when I read an SQL vs. NoSQL article, because usually at some point you read that one benefit of SQL is “data integrity is essential”.

    My first thought becomes, “so with NoSQL you don’t have data integrity?” What person in their right mind would want to build a database where you can’t trust in data integrity? I hope my bank doesn’t use NoSQL!

    But seriously, I’ve yet to find good examples of where the use of NoSQL shines. What types of systems DON’T have a lot of relational data?? At the least, most data storage needs tend to be related to account records and users.
    What are some good examples of data where you don’t need integrity, nor any relational connections with other data? I don’t know any offhand.

  • peratik@gmail.com

    LAMP: Linux, Apache, MySQL (SQL), PHP
    MEAN: MongoDB (NoSQL), Express, Angular, Node.js
    .NET, IIS and SQL Server
    Java, Apache and Oracle.

    What about phyton and ruby on rails?

    • Craig Buckler

      What about C++, Swift, Clojure, Perl, COBOL, Ada, Fortran and QBasic?!

      I was listing a handful of web development stacks to point out that there’s no need to choose a specific combination because it’s popular. It wasn’t intended to be a comprehensive list and you should be pleased that neither Python or Ruby have particular alignments with specific databases.

  • Wutikrai Pornchai

    Good article showing a big picture of its different and NoSQL is not a candidate but an option. At this point I think in make use of NoSQL is to use it as an sub system of SQL. Let SQL validate all input the put it to NoSQL, and vice versa.

    • Craig Buckler

      That’s a possibility. Or you could use a SQL database as a NoSQL data-store and have the benefits (and problems?) of both.

  • http://junaidpv.in Junaid P V

    Thanks for the great article!

    There is a typo in last part of article. “:rojects where SQL is ideal:” -> “Projects where SQL is ideal:”

    • Ralph Mason

      Oops! Thanks. :-)

  • Hector Sehaswana

    After this article I clearly see how I can use both on the same project. Thank you very much.

  • Craig Buckler

    I mentioned some SQL databases were implementing NoSQL features. Any SQL database can store JSON data but PostgreSQL has taken it further by allowing JSONB (more efficient binary JSON) which can be directly queried.

    It’s an interesting development and could be useful for some projects. Designing your database schema could become more complex, though.

  • http://gatesvp.blogspot.com Gaëtan Voyer-Perrault

    You are clearly confusing _”NoSQL”_ with _”Document-Oriented database”_. Or even more specifically with MongoDB.

    For example:

    > _NoSQL databases store JSON-like field-value pair documents…_

    This is totally wrong:
    – Redis stores just accepts a bunch of bytes. Though it supports HashSet which can kind of mimic a single-level JSON document.
    – Riak can actually store & receive data in Protocol Buffers format. It has pluggable storage engines many of which are not just storing RAW JSON bytes.
    – Cassandra kind of seems to accept JSON, but it’s actually storing SSTables underneath.

    > There’s no need to specify a document design or even a collection up-front.

    This really trivializes the reality. The reality here is that you _can’t_ define these in (many) Document-Oriented Databases. This actually means that a simple typo can result in hordes of data ending up in a “collection” lacking indexes or one that is untracked by the ops team.

    > Projects where SQL is ideal:…

    A DB like MongoDB is actually _really bad_ for “…indeterminate or evolving data requirements.”

    If your “evolving” data requirements for you re-structure parts of your data this is _really_ hard with MongoDB. MongoDB forces you to de-normalize early and those evolving requirements can force you to re-de-normalize the data. But because your data has no integrity this process is ridiculously difficult.

    Likewise when data is “indeterminate”, MongoDB will accept whatever is provided. This means that multiple developers, apps, subsystems can actually destroy each other’s data by accidentally re-using field names or using a full `Save()` instead of specific `Set()` commands.

    One of the first things you have to do with MongoDB is write a very clear data layer and ensure that _everyone_ uses it and ensure that it version correctly. Otherwise you run a very real risk of silently destroying data.

    At the end of the day, normalized databases are _easier_ to evolve than databases that have been “pre-optimized” via denormlization. This actually makes SQL a _better_ solution in these case not a worse one.

    > speed and scalability is imperative

    “Document-oriented” databases are not inherently faster. In fact, MongoDB is needlessly slow in many cases where SQL is particularly fast. What MongoDB allows you to do is to structure your data to pre-optimize certain queries over other queries.

    Generally, this means that MongoDB can out-perform SQL on loading up single documents from the DB. But it dramatically underperforms SQL on aggregation and ad-hoc queries and frankly anything without an index.

    It’s understood that MongoDB provides scalability at the expense of features. So let’s not play up features it doesn’t have.

    • reinhardt1053

      Well said.

      • http://SalaryNet30.com Latosha Burns

        `I’m` finally` getting 94`Dollars“` an hr,….It’s time to take some action` and you can join it too.It is easy way to get rich`.Three weeks from now you will wish you have started today….

        +++++++++++++>>> Vis!t My Pr0f1le:)“`

        ~gfg

    • Alius Umbra

      I’ve always wondered about these things. So, what’s the point of document-oriented databases, then?

      • http://gatesvp.blogspot.com Gaëtan Voyer-Perrault

        SQL doesn’t scale infinitely. It works really well on a single machine or possibly some small cluster that can be made to _look_ like a single machine. But there’s a point where you can no longer keep scaling SQL and maintaining consistency in the system (CAP theorem). When that happens, you typically start storing your data in document-sized chunks. At that point, things like easy sharding and replication become important and that’s where tools like MongoDB come in.

        You also start using other tools to do things you take for granted in SQL. If you have a commerce system with user purchases spread across 50 servers, you can’t just use a SUM() function with a GROUP BY, there’s a whole process for gathering data from across machines. At that scale you use tools like Hadoop.

        Scaling out a system typically involves putting data in diverse but appropriate data storage engines in order to answer the queries you need answered.

        • Alius Umbra

          Huh. Sounds like a whole lotta work. So far in my 15 years in IT software development, I am yet to encounter a scenario where we needed more than 2-3 SQL Servers with beefy hardware. So what you described sounds definitely above my pay grade. For now :)

          • AsGiants Astros

            It’s really not that complicated (though it can definitely seem so at first!). Most businesses *think* they need noSql/big data scale, but in reality are fine with “2-3 SQL servers with beefy hardware”. Also, luckily, Hadoop/Impala/Hive/SparkSql have SQL-like libraries for Mysql/aggregate queries, so instead of using a Mysql SUM() function, you can just use the SparkSql SUM() function.

  • Ralph Mason

    Part 2 of this series is now live! http://www.sitepoint.com/sql-vs-nosql-choose/

  • Adrian Voicu

    Why do you say that MyISAM is “defunct”? Did I miss something?

    • Craig Buckler

      InnoDB has been the default engine since MySQL 5.5.5. “Defunct” is perhaps a little strong – you can still use MyISAM – but I wouldn’t recommend it. It doesn’t support foreign keys or transactions and even locks the whole table when writing a single record.

      In general, MyISAM will only offer a small performance benefit on very large read-only data stores.

  • Bruno Seixas

    Simple and directly to the point. Great read :)

  • Craig Buckler

    Thanks – that’s interesting. As the article points out, there’s not a clear distinction between SQL and NoSQL and the boundaries are increasingly blurred with both offering features from the other.

    • Gerald Sangudi

      It would be great if the article was updated to point out that some NoSQL databases do in fact provide JOINs, and that some NoSQL databases provide a SQL syntax instead of “JavaScripty-looking queries” that can become “increasingly convoluted”. Not every reader will get to all the comments :) Thanks.

  • zaid26

    thinking out aloud. nosql will be a good choice in systems where you have some sort of month end procedure for example. at end of month data sets where the data is not going to change can be extracted from the sql db and moved into a no sql document. so you could have a single document for product x which will have

    all related info in a single document. great for a sales history or analytics. would speed up the process of retrieving data history without bogging down the sql db with doing multi joins and groupings etc.

  • http://rafaelstz.github.io Rafael Corrêa Gomes ♛

    Cool!

  • http://xanview.com Roman Gaufman

    Just to comment on your SQL Relational JOIN vs NoSQL section – while it is true, it is also reasonably easily subvertible. In SQL you would do:

    SELECT book.title, book.author, publisher.name FROM book LEFT JOIN book.publisher_id ON publisher.id;

    In Mongo (in my case I’m using the Mongoid ORM), I would do:

    Book.where(publisher: publisher).only(:title, :author).include(:publisher, only: :name)

    This would be slow by default, true, but if you add “belongs_to :publisher, index: true” in your book model file and “has many :books, index: true” in your publisher mode file, it will tell Mongo to create indexes on publisher_id and on book_id, on both sides. This makes complex adhoc “joins” super fast without denormalization.

  • Surya Teja Tanuku

    I am new to MongoDB, I knew basic view commands of Mongo, using in Windows CMD Shell, but I want to know how to use in real time application, suppose if we take small example i,e. Registration or anything else.. in MySQL, we were create table and insert the data(schema), In MongoDB(noschema), if users will insert data in the form, then how it was saving in mongo.
    can anyone please elaborate..

  • Jacob Johnston

    I found this extremely helpful. I’d consider myself an adept novice developer trying to decide whether to use SQL Server or MongoDB for a project I feel NoSQL would really shine in. As a data analyst for several years I have a lot of exposure to RDB and love tools like Excel with VBA but have always shied away from SQL and of course terrible but popular data analyst tools like Access with its inherent inflexibility.

    I digress… I’ve periodically researched the differences, pros/cons, etc… over the past 8 months or so and really wish I’d come across this article sooner. Not only does it solidify my impression that MongoDB would really shine for a sort of pseudo-CMS open-source educational tool built with an Angular 2 front end (not sure whether to stick with what I know: asp.net with C#, or learn something new: node.js for the back end) but it elucidates the prospects of spooling up a SQL server on the side for transactions requiring data integrity such as financial transactions and banking information!

    If anyone could point to some resources on pros/cons and methodologies for combining SQL with MongoDB or other NoSQL options that would sure be helpful. I feel that a flexible document-style storage system would be ideal for all the user-generated content being constantly edited, but I’m concerned about some of the other data getting messy.

  • Ahmad Mobaraki

    Thank you Craig Buckler! very helpful.

Recommended

Learn Coding Online
Learn Web Development

Start learning web development and design for free with SitePoint Premium!

Instant Website Review

Use Woorank to analyze and optimize your website to improve your website to improve your ranking!

Run a review to see how your site can improve across 70+ metrics!

Get the latest in Front-end, once a week, for free.