Should I use SQL DB + ASP.NET API or firebase/appwrite for my flutter ERP project?

I would recommend keeping your .NET and SQL backend, considering the high investment and also considering using Firebase via C#. Firebase is just a database. No need to throw out working code.

Also, you will have to migrate your data from flat SQL table structures to basically to giant JSON docs in Firebase. Be absolutely certain you want to lose the strengths of SQL before you jump to the more trendy JSON doc storage models like Firebase, MongoDB and others.

Personally, I don't think you should put all your eggs in one basket. Try both, and see which works for what use cases.

As a contractor, I've always been forced to put 3d data into 2d SQL-scapes and used to dream of magical solutions like Firebase/Mongo to get me out of SQL Hell. I've since learned that SQL is just fine for transactional, flat data like invoices, orders, loan schedules, logging, etc.

For something requiring nesting, JSON dbs are good at those things, but pretty awful at querying efficiently while maintaining potentially duplicated copies of docs.

If you really need hefty, nested persistence in the form of JSON and simply cannot model everything in SQL (and why would you?), then try something like Neo4J graph database, or something more comprehensive like SurrealDB.

Finally, it sounds like you learned Flutter. Don't base your backend decisions on Flutter's need to juggle JSON like so many other popular frameworks like React. What I mean by "JSON juggling" is, you will have to not only maintain your persistance 'state' in your DB(s) of choice, but also have to maintain JSON 'state' in the UI. I would avoid this at all costs (htmx.org) and just have my API return bits of HTML, or, at worst, use either templaters like nunjucks or something that stays mostly out of the way, but can still be used on mobile like Svelte. Don't be a slave to a UI framework, if you are (many of them create more problems). UI should be both a representation of your persisted state and a remote control for the user, NOT a JSON manager, in my opinion.

What I like to do with C# is write little Services that do specialized work. In your case, I'd write one Firebase service to rule them all, then inject it into whatever API or UI you desire (and throw that sucker up in your Nuget registry of choice). Write once, cry once.

I hope this helps in your future endeavors.

Regards,

Nicholas Preston
https://peerlist.io/nickpreston
My stack: https://github.com/nickpreston24/trash-stack