Problem with alter table migration when wiping database.

[nmacuk]

Hello
I have encountered an issue when trying to migrate an empty database to a version with AlterTable migration
my migrations.rb file looks like this

VIEW_SCHEMA_VERSION = 6

class Migrations < Sequent::Migrations::Projectors
  def self.version
    VIEW_SCHEMA_VERSION
  end

  def self.versions
    {
      "1" => [
       ...
      ],
      "2" => [
        ...
      ],
      "3" => [
        ...
      ],
      "4" => [
        ...
        SubscriptionProjector,
      ],
      "5" => [
        Sequent::Migrations.alter_table(SubscriptionRecord
        
        ),
      ],
      "6" => [
        ...
      ]
    }
  end
end

And I had a problem when running migrations on an EMPTY database that looked like that:

Missing file db/tables/subscription_records_5_6.sql to apply for version 6

I followed documentation when making alter table migration for subscription_records
the files look like this:
subscription_records.sql

CREATE TABLE subscription_records%SUFFIX% (
...  
provider_subscription_id character varying,
  ...
);

subscription_records_6.sql

alter table subscription_records add column provider_subscription_id character varying;

The workaround I found was:

1. I modified the subscription_records.sql file by removing the provider_subscription_id column.
2. I successfully migrated to version 5 without any errors.
3. I added the provider_subscription_id column back to the subscription_records.sql file and created a new file called subscription_records_6.sql containing the alter table statement to add the column.
4. I performed the migration to version 6, and it worked as expected.

However, this workaround involved manual modifications and interrupting the migration process, which was not ideal. I'm concerned if this behavior is expected, and whether it means that once an alter_table migration is added, it becomes impossible to wipe the entire database again.

What is the expected behavior here?

[lvonk]

Hi,

The expected behavior is that this should work, you are taking the correct steps.

Please add IF NOT EXISTS to you alter table migration, this is needed to prevent it from error when re-creating after wiping completely.

alter table subscription_records add column IF NOT EXISTS provider_subscription_id character varying;

See also #382 for more background and a similar issue.

If this doesn't help please provide:

• sequent version
• ruby version
• rails version (if you are using rails)
• database.yml

[lvonk]

Closing due to inactivity. Feel free to reopen if above comment did not resolve it.