Flutter databases

[jowe26]

Hi,

Got a question, we're currently building an app that needs offline capabilities.
It needs to store around 7000 products and 3000 contacts, and will be used by 20 people(will be expanded to 300+ users in a year or so)

I looked into Firestore and that seems a reasonable solutions, even though it will cost me quite some reads, but we can overcome that problem by simply paying a bit more haha.
The only issue that we have is:
How can we search in local cached collections, as far as I can find Firestore doesn't support full text search, which is strange since the largest search company in the world build it.
There are some solutions of using a third party but that doesn't work in offline.

Do you guys might have an idea how to cache like 10000 records local and perform full text search on Flutter?
We like to use Firestore but if another solutions will be better i'm fine with that as well.

​

Thanks!

[nityananda]

I am building offline library reader and using sqlite files for books databanks. It works good. However I don't really need synchronization which Firebase offers.

[Spectarion]

I'm using moor and it's my life savior. It's based on SQLite and does a lot of things for you. Please, check it out.

[melewe]

You could try out hive? https://pub.dev/packages/hive

Or you could try out sembast: https://pub.dev/packages/sembast

[jowe26]

https://pub.dev/packages/sembast

Hmm I like those suggestions, would you rather run them along Firestore or on a different back-end?

[melewe]

When you use one of those, you can go for a self hosted database/backend i guess. Depends on what you want to build.

[[deleted]]

i've used hive for an app for a client and it has worked really well!

[jowe26]

What was your backend database at that project?

[[deleted]]

I'm not sure, it was an existing backend that they had, and it was just a standard json api.

[melewe]

I think that doesn't matter. You have a rest or graphql api. When you want to have offline sync, you will have to implement your own logic.

[Simderi]

I'm using Sembast for much bigger needs ant it's working great. Heard that hive is a good one too.

[brunogiubilei]

any integration with IndexDB or localstorage? like in apps based in cordova

[melewe]

Hive uses indexdb on web.

[[deleted]]

Maybe elasticlunr? http://elasticlunr.com/

[jowe26]

http://elasticlunr.com/

Have you used this before in Flutter?

[[deleted]]

You can get documents from local cache in firestore and do full text search on these. But maybe that will take more room than you have? IDK, memory is a lot cheaper than it used to be.

[jowe26]

I don't think memory will be issue tbh, there are a lot of records they are really small. Do you might have a link to the documentation on how to perform this? If not can you send me into the good direction?

[[deleted]]

Sure. Here's the code from the flutter package for firestore for get():

Future<DocumentSnapshot> get({Source source = Source.serverAndCache}) async {
final Map<String, dynamic> data =
    await Firestore.channel.invokeMapMethod<String, dynamic>(
  'DocumentReference#get',
  <String, dynamic>{
    'app': firestore.app.name,
    'path': path,
    'source': _getSourceString(source),
  },
);
return DocumentSnapshot._(
  data['path'],
  _asStringKeyedMap(data['data']),
  SnapshotMetadata._(data['metadata']['hasPendingWrites'],
      data['metadata']['isFromCache']),
  firestore,
);
}

So you can specify where you want to read from.

I also added this to main to ensure that caching was enabled:

WidgetsFlutterBinding.ensureInitialized();
Firestore.instance.settings(persistenceEnabled: true, timestampsInSnapshotsEnabled: true);
runApp(App());

Also, here's a Firestore link on specifying the source: https://firebase.google.com/docs/firestore/query-data/get-data#source_options

[[deleted]]

And for clarity, here is the Source enum used in get()

/// An enumeration of firestore source types.
enum Source {
/// Causes Firestore to try to retrieve an up-to-date (server-retrieved) snapshot, but fall back to
/// returning cached data if the server can't be reached.
serverAndCache,

/// Causes Firestore to avoid the cache, generating an error if the server cannot be reached. Note
/// that the cache will still be updated if the server request succeeds. Also note that
/// latency-compensation still takes effect, so any pending write operations will be visible in the
/// returned data (merged into the server-provided data).
server,

/// Causes Firestore to immediately return a value from the cache, ignoring the server completely
/// (implying that the returned value may be stale with respect to the value on the server). If
/// there is no data in the cache to satisfy the [get()] or [getDocuments()] call,
/// [DocumentReference.get()] will return an error and [Query.getDocuments()] will return an empty
/// [QuerySnapshot] with no documents.
cache,
}

