Using SQL clause "NOT IN" for syncing DBs (pulling remote data that is not present locally)

[stefanorumi7]

First of all, let me say "thanks" for developing and maintaining this project. It is amazing!

Now lets get into my scenario:

• I would like to pull all the records of EntityA from the remote DB into my local DB to allow future offline workflow.
• It is a large number of them, and they have a lot of asociations, so I have to batch the request, or some associations will be missing.
• Before reaching this step, I created an instance locally of EntityA, so my local DB is not empty, but missing lot of records.
• The idea is to do this load optimistically, but not depend on it.

So my solution is:

In an unawaited async method request all the data of EntityA from the remote DB using OfflineFirstGetPolicy.awaitRemote`. It looks something like this:

  Future<void> pullRemoteData(String userId) async {
      int processedCount = 0;
      const pageSize = 5;
      bool hasMoreRecords = true;

      while (hasMoreRecords) {
        final query = Query(
          where: [Where('userId', value: userId)],
          limit: pageSize,
          offset: processedCount,
        );

        final remoteEntities = await BrickRepository().get<EntityA>(
          query: query,
          policy: OfflineFirstGetPolicy.awaitRemote,
        );

        if (remoteEntities.isEmpty) {
          hasMoreRecords = false;
          break;
        }

        processedCount += remoteEntities.length;

        if (remoteEntities.length < pageSize) {
          hasMoreRecords = false;
        }
      }
  }

Limitation i am facing:

• If I would like to periodically trigger this sync to ensure the localDB is up to date, this querying of the whole table would get to expensive.
• I would like to use a NOT IN SQL clause to query from the remote DB only records that I don't have locally.
• I cannot find a way to do this using Brick.

What I tried but didn't work

  Future<void> pullRemoteData(String userId) async {
      final localQuery = Query(where: [Where('userId', value: userId)]);
      final localEntities = await BrickRepository()
          .get<EntityA>(
            query: localQuery,
            policy: OfflineFirstGetPolicy.localOnly,
          );

      final localIds =
          localEntities.map((e) => e.id).toSet();

      int processedCount = 0;
      const pageSize = 5;
      bool hasMoreRecords = true;

      while (hasMoreRecords) {
        final query = Query(
          where:
              localIds.isNotEmpty
                  ? [
                    Where(
                      'id',
                      compare: Compare.doesNotContain,
                      value: localIds.toList(),
                    ),
                  ]
                  : [],
          limit: pageSize,
          offset: processedCount,
        );

        final remoteEntities = await BrickRepository().get<EntityA>(
          query: query,
          policy: OfflineFirstGetPolicy.awaitRemote,
        );

        if (remoteEntities.isEmpty) {
          hasMoreRecords = false;
          break;
        }

        processedCount += remoteEntities.length;

        if (remoteEntities.length < pageSize) {
          hasMoreRecords = false;
        }
      }
  }

Is there a way of doing the NOT IN clause? Or maybe an alternative flow to achieve the same result?

Note that it is not critical to the flow in my app, but I believe it is something more people could face.

[tshedor]

@stefanorumi7 if you're using Supabase, I don't know if PostgREST supports a NOT IN operator. If it does, could you please link the documenation?

Otherwise, I think what you're trying to do is already available using getBatched. Instead of a NOT IN, which would require querying all local IDs and submitting them, instead consider a fetch time key. This be stored and accessed in Dart as a DateTime, maybe using local storage. Your remote table should have an updated_at timestamp field that would be Query'd in Brick using a greaterThan for that value.

[stefanorumi7]

@tshedor thanks for the proposal, I think I can work with that without the usage of an IN condition!

It is impresive how hard it is to find documentation on this one.
I found some docs for IN (I was referring to NOT IN as a negation of that condition).

• Here i could find some samples on the official Postgres doc that use IN. But only samples, no proper documentation.
• Here is the Oracle definition in SQL. This is more proper.

[stefanorumi7]

As having a NOT IN condition would still be a useful condition, lets see if #602 goes through and add the posibility of negating it.

[tshedor]

@stefanorumi7 specifically, PostgREST needs to support/document this as that's the interface for querying Supabase data.

[stefanorumi7]

Sorry @tshedor I misunderstood. In the operators documentation I was able to find IN and NOT defined. Where NOT negates another operator, so the composition of them should work.

[tshedor]

@stefanorumi7 gotcha, I see it now. Is the NOT IN a better solution than using a fetch key?

[stefanorumi7]

I don't think so, using a fetch key looks better. But it could be a fallback in case we don't have an updated_at timestamp.
ATM I don't need any more the NOT IN operation, I changed my implementation to follow your suggestion. So feel free to close the issue, unless you consider it might be useful for other users 🙂

[tshedor]

@stefanorumi7 I think for now I'm going to close the issue because it's a bigger lift than what I have time for. PRs are open though, so if you'd like to add this operator similar to #602 it is functionality that would be useful within Brick.