Validate Fields Early

[mcmah309]

I got into a state where

powersync.dart/packages/powersync/lib/src/crud.dart

Line 86 in 5123578

data['type'], data['id'], row['tx_id'], data['data']);

was failing since somehow data['id'] was an int. I didn't see an easy way to resolve this so I had to run

await db.execute("DELETE FROM ps_crud;");

To drop the queue. I believe this resulted from me accidentally writing something like

INSERT INTO table_name(id, ...) VALUES (random_int)

Instead of

INSERT INTO table_name(id, ...) VALUES (uuid())

Which messed up all of syncing, even if I deleted the original code/table. Ideally a mistake like this would never make it to production, but if it did, is there a better way to recover without losing user data? Or at least in this case, validate early so for essential fields these situations cannot occur.

It is worth noting that all subsequent inserts would not sync until ps_crud was dropped.

[rkistner]

Thanks for reporting the issue. I've seen at least one other report of the same issue, and we'll implement a validation for this.

is there a better way to recover without losing user data?

You could probably replace the ids in the ps_crud table if you need to keep the data, e.g. update ps_crud set id = cast(id as text). I do imagine this issue should be picked up before production in most cases though, since it will affect all writes to that table.