CODEWITHSUNDEEP
javascriptpostgresqlknex.js
Zack Shapiro
Zack Shapiro

Reputation: 7008

Postgres migration error - error: type already exists

exports.up = async (knex) => {
  await knex.raw(`
    ALTER TABLE accounts.login RENAME COLUMN type TO old_type;
    CREATE TYPE newest_login_type AS ENUM('hidden', 'github', 'twitter', 'google');
    ALTER TABLE accounts.login ADD COLUMN type newest_login_type;
  `);

  const types = await knex('accounts.login').select('old_type', 'user_id');

  await Promise.all(types.map(async ({ user_id, old_type }) => {
    return knex('accounts.login').where('user_id', user_id).update({ type: old_type });
  }));

  await knex.raw(`
    ALTER TABLE accounts.login DROP COLUMN old_type;
  `);
};

exports.down = async (knex) => {
  await knex.raw(`
    ALTER TYPE newest_login_type RENAME TO old_login_type;
    CREATE TYPE newest_login_type AS ENUM('hidden', 'github', 'twitter');
    ALTER TABLE accounts.login ALTER COLUMN type TYPE newest_login_type USING type::text::newest_login_type;
    DROP TYPE old_login_type;
  `);
};

When I migrate it works, then rollback works, then migrate again throws the following error:

error: type "newest_login_type" already exists

What am I doing wrong here? Thanks

Upvotes: 2

Views: 3007

Answers (1)

Mikael Lepistö
Mikael Lepistö

Reputation: 19728

I'm surprised how that code even can work, but your problem is that up script expects that newest_login_type doesn't exist and your down script does not remove that type, but only renames and recreate it.

So:

  1. running up, newest_login_type is created
  2. running down newest_login_type is modified
  3. running up again fails, because newest_login_type already exist

ps. That shouldn't work at all because you should not pass multiple SQL statements to single knex.raw call. That is just not supported by many DB drivers including pg driver that is used with knex's postgresql dialect.

Upvotes: 1

Related Questions