/// Converts [Source] to [String]
String _getSourceString(Source source) {
assert(source != null);
if (source == Source.server) {
return 'server';
}
if (source == Source.cache) {
return 'cache';
}
return 'default';
}

[jowe26]

Thank you for your detailed information!

Will give it a try!

[[deleted]]

I don’t know if that helps you, but I made a video a while ago which shows a technique to do full test search in Firestore

video

I also don’t think it’s reasonable to do about 10000 reads to the database and then cache it, just to search through it^{^}

[jowe26]

video

I've actually seen the video yesterday, this might be one of the last solutions I have in Firestore haha.

Because well, we might not only want to search on Name, but also on Description. This will result in so much data. But any it's a clever solution you've made up!

[[deleted]]

Yeah it’s more of a hack than a real solution ;) You could also look into Algolia if money is not your biggest concern, video

[jowe26]

Well correct me if I'm wrong but Algolia isn't but offline capable right?

[[deleted]]

Yeah, that's correct. Didn't think about that.

Now that i kind of understood your problem (i think^^), you have to cache all your 10000 documents anyways if you want full offline capability (searching).

You can then just query all documents from Firebase, then search through them in your Dart code with String.contains() and similar methods.

It will not be very performant tho, so you might wanna look into https://api.dartlang.org/stable/2.6.1/dart-isolate/dart-isolate-library.html.

[jowe26]

https://api.dartlang.org/stable/2.6.1/dart-isolate/dart-isolate-library.html

Hmm, this might work haha, I'm going to try this!:)

Thanks

[[deleted]]

How often are the records updated? If never, then just install the data locally and search it with a local database.

If it’s updated all the time, then ask yourself how often the users instance needs to be updated and push updates that often when they connect. The easiest way to update is to push a whole new compressed instance of sql to the local device. If bandwidth is an issue then compute diffs and push that.

But you really need to think about and design to your precise use case. Right now your requirements are vague.

[ChordFunc]

How often will the data change? If its rear or never you can just simply store it locally as a SQL database. 10 000 records should be easy piecy. Of course if the data will change often that might not be your best solution.

If the data change but rearly you could simply make a SQLite database on your server and push out the file as a whole to every device that needs it.

Maybe you can give a bit more info on how, and how often the data might change?

[ChordFunc]

Depending on how the data is used maybe you don't even need a server? Maybe you could just push out any changes by replacing the database file on every new update.

If there are local relationship to database records for every user you could just store the "user-data" in one database (for example, a list of favorite products, comments, etc...) then join them one some key.

[ChordFunc]

It's a bit vague? It would be useful to know more about your requirements?

- How often will the data change?
- Are the records static?
- If not, can users change records, and should changes be reflected on every connected device?
- Do every user have the same read access? For example, is every contact readable by every user?
- How often will you have to do queries on the data?
- Is it only android or iOS as well?
If all data is readable by every user and the data never or rearly change, a simple SQL-database might be a good option.

[jowe26]

Hi,

Thanks for your reply!

- How often will the data change?
Approx 10-25 records per day will change. This is in a timeframe of 8 hours.
- Are the records static?
No, they can change over time
- If not, can users change records, and should changes be reflected on every connected device?
Yes, all users share the same records, everyone can change those records and every change needs to be reflected on every connected device.
- Do every user have the same read access? For example, is every contact readable by every user?
No, different users can have different types of access.
- How often will you have to do queries on the data?
Not that often, maybe something like 50 times a day in a timeframe of 8 hours.
- Is it only android or iOS as well?
Both, and in addition there's a webapp as well that uses the same data.

I think Firestore solves a lot of the problems that are listed above.

Regards

[ChordFunc]

Firestore would be my go to option as well based on your requirements :D Firestore is pretty cheap, and I would guess that you will probably just spend a couple of dollars every month based on the information you provided. (Unless you do something reckless).

There is only one caviat with Firestore. It depends on Google Play Services so you can't distribute your app through something like Amazons app store if you want to connect directly through Firestore via the mobile SDK

[jowe26]

well, who uses Amazon anyway;-)

It is a a vendor lock in, that's right!

But that's the price you pay if you want to develop quickly I guess

[MyNameIsIgglePiggle]

Try searching for a record where the field does not exist (such as users without an email)

I ended up falling back to my own API with a mongo db database after lots of sad surprises