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

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:

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:
  • 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.

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.

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.

1 Like