Reputation: 7008
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
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:
newest_login_type
is creatednewest_login_type
is modifiednewest_login_type
already existps. 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