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

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

• javascript
• enums
• migration
• knex.js

• try to drop column first in one migration and create it on a second migration. if everything else fails, try knex.raw with pure sql – Sombriks Commented Sep 27, 2016 at 3:06
• Also created as a GitHub issue: github./knex/knex/issues/1699 – icc97 Commented Jan 26, 2023 at 11:49

5 Answers 5

const tableName = 'user_associations'

export function up (knex, Promise) {
  let existRows;
  return knex.select()
  .from(tableName)
  .then((rows) => {
    existRows = rows
    return knex.schema.table(tableName, (table) => table.dropColumn('service'))
  })
  .then(() => knex.schema.table(tableName, (table) => table.enu('service', ['alpha', 'beta', 'gamma']).notNullable().default('alpha')))
  .then(() => {
    return Promise.all(existRows.map((row) => {
      return knex(tableName)
      .update({ service: row.service })
      .where('id', row.id)
    }))
  })
}

export default down(kenx, Promise) {
  let existRows;
  return kenx.select()
  .from(tableName)
  .then((rows) => {
    existRows = rows
    return knex.schema.table(tableName, (table) => table.dropColumn('service'))
  })
  .then(() => knex.schema.table(tableName, (table) => table.enu('service', ['alpha', 'beta']).notNullable().default('alpha')))
  .then(() => {
    return Promise.all(existRows.map((row) => {
      return knex(tableName)
      .update({ service: row.service === 'gamma' ? 'alpha' : row.service })
      .where('id', row.id)
    }))
  })
}

• notNull column need a default value ?
• better not use enum 'cause it's not reactive...I'll use tiny integer field and constants in code to control optional field

As of 2018-09-05, this is still an open knex issue #1699: https://github./knex/knex/issues/1699 (I believe you opened it!). If you're using PostgreSQL, then this is what I'd do:

1. open up $ psql
2. connect to your database > \c ...
3. > \dt to see all the tables
4. > \d user_associations to see all the info for that table
5. find what the name of the check is. It should be user_associations_service_check

Then back in your migration:

exports.up = knex =>
    knex.raw(`
        ALTER TABLE ONLY user_associations
        DROP CONSTRAINT user_associations_service_check;

        ALTER TABLE ONLY user_associations
        ADD CONSTRAINT user_associations_service_check
        CHECK ("service" = ANY (ARRAY['alpha'::text, 'beta'::text, 'gamma'::text]));
    `)

exports.down = knex =>
    knex.raw(`
        ALTER TABLE ONLY user_associations
        DROP CONSTRAINT user_associations_service_check;

        ALTER TABLE ONLY user_associations
        ADD CONSTRAINT user_associations_service_check
        CHECK ("service" = ANY (ARRAY['alpha'::text, 'beta'::text));
    `)

This ment on your knexjs issue #1699 has a clever utility function for acplishing this:

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

Carrying on from @rpearce's answer, as of Jan 2023 there is still no direct solution for this. It's even been added to the documentation that there is no Knex solution for this:

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

-- Knex.js .enum doc

As mentioned by @rpearce, there is a good workaround solution for Postgres only in the related GitHub issue.

The core being:

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

This gives you a SQL string that you can run in knex.raw.

However this wasn't enough for me because I need to add & delete an enum not just add one.

I wrote this ment on the GitHub #1699 issue and thought I'd share it here too.

With @holloway 's script I got the following error:

migration failed with error: ALTER TABLE screenings ADD CONSTRAINT screenings_status_check CHECK (status = ANY (ARRAY['CREATED'::text, 'NEEDS_REVIEW'::text, 'ARCHIVED'::text, 'FAILED'::text])); - check constraint "screenings_status_check" of relation "screenings" is violated by some row

This is because I am renaming NEEDS_REVIEW status to REVIEW. Instead of just adding a new enum, I am adding a new enum, but also deleting an old enum.

What I needed to do was roughly DROP CONSTRAINT , UPDATE, ADD CONSTRAINT. With some minor refactoring I was able to re-use the core function.

Also I did a further refactor to the code for the constraint array:

ARRAY[${enums.map((e) => `'${e}'::text`).join(',')}]

This removes the duplicate ::text and having to wrap it all in '' - I just spotted that @sam-super did something similar in his code.

const oldEnum = { my_column_name: 'OLD_VALUE' }
const newEnum = { my_column_name: 'NEW_VALUE' }

const enumConstraintName = (tableName, columnName) =>
    `${tableName}_${columnName}_check`
const dropEnumConstraint = (tableName, columnName) =>
    `ALTER TABLE ${tableName} DROP CONSTRAINT IF EXISTS ${enumConstraintName(
        tableName,
        columnName
    )};`
const addEnumConstraint = (tableName, columnName, enums) =>
    `ALTER TABLE ${tableName} ADD CONSTRAINT ${enumConstraintName(
        tableName,
        columnName
    )} CHECK (${columnName} = ANY (ARRAY[${enums
        .map((e) => `'${e}'::text`)
        .join(',')}]));`

/**
 * SQL to replace Knex enum constraint for Postgres
 *
 * @param {string} tableName Postgres table name - assumes lowercase / snake_case
 * @param {string} columnName Postgres column name - assumes lowercase / snake_case
 * @param {string[]} enums Enum values
 * @returns {string} Raw SQL statement
 * {@link https://github./knex/knex/issues/1699#issuement-402603481}
 *
 * Get this error if you don't use this function:
 *
 * > migration file "20230126110521_migration.js" failed
 * > migration failed with error: update "my_table_name" set "my_column_name" = $1 where "my_column_name" = $2 - new row for relation "my_table_name" violates check constraint "my_table_name_my_column_name_check"
 * {@link https://stackoverflow./questions/39714345/update-enum-column-types-in-knex-migration}
 */
const formatAlterTableEnumSql = (tableName, columnName, enums) =>
    [
        dropEnumConstraint(tableName, columnName),
        addEnumConstraint(tableName, columnName, enums),
    ].join('\n')

export const up = async (knex) => {
    // OLD_VALUE => NEW_VALUE
    await knex
        .raw(dropEnumConstraint('my_table_name', 'my_column_name'))
        .then(() => knex('my_table_name').where(oldEnum).update(newEnum))
        .then(() =>
            knex.raw(
                addEnumConstraint('my_table_name', 'my_column_name', [
                    'enum1',
                    'enum2_new',
                    'enum3',
                ])
            )
        )
}

export const down = async (knex) => {
    // NEW_VALUE => OLD_VALUE
    await knex
        .raw(dropEnumConstraint('my_table_name', 'my_column_name'))
        .then(() => knex('my_table_name').where(newEnum).update(oldEnum))
        .then(() =>
            knex.raw(
                addEnumConstraint('my_table_name', 'my_column_name', [
                    'enum1',
                    'enum2',
                    'enum3',
                ])
            )
        )
}

export default {
    up,
    down,
}

Actually, according to Knex documentation we could user alter() statement:

const table = 'user_associations'

export async function up (knex) {
  return knex.schema.alterTable(table, (table) => {
    table.enu('service', ['alpha', 'beta', 'gamma']).notNullable().alter()
  })
}

export async function down (knex) {
  return knex.schema.alterTable(table, (table) => {
    table.enu('service', ['alpha', 'beta']).notNullable().alter()
  })
}

Comment mentioned by @Sombriks also worked fine for me-

1. First drop 'service' column in one migration

   const table = 'user_associations'
   
   export.up= async function (knex) {
     await knex.schema.table(table, (table)=> {
       table.dropColumn('service');
     });
   }
   
   export.down= async function (knex) {
     await knex.schema.table(table, (table)=> {
       table.enu('service', ['alpha', 'beta').notNullable()
     });
   } ```
   
   

2. Add option in 'service' column in another migration

   const table = 'user_associations'
   
   export.up= async function (knex) {
     await knex.schema.table(table, (table)=> {
       table.enu('service', ['alpha', 'beta', 'gamma']).notNullable()
     });
   }
   
   export.down= async function (knex) {
     await knex.schema.table(table, (table)=> {
       table.dropColumn('service');
     });
   } ```