SQL vs NoSQL No smoke of war!

Disclaimer: This article is translated from SQL vs NoSQL the Differences, please specify the source if you want to reprint.

SQL (Structured Query Language) database has been a major data storage mechanism for more than 40 years. With the advent of Web applications and open source items like MySQL, PostgreSQL, and SQLite, SQL has greatly increased the usage.

NoSQL databases have emerged in the the 1960s, but have recently been widely watched for MongoDB, Couchdb,redis, and Apache Cassandra.

You will find that many tutorials will explain how to use SQL or NoSQL based on your interests, but seldom discuss why you should choose it. I hope to fill this void. In this article, we'll cover the basic differences. In a later article, we will look at some typical scenarios and determine the best choice.

Most of the examples are applicable to the current popular MySQL SQL and MongoDB NoSQL database systems. Other Sql/nosql databases are similar, but have subtle differences and grammatical features.

The jihad of SQL and NoSQL

Before we begin, correct some of the so-called myths ...

• Myth 1:nosql will replace SQL

This is like saying that the ship will be replaced by a car because it is a new technology. SQL and NoSQL do the same thing: data storage. They take a different approach, which may help to back up the group or hinder your project. Despite the sensation of technical updates and often in recent headlines, NoSQL is not an alternative to SQL-it's a choice.

• Myth 2:nosql better or worse than SQL

Some projects are better suited to using SQL databases, some are better for nosql, and some can be used interchangeably. This article will not be SitePoint smackdown, because you cannot apply the same generalized assumptions in all respects.

• Myth 3:sql and NoSQL worlds apart

This is not necessarily a fact. Some SQL databases are characterized by NoSQL and vice versa. The choice may become more and more blurred, and the Newsql hybrid database may provide some interesting choices in the future.

• Myth 4: Language/framework determines what database to use

We're used to the technology heap, for example--

• 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 to explain the development of these stacks-but they cannot be thought of as rules. You can use MongoDB NoSQL databases in your PHP or. NET projects. You can connect to MySQL or SQL Server in node. js. You may not find many tutorials and resources, but your needs determine the type of database-not the so-called language.

(There is a saying, do not let life have a purpose to embarrass themselves!) Choosing an unusual combination of technologies or SQL and NoSQL combinations is possible, but the challenge is to find support and hire experienced developers.

With this idea, let's take a look at the main differences.

SQL Tables vs NoSQL documents

SQL database provides storage of related data tables. For example, if you have an online bookstore, the information about the book will be added to the table of the books:

Each row is a different record. The design is rigid; You cannot use the same table to store different information, or enter characters in a number format.

NoSQL databases store field values in JSON format for documents such as:

{isbn:9780992461225,title: "Javascript:novice to Ninja", Author: "Darren Jones", Format: "ebook", price:29.00}

　　

Similar documents can be stored in a collection, similar to a SQL table. However you can store any data in any document, and NoSQL databases will never complain, for example:

9780992461225to 29.00} 

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

SQL Mode vs NoSQL modeless

In a SQL database, it is not possible to add data unless you have defined table and field formats in the specified schema. The pattern can also contain other information, such as--
Primary Key-A unique identifier, such as ISBN, that applies to a single record.
Index-The field that is usually queried to help with fast-ripening searches.
Relationships-logical connections between data fields
Features-such as triggers and stored procedures

Your data schema must be designed and implemented before any business logic can be developed to process the data. When you are done, you can move on to some updates, but not big changes.

In a NoSQL database, data can be added anytime, anywhere. There is no need to develop a document design, or even a collection of front ends. For example, in MongoDB, the following statement creates a new document in the new book collection if the document was not previously created:

Db.book. Insert  978099418265429.00);

(MongoDB automatically adds a unique _id value to the document within each collection.) You may still want to define the index, which you can do later if you want. ）

If a project's initial data requirements are difficult to determine, NoSQL databases may be more appropriate. In other words, don't make trouble for idleness: Ignoring the importance of designing a suitable database in a project will cause a lot of trouble later on.

SQL Normalization vs NoSQL anti-normalization

Suppose we want to add publisher information to the bookstore database. A single publisher can provide multiple titles, and in a SQL database, we create a new publisher table:

We can then add publisher_id to the Book table, which is the publisher.id reference.

This minimizes the redundancy of the data; we don't have to repeat the publisher information for each book-just index it. This technique can be called normalization and has practical benefits. We only update a single publisher without changing the entire book data.
In NoSQL, we can also use normalization techniques. Documents in the book Collection--

