Sequelize插入关联表时breadcrumb_id为空致应用崩溃求助

问题详情 #

应用发生崩溃，Sequelize无法向关联表breadcrumb_to_term_submission插入数据。服务已成功生成id为23的breadcrumb，但插入关联表时breadcrumb_id被识别为null，违反数据库非空约束。

服务日志 #

===> [✓] > BREADCRUMBS_SERVICE > createBreadcrumb ->  successfully created a [create] breadcrumb with id: [23]
Executing (default): INSERT INTO "breadcrumb_to_term_submission" ("breadcrumb_id","submission_id","created_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "breadcrumb_id","submission_id","created_at","updated_at","created_at","updated_at","breadcrumb_id";

  ===> [x] > BREADCRUMBS_SERVICE > createBreadcrumbToTermSubmission ->  error creating a breadcrumb with id: [23] relationship to term with id: [35] with error: [SequelizeDatabaseError: null value in column "breadcrumb_id" of relation "breadcrumb_to_term_submission" violates not-null constraint]
node:internal/process/promises:279
            triggerUncaughtException(err, true /* fromPromise */);

错误日志 #

original: error: null value in column "breadcrumb_id" of relation "breadcrumb_to_term_submission" violates not-null constraint
      at Parser.parseErrorMessage (/Users/xxx/backend/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/Users/xxx/backend/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/Users/xxx/backend/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/Users/xxx/backend/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 314,
    severity: 'ERROR',
    code: '23502',
    detail: 'Failing row contains (null, 35, 2022-11-28 17:23:55.57+00, 2022-11-28 17:23:55.57+00).',
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: 'public',
    table: 'breadcrumb_to_term_submission',
    column: 'breadcrumb_id',
    dataType: undefined,
    constraint: undefined,
    file: 'execMain.c',
    line: '1953',
    routine: 'ExecConstraints',
    sql: 'INSERT INTO "breadcrumb_to_term_submission" ("breadcrumb_id","submission_id","created_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "breadcrumb_id","submission_id","created_at","updated_at","created_at","updated_at","breadcrumb_id";',
    parameters: [
      null,
      35,
      '2022-11-28 17:23:55.570 +00:00',
      '2022-11-28 17:23:55.570 +00:00'
    ]
  },
  sql: 'INSERT INTO "breadcrumb_to_term_submission" ("breadcrumb_id","submission_id","created_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "breadcrumb_id","submission_id","created_at","updated_at","created_at","updated_at","breadcrumb_id";',
  parameters: [
    null,
    35,
    '2022-11-28 17:23:55.570 +00:00',
    '2022-11-28 17:23:55.570 +00:00'
  ]
}

执行的SQL语句 #

