clientGeneratedAs column option

[tomasweigenast]

It would be nice if Drift implements the column option clientGeneratedAs. It would look like clientDefault, taking a function that accepts the companion as input, and returns a Value<ColumnType>?, but it would be executed every time we mutate the data in the database.

For example:

class Customers extends Table {
  Int64Column get id => int64().clientDefault(() => randomId())();
  IntColumn get etag => integer().withDefault(currentEpoch)();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
  DateTimeColumn get modifiedAt => dateTime().withDefault(currentDateAndTime)();
  DateTimeColumn get deletedAt => dateTime().nullable()();
  TextColumn get name => text()();
  TextColumn get search => text().clientGeneratedAs((companion) => companion.name.present ? Value(companion.name.value.withoutDiacritics()) : null);

  @override
  Set<Column<Object>>? get primaryKey => {id};
}

It would allow us to specify how data mutates in the database without the need of using companion.copyWith() to mutate the search, which in this case changes when name changes.

[simolus3]

I think I understand what you're getting at, but I'm still trying to understand how this use case is distinct from a stored generatedAs. Is the intention essentially to run more complex Dart code in generatedAs?

I guess what mainly worries me is how this would play out with e.g. a CustomersCompanion.custom used to insert or update rows based on arbitrary SQL expressions.

It's worth mentioning that this is possible already (at least in SQLite) by defining a custom function:

final db = Database(NativeDatabase.memory(setup: (database) {
  database.createFunction(
    functionName: 'remove_diacritics',
    function: (args) {
      return (args[0] as String).removeDiacritics();
    },
  );
}));

And then calling something like this in generatedAt:

extension RemoveDiacritics on Expression<String> {
  Expression<String> removeDiacritics() => FunctionCallExpression('remove_diacritics', [this]);
}

(obviously, possible is not the same thing as simple and we should perhaps find a better solution for this)

[tomasweigenast]

I completely forgot about custom sqlite functions. Maybe this solution won't work using .custom in companions, and alert the user about that.

[tomasweigenast]

@simolus3 custom functions work in web database?

[simolus3]

It's possible to get them to work, but it requires using a custom drift worker. You can create a Dart file that looks like this:

import 'package:drift/wasm.dart';

void main() {
  WasmDatabase.workerMainForOpen(setupAllDatabases: (database) {
    database.createFunction(
      functionName: 'remove_diacritics',
      function: (args) {
        return (args[0] as String).removeDiacritics();
      },
    );
  });
}

You can compile it with dart compile js -O4 that_file.dart and use the resulting file in place of the existing drift_worker.js. To open the database, you'll need to provide the same setup code in case no workers are available:

await WasmDatabase.open(
  databaseName: 'instantlist',
  sqlite3Uri: Uri.parse('sqlite3.wasm'),
  driftWorkerUri: Uri.parse('worker.dart.js'),
  localSetup: (database) {
    database.createFunction(
      functionName: 'remove_diacritics',
      function: (args) {
        return (args[0] as String).removeDiacritics();
      },
    );
  },
);