Blog
Back

SQL vs. NoSQL: Choosing the Right Database Architecture for Your Mobile App

79
#Development 12 july 2024
  • Nick Gribkov

    Flutter-developer

Hi there! I'm Nick Gribkov, a Flutter developer at imaga. There are countless articles online about the differences between SQL and NoSQL, but most are heavy on theory and light on practical advice. In this article, I’ll take a different approach. I'll clearly explain which system to choose and when. Spoiler alert: it all depends on the project and the client's ambitions. While I'll focus on mobile development, the core principles apply to web development as well.

Fundamentals

Photo

You've probably seen dozens of articles describing the differences between SQL and NoSQL. While my goal is to avoid repeating what's already been said, I still need to cover the basics. Don’t worry, I'll keep it brief.

If you're not here for the basics, scroll down for great charts comparing the performance of SQL and NoSQL on different mobile platforms. At the end, you'll find clear tips on how to make your choice. Feel free to skip the theory section.
SQL (Structured Query Language) is a query language used to work with relational databases. These databases have a rigid structure in the form of tables, where all information is stored in columns and rows. This structured approach is one of the main advantages of SQL databases.
SQL is also reliable because it has a large community and a long history. Most of the issues that SQL encountered in the past are probably resolved by now. And if something does go wrong, there's always someone to ask for help. Therefore, developers can focus on writing custom events like selectors, joins, and so on.
NoSQL databases are non-relational, meaning they lack a rigid structure. To understand this better, let's use an example. Imagine we have a class called "users." Each user has attributes like ID, name, surname, year of birth, and so on. Instead of storing all these data in a structured table format, they're stored in a single file. All interactions occur solely with this file. You can no longer retrieve just one field, such as ID; you work with the entire class as a whole.

Advantages and Disadvantages of SQL

Pros Cons Заголовок 7
SQL is suitable for more complex tasks. For instance, if you have 10 thousand receipts and you need to find out how much you spent on milk throughout the year, having a structured table is crucial. In this table, there would be a specific field for "Milk", and SQL can gather data specifically based on this field. On the other hand, to work with NoSQL in this scenario, you would need to export the entire database, scan through all the receipts, isolate the "Milk" field, and only then begin working with it. This process is inconvenient, which makes SQL the better choice here. Complexity. Writing code in SQL requires skillful handling. First and foremost, we must write high-performance code capable of quickly processing any queries. Secondly, our approach must be decomposed. This means we end up writing a significant amount of code, which is unavoidable—otherwise, nothing will work as intended. In some databases that directly interact with SQL, such as SQLite, developers often have to write entire selectors and joins—direct SQL database queries. This is challenging because it requires adhering to a specific structure.
Transaction security. This concept ensures that data transactions from point A to point B are successfully completed. When a user clicks "Save," the information goes to local storage. This path is what we call a transaction. It's considered secure if we are confident that the data will be saved accurately. NoSQL databases can encounter issues with this. If a user closes the application during a transaction, we may end up with a corrupted package. The data may either not be saved at all or saved incompletely. In this regard, SQL is more reliable. With properly written code, corrupted packages are nearly impossible. Scalability challenges. Let's say we have two tables — two different classes that we plan to store. To store a third class, we would have to start over: create a new table for it and write events specific to it that will trigger. In SQL, there isn't a unified space where we create an object and then work with it.

Advantages and Disadvantages of NoSQL

Pros Cons Заголовок 7
NoSQL databases are fast and lightweight. They often include code generators that streamline work. Essentially, we just write annotations, and these annotations partially generate the code for us. This means we don't have to write wrapper classes for the classes we want to store; instead, we get them through code generation. NoSQL lacks a clear set of actions. Each NoSQL database vendor writing libraries for a particular technology relies on their own vision rather than widely accepted standards. Consequently, some of their decisions could render the database unreliable. For instance, in NoSQL, data is sometimes stored in encrypted archives. This makes it possible for a virus from a phone to gain access and steal users' personal information.
In NoSQL, adding new data is straightforward. Once we've set up the storage infrastructure, we can insert one class, then another, and so on, without much hassle. All we need to do is write an adapter, but even that can often be handled by a code generator. Limited transaction support. It takes considerable effort to make a NoSQL database as secure as SQL, as mentioned earlier. For instance, in Flutter, you can utilize separate isolates, essentially separate threads, to execute these transactions. The main thread handles the application's logic, UI rendering, and object rendering, while transactions occur on a parallel request. This ensures that transactions are fast and reliable.

Usage in Flutter, Kotlin, and Swift

For different platforms, various types of SQL databases are suitable:
  • Flutter: Supports SQLite — a library based on SQL but optimized for mobile applications.
  • Kotlin: Room is commonly used — an interface for working with SQL databases.
  • Swift: Typically uses CoreData — a framework for managing SQL databases.
For NoSQL, across all three platforms, common solutions are frequently used. This includes cloud platforms like Firebase or MongoDB. In Kotlin and Swift, MongoDB is represented by the Realm library, while in Flutter, it's the library with the same name.
Let me elaborate on Isar — a cool NoSQL database for Flutter. It was developed by the team behind the Hive library. Essentially, Isar is an enhanced version of Hive, and it significantly outperforms other non-relational databases.
  1. Isar is very fast, which partly resolves the transactional security issue. Users simply can't do anything that would interrupt a transaction.
  2. Isar features static typing, which enhances its reliability.
  3. Thanks to its reactivity and internal architecture, Isar scales well, handling both asynchronous and synchronous queries.
  4. Isar offers a wide range of features, including full-text search capabilities.
    Imagine you have a user class with fields like ID, name, surname, and birth year, totaling 10,000 users in the database. With a typical NoSQL database, you'd need to fetch all users, iterate through the array, and filter out the ones you need. In Isar, however, you don't need to perform all these operations. You can filter based on a specific field, similar to SQL. This allows us to sort the database with a single query, fetch only the required users, and return them efficiently.
  5. Isar includes a useful feature called "links." They allow you to establish relationships between one object and another.

Application Performance

Talking about the features of SQL and NoSQL in a vacuum can be challenging. So, I conducted a small experiment to demonstrate how the mentioned libraries work. On each platform, we'll test two databases: one relational and one non-relational. We'll compare them based on 50,000 queries.
In this case, we're focused on four main actions:
  • Creating a query
  • Reading objects
  • Updating data
  • Deleting data
Here’s what we found:
Photo

The fewer milliseconds it takes to process a query, the better. The chart shows that Room creates queries faster than Realm, but the difference is so minor that users likely won't notice it. On Flutter, Isar is much faster, while on Swift, the relational CoreData performs best. These measurements were taken with identical data volumes and types; results may vary with different inputs.
However, these numbers highlight the difference between SQL and NoSQL. NoSQL is often faster and more efficient.

So how do you choose between SQL and NoSQL?

It all depends on your business needs and goals.
  • If you are creating a small app for a niche audience or an MVP to test a hypothesis, you can confidently choose a NoSQL solution. These databases are fast, easier to work with, and easily scalable. And if your project takes off, you can quickly transition fr om NoSQL to SQL.
  • If you're working on a medium-sized project, I would recommend SQL. However, if you really want to, NoSQL is also an option. But there are some nuances: choose non-relational databases only if you have well-established events, a well-implemented database setup, and experience working with NoSQL.
  • If you're developing an enterprise solution, I recommend sticking with SQL. Imagine a scenario in a large marketplace wh ere 10,000 users paid for purchases but didn't receive their goods due to a data transaction error. The cost of this error is too high—hence, SQL is the way to go.
  • Nick Gribkov

    Flutter-developer

Blog

0 / 0