SequelizeDatabaseError: type "public.enum_..." does not exist

Question

I am trying to run some db migrations but i keep getting the below error:

DatabaseError [SequelizeDatabaseError]: type "public.enum_companies_accountingSwStatus" does not exist

I have done the same in another project and it doesn't have this problem.

This is really bugging me because I have other columns in the same table using ENUMS and they seem to be working fine. So I am not sure where the problem is.

Here are my files:

Company.model.ts

const {
  employeeSize,
  revenueClass,
  accountingSwStatus,
  accountingSwType,
  paymentFreq,
  freeTrial,
} = enums.Company;

class Company extends Model {
  public id!: number;

  public employeeSize!: Enumerator;

  public revenueClass!: Enumerator;

  public accountingSwStatus!: Enumerator;

  public accountingSwType!: Enumerator;

  public static initModel(sequelize: Sequelize): void {
    Company.init(
      {
        id: {
          type: DataTypes.INTEGER,
          autoIncrement: true,
          primaryKey: true,
        },
        // works fine
        employeeSize: {
          type: DataTypes.ENUM,
          values: getValues(employeeSize),
        },
        // works fine
        revenueClass: {
          type: DataTypes.ENUM,
          values: getValues(revenueClass),
        },
        // error
        accountingSwStatus: {
          type: DataTypes.ENUM,
          values: getValues(accountingSwStatus),
        },
        accountingSwType: {
          type: DataTypes.ENUM,
          values: getValues(accountingSwType),
        },
      },
      {
        sequelize,
        modelName: 'companies',
      }
    );
  }

Migration file

module.exports = {
  up: async (queryInterface, Sequelize) => {
    try {
      await queryInterface.createTable('companies', {
        id: {
          type: Sequelize.INTEGER,
          autoIncrement: true,
          primaryKey: true,
        },

        // works fine
        employeeSize: {
          type: Sequelize.ENUM,
          values: ['500', '100 - 500', '50 - 100', '10 - 50', '< 10'],
        },
        // works fine
        revenueClass: {
          type: Sequelize.ENUM,
          values: [
            '> 500M',
            '100M - 500M',
            '50M - 100M',
            '10M - 50M',
            '5M - 10M',
            '1M - 5M',
            '< 1M',
          ],
        },
        // error
        accountingSwStatus: {
          type: Sequelize.ENUM,
          values: ['new', 'ended', 'failed to link'],
        },
        accountingSwType: {
          type: Sequelize.ENUM,
          values: ['quickbooks', 'xero'],
        },
      });
    } catch (err) {
      console.log(err);
    }
  },
  down: (queryInterface) => queryInterface.dropTable('companies'),
};

My Enum

const Company = {
  employeeSize: {
    TIER_ONE: '500',
    TIER_TWO: '100 - 500',
    TIER_THREE: '50 - 100',
    TIER_FOUR: '10 - 50',
    TIER_FIVE: '< 10',
  },
  revenueClass: {
    TIER_ONE: '> 500M',
    TIER_TWO: '100M - 500M',
    TIER_THREE: '50M - 100M',
    TIER_FOUR: '10M - 50M',
    TIER_FIVE: '5M - 10M',
    TIER_SIX: '1M - 5M',
    TIER_SEVEN: '< 1M',
  },
  accountingSwStatus: {
    NEW: 'new',
    EXISTING: 'existing',
    FAIL_TO_LINK: 'failed to link',
  },
  accountingSwType: {
    QUICKBOOKS: 'quickbooks',
    XERO: 'xero',
  },
  paymentFreq: {
    MONTHLY: 'monthly',
    YEARLY: 'yearly',
  },
  freeTrial: {
    YES: 'yes',
    NO: 'no',
    ENDED: 'ended',
  },
};

export default {
  Company,
};

Answer 1

The error "SequelizeDatabaseError: type 'public.enum_...' does not exist" typically occurs when you are trying to use an enum data type in your Sequelize model, but the enum type is not defined in your PostgreSQL database. To fix this error, you need the steps to do that:

1. Check the name of the enum type that was created in your database. You can do this by running the following SQL command in your PostgreSQL database:

SELECT * FROM pg_type WHERE typcategory = 'E';

2. Most likely there is no enum related to your PostgreSQL database yet. Below example SQL command to create the enum type:

CREATE TYPE enum_companies_accountingSwStatus AS ENUM ('new', 'ended', 'failed to link');

You can adjust according to your needs.

3. Try migrate back.

For additional information, I had the same issue when I ran the first migration and then I adjusted the code by adding 2 column enums. When you try to run the second migration in the same file, what happens is that this issue will only occur with the newest 2 column enums, if there are column enums in the first migrate it won't be a problem because the enums have already been created in the PostgreSQL database.