Alter table modify enum in Knex js for Postgresql gives error

Question

I am using knex js and postgresql database. I have used a migration file to create a table knex migrate:make create_car_table. In this I have added a column fuel_type. table.enu('fuel_type', ['PETROL', 'DIESEL', 'CNG']).

Now I need to alter the table and I need these enum values ['HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL'].

I have created another migration file using knex migrate:make alter_car_table and added the below code

exports.up = function(knex, Promise) {
    return knex.schema.alterTable('car', function (table) {
        table.enu('fuel_type', ['HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL']).alter();
    });
};

exports.down = function(knex, Promise) {
    return knex.schema.alterTable('car', function (table) {
        table.enu('fuel_type', ['PETROL', 'DIESEL', 'CNG']).alter();
    });
};

when I run knex migrate:latest I get the below error.

Knex:warning - migrations failed with error: alter table "car" alter column "fuel_type" type text check ("fuel_type" in ('HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL')) using ("fuel_type"::text check ("fuel_type" in ('HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL'))) - syntax error at or near "check"

I have refered Knex Js for this.

Schwarz

Answer 1

Alter column does not work for enum types in knex 0.13.0.

Also enums are implemented as check constraints, so to change it you need to recreate the.

Something like this:

exports.up = function(knex, Promise) {
  return knex.schema.raw(`
    ALTER TABLE "car"
    DROP CONSTRAINT "car_fuel_type_check",
    ADD CONSTRAINT "car_fuel_type_check" 
    CHECK (fuel_type IN ('HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL'))
  `);
};

exports.down = function(knex, Promise) { ... };

You might need to check your constraint name that was originally generated by knex from the DB.

Currently knex.schema.raw is the only way to modify enums.

Gratitude

Thanks @mikael I changed the constraint name and it worked. – Schwarz

Answer 2

You first need to drop the existing constraint, and create a new one with the new values. The code sample below should help.

exports.up = function(knex, Promise) {
  return knex.schema.raw(`
    ALTER TABLE "car" DROP CONSTRAINT "car_fuel_type_check";
    ALTER TABLE "car" ADD CONSTRAINT "car_fuel_type_check" CHECK (fuel_type IN ('HYBRID'::text, 'ELECTRIC'::text, 'PETROL'::text, 'DIESEL'::text))
  `);
};

// The reverse migration is similar
exports.down = function(knex, Promise) {
  return knex.schema.raw(`
    ALTER TABLE "car" DROP CONSTRAINT "car_fuel_type_check";
    ALTER TABLE "car" ADD CONSTRAINT "car_fuel_type_check" CHECK (fuel_type IN ('PETROL'::text, 'DIESEL'::text, 'CNG'::text));
  `);
};

I'm assuming your constraint name is car_fuel_type_check. If not, you should replace car_fuel_type_check with your constraint name.

Aventurine

Answer 3

The PostgreSQL Knex.js "enum" type is shorthand for:

1. Creating a field with a "text" data type
2. Add a "checkIn" constraint on the field.

Therefore to alter we can use the Knex.js dropChecks method and the alter command to modify the "enum" type.

See below examples:

Example of what the "enum" data type does:

export async function up(knex: Knex): Promise<void> {
  // Using enum shorthand
  await knex.schema.createTable('cars', (table) => {
    table.enum('type', ['sedan', 'suv']);
  });

  // Doing things manually
  await knex.schema.createTable('cars', (table) => {
    table.text('type').checkIn(['sedan', 'suv'], 'cars_type_check');
  });
}

Example of altering manually altering a enum field

export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable('cars', (table) => {
    // Drop the existing checkIn
    table.dropChecks('cars_type_check');

    // Add a new checkIn
    table.text('type').checkIn(['sedan', 'suv', 'pickup'], 'cars_type_check').alter();
  });
}

Steamship

Answer 4

We can update enum's value by using knex migrate.

exports.up = async function(knex) {
    return knex.raw(`
    ALTER TABLE organizations 
    CHANGE subscriptionStatus subscriptionStatus enum('Past Due','Paid','Free Trial','Free Trial Expired','Pre Trial','ddd') DEFAULT 'Pre Trial';
    `);
  };
  
  exports.down = async function(knex) {
    return knex.raw(`
    ALTER TABLE organizations 
    CHANGE subscriptionStatus subscriptionStatus enum('Past Due','Paid','Free Trial','Free Trial Expired','Pre Trial') DEFAULT 'Pre Trial';
    `);
  };

Distefano