CODEWITHSUNDEEP
javascriptknex.js
Lucas
Lucas

Reputation: 12

Knex migration down error when trying to drop constraint and alter column

I had, on a previous migration, a column named status which was boolean. In this migration, I altered it to integer and created a reference to the meetings_statuses.

This is the migration:

exports.up = (knex) => {
  return knex.schema.alterTable("meetings", (table) => {
    table.integer("status").notNullable().unsigned().alter();

    table
      .foreign("status")
      .references("id")
      .inTable("meetings_statuses")
      .onDelete("RESTRICT");
  });
};

exports.down = (knex) => {
  return knex.schema.debug().alterTable("meetings", (table) => {
    table.dropForeign("status");

    table.boolean("status").notNullable().defaultTo(true).alter();
  });
};

When migrating down I want to go back to the old column type and drop the foreign relationship. However, when I do it I get the error:

migration file "20210205004841_alter_meetings_table.js" failed
migration failed with error: alter table "meetings" alter column "status" type boolean using ("status"::boolean) - foreign key

The debug shows me this:

[
  {
    sql: 'alter table "meetings" alter column "status" drop default',
    bindings: []
  },
  {
    sql: 'alter table "meetings" alter column "status" drop not null',
    bindings: []
  },
  {
    sql: 'alter table "meetings" alter column "status" type boolean using ("status"::boolean)',
    bindings: []
  },
  {
    sql: `alter table "meetings" alter column "status" set default '1'`,
    bindings: []
  },
  {
    sql: 'alter table "meetings" alter column "status" set not null',
    bindings: []
  },
  {
    sql: 'alter table "meetings" drop constraint "meetings_status_foreign"',
    bindings: []
  }
]

What I'm thinking is that, for some reason, Knex is trying to change the column first instead of dropping the constraint, causing the error, but I couldn't find any solution.

Can anyone help me?

Upvotes: 2

Views: 3618

Answers (1)

felixmosh
felixmosh

Reputation: 35503

You must firstly drop the foreign key and only then alter it.


exports.down = async(knex) => {
  await knex.schema.alterTable("meetings", (table) => {
    table.dropForeign("status");
  });
  await knex.schema.alterTable("meettings", (table) => {
    table.boolean("status").notNullable().defaultTo(true).alter();
  });
};

Upvotes: 2

Related Questions