(2023) Under what app-requirements should @capacitor-community/sqlite be preferred over IonicStorage

[folsze]

I want the answers to this question be a new up to date (2023) reference point for people deciding whether they should use @capacitor-community/sqlite or IonicStorage for their mobile app.

this post from 2021 states that @capacitor-community/sqlite usually overkill and a pain to work with:

SQLite and Capacitor 3

First thing I would recommend is finding three friends. Order them in terms of transigence: let’s say Alice is the most easygoing, Barbara is a little harder to convince, and Carol is downright stubborn as a mule. Now have a conversation with each of them in turn, trying to convince them that your life would be better off with SQLite in this app. Only if you can get Carol to agree should you proceed. SQLite is a PITA to work with. Ionic has done a great job of making it appear seamless whether…

I have already had many problems making it very hard for me to even set @capacitor-community/sqlite up in my project, since I am rather a beginner when it comes to working with external libraries (I did not manage to integrate it yet).

I will give the requirements of my app:

• I would like my app to be available offline, thus I would prefer not using an API for data-access if possible.
• I have three entites, A, B, C:
  EntityA. contains many:
  EntityB. contains many:
  EntityC.
• I would have one data-query where I would update a single property of one EntityC
• I would have one data-query, where I would get a SQLite.random() entity of C (which is inside one B, which is inside one A), whose number Property ‘X’ is smaller than ‘max’
• eventually users would be able to create their own instances of B
• In a final version I would have ~200 Entities of A, ~5 of B, ~50 of ~C, (~50.000 C in total)
• I would like my app to work on the web too, but I think that’s still achievable with SQLite according to: GitHub - capacitor-community/sqlite: Community plugin for native & electron SQLite databases

Now I think one main advantage of @capacitor-community/sqlite might be, that you can inspect your data easier, maybe with some database inspector or who knows what. Currently with IonicStorage I am storing A-B pairs as arrays of C, so in the end I would have to search through 200*5 = 1000 rows of arrays when inspecting the indexedDB browser console if stuff goes wrong. I think it could be much easier inspectable with @capacitor-community/sqlite and this is probably the main advantage of it for me, but correct me if I am wrong.

I think when coming up with new queries in the future or getting some primitive analysis-data it would also be more convenient to do it with SQLite.

Another advantage would be, that the queries happen faster, since right now I will store 1000 arrays and select and update a whole array of O(200) elements, which surely takes longer than updating a single property. But I think this can actually be 100% ignored since updating an index in an array of 200 Objects or a single property both will not take very long for the UX anyways (speed is not ‘crucial’ for me). But correct me if I am wrong about this assumption!

I think the classic problem with IonicStorage is always, that accessing by a key is just kind of weird & error-prone. For example to store & update one C, I would have to first get the array with a key “Akey + Bkey” and then update the array index of C and then put that updated array back into IonicStorage.

But maybe I am wrong and you guys think that while it might be weird doing things like accessing something with “Akey + Bkey” and then modifying one array index and sending it back, however it still pays off since direct SQLite access is even more of a ‘pain’ /overkill with Ionic.

Interested to hear some thoughts of people who know all this well.

[mhartington]

I’m typically a “simpler is better” in most cases here. If you’re just dealing with simple values (no objects, non-relational data) then yes, sqlite is probably not needed.

I typically just use localstorage directly if I need anything.

But if you have some relational data, grab sqlite. It’s powerful enough to cover a lot of use cases, and should feel familiar enough if you’ve used any sql-based APIs.

[folsze]

Thank you. Looking at the example I provided and the answer you wrote, I will definetly go for @capacitor-community/sqlite now. I was skeptic at first since I heard terrible things in the post mentioned above, but if you think it should feel familiar enough after some time then I am confident I’ll learn how to use it in my app now, already know a great deal of SQL actually.