INSERT INTO "breadcrumb_to_term_submission" ("breadcrumb_id","submission_id","created_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "breadcrumb_id","submission_id","created_at","updated_at","created_at","updated_at","breadcrumb_id";

模型定义 #

breadcrumb_to_term_submission #

module.exports = (sequelize, DataTypes) => {
    const BreadcrumbToUser = sequelize.define('BreadcrumbToTermSubmission', {
        breadcrumb_id: DataTypes.INTEGER,
        submission_id: DataTypes.INTEGER,
        created_at: DataTypes.DATE,
        updated_at: DataTypes.DATE
    }, {
        freezeTableName: true,
        tableName: 'breadcrumb_to_term_submission',
        underscored: true
    });
    BreadcrumbToUser.associate = function (models) {
        BreadcrumbToUser.belongsTo(models.TermSubmission);
        BreadcrumbToUser.belongsTo(models.Breadcrumb);
    };
    return BreadcrumbToUser;
};

breadcrumb #

module.exports = (sequelize, DataTypes) => {
    let Breadcrumb = sequelize.define('Breadcrumb', {
            type: DataTypes.STRING,
            created_at: DataTypes.DATE,
            updated_at: DataTypes.DATE,
            before: DataTypes.TEXT,
            after: DataTypes.TEXT
        },
        {
            freezeTableName: true,
            tableName: 'breadcrumb',
            underscored: true
        });
    Breadcrumb.associate = function (models) {
        Breadcrumb.belongsToMany(models.Term, {through: 'BreadcrumbToTerm'});
        Breadcrumb.hasMany(models.BreadcrumbToTerm);

        Breadcrumb.belongsToMany(models.TermSubmission, {through: 'BreadcrumbToTermSubmission'});
        Breadcrumb.hasMany(models.BreadcrumbToTermSubmission);

        Breadcrumb.belongsToMany(models.User, {through: 'BreadcrumbToUser'});
        Breadcrumb.hasMany(models.BreadcrumbToUser);
    };
    return Breadcrumb;
};

termSubmission #

module.exports = (sequelize, DataTypes) => {
    let TermSubmission = sequelize.define('TermSubmission', {
        user_id: DataTypes.INTEGER,
        term_id: DataTypes.INTEGER,
        acronym: DataTypes.STRING(60),
        definition: DataTypes.TEXT,
        links: DataTypes.JSON,
        status: DataTypes.INTEGER,
        deprecate: DataTypes.BOOLEAN,
        edit: DataTypes.BOOLEAN,
        approved_at: DataTypes.DATE,
        approved_by: DataTypes.STRING,
        rejected_at: DataTypes.DATE,
        rejected_by: DataTypes.STRING,
        created_at: DataTypes.DATE,
        updated_at: DataTypes.DATE,
        updated_by: DataTypes.STRING,
        submission_notes: DataTypes.TEXT,
        rejection_notes: DataTypes.TEXT
    }, {
        freezeTableName: true,
        tableName: 'term_submissions',
        underscored: true
    });
    TermSubmission.associate = function (models) {
        TermSubmission.belongsTo(models.User);

        TermSubmission.belongsToMany(models.Group, {through: 'TermSubmissionGroup'});
        TermSubmission.hasMany(models.TermSubmissionGroup);
        TermSubmission.belongsTo(models.Term);

        // Breadcrumbs relations
        TermSubmission.hasMany(models.BreadcrumbToTermSubmission, {foreignKey: 'submission_id'});
        TermSubmission.belongsToMany(models.Breadcrumb, {through: 'BreadcrumbToTermSubmission', foreignKey: 'submission_id'});
    };
    console.log(TermSubmission.getUser)
    return TermSubmission;
};

解决方案 #

1. 修正关联表模型定义 #

关联表BreadcrumbToTermSubmission需要明确外键的非空约束，同时在belongsTo关联中指定对应外键，确保Sequelize正确映射字段：

module.exports = (sequelize, DataTypes) => {
    const BreadcrumbToTermSubmission = sequelize.define('BreadcrumbToTermSubmission', {
        breadcrumb_id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            references: {
                model: 'breadcrumb',
                key: 'id'
            }
        },
        submission_id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            references: {
                model: 'term_submissions',
                key: 'id'
            }
        },
        created_at: DataTypes.DATE,
        updated_at: DataTypes.DATE
    }, {
        freezeTableName: true,
        tableName: 'breadcrumb_to_term_submission',
        underscored: true
    });
    BreadcrumbToTermSubmission.associate = function (models) {
        BreadcrumbToTermSubmission.belongsTo(models.TermSubmission, { foreignKey: 'submission_id' });
        BreadcrumbToTermSubmission.belongsTo(models.Breadcrumb, { foreignKey: 'breadcrumb_id' });
    };
    return BreadcrumbToTermSubmission;
};

2. 修正Breadcrumb模型的关联配置 #

在belongsToMany和hasMany关联中，明确指定外键对应关联表的breadcrumb_id，避免Sequelize自动生成错误的字段映射：

Breadcrumb.associate = function (models) {
    // 其他关联保持不变
    Breadcrumb.belongsToMany(models.TermSubmission, { 
        through: 'BreadcrumbToTermSubmission',
        foreignKey: 'breadcrumb_id'
    });
    Breadcrumb.hasMany(models.BreadcrumbToTermSubmission, { 
        foreignKey: 'breadcrumb_id'
    });
    // 其他关联保持不变
};

3. 使用Sequelize关联方法创建关系 #

避免手动插入关联表数据，改用Sequelize提供的关联方法自动填充关联字段：

// 示例：创建breadcrumb后关联TermSubmission
const breadcrumb = await models.Breadcrumb.create(breadcrumbData);
await breadcrumb.addTermSubmission(35); // 35为目标submission_id

4. 验证数据库表结构 #

确保数据库中breadcrumb_to_term_submission表的breadcrumb_id字段已设置非空约束，若未同步可执行以下SQL：

ALTER TABLE breadcrumb_to_term_submission 
ALTER COLUMN breadcrumb_id SET NOT NULL;

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