Multiple custom constraint migrations on the same column

[frankvollebregt]

Some versions ago, I made my column nullable. In SQL (my tables are defined in .drift files) this is similar to this:

-- From 1
category INT NOT NULL REFERENCES todo_category(id),

-- To 2
category INT REFERENCES todo_category(id),

Which I could migrate with the migration API by running the 'default' TableMigration:

await m.alterTable(TableMigration(TodoItems));

Now recently, I tried to add an ON DELETE constraint to this column.

-- From 2
category INT REFERENCES todo_category(id),

-- To 3
category INT REFERENCES todo_category(id) ON DELETE SET NULL,

When running the automated schema tests, however, the migration from 1 to 2 now fails, because it immediately migrates this column to the latest version. When the TableMigration is applied.

Disclaimer

I've tried to adapt the example to use the example database from the docs, but perhaps there is a subtle difference between this example and my actual database.

[simolus3]

This is unfortunately a fundamental problem with the default migration APIs because they're unaware of older schema versions (as the generated code can only depend on the current definitions).

We have tooling that allows you to create older schema versions too, but it's a separate CLI invocation. There is a standalone command that will export your current schema into a JSON file (I suppose you're already doing that or a similar command for tests). When you run that command again after making schema changes and incrementing the schema version, we generate helpers for the migration that are aware of the schema you're migrating too. It's hard to do that retroactively, but we have guides on using the version-aware migrations only for some newer versions.

If you have schema exports but aren't using drift_dev make-migrations yet, you could migrate to that command. Or, if you prefer your existing workflow, you could use this command which generates helpers for you without doing anything else.

In that case, your migration would look like this:

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      onUpgrade: stepByStep(
        from1To2: (m, schema) async {
          await m.alterTable(TableMigration(schema.todoItems));
        },
        from2To3: (m, schema) async {
          await m.alterTable(TableMigration(schema.todoItems));
        },
      ),
    );
  }

[frankvollebregt]

Thank you, and that makes sense. For now, I have just disabled this 1 schema migration test (it concerns a version that is over a year old, so it's not a big deal).

I am indeed already exporting the schema for the steps migrations, so I'll take a look at how I can adapt my migration. Thank you for the guidance