Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
2 tasks done
papb opened this issue Aug 5, 2019 · 9 comments
Open
2 tasks done
Labels
dialect: postgres For issues and PRs. Things that involve PostgreSQL (and do not involve all dialects). Great SSCCE This issue has a great SSCCE/MCVE/reprex posted and therefore deserves extra attention! :) status: understood For issues. Applied when the issue is understood / reproducible. type: bug

Comments

@papb
Copy link
Member

papb commented Aug 5, 2019

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:

Utils.generateEnumName(options.field.Model.getTableName(), options.field.fieldName),

Environment

Dialect:

  • postgres

Tested with latest release:

  • 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
    }
});
@papb papb added type: bug dialect: postgres For issues and PRs. Things that involve PostgreSQL (and do not involve all dialects). status: understood For issues. Applied when the issue is understood / reproducible. labels Aug 5, 2019
@papb papb added the Great SSCCE This issue has a great SSCCE/MCVE/reprex posted and therefore deserves extra attention! :) label Aug 5, 2019
@harrytran998
Copy link

. (To watch to be notified the solve!)

@jeremyputeaux
Copy link

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

@papb
Copy link
Member Author

papb commented Sep 4, 2019

@jetaimefrc You can subscribe by clicking the button

image

@jeremyputeaux No progress that I know of

@rkneel123
Copy link

'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
Copy link

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
Copy link
Contributor

github-actions bot commented Nov 8, 2021

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. 🙂

@github-actions github-actions bot added the stale label Nov 8, 2021
@WikiRik WikiRik removed the stale label Nov 15, 2021
@LWanjiru
Copy link

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
Copy link

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
Copy link

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[]"),
    }
});

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
dialect: postgres For issues and PRs. Things that involve PostgreSQL (and do not involve all dialects). Great SSCCE This issue has a great SSCCE/MCVE/reprex posted and therefore deserves extra attention! :) status: understood For issues. Applied when the issue is understood / reproducible. type: bug
Projects
None yet
Development

Successfully merging a pull request may close this issue.

8 participants