exports.up = async (knex) => {
await knex.raw(`
ALTER TABLE users.sessions RENAME COLUMN role TO old_role;
CREATE TYPE newest_user_role AS ENUM('admin', 'editor', 'viewer');
ALTER TABLE users.sessions ADD COLUMN role newest_user_role;
`);
const roles = await knex('users.sessions').select('old_role', 'user_id');
await Promise.all(roles.map(async ({ user_id, old_role }) => {
return knex('users.sessions').where('user_id', user_id).update({ role: old_role });
}));
await knex.raw(`
ALTER TABLE users.sessions DROP COLUMN old_role;
`);
};
exports.down = async (knex) => {
await knex.raw(`
ALTER TYPE newest_user_role RENAME TO old_user_role;
CREATE TYPE newest_user_role AS ENUM('admin', 'editor', 'viewer');
ALTER TABLE users.sessions ALTER COLUMN role TYPE newest_user_role USING role::text::newest_user_role;
DROP TYPE old_user_role;
`);
};
After successfully running the migration and rollback processes, reapplying the migration throws the error:
error: type "newest_user_role" already exists
I'm facing this issue during the migrations. Any help on what I might be missing is greatly appreciated. Thank you.