SQL or/with Nosql

Question

I am designing a mobile app with basic functionality such as sign-up, Auth, posts, real time messages etc. I think for this kind of work Nosql should work better. But over a long period of time I want to introduce payment transaction as well. Due to eventual consistency NoSql should be a problem here (I may be wrong). So can I use SQL only for transactions with userid field from Nosql user data. Is it a good practice to mix both.

I know I could use SQL database from start but I think NoSql will be a good fit based on factors such as scaling, distributied database etc.

PS: I am also a software developer but worked mostly on Machine Learning and computer vision. Didn't work much on databases.

Answer 1

So can I use SQL only for transactions with userid field from Nosql user data. Is it a good practice to mix both.

I don't believe it's a good idea to couple two different database systems, especially different types of database systems (RDBMS and NoSQL) so closely together for related use cases. It'll prove difficult to manage, and be hard to maintain consistency across the database systems (especially with a NoSQL system that is likely not ACID compliant).

I think NoSql will be a good fit based on factors such as scaling, distributied database etc.

RDMBS can scale just as well as NoSQL databases. Typically, from a hardware perspective, RDBMS scale vertically whereas NoSQL databases scale horizontally, but there's no inherent benefit of one methodology over the other. There's also RDBMS that scale horizontally, just as well as NoSQL database systems being able to scale vertically.

From a software perspective, the performance of scaling as the data grows depends mostly on how one architects their database and negligibly on the actual database system chosen, since most modern database systems can handle trillions of records over petabytes of data in a single logical storage unit (e.g. a table or document).

I am designing a mobile app with basic functionality such as sign-up, Auth, posts, real time messages etc.

When to choose a NoSQL database system over a RDBMS depends on a number of factors, none of which I would say primarily involve performance. But one of the main factors that is useful for determining when to choose which type of database system is based on the fact of if you have a well defined schema.

NoSQL databases are great for storing data that doesn't have a concrete schema, are liable to change in structure at free will / outside of your control, or where the rate at which the structure will change exceeds your tolerance as a developer to maintain the structure in the data layer. It sounds like you have some level of a concrete schema, so a RDBMS could work for your use cases, but you should also think about if the structure of your models are liable to change frequently or outside of your control, and if you as a developer don't want to manage maintaining those changes in the data layer.

In short, NoSQL databases are great for deferring when you need to concretely define the structure of your data until it is consumed in the application layer. But they are not necessarily inherently better at scaling, distributing, or performance. Unfortunately there's a lot of misconception on the internet regarding that.

Answer 2

NoSQL covers a very broad range of solutions that generally only have "does not use SQL as a default query language" in common. For example, MongoDB has had support for multi-document ACID transactions since MongoDB 4.0 (2018) and distributed transactions in sharded clusters since MongoDB 4.2 (2019). The default is strong consistency (all reads & writes from primaries) with eventual consistency as an optional read preference. MongoDB also supports optional JSON schema validation, which is a more flexible approach compared to a fixed schema catalog.

However, since you are designing a mobile application I think the more important requirements would come from your mobile use case and the platforms you plan to support. If your use case includes offline data usage and local queries (perhaps with sync to cloud), there is a broader solution space to consider than just database server solutions. Each mobile O/S generally has a native persistence solution (eg Core Data in iOS or Room in Android) and there are cross-platform libraries like SQLite (which Core Data and Room both happen to build on).

There are also cross-platform solutions like Realm (acquired by MongoDB in 2019) that provide on-device persistence (including schema, transactions, and relationships) with optional bi-directional cloud sync. Realm SDKs are open source (Apache 2.0 license) and can be used as a high performance local persistence solution, or as part of an offline-first approach for mobile applications syncing data with a central data store in the cloud. Realm Sync uses MongoDB Atlas for cloud storage, and you also have the option of interacting with data via MongoDB drivers and tools (for example, for analytics or machine learning use cases). The cloud side of Realm includes Application Services like user authentication providers, functions, triggers, GraphQL, and static file hosting.

I would try to narrow down your actual and anticipated requirements including on-device and off-device services before choosing a data persistence approach.

Answer 3

you might want to take a look at SchemafreeSQL. it creates a schemaless object store withing a sql database. You continue to use the db as you normally would and access the Nosql features SFSQL provides as needed.