9780992461225to29.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 feasible, and the reasons are obvious below. We may choose to reverse-normalize our documentation and repeat the publisher information for each book:

9780992461225to29.00, Publisher: {name: "SitePoint",    Country: "Australia",    email: "Feedback@sitepoint. com"}}

This can speed up the query, but updating the publisher information in multiple records can be significantly slower.

SQL Relational Connection vs NoSQL

SQL queries provide a powerful join clause. We can use a single SQL statement to get related data from different tables. For example:
SELECT Book.title, Book.author, Publisher.name
From book
Left JOIN book.publisher_id on publisher.id;
This will return all titles, authors, and associated publisher names.

NoSQL has no equivalent join, and having the experience of SQL may be surprising. If we use the normalization set above, we will need to get all the book documents, retrieve all the relevant publisher documents, and manually connect the two in the program logic. This is one of the reasons why anti-normalization is often necessary.

SQL VS NoSQL Data integrity

Most SQL databases allow you to use FOREIGN KEY constraints to enforce data integrity (unless you're still using the old, MyISAM storage engine that doesn't already exist in MySQL). Our bookstore CAN--

• Make sure that all the books have a valid publisher_id code that has matching entries in the Publisher table
• If one or more books are assigned to them, the Publisher cannot be deleted.

Mode forces the database to follow these rules. Developers or users cannot add, edit, or remove data that could cause invalid or orphaned data

The same data integrity option is not available in a NoSQL database; You can store everything you want to store. Ideally, a single document will be the only source of all information for the project.

SQL VS NoSQL Transactions

In a SQL database, two or more updates can be executed in the same transaction-a all-or-nothing encapsulation guarantees success or failure. For example, suppose our bookstore contains the order and stock tables. When a book is ordered, we add a record to the order table and reduce the number of stocks in the stock table. If we perform these two updates separately, one might succeed and the other will fail-so our data will be out of sync. Placing the same update in one transaction can guarantee success or failure at the same time.

In a NoSQL database, the modification of a single document is tiny. Other words. If you are updating three values in a document, either three values are successful, or three values remain unchanged. However, there is no equivalent transaction to update the different documents. There are similar options, however, when writing these, it must be handled manually in your code.

SQL VS NoSQL CRUD syntax

creating, reading, updating, and deleting data are the foundation of all database systems. Essentially--

• SQL is a lightweight declarative language. This is very powerful and has become an internationalized standard, although most systems implement a slightly different syntax.
• NoSQL databases use similar javascripty-looking queries as JSON! The basic operation is simple, but nested JSON becomes more complicated for complex queries.

A simple comparison:

SQL VS NoSQL Performance

This is perhaps the most controversial comparison, and NoSQL is often thought to be faster than SQL. This is not surprising; NoSQL's simpler anti-normalization store allows you to use a single request to query for a specific item in all information. There is no need to use related JSON or complex SQL queries.

In other words, your project design and data requirements will have the greatest impact. A well-designed SQL database must be better than a poorly designed nosql performance, and vice versa.

SQL VS NoSQL Zoom

As your data grows, you may find it necessary to distribute the load before multiple servers. This can be tricky for SQL-based systems. How do you allocate the relevant data? Clustering may be the simplest choice; multiple servers access the same central storage-but even this can be a challenge.

A nosql simple data model can make this process much easier, and many start with scaling capabilities. This is a general introduction, so if you encounter this situation, please consult the expert opinion.

SQL VS NoSQL Utility

Finally, let's consider security and system issues. The most famous NoSQL databases exist for years; they are more prone to problems than more mature SQL products. Many of the problems have been exposed, but most of them boil down to one problem: knowledge.

Developers and system administrators have less experience with new database systems, so errors often occur. Choosing NoSQL is because it feels faster, or because you want to avoid architectural design and cause problems later.

Summary of SQL VS NoSQL

SQL and NoSQL databases do the same thing in different ways. Switching from one to the other is possible, but a little plan can save a lot of time and money.

Projects that are more appropriate for SQL:

• 可预先确定的逻辑关系离散数据的要求
• 数据完整性是必不可少的
• 有良好开发经验和支持的标准基础技术

More suitable for NoSQL projects:

• 不相关的、不确定或不断变化的数据要求
• 更加简单宽松的项目对象，可以立即编码
• 速度和扩展性是必要的

In the context of this bookstore example, the S-QL database is the most practical option-especially when we introduce e-commerce facilities that require strong transactional support.

Since we are the messaging service for the Kua-device platform, the speed and scale of data access is very high, and NoSQL is the most appropriate for us.

SQL vs NoSQL No smoke of war!