解决Knex/PostgreSQL中Upsert方法报错update is not a function

环境信息 #

• Knex版本：knex@2.3.0
• PostgreSQL客户端：pg@8.8.0
• 需求：编写种子文件实现Upsert逻辑，首次运行插入新数据，重复运行时更新已有数据

报错的Upsert实现代码 #

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.seed = async function (knex) {
  // Inserts a new questionnaire or updates an existing one
  const [questionnaire] = await knex('questionnaires')
    .insert({
      title: 'User Consent Questionnaire',
      description:
        'This questionnaire is used to obtain user consent to the privacy',
      eligibility_criteria: '[{"privacy_policy":"yes"}]',
      created_by: '0',
      type: 'USER_CONSENT',
      // locale: 'en_US', by default is already as en_US
      status: 'ACTIVE',
      // study_id: '',  by default is NULL
    })
    .onConflict(['type'])
    // Running the seed again the same row will be updated but with new values
    // The values can be edited right now are used as a starting point 
    // to ensure the correct functionality of the seed
    .update({
      title: 'Privacy Consent', 
      description: 'The user consenting to the privacy agreement',
      // eligibility_criteria: '[{}]'
      // created_by: '0',
      // locale: 'en_US',
      // study_id: '',  by default is NULL
    })
    .returning('id');

  const questionnaire_id = questionnaire.id;

  // Inserts a new question or updates an existing one
  await knex('questions')
    .insert({
      questionnaire_id,
      question: 'Do you consent to the privacy requirements?',
      description: 'privacy consent',
      type: 'yesno',
      key: 'privacy_policy',
      sort_order: 0,
    })
    .onConflict(['questionnaire_id', 'key'])
    .update({
      question: 'Do you agree to the privacy policy?',
      description: 'Privacy policy agreement',
    });

  return await knex('assigned_questionnaires').insert({
    questionnaire_id,
    eligible: 'NEVER_CHECKED',
  });
};

运行报错信息 #

Error while executing "/test/seeds/adding_default_user_consent_questionnaire.js" 
seed: knex(...).insert(...).onConflict(...).update is not a function

已实现的替代方案（通过判断数据存在性实现Upsert） #

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.seed = async function (knex) {
  let questionnaire_id;

  // Check if a USER_CONSENT questionnaire already exists
  let [questionnaire] = await knex('questionnaires')
    .where({ type: 'USER_CONSENT' })
    .select('id');

  // If the questionnaire exists, update it; otherwise, insert a new one
  if (questionnaire) {
    questionnaire_id = questionnaire.id;
    await knex('questionnaires')
      .where({ id: questionnaire_id })
      .update({
        title: 'User Consent Questionnaire',
        description: 'This questionnaire is used to obtain user consent to the privacy',
        eligibility_criteria: '[{"agree_privacy":"yes"}]',
        type: 'USER_CONSENT',
        status: 'ACTIVE',
      });
  } else {
    [{ id: questionnaire_id }] = await knex('questionnaires')
      .insert({
        title: 'User Consent Questionnaire',
        description: 'This questionnaire is used to obtain user consent to the privacy',
        eligibility_criteria: '[{"agree_privacy":"yes"}]',
        type: 'USER_CONSENT',
        status: 'ACTIVE',
      })
      .returning('id');
  }

  // Check if the consent question already exists
  let [question] = await knex('questions')
    .where({
      questionnaire_id,
      key: 'agree_privacy'
    })
    .select('id');

  // If the question exists, update it; otherwise, insert a new one
  if (question) {
    await knex('questions')
      .where({ id: question.id })
      .update({
        questionnaire_id,
        question: 'Do you consent to the privacy requirements?',
        description: 'privacy consent',
        type: 'yesno',
        key: 'agree_privacy',
        sort_order: 1,
      });
  } else {
    await knex('questions')
      .insert({
        questionnaire_id,
        question: 'Do you consent to the privacy requirements?',
        description: 'privacy consent',
        type: 'yesno',
        key: 'agree_privacy',
        sort_order: 1,
      });
  }

  // Check if the assigned questionnaire already exists
  let [assignedQuestionnaire] = await knex('assigned_questionnaires')
    .where({ questionnaire_id })
    .select('id');

  // If the assigned questionnaire exists, update it; otherwise, insert a new one
  if (assignedQuestionnaire) {
    return await knex('assigned_questionnaires')
      .where({ id: assignedQuestionnaire.id })
      .update({
        questionnaire_id,
        eligible: 'NEVER_CHECKED',
      });
  } else {
    return await knex('assigned_questionnaires').insert({
      questionnaire_id,
      eligible: 'NEVER_CHECKED',
    });
  }
};

报错原因解析 #

你原代码报错的核心原因是Knex针对PostgreSQL的Upsert语法，在onConflict()之后需要用merge()方法来定义冲突时的更新字段，而不是update()。

Knex的API设计中，update()是独立的查询方法（用于单独的UPDATE语句），并不属于onConflict()链式调用的一部分。对应PostgreSQL的INSERT ... ON CONFLICT ... DO UPDATE SET语法，Knex提供的是.merge([updateFields])方法来实现。

把原代码中的.update({...})替换成.merge({...})，就能正确执行Upsert逻辑，无需通过查询判断数据是否存在的方式实现。

比如修正后的问卷Upsert代码片段：

const [questionnaire] = await knex('questionnaires')
  .insert({
    title: 'User Consent Questionnaire',
    description: 'This questionnaire is used to obtain user consent to the privacy',
    eligibility_criteria: '[{"privacy_policy":"yes"}]',
    created_by: '0',
    type: 'USER_CONSENT',
    status: 'ACTIVE',
  })
  .onConflict(['type'])
  .merge({
    title: 'Privacy Consent', 
    description: 'The user consenting to the privacy agreement',
  })
  .returning('id');

内容的提问来源于stack exchange，提问作者Jakub