Question: Update enum column types in knex migration

[reggi]

Stackoverflow

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

[ebramanti]

Bump on this, any way to use the alter API?

[elhigu]

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.

[ebramanti]

@elhigu I wrote a custom enum query so that I could use Postgres enums, however there is some debate in the Postgres community about check conditions vs enums

[holloway]

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

[Jordan24]

@holloway Thanks for the migration code example. Just what I was looking for. I'm running into one issue though. 'myColumnName' is throwing an error when migrating. The message says "column 'mycolumnname' does not exist". Notice the camelCase is now missing. Any ideas on how to get around that?

[kibertoad]

@Jordan24 Can you share your code?

[Jordan24]

'use strict';

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('item_messages', 'sentMethod', [
      'Web App', 'Email', 'Text Message', 'Log Conversation'
    ])
  );
};

exports.down = async function down(knex) {

  await knex.raw(
    formatAlterTableEnumSql('item_messages', 'sentMethod', [
      'Web App', 'Email', 'Text Message'
    ])
  );
};

[Jordan24]

If I wrap the columnName in double quotes I get a syntax error because the single quotes aren't removed in the raw code.
I think I might drop the function and just write the raw code out twice.

[kibertoad]

@Jordan24 Without the quotes PostgreSQL folds all identifiers to lowercase.

[Jordan24]

Hard coded all but the 'enum' to get around it. Thanks for the tip @kibertoad!

[machineghost]

Any news on this?

If there's no fix coming, could we just get a single line added to the documentation for .enum ... something to the effect of:

Knex does not provide a way to alter migrations after the fact, so you must use Knex.raw and the appropriate command for your database to alter any enumerations after they are created.

?

[kibertoad]

@machineghost PR for this would be most welcome :)

[machineghost]

@machineghost PR for this would be most welcome :)

How does one submit a documentation PR? I don't see any files for the website in the main repo: is there a separate one for the documentation site?

[kibertoad]

Yup! https://github.com/knex/documentation

[liaison-victor]

I added the double quotes to $tableName and $constraintName and it worked!

[sam-super]

This issue is still helping people (me) all this time later 🤣

In case it helps anyone else further, i had to adapt @Jordan24's example like so in typescript (also solves the casing issue):

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

[ianchanning]

@holloway Thanks for the migration code example. Just what I was looking for. I'm running into one issue though. 'myColumnName' is throwing an error when migrating. The message says "column 'mycolumnname' does not exist". Notice the camelCase is now missing. Any ideas on how to get around that?

Just to clarify as I didn't understand at first - I thought there was a problem with the script.

If your column names are snake case like my_column_name then @holloway 's comment script should work fine. It's only if you have myColumnName that you hit problems.

[ianchanning]

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. You will need to something similar if you are only deleting an 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.com/knex/knex/issues/1699#issuecomment-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.com/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,
}

[paul4156]

Any updates on this feature? 🙏

[siefimov]

This issue is still helping people (me) all this time later 🤣

In case it helps anyone else further, i had to adapt @Jordan24's example like so in typescript (also solves the casing issue):

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

Yes, it works, you helped me so much!