Modify change table variable in Knex js for Postgresql gives error
I am using knex js and postgresql databases. I used a migration file to create a table knex migrate:make create_car_table
. In this, I added the fuel_type column. table.enu('fuel_type', ['PETROL', 'DIESEL', 'CNG'])
...
Now I need to modify the table and I need these enum values ['HYBRID', 'ELECTRIC', 'PETROL', 'DIESEL']
.
I created another migration file using knex migrate:make alter_car_table
and added the following 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 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 mentioned Knex Js for this.
source to share
The Alter string does not work for enum types in knex 0.13.0
.
Also enums are implemented as check constraints, so you need to recreate to change it.
Something like that:
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 may need to check your constraint name, which was originally created by knex from the database.
This is currently the knex.schema.raw
only way to modify enums.
source to share
First, you need to remove the existing constraint and create a new one with new values. The sample code 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));
');
};
source to share