Code &JavaScript 19 Sep 2023 15:51:13
Ordbogit App Mid-Mortem
I am currently in the process of developing an offline cross-platform version of Oqaasileriffik’s Greenlandic Dictionaries, ideally as an app. The concept is really simple: Take the existing 150 MiB SQLite database and bundle that as an app.
A year ago, I tried to use .NET MAUI, but that quickly proved feature anemic and buggy. I also looked at React Native and a few others. But in all of those frameworks, I ran into the same limitation: There is no way to just use a bundled read-only SQLite database. It has to be copied from the bundled resources into the app storage space, meaning it would exist on the device twice, and that was unacceptable to me.
Three weeks ago I was reminded of the project, and this time I started from scratch and tried working at it from the Progressive Web App (PWA) angle. I figured it was worth a shot to take the existing website and just let users run that offline. But again there was the quirk of how to get that 150 MiB of data to the user and persist it once and only once, ideally in a way that’s not in RAM all the time.
First I tried Dexie.js, an IndexedDB wrapper. Unfortunately this proved much too slow. Preloading files and inserting all the data into the database took 6 minutes on a beefy desktop computer. Turns out inserting into IndexedDB is really slow, even when doing bulk inserts, and when you’re inserting 3.3 million rows there is no way to overcome that. Could I reorganize the data so it’s fewer rows and differently indexed? Maybe, but that’s an absolutely last resort.
Next I tried Roy Hashimoto’s wa-sqlite. This was much faster, bringing insertion time down to 2m30s, despite still using IndexedDB as storage. But 2m30s on desktop translated to over 10 minutes on a fast mobile device, and that’s not acceptable.
I finally relented and tried caching the raw SQLite database and loading that with sql.js. I really didn’t want to do this as it requires keeping the whole 150 MiB database in RAM. Well, the result is that the PWA preloads and initializes in under 20 seconds from localhost. Naturally this will be slower over the internet, but this makes it network-bound instead of CPU-bound. It’s the same amount of data to transfer either way, and I guess 150 MiB isn’t much in this day and age.
