This is like comparing oranges to apples, each serves a purpose, they are not exactly competing, though they are a bit. According to the other answer, many people adopt in tutorials to facilitate the explanation and end up directing people to do something wrong because of it. That’s why I always say you should be careful with content on the Internet, you don’t know if the person has adopted something to help you or help you have less work, or even if the person knows what they’re doing.
Firestore
It’s a cloud database, and it’s controlled by Google. You access the data if you have a connection, Google is ok, want to give you access to the data and you pay (yes, it is free to some extent, not for all situations, some people may regret too late to have adopted this technology). Perhaps in most cases it will not happen because in adopts without thinking well usually does not make applications that will require more than the basic. In addition it is a bunch of data to store documents, in many cases people use to store tabular and related data.
Sqlite
It is local, completely free, in every sense, does not depend on third parties, connection, has enough flexibility, is great for tabular and related data and generally makes the access via SQL. Obviously to exchange the same data from one device to another needs some extra mechanism because it is not centralized.
The choice
The choice goes a long way if you want something local or cloud, if you can handle the constraints of each, and especially if the type of data you are using are more documents or more related varied data.
There are several differences in the details, but it is not appropriate to speak of all here, because it would be an extensive list, the most important is the access model (relational X document) and access form, as well as how it is licensed and distributed.
My experience is that most people do things so simple with them that even if they choose the wrong option should not affect much, but for more important applications it becomes necessary a detailed study by someone experienced to make the most appropriate choice.
Compare Flutter with Android Studio becomes more complicated because there is compared bananas (a stack large technology) with spoons (an IDE).