Question: When to use Sembast vs SQFlite vs Both?

[Dokotela]

Hello!

I was hoping to get some advice about the database Flutter packages you maintain (which are all incredible, by the way). I'm building a small medical application for collecting patient data in low resource settings. All of the data will be in the FHIR specification (which is all json). Since it will be protected information it will need to be encrypted. My first reaction was that Sembast would work well. However, I did note that Sembast doesn't currently do indexing (not that it's currently important, as we aren't going to be having that many patients). But I was wondering in which situations would you recommend Sembast alone vs SQFlite vs Sembast_SQFlite? Would it be mostly based on if I need indexing, or if I think at some point we ARE going to have a large number of patients? Or are there other factors to consider? Thanks for any recommendations, and thanks again for all your work on the Flutter libraries!

[alextekartik]

Hi, not easy to give recommendation here as I personally always wonder which one to use too!. SQL and NoSQL fits different needs and tons of people have tons of opinions on this.

I use sembast for small databases (< 10K records), when I need web support or when I need real time changes. I use typically sembast_sqflite on flutter mobile/desktop instead of sembast io implementation (i use it mainly for unit tests) and sembast_web for flutter web. I even use sometimes idb_shim (idb_sqflite) when I need web support and don't want the db to be loaded in memory.

When I need heavy join between tables or bigger databases or strong database constraints and indexing without web support, I use sqflite.

My recommendation, if your database can fit easily a NoSQL or SQL design is to have a wrapper that could allow changing the implementation in the future (i.e. switching from sembast to sqflite and vice-versa or to any other database system that exists now or that could be created in the future - yes I hope that better/faster solutions would be created).

Indexing

Regarding indexing, I still plan to add indexing to sembast but I'm not sure about the best way though. sembast is design to never fail (no constraint, no exception), adding index with a unique constraint mean to choose some behavior (throw, ignore or replace) that I'm not sure about yet.

My initial plan is to only support basic index on a single field without constraint but at least to allow fast retrieval by secondary key (although with everything in memory and small databases it is not really an issue).

I would appreciate If you can share you needs on this.

[Dokotela]

Thank you so much for the reply!

I probably need to think a bit more about what I would actually need. The json specification (which is an external standard, so I don't really control any of the formatting) consists primarily of "resources". Each resource has some common fields, including a "resourceType" field and an ID field. Usually the ID fields are all Unique, but this is not required, and different types of resources could conceivably have the same ID.

I've created a basic database in SQFLite that has tables for each resourceType, but just has two columns, one for the ID, and then the resource is stored as a String in the second column. It worked fine, but at this stage since I'm not doing anything terribly complicated with it, I was thinking of maybe trying Sembast instead. As you said, perhaps I should just create the wrapper so I can use either depending on which one ends up fitting better. Thanks again for your insights, and if you have any other thoughts I'd love to hear them!

[Dokotela]

Apologies, I apparently hadn't looked deeply enough into it to realize that "stores" provide the functionality I was looking for. Looks like it's what I needed. Sembast_sqflite here I come. Thanks again!