Drop and create ENUM with sequelize correctly?
Dropping the ENUM manually in down worked pretty well for me.
module.exports = {
up: function (queryInterface, DataTypes) {
queryInterface.createTable('Users', {
//...
status: {
type: DataTypes.ENUM,
values: [
'online',
'offline',
],
defaultValue: 'online'
}
//...
})
},
down: function (queryInterface) {
return queryInterface.sequelize.transaction(t => {
return Promise.all([
queryInterface.dropTable('Users'),
queryInterface.sequelize.query('DROP TYPE IF EXISTS "enum_Users_status";'),
]);
});
}
};
If you want to change/edit type enum without losing data. here is my migration code. hopefully it helps.
queryInterface.changeColumn(
'table_name',
'Column_name',
{
type: Sequelize.TEXT,
},
),
queryInterface.sequelize.query('drop type enum_tableName_columnName;')
.then(() => queryInterface.changeColumn(
'table_name',
'column_name',
{
type: Sequelize.ENUM('value1','value2'),
},
)),
UPDATE: I have used this in three projects up to now, so I decided to create a npm module: https://www.npmjs.com/package/replace-enum-postgresql.
I made a utility to do this, hope you it this helpful.
utils/replace_enum.js:
'use strict';
/**
* Since PostgreSQL still does not support remove values from an ENUM,
* the workaround is to create a new ENUM with the new values and use it
* to replace the other.
*
* @param {String} tableName
* @param {String} columnName
* @param {String} defaultValue
* @param {Array} newValues
* @param {Object} queryInterface
* @param {String} enumName - Optional.
*
* @return {Promise}
*/
module.exports = function replaceEnum({
tableName,
columnName,
defaultValue,
newValues,
queryInterface,
enumName = `enum_${tableName}_${columnName}`
}) {
const newEnumName = `${enumName}_new`;
return queryInterface.sequelize.transaction((t) => {
// Create a copy of the type
return queryInterface.sequelize.query(`
CREATE TYPE ${newEnumName}
AS ENUM ('${newValues.join('\', \'')}')
`, { transaction: t })
// Drop default value (ALTER COLUMN cannot cast default values)
.then(() => queryInterface.sequelize.query(`
ALTER TABLE ${tableName}
ALTER COLUMN ${columnName}
DROP DEFAULT
`, { transaction: t }))
// Change column type to the new ENUM TYPE
.then(() => queryInterface.sequelize.query(`
ALTER TABLE ${tableName}
ALTER COLUMN ${columnName}
TYPE ${newEnumName}
USING (${columnName}::text::${newEnumName})
`, { transaction: t }))
// Drop old ENUM
.then(() => queryInterface.sequelize.query(`
DROP TYPE ${enumName}
`, { transaction: t }))
// Rename new ENUM name
.then(() => queryInterface.sequelize.query(`
ALTER TYPE ${newEnumName}
RENAME TO ${enumName}
`, { transaction: t }))
.then(() => queryInterface.sequelize.query(`
ALTER TABLE ${tableName}
ALTER COLUMN ${columnName}
SET DEFAULT '${defaultValue}'::${enumName}
`, { transaction: t }));
});
}and this is my example migration:
'use strict';
const replaceEnum = require('./utils/replace_enum');
module.exports = {
up: (queryInterface, Sequelize) => {
return replaceEnum({
tableName: 'invoices',
columnName: 'state',
enumName: 'enum_invoices_state',
defaultValue: 'created',
newValues: ['archived', 'created', 'paid'],
queryInterface
});
},
down: (queryInterface, Sequelize) => {
return replaceEnum({
tableName: 'invoices',
columnName: 'state',
enumName: 'enum_invoices_state',
defaultValue: 'draft',
newValues: ['archived', 'draft', 'paid', 'sent'],
queryInterface
});
}
};