CODEWITHSUNDEEP
enumsknex.jspostgresql-9.5
Arjun Nayak
Arjun Nayak

Reputation: 1260

Alter table modify enum in Knex js for Postgresql gives error

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.

Upvotes: 15

Views: 13812

Answers (4)

Joel Colucci
Joel Colucci

Reputation: 461

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();
  });
}

Upvotes: 2

aadilraza339
aadilraza339

Reputation: 69

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';
    `);
  };

Upvotes: 1

Kwame Opare Asiedu
Kwame Opare Asiedu

Reputation: 2355

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.

Upvotes: 3

Mikael Lepist&#246;
Mikael Lepist&#246;

Reputation: 19718

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.

Upvotes: 29

Related Questions