`Migration.renameColumn` and `Migration.renameTable` are not idempotent

[ichiko]

Describe the bug

The Migration.renameColumn and Migration.renameTable methods in Drift generate SQL statements that are not idempotent. When the same migration is executed multiple times, it fails because the SQL statements do not check whether the column or table has already been renamed.

Environment

• Drift Version:
  • drift: ^2.28.1
  • drift_dev: ^2.27.0
• Dart Version: 3.9.2
• Flutter Version: 3.35.5
• Database: SQLite

Description

When using Migration.renameColumn or Migration.renameTable in a migration step, the generated SQL statements (e.g., ALTER TABLE ... RENAME COLUMN ... or ALTER TABLE ... RENAME TO ...) do not include existence checks. This means:

1. If a migration is applied successfully once, attempting to apply it again will fail
2. The migration cannot be safely re-run in case of partial failures or rollbacks
3. This violates the principle of idempotency, which is crucial for reliable database migrations

Steps to Reproduce

1. Create a migration that uses Migration.renameColumn:

from3To4: (m, schema) async {
  await m.renameColumn(
    schema.notebookMemoRecords,
    'leaf_id',
    schema.notebookMemoRecords.pageId,
  );

  // before run other steps, some time app killed by OS on mobile device

  // other steps ...
}

2. Apply the migration to a database (schema version 3 → 4)
3. Attempt to apply the same migration again (e.g., by resetting the schema version or re-running the migration)

Expected Behavior

The migration should be idempotent. If the column or table has already been renamed, the migration should either:

• Skip the rename operation silently, or
• Check for the existence of the target name before attempting the rename

This would allow the migration to be safely applied multiple times without errors.

Actual Behavior

When the migration is executed a second time, SQLite throws an error because:

• For renameColumn: The source column no longer exists (it was already renamed)
• For renameTable: The source table no longer exists (it was already renamed)

[simolus3]

I don't think the methods should be idempotent. Even if they were, it's possible for migration callbacks like from3To4 to have side effects (e.g. when deleting stale data in a migration) that can't be avoided. So what should happen is that the migrations are not called again.

You could use a setup like this to run the entire callback in a transaction. To that method, you could also pass a wrapper for steps that does something like this:

final impl = migrationSteps(...);

await VersionedSchema.runMigrationSteps(
  migrator: m,
  from: from,
  to: to,
  steps: (currentVersion, database) => database.transaction(() => impl(currentVersion, database)),
)

The reason this is not the default is that some pragmas you might want to set in migrations don't work if used within a transaction. But this is something worth doing to avoid partial migrations. I'll add this to the documentation.

[ichiko]

I couldn't use transaction in my case.
Because the migration callbacks have also Migration.alterTable that use custom statement with PRAGMA foreign_keys = OFF , and our tables has foreign keys constraints.

[simolus3]

Could you disable foreign keys before the migration, then use transactions in the individual steps and re-enable foreign keys afterwards?

I don't think getting the individual migration APIs to be idempotent is generally possible, so I think the solution would necessarily involve transactions in the migration.