Watch not working with IN and ANY Query.

[tejas-codiste]

I am using PowerSync in my Flutter app, and I need to listen for real-time updates in my local database. However, db.watch is not triggering updates when changes occur in the synced tables.

Code Implementation
This is how I'm using db.watch in my app:

var stream = db.watch(
        'SELECT * FROM events WHERE creator_user = ${FFAppState().user.userId} OR ${FFAppState().user.userId} = ANY(manager_ids) ORDER BY start_date ASC');
    eventsSubscription = stream.listen((data) {
      callback(data.map((json) => EventsRow(Map<String, dynamic>.from(json))).toList());
    });

The query works as expected in the PowerSync console but does not work inside the Flutter app.

Even when testing via the PowerSync diagnostics platform, the query does not return results.

There issue in IN and ANY Query.

Screenshots
The following screenshots illustrate the issue:

I have verified that the table is updating correctly by running sync rules.

No errors or exceptions appear in the logs related to db.watch.

Manually querying the database reflects expected changes, but the listener does not trigger.

Would appreciate any guidance on resolving this issue. Thanks in advance! 🚀

[simolus3]

So I think this boils down to the query not being valid (it might be valid for Postgres which supports some additional syntax that SQLite does not, which is why you can run it when managing the instance).

${FFAppState().user.userId} = ANY(manager_ids)

ANY is not a function in SQLite, you can't use it. The right structure would be ${FFAppState().user.userId} IN manager_ids, but then the next question is where do manager_ids come from? Is that an array column on the events table? In that case, you might want to use ${FFAppState().user.userId} IN (SELECT value FROM json_each(manager_ids)) as a query on the client.
Even better would be to use prepared statements:

var stream = db.watch(
        'SELECT * FROM events WHERE creator_user = ?1 OR ?1 IN (SELECT value FROM json_each(manager_ids)) ORDER BY start_date ASC', parameters: [FFAppState().user.userId]);
    eventsSubscription = stream.listen((data) {
      callback(data.map((json) => EventsRow(Map<String, dynamic>.from(json))).toList());
    });

No errors or exceptions appear in the logs related to db.watch.

That's something we should take a look at (I would the query to report an error), thanks! I think the stream you're listening to should emit an error event, and since you don't have an onError callback on .listen it should be reported as an unhandled exception somewhere. Do you see a SqliteException pop up anywhere in the logs?

[tejas-codiste]

Hi @simolus3,

Apologies, but none of the suggested solutions have worked for me.

After applying the json_each query, it works successfully in the PowerSync Diagnostics tool but does not trigger updates in the Flutter app.

manager_ids is nothing but Int array in event table.

I have attached screenshots for better clarity. Please take a look and let me know if there's a possible fix.

[simolus3]

So at least we know that there is an error getting logged after all :)

I have attached screenshots for better clarity. Please take a look and let me know if there's a possible fix.

Given that manager_ids appears to be a column that exists in your data, could you please check whether your client-side schema includes the manager_ids column? I assume it's missing based on the "no such column" error, you can either add it manually or by re-exporting your schema from the dashboard.

[tejas-codiste]

Yes, the declaration resolved the crash issue, but the data is still not loading.

Please check the attachment—I declared it as follows, but I can't find the integer array in the schema:

powersync.Table('events', [
    powersync.Column.integer('uid'),
    powersync.Column.integer('event_id'),
    powersync.Column.text('title'),
    powersync.Column.text('start_date'),
    powersync.Column.text('end_date'),
    powersync.Column.text('address'),
    powersync.Column.integer('creator_user'),
    powersync.Column.text('access_code'),
    powersync.Column.integer('total_attendees'),
    powersync.Column.integer('pin'),
    powersync.Column.text('sync_status'),
    powersync.Column.integer('manager_ids'),
    powersync.Column.integer('head_seller_ids'),
    powersync.Column.integer('seller_ids')
]),

I can retrieve only exact matches, but queries using IN are not working.

[simolus3]

The _ids column should be powersync.Column.text to reflect that the data is an array. Are you syncing from MongoDB or Postgres? If it's Postgres, could you also post the CREATE TABLE statement for events on the backend? If it's MongoDB, could you check whether there's an entry in the collection where manager_ids, head_seller_ids and seller_ids is not array but rather a single integer value?

There might be an issue on our side here, I'm just wondering if it works when you manually change the type to powersync.Column.text for manager_ids, head_seller_ids and seller_ids?

[tejas-codiste]

I'm syncing data from Supabase Postgres. The same query executes correctly in the console and even appears correctly in diagnostics, but in the app, it only returns results for exact matches (=) and does not work with IN.

[simolus3]

but in the app, it only returns results for exact matches (=) and does not work with IN.

Is that still true after changing powersync.Column.integer to powersync.Column.text manually for the array columns?

To help us understand what's causing us to miss the type here, could you please run the following query in the PowerSync dashboard and share the results (I know it's quite long, that's the query we use internally to figure out types, I've changed it so that it only reports the single row for the events table here)? Ideally copy the value of columns please, it will be quite long and not easy to see from a screenshot.

SELECT
tbl.schemaname,
tbl.tablename,
tbl.quoted_name,
json_agg(a ORDER BY attnum) as columns
FROM
(
  SELECT
    n.nspname as schemaname,
    c.relname as tablename,
    (quote_ident(n.nspname) || '.' || quote_ident(c.relname)) as quoted_name
  FROM
    pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE
    c.relkind = 'r'
    AND c.relname = 'events' -- only include the one table we care about here
    AND n.nspname not in ('information_schema', 'pg_catalog', 'pg_toast')
    AND n.nspname not like 'pg_temp_%'
    AND n.nspname not like 'pg_toast_temp_%'
    AND c.relnatts > 0
    AND has_schema_privilege(n.oid, 'USAGE') = true
    AND has_table_privilege(quote_ident(n.nspname) || '.' || quote_ident(c.relname), 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') = true
) as tbl
LEFT JOIN (
  SELECT
    attrelid,
    attname,
    format_type(atttypid, atttypmod) as data_type,
    (SELECT typname FROM pg_catalog.pg_type WHERE oid = atttypid) as pg_type,
    attnum,
    attisdropped
  FROM
    pg_attribute
) as a ON (
  a.attrelid = tbl.quoted_name::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND has_column_privilege(tbl.quoted_name, a.attname, 'SELECT, INSERT, UPDATE, REFERENCES')
)
GROUP BY schemaname, tablename, quoted_name

[tejas-codiste]

@simolus3 Thanks for your kind support!

Please find the following requested details by executed your query in powersync dashboard.

schemaname	tablename	quoted_name	columns
public	events	public.events	[{"attrelid":"32586","attname":"uid","data_type":"integer","pg_type":"int4","attnum":1,"attisdropped":false}, {"attrelid":"32586","attname":"event_id","data_type":"integer","pg_type":"int4","attnum":2,"attisdropped":false}, {"attrelid":"32586","attname":"title","data_type":"text","pg_type":"text","attnum":3,"attisdropped":false}, {"attrelid":"32586","attname":"start_date","data_type":"timestamp(3) without time zone","pg_type":"timestamp","attnum":4,"attisdropped":false}, {"attrelid":"32586","attname":"end_date","data_type":"timestamp(3) without time zone","pg_type":"timestamp","attnum":5,"attisdropped":false}, {"attrelid":"32586","attname":"address","data_type":"text","pg_type":"text","attnum":6,"attisdropped":false}, {"attrelid":"32586","attname":"access_code","data_type":"text","pg_type":"text","attnum":7,"attisdropped":false}, {"attrelid":"32586","attname":"pin","data_type":"integer","pg_type":"int4","attnum":8,"attisdropped":false}, {"attrelid":"32586","attname":"id","data_type":"integer","pg_type":"int4","attnum":9,"attisdropped":false}, {"attrelid":"32586","attname":"creator_user","data_type":"integer","pg_type":"int4","attnum":10,"attisdropped":false}, {"attrelid":"32586","attname":"total_attendees","data_type":"integer","pg_type":"int4","attnum":11,"attisdropped":false}, {"attrelid":"32586","attname":"sync_status","data_type":""SyncStatus"","pg_type":"SyncStatus","attnum":12,"attisdropped":false}, {"attrelid":"32586","attname":"fetched_attendees","data_type":"double precision","pg_type":"float8","attnum":13,"attisdropped":false}, {"attrelid":"32586","attname":"cron_status","data_type":""CronStatus"","pg_type":"CronStatus","attnum":14,"attisdropped":false}, {"attrelid":"32586","attname":"head_seller_ids","data_type":"integer[]","pg_type":"_int4","attnum":18,"attisdropped":false}, {"attrelid":"32586","attname":"manager_ids","data_type":"integer[]","pg_type":"_int4","attnum":19,"attisdropped":false}, {"attrelid":"32586","attname":"seller_ids","data_type":"integer[]","pg_type":"_int4","attnum":20,"attisdropped":false}]

And the following way, I have added in flutter code

    powersync.Column.text('manager_ids'),
    powersync.Column.text('head_seller_ids'),
    powersync.Column.text('seller_ids')

Let me know if needed to refactor any.

[simolus3]

Thanks for posting the results, we're looking into this!

And the following way, I have added in flutter code

I think that should be everything. Does the query work now that you've changed the type here?