Provide createdAt/updatedAt fields

[ddxl123]

Provide createdAt/updatedAt fields.
Automatically write createdAt when a row is created,
and update updatedAt when a row is updated.

[simolus3]

This would have to be stored in the database, and I don't believe that drift should add those columns by default.

You can easily add those fields to your table with a mixin:

mixin WithModificationTimestamps on Table {
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
  DateTimeColumn get updatedAt => dateTime().withDefault(currentDateAndTime)();
}

Then, you just have to use extends Table with WithModificationTimestamps on your table definitions to get the fields. createdAt should already be working with that, for updatedAt you'll also need to define triggers:

  MigrationStrategy get migration {
    return MigrationStrategy(onCreate: (m) async {
      await m.createAll();

      for (final table in allTables) {
        if (table is WithModifiedTimestamps) {
          final name = table.entityName;

          await customStatement('''
            CREATE TRIGGER updated_at_$name AFTER UPDATE ON $name
            BEGIN
              UPDATE $name SET updated_at = CURRENT_TIMESTAMP;
            END;
          ''');
        }
      }
    });
  }

[simolus3]

Following up on this: Especially when using validateDatabaseSchema(), it is better to include the trigger in allSchemaEntities instead of creating it manually:

  @override
  List<DatabaseSchemaEntity> get allSchemaEntities {
    final result = super.allSchemaEntities;

    for (final table in result.whereType<TableInfo>().toList()) {
      if (table is WithModifiedTimestamps) {
        final name = table.entityName;
        final triggerName = 'updated_at_$name';

        result.add(
          Trigger(
            '''
            CREATE TRIGGER $triggerName AFTER UPDATE ON $name BEGIN
              UPDATE $name SET updated_at = CURRENT_TIMESTAMP;
            END;
          ''',
            triggerName,
          ),
        );
      }
    }

    return result;
  }

With this override, the custom onCreate strategy can be removed.

[darkomenx]

Why this very good explain is not in your documentation ?

Thank you for your advice. I confirm that It works like a charm in my part.