Delete table

[Sandrichka22]

Is there an example of how to delete a table which has been added in a previous version of the DB using migration?
To note that in my scenario, the primary key of the table (the one to be deleted) is also referenced as a foreign key in another table. I have gone through the documentation a bit and could not see an example about this. Any help is appreciated.

[simolus3]

In this case, you should first restructure the table with the foreign key. This can be done with await m.alterTable(TableMigration(tableReferencingTheDeletedTable)), which will drop the foreign key constraint without deleting any data. To drop the table itself, you can run a custom statement (await customStatement('DROP TABLE table_to_delete;')) - the migrator APIs aren't really helpful here since deleted tables no longer appear in generated code.

[Sandrichka22]

Thanks for the quick reply, much appreciated.
However I might need to explain my scenario in more detail, because I still have some bits that I cannot work through (don't understand how to accomplish).

First I want to note that I am defining the tables using .drift files. So in my table_referencing_deleted_table.drift I have the import for the table_to_delete.drift file and inside CREATE TABLE the foreign key which references the public key of the tableToDelete table.

For the implementation of the migration logic I am using the generated stepByStep method, so my database.dart file looks something like the following:

@DriftDatabase(include: {
table_referencing_deleted_table.drift,
table_to_delete.drift,
})
class AppDb extends _$AppDb {
  AppDb(QueryExecutor executor) : super(executor);

  @override
  int get schemaVersion => 55;

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      onCreate: (Migrator m) async {
        await m.createAll();
      },
      onUpgrade: stepByStep(from53To54: (m, schema) async {
        await m.createTable(tableReferencingTheDeletedTable);
        await m.createTable(tableToDelete);
        await m.recreateAllViews();
      }, from54To55: (m, schema) async {
        // Add migration logic to delete tableToDelete
      }),
    );
  }
}

So my question is, is it ok to only add the following (and not delete any file) to the from54To55 callback:

  await m.alterTable(TableMigration(tableReferencingTheDeletedTable));
  await customStatement('DROP TABLE IF EXISTS TableToDelete;');

I tried doing that, but the tests for migrating from 54 to 55 fail with the following:

Schema does not match
comparing TableToDelete:
 The actual schema does not contain anything with this name.

Also, I am expecting to be able to delete the table_to_delete.drift file. If I delete it, then I would also need to remove every reference of it inside the table_referencing_deleted_table.drift (import and foreign key) and also remove it from the include section in database.dart.
Which I have also tried, but then I am faced with other multiple issues, the apparent one not being able to use await m.createTable(tableToDelete) inside from53To54 callback. I can bypass this by simply defining the TableToDelete class in dart and then using an instance of it in from53To54 -> await m.createTable(TableToDelete(this)).
This setup passes my tests for migrating from version 54 to 55, but make my tests for migrating from 53 to 54 fail, because I have changed the table_referencing_deleted_table and there is a constraint (foreign key referencing table_to_delete) which I have removed and it is in fact expected in version 54.

So I am still confused about how to accomplish the deletion of the table.

[simolus3]

I tried doing that, but the tests for migrating from 54 to 55 fail with the following:

The "actual" schema is the schema of your database, so this error means that drift expected the TableToDelete table to still exist om schema 55. This is likely because you haven't deleted the table's definition before exporting the new schema. The CREATE TABLE statements in drift files always represent the latest schema, so if you want to delete a table you should also delete it from your sources.

If I delete it, then I would also need to remove every reference of it inside the table_referencing_deleted_table.drift (import and foreign key)

That's intentional though - if the table no longer exists, other tables in the same (currently latest) schema version can't reference it either.

Which I have also tried, but then I am faced with other multiple issues, the apparent one not being able to use await m.createTable(tableToDelete) inside from53To54 callback

This is actually where the step-by-step migrations come in really handy, because that's exactly what drift is doing for you! The schema parameter you receive for each step describes the database schema at that specific version. So the migration should actually look like

      onUpgrade: stepByStep(from53To54: (m, schema) async {
        await m.createTable(schema.tableReferencingTheDeletedTable);
        await m.createTable(schema.tableToDelete);
        await m.recreateAllViews();
      }, from54To55: (m, schema) async {
        await m.alterTable(TableMigration(schema.tableReferencingTheDeletedTable));
        await customStatement('DROP TABLE IF EXISTS TableToDelete;');
      }),

Note that things like recreateAllViews() are still fine because the migrator passed to step callbacks knows about the full schema too - the problem just occurs when you invoke it with a table from the current database class.

[Sandrichka22]

All clear now :) This is what I was missing, having the access to a specific version of the schema so that tables related to that specific version are still created in the same way (and are not affected by the current schema and new changes there). Which I guess was there all along with the schema parameter for each step, not sure how I missed it! Thanks a lot for your help and your explanations! Everything good now :)