Sharing Table definition between multiple projects with support of foreign keys

[petrnymsa]

We have application module which should be shared between two applications. This module has one table "SharedModule" which ideally needs to reference "UserProfile" table as foreign key

@DataClassName('SharedRow')
class SharedTable extends Table {
  TextColumn get id => text()();

  IntColumn get profileId => integer().references(UserProfiles, #id, onDelete: KeyAction.cascade)();

  @override
  Set<Column> get primaryKey => {id, profileId};

  const EmergenciesTable();
}

However UserProfiles defines each application and it has different structure. My idea was to create mixin or interface

abstract interface class UserProfilesTableInterface implements Table {
  IntColumn get id;
}

and reference it in SharedTable

  IntColumn get profileId => integer().references(UserProfilesTableInterface, #id, onDelete: KeyAction.cascade)();

But this produce warning

 The referenced element, UserProfilesTableInterface, is not understood by drift.

Is this scenario somehow supported or do we need to come up with different solution / architecture?

[simolus3]

This is not supported, and there isn't really a good way for us to start supporting it because UserProfilesTableInterface is not a table. So when the SharedTable is created, we need to generate the REFERENCES clause in SQL and it's not clear which table to reference! So we don't support "generic" tables that would be instantiated to different actual tables in different apps.

So I think a different setup would probably be better. One option may be to change UserProfiles to be an actual table and e.g. using a generic text column storing JSON with structures that would be different between the two apps.

[petrnymsa]

Yeah I thought that. One real advantage of foreign keys for us is cascade delete but I think we can safely refactor that SharedTable without foreign key and handle "cascade" deletion manually.