queryInterface.createTable() does not work with ARRAY(ENUM) with defaultValue

[papb]

What are you doing?

In postgres:

await sequelize.queryInterface.createTable('test', {
    test: {
        type: Sequelize.ARRAY(Sequelize.ENUM(['foo', 'bar', 'baz', 'qux'])),
        allowNull: false,
        defaultValue: ['foo']
    }
});

To Reproduce: Just run the snippet above, no models or other setup needed

2. See error: TypeError: Cannot read property 'getTableName' of undefined

What do you expect to happen?

It should work

What is actually happening?

TypeError: Cannot read property 'getTableName' of undefined

Thrown at line:

https://github.com/sequelize/sequelize/blob/748f69080fdf86b8968e14c02e330b2a1cf41ebc/lib/dialects/postgres/data-types.js#L481

Environment

Dialect:

• [x] postgres

Tested with latest release:

• [x] Yes, specify that version: 5.12.3

Extra info

If the defaultValue is removed, it works:

await sequelize.queryInterface.createTable('test', {
    test: {
        type: Sequelize.ARRAY(Sequelize.ENUM(['foo', 'bar', 'baz', 'qux'])),
        allowNull: false
    }
});

[harrytran998]

. (To watch to be notified the solve!)

[jeremyputeaux]

I experimented the same problem today. Any progress on this issue?

[papb]

@jetaimefrc You can subscribe by clicking the button

@jeremyputeaux No progress that I know of

[rkneel123]

'use strict';

module.exports = { up: async (queryInterface, Sequelize) => { return [ await queryInterface.sequelize.query('CREATE TYPE "enum_tenants_access" AS ENUM (\'INSPECTION\', \'INSTRUCTION\')'), await queryInterface.sequelize.query('ALTER TABLE tenants ADD COLUMN access "enum_tenants_access"[]'), ] },

down: async (queryInterface, Sequelize) => { return [ await queryInterface.removeColumn('tenants', 'access') ] } };

You can use raw query for array of enum type. It will work.

[SqrtMinusOne]

Here is a workaround I found, if that helps anyone

    await queryInterface.sequelize.transaction(async (t) => {
      const rightsEnum = [
        "view",
        "edit",
        "coordinate",
        "coordinateDepartment",
        "approve",
        "assign",
        "viewDraft",
      ];
      await queryInterface.sequelize.query(
        `DROP TYPE IF EXISTS "enum_Faculties_facultyOpopRights"`,
        { transaction: t }
      );
      await queryInterface.createTable(
        "UserAisFaculties",
        {
          <<fields>>
          facultyOpopRights: {
            allowNull: false,
            defaultValue: [],
            type: Sequelize.ARRAY(Sequelize.ENUM(...rightsEnum)),
            fieldName: "facultyOpopRights",
            Model: {
              getTableName() {
                return "UserAisFaculties";
              },
            },
          },
        },
        { transaction: t }
      );
    });

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[LWanjiru]

If anyone is still looking for a solution to this. I encountered it and this is how I went about solving it.

Environment I am using the following:-

• Node - v18.18.0
• npm - 9.8.1
• PostgreSQL@15
• sequelize: "^6.33.0"
• Postico GUI - this actually helped to see the way my columns were structured in the table while debugging.

SOLUTION

migration Just adding the relevant part here so use the migration schema when doing the actual thing

await queryInterface.createTable('Test', {
    myEnum: {
        type: Sequelize.ENUM,
        values: ['foo', 'bar', 'baz', 'qux'],
        field: 'myEnum', 
        defaultValue: 'foo'
        allowNull: false,
    },
 });

model

module.exports = (sequelize, DataTypes) => {
    const Test = sequelize.define('Test', {
        myEnum: {
            type: DataTypes.ENUM,
            values: ['foo', 'bar', 'baz', 'qux'],
            field: 'myEnum', 
            allowNull: false,
            // DO NOT add default value here
       },
    },{
       freezeTableName: true, // This ensures that sequelize will not change your table names to plural ie Tests as a default behaviour
    });
    // Define the associations
                 ..........
    return Test;
}

Post Notes

• It is a good practice to name your table with Capitalization.

• Refrain from using table attributes with a similar name to the table ie if the table is named Test, don't have the field as test. Just for best practice and because sometimes the tools will have certain syntax rules and constraints that might cause weird errors.

• The field was the missing link and the documentation was crappy in making that clear. Now when entering data in your table or with seeders it will look something like this(just the normal way):

  {
    id: 5,
    testName: 'Test1',
    testEmail: 'tester@example.com',
    password: await hashedPassword(process.env.TESTER_PASSWORD), // hash user password from environment for seeder
    myEnum: 'foo', // Following the syntax in the sequelize ENUM docs I linked below
    createdAt: new Date().toUTCString(),
    updatedAt: new Date().toUTCString(),
  },

  And when you use Postico to check the table Test's structure it will have the following details:-

COLUMN NAME: myEnum,
TYPE: "enum_Test_myEnum",
DEFAULT: constant, foo

• What the scanty sequelize docs fail to mention is that similar rules for this Data type: ARRAY(ENUM) - PostgreSQL only. Apply regardless of whether you use the ARRAY(ENUM) or not. Moreso, they fail to mention that when using sync you have to explicitly specify the field in order for the sync to happen successfully. When you fail to set the field, you will see the Table structure(Postico) as above(minus the DEFAULT which will read no default )and will have the following bugs when running migrations or trying to seed the database :-

• error: invalid input value for enum "enum_Test_myEnum": "" - when trying to seed the database or create a user.

• The defaultValue will NOT be set.

I hope this helps.

[SqrtMinusOne]

For anyone still struggling with this, somewhere before the version 6.35.0 fieldName was renamed to field.

The following seems to work:

 roles: {
  type: Sequelize.ARRAY(Sequelize.ENUM({ values: ['confirmer'] })),
  allowNull: false,
  defaultValue: [],
  field: 'roles', // Same as attribute name
  Model: {
    getTableName() {
      return 'DepartmentUsers'; // Table name
    },
  },
},

[robotkoruslan]

Works with the defaultValue

await sequelize.queryInterface.createTable('cars', {
    test: {
        type: Sequelize.ARRAY(Sequelize.ENUM(['foo', 'bar', 'baz', 'qux'])),
        allowNull: false,
        defaultValue: Sequelize.literal("ARRAY['foo']::enum_cars_test[]"),
    }
});