Question: Update enum column types in knex migration

I’m looking to write a migration string to add a new string to the enum column type. I’m trying to add gamma to the service column.

I tried with this code below. This collides because the table and the column already exists.

const table = 'user_associations'

export function up (knex, Promise) {
  return knex.schema.table(table, function (table) {
    table.enu('service', ['alpha', 'beta', 'gamma']).notNullable()
  })
}

export function down (knex, Promise) {
  return knex.schema.table(table, function (table) {
    table.enu('service', ['alpha', 'beta']).notNullable()
  })
}

Issue Analytics

State:
Created 7 years ago
Reactions:24
Comments:16 (2 by maintainers)

Top GitHub Comments

75reactions

hollowaycommented, Jul 5, 2018

Just for anyone wanting to know how the code might look in Postgres 9

const formatAlterTableEnumSql = (
  tableName,
  columnName,
  enums,
) => {
  const constraintName = `${tableName}_${columnName}_check`;
  return [
    `ALTER TABLE ${tableName} DROP CONSTRAINT IF EXISTS ${constraintName};`,
    `ALTER TABLE ${tableName} ADD CONSTRAINT ${constraintName} CHECK (${columnName} = ANY (ARRAY['${enums.join(
      "'::text, '"
    )}'::text]));`,
  ].join('\n');
};


exports.up = async function up(knex) {
  await knex.raw(
    formatAlterTableEnumSql('myTableName', 'myColumnName', [
      'enum1',
      'enum2',
      'enum3',
    ])
  );
};

exports.down = async function down(knex) {
  await knex.raw(
    formatAlterTableEnumSql('myTableName', 'myColumnName', [
      'enum1',
      'enum2',
    ])
  );
};

9reactions

elhigucommented, Mar 5, 2017

enums are done with check constraints currently and .alter() doesn’t have any special treatment for them…

One problem is that .alter() doesn’t know what is the original type of the column and thats why it doesn’t know if it should drop the old check constraint of enum.

As far as I know currently one can change the allowed enum values only with knex.rawby dropping old and creating new check constraint…

I think it could be implemented by some drop old constraint if exist and then create new one if the altered type of the column is enum.

I would love to see this feature being implemented.

Top Results From Across the Web

Update enum column types in knex migration - Stack Overflow

4 Answers 4 · First drop 'service' column in one migration const table = 'user_associations' export.up= async function (knex) { await knex.schema.table(table, ( ......

Question: Update enum column types in knex migration

I'm looking to write a migration string to add a new string to the enum column type. I'm trying to add gamma to...

Alter enum type in migration using Knex + postgres-postgresql

[Solved]-Alter enum type in migration using Knex + postgres-postgresql. Mikael Lepistö proposed to use . raw and it was really only possible way...

Schema Builder | Knex.js

Knex does not provide any way to alter enumerations after creation. To change an enumeration later on you must use Knex.raw, and the...

how change the column of a table of enum type? - Laracasts

Just create a new migration and add the old items + the new items. Copy Code public function up() { Schema::table('table_name ...