marko devcic

  • github:
    deva666
  • email:
    madevcic {at} gmail.com

Firestore and SQLite

Posted on 3 June 2020

One of the main reasons why I use Firebase is productivity. You get a backend database with almost zero setup.
Plus, authentication with major providers, server-less functions, file storage, etc ... all with a generous free tier that can last easily until you start monetizing your app.

The downside is that you are sort of locked in using their NoSQL database. Almost everything you are used to with SQL can be done with Firestore, except Full Text Search.
Even if it supported it, the network roundtrip would bring additional overhead and it could never compete with the speed of SQLite FTS.
When I started working on Nutrinary one of the features I wanted is search as you type for foods. Its database has more than 300.000 foods and even though Firebase Firestore automatically indexes all database fields, text searching was slow.
Nowhere near the speed I wanted. First option I considered then was to have all the foods locally, in a SQLite database and use Firebase Firestore for everything else user related. Since foods and their nutritional information is read-only, this could work, but would complicate potential future updates because you have to distribute a new app version with new SQLite file when foods change. A Python script that dumped all the food data (with all nutritional information) from MySQL database showed that SQLite database is more than 200MB in size. On top of that all application and Flutter code would bring total app download size close to 300MB.
Not good. But I am interested in searching for foods by their names only, I don't need all other nutritional info locally. I just need a FTS table with two columns, ID and Name.
A couple of changes to the Python script showed that 300.000 food names are less than 50MB in size when stored in SQLite FTS table. This could work.

So the idea was then to have only food IDs and names locally, and all other information in Firestore. When a user searches for foods I just have to query the local SQLite database. That way I have blazing fast food searching by name and if a user selects a food to view its details I pull the rest of the information from the Firestore. This would also allow me to add new foods without having to submit new app version to App Store / Play Store.
I can store database version and food IDs for that version in Firestore and if Firestore and SQLite database versions don't match, read all missing food Ids and insert their food names to the local FTS table.

Firebase also has a Python SDK which made dumping all the food data from MySQL to Firestore easy. At first it went really slow. It took several hours to write 10.000 rows to Firestore.
After a little bit of research I found out that Firestore supports Batch Writes. There's a limit of 500 operations in a single batch. This increased the transfer of data noticeably and I had all the foods and their nutritional information in the Firestore database in a couple of days. Considering that +300.000 food rows each have on average additional 20 rows of nutritional information with them, this gives around 6 Million of rows transferred in total less than 24hours of uploading.

Now I have all the food data in the cloud without having to worry about app download size or having to submit new app version whenever I add new foods to the database.


Nutrinary links to App Store and Play Store