How to make a migration with default initial value for a foreign key

[Romain-Guillot]

Assuming I have this table:

// version=1
class Product extends Table {
  late final id = integer().autoIncrement()();
  late final name = text().nullable()();
}

And I want to make this schema update (adding a non-nullable category column):

// version=2
class Products extends Table {
  late final id = integer().autoIncrement()();
  late final name = text().nullable()();
  late final category = integer().references(
    ProductCategories,
    #id,
    onDelete: KeyAction.restrict,
  )();
}

class ProductCategories extends Table {
  late final id = integer().autoIncrement()();
  late final name = text().nullable()();
}

What's the best way to handle the migration, knowing that I need to set an initial value for Products.category (while waiting for the backend to return the real data, for example):

// migration
class AppDatabase extends _$AppDatabase {
  AppDatabase({QueryExecutor? executor})
    : super(executor ?? driftDatabase(name: kLocalDatabaseName));

  @override
  int get schemaVersion => 17;
  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      from1To2: (migrator, schema) async {
        await migrator.create(schema.productCategories);
        await schema.productCategories.insert().insert(
          RawValuesInsertable({
            schema.productCategories.id.name: const Constant(1),
            schema.productCategories.name.name: const Constant('Misc'),
          }),
        );
        await migrator.alterTable(
          TableMigration(
            schema.products,
            newColumns: [schema.products.category],
            columnTransformer: {
              schema.products.category: const Constant(1),
            },
          ),
        );
      },
    );
  }
}

I'm not totally satisfied with the RawValuesInsertable approach, as I believe there should be a better solution.
Also, I don't want to directly use the database schema or the generated companions inside my migration, as there could be breaking changes to these companions/global schema in future db versions.

[simolus3]

Inserting a bogus value into ProductCategories that is later replaced with real data could be a good approach when adding the new column. Other approaches might be to make the column nullable, but only use NULL for old, existing rows.

If you want to go with this approach but add type-safety, you're correct that using the current companions is dangerous since they could be altered in subsequent schema versions. You can run something like dart run drift_dev schema generate drift_schemas/default lib/src/schema_versions --data-classes --companions to generate all schemas with companions for that specific schema version. Doing this for all schema versions can generate quite a bit of code though, so the RawValuesInsertable might not be that bad of an option if you write unit test your migrations.