更新 knex 迁移中的枚举列类型

2022-01-23 00:00:00 enums migration javascript knex.js

我正在寻找编写迁移字符串以将新字符串添加到枚举列类型.我正在尝试将 gamma 添加到 service 列.

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

推荐答案

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 列需要默认值吗?
  • 最好不要使用枚举，因为它不是响应式的...我将在代码中使用微小的整数字段和常量来控制可选字段

相关文章