Database atomic increase/decrease of a numerical value

[lukehutch]

Is your feature request related to a problem? Please describe.

I need to be able to maintain a credit balance in my users' accounts. The balance cannot go below zero. I want to be able to atomically increase or decrease the credit balance by a given integer amount. (Presumably doing the same for floating point values also makes sense.)

Describe the solution you'd like

Something along the lines of:

await Account.modifyRowValue(accountRow, column: Account.t.userCredits, subtract: 4, min: 0);

Comments by @Isakdl on #1422, where I originally proposed this:

Perhaps some thought is needed on the min/max values, it would be interesting if this could be added to the yaml file. Then we could also enforce these as restrictions in the database.

[lukehutch]

Here is the code I am using right now to update one or more columns by a specified amount. It would be nice to have Serverpod syntax for as much of this as possible, since it is a very common usecase to need to atomically increase or decrease a count by a specified amount, with fixed limits (e.g. a min of 0), treating NULL as 0, etc., and with the whole query working as an UPSERT.

String _updateColumnExpr(
  ColumnInt columnToUpdate,
  int updateAmount,
) =>
    updateAmount > 0
        ? '"${columnToUpdate.columnName}" = '
            // Use COALESCE to treat NULL as 0
            'COALESCE("${columnToUpdate.columnName}", 0) + $updateAmount' //
        // Don't allow counts to go negative
        : '"${columnToUpdate.columnName}" = GREATEST(0, '
            'COALESCE("${columnToUpdate.columnName}", 0) - ${-updateAmount})';

/// Update int counters in an existing row in a table. The [idColumn] must be
/// unique.
Future<Map<String, dynamic>?> updateCountColumn({
  required Session session,
  required Table table,
  required ColumnInt idColumn,
  required int idColumnValue,
  required Map<ColumnInt, int> updateAmounts,
}) async {
  final entries = updateAmounts.entries;
  final updateColumnNames =
      entries.map((e) => '"${e.key.columnName}"').join(', ');
  final updateBaseValues = entries.map((e) => e.value.toString()).join(', ');
  final updateExprs =
      entries.map((e) => _updateColumnExpr(e.key, e.value)).join(', ');
  final result = await session.db.unsafeQuery(
    'INSERT INTO "${table.tableName}" '
    '  ("${idColumn.columnName}", $updateColumnNames) '
    '  VALUES ($idColumnValue, $updateBaseValues) '
    'ON CONFLICT ("${idColumn.columnName}") DO UPDATE '
    '  SET $updateExprs',
  );
  return result.firstOrNull?.toColumnMap();
}