How to increment a column value with a companion in Drift/Moor/Flutter

Question

I want to increment a value of a column in Drift (Moor). Here´s how it looks like in custom SQL:

await _db.customStatement('UPDATE items SET sequence = sequence + 1 WHERE sequence >= ?', [insertAtSequence]);

That works fine - however, as I´m using streams to fetch data from the table I´ve to use the Drift API instead of custom SQL:

await (_db.update(_db.items)..where((e) => e.sequence.isBiggerOrEqualValue(insertAtSequence))).write(ItemsCompanion(sequence: sequence + 1)));

And here is my question: sequence: sequence + 1 doesn´t work cause "sequence" after the : doesn´t refer the current column value. How can I access the current value in the column and increment it by 1 (in this example)?

Answer 1

sorry for the late answer but I bumped on yours as I had the same issue. I checked on the Drift Github repository and found that you can do it as follows:

await (_db.update(_db.items)
..where((e) => e.sequence.isBiggerOrEqualValue(insertAtSequence)))
.write(ItemsCompanion.custom(sequence: _db.items.sequence + const Constant(1))));

Source of similar example with answer from the owner of the package: https://github.com/simolus3/drift/issues/673#issuecomment-652617026