I am having a mid size React Native project, I work with SQL databases but I am thinking about moving to NoSQL, I tried to learn NoSQL databases like Cloud Firestore but I faced issues at building the relation between models, also I noticed that the data is being duplicated in many places, should I stick with SQL databases like PostgreSQL or try again with Firebase.
Moving from a SQL database to a noSQL database is a really big deal, congrats 👏! In the current industry having handy knowledge of both is really important and I would recommend continuing to learn Firebase even if it feels a little unnatural.
When using noSQL databases you have consider the idea that the models you work with are individual documents. The data in the documents can be duplicated, re-referenced, and completely dissimilar to each other-the only thing differentiating two documents is an ID value. It's normal for data to be duplicated if you don't perform operations on the same document you already created. You stop unnecessary duplication by wrapping your document creation in a function that checks for a certain value (say if you don't want a username value to appear twice you would check if your collection of documents already has one with a username value and then throw an error).
Creating relations in a noSQL database would be done by referencing a different document in another collection (typically with the aforementioned ID value) and then going and finding that document in that collection. Say you want to find a user's posts. In the User document you would have an array of references to different documents in a Post collection, and then you would go to the Post collection and find each document that has a matching ID number to the ones stored in the User's references.
All in all, you have to realize you are working with two completely different ways of storing and relating data, similar to learning a new and completely different language. If you stick too it and don't try to force SQL onto noSQL then you will get the hang of it and it will be very rewarding.
Postgres is a very capable database and it sounds like your application is already designed with relationships in mind. Firestore is not relational and so that's probably why you're having a tough time - your application is just better suited by a relational database and that's ok.
I'd need to know more about your application and it's needs. What was the draw to Firebase? Just that it's a "noSQL" database? Don't let anyone tell you one is better than the other.
Interestingly enough, some people are building an open source project that provides an alternative to Firebase. At least some of its features anyway. It's called supabase. It uses Postgres too. It's a collection of a few modules, one of which is called "realtime" that provides realtime updates via websockets. I'd check that project out because it may provide some of the features you were interested in. Again, I'd need to know more about what your application needs and what you were drawn to with Firebase.
Also bear in mind the convenience of working with Postgres over Firebase. It's a lot more straightforward and you can find many GUI clients or use CLI to make queries and browse data. Yes, there are some for Firestore as well, but not as many. Also, don't forget about query insights if you're using GCP Cloud SQL or if you run your own database instance and install observability tools. Firebase may hide all of this, but you still may one day end up with a performance concern. Even though it's advertised as this infinitely scalable thing. That's not quite true, though it is very scalable, one of the trade offs is limited query features.
Then consider vendor lock-in. If you're really interested in a noSQL database or want a more straightforward one that doesn't include vendor lock-in and limited querying, check out something like MongoDB instead.
The reason of me considering something like firebase is the simplicity, sometimes I have big projects which I definitely use SQL database, but last time I built an application it was quite simple, I had to build an entire backend which is something I did not have to do if I used firebase, I think firebase could handle this kind of application without wasting a lot of time dealing with authentication and storing data , firebase uses NOSQL database, I could not deal with it correctly.
so overall, can I say it is a good idea to use firebase with small projects and Dotnet core as my backend for bigger projects??