DEV Community

Cover image for NodeJs Sequelize - Update Enum column's default value
Kyle Foo
Kyle Foo

Posted on • Edited on

3

NodeJs Sequelize - Update Enum column's default value

Intend to modify a Enum column's default value? we can modify it without losing the original data. Here's an use case when original enum value for size column is small, we're changing it to big.

'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    // Create a new enum type, make sure its name isn't the same as current column's enum type
    await queryInterface.sequelize.query(`
      CREATE TYPE enum_new_size AS ENUM ('big', 'medium', 'small');
    `);

    // Add a new temp column with the enum type, setting the new default value as 'big'
    await queryInterface.addColumn('Shirts', 'sizeTemp', {
      type: 'enum_new_size',
      allowNull: false,
      defaultValue: 'big',
    });

    // Copy over the old data to new column and cast to Enum values
    await queryInterface.sequelize.query(`
      UPDATE "Shirts"
      SET "sizeTemp" = CASE
        WHEN "size" = 'small' THEN CAST('small' AS enum_new_size)
        WHEN "size" = 'medium' THEN CAST('medium' AS enum_new_size)
        WHEN "size" = 'big' THEN CAST('big' AS enum_new_size)
        ELSE CAST('big' AS enum_new_size)
      END;
    `);

    // Remove the old column
    await queryInterface.removeColumn('Shirts', 'size');

    // Rename the temporary column
    await queryInterface.renameColumn(
      'Shirts',
      'sizeTemp',
      'size',
    );
  },

  async down(queryInterface, Sequelize) {
    /**
     * Add reverting commands here.
     */
    await queryInterface.removeColumn('Shirts', 'sizeTemp');
  },
};

Enter fullscreen mode Exit fullscreen mode

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay