Replace ignores Null values

[TillJohanndeiter]

I have this data class with a custom upsert methode:

@MappableClass()
class TrainingRecord {
  static const tableName = "training_record";

  @override
  final String id;
  final DateTime? end;

  TrainingRecord({
    required this.id,
    this.end,
  });

  Future<void> upsert() async {
    await db.execute('''
      INSERT OR REPLACE INTO $tableName (id, end)
      VALUES (?, ?)
    ''', [
      id,
      end?.toIso8601String(),
    ]);
  }

If upsert is invoked then in my PowerSyncSupabaseConnector the map op.opData doesn't contains null values.

  @override
  Future<void> uploadData(PowerSyncDatabase database) async {
    final transaction = await database.getNextCrudTransaction();
    if (transaction == null) {
      return;
    }

    final rest = Supabase.instance.client.rest;
    CrudEntry? lastOp;
    try {
      for (var op in transaction.crud) {
        lastOp = op;

        final table = rest.from(op.table);
        if (op.op == UpdateType.put) {
          **var data = Map<String, dynamic>.of(op.opData!); // The 'end' field is missing from op.opData when its value is null“**

As a result i have to insert them manually in the map e.g:

if (op.table == TrainingRecord.tableName && !data.containsKey("end")) {
    data['end'] = null;
}

Otherwise they are ignore by

data['id'] = op.id;
await table.upsert(data);

I'm unsure if this is a bug or a feature 🤔

Thanks in advance

[simolus3]

Moving this here because this affects all SDKs, it can also be reproduced with a test here:

import 'dart:convert';

import 'package:sqlite3/sqlite3.dart';
import 'package:test/test.dart';

import 'utils/native_test_utils.dart';

void main() {
  setUpAll(() {
    loadExtension();
  });

  test('repro upsert', () {
    final db = sqlite3.openInMemory();
    addTearDown(db.dispose);

    db.execute('SELECT powersync_init()');
    db.execute('SELECT powersync_replace_schema(?);', [
      json.encode({
        'tables': [
          {
            'name': 'items',
            'columns': [
              {'name': 'end', 'type': 'TEXT'}
            ]
          }
        ]
      })
    ]);

    db.execute('INSERT OR REPLACE INTO items (id, end) VALUES (?, ?)',
        ['foo', 'initial']);
    db.execute('DELETE FROM ps_crud');
    db.execute(
        'INSERT OR REPLACE INTO items (id, end) VALUES (?, ?)', ['foo', null]);

    expect(db.select('SELECT * FROM ps_crud'), [
      {
        'id': 2,
        'tx_id': 2,
        // This actually has an empty data value
        'data': '{"op":"PUT","id":"foo","type":"items","data":{"end": null}}',
      }
    ]);
  });
}

[simolus3]

Ok, it looks like for inserts, CrudEntry.data is only supposed to store non-null values (if you do a regular insert without the OR REPLACE with a null value for end, data would also be an empty map).

For updates, changing a non-null value of end to null is supposed to include the null value in CrudEntry.data. But that path isn't triggered here because the statement is doing an insert (and ON CONFLICT DO UPDATE doesn't work for views).

So this looks like the expected behavior to me. For safety, that means connector should fill in missing values with null if that's required from the backend API.