Sequelize create()及手动INSERT插入订单失败，直接SQL可执行求助

1. 强制捕获错误，排查静默失败 #

你的代码缺少错误捕获逻辑，await抛出的异常可能被静默忽略，导致无法看到错误信息。先给代码添加try/catch：

try {
  let userId = 71;
  let restaurantId = 43;
  let status = 'submitted';
  let items = '[{"itemName":"meal 1","itemDescription":"meal 1","itemPrice":2}, {"itemName":"drink 1","itemDescription":"drink 1","itemPrice":4},{"itemName":"drink 1","itemDescription":"drink 1","itemPrice":4}]';

  let order = await Order.create({
    customerId: userId,
    restaurantId: restaurantId,
    status: status,
    items: items,
  });
  console.log('订单创建成功:', order.toJSON());
} catch (err) {
  console.error('创建订单失败:', err);
}

手动查询版本同样添加捕获：

try {
  const query = `
  INSERT INTO orders (customerId, restaurantId, status, items)
  VALUES (:customerId, :restaurantId, :status, :items);
  `;

  const result = await sequelize.query(query, {
    replacements: {
      customerId: userId,
      restaurantId: restaurantId,
      status: status,
      items: items,
    },
    type: QueryTypes.INSERT
  });
  console.log('手动插入结果:', result);
} catch (err) {
  console.error('手动插入失败:', err);
}

2. 修复JSON字段的传入格式 #

模型中items字段定义为DataTypes.JSON，Sequelize期望传入JavaScript对象而非JSON字符串。当前传入的转义字符串可能导致解析异常：

// 先将转义的JSON字符串转为JavaScript对象
let items = JSON.parse('[{"itemName":"meal 1","itemDescription":"meal 1","itemPrice":2}, {"itemName":"drink 1","itemDescription":"drink 1","itemPrice":4},{"itemName":"drink 1","itemDescription":"drink 1","itemPrice":4}]'.replace(/"/g, '"'));

// 直接传入对象给Order.create
let order = await Order.create({
  customerId: userId,
  restaurantId: restaurantId,
  status: status,
  items: items,
});

手动查询也可直接传对象，Sequelize会自动处理为JSON格式：

const result = await sequelize.query(query, {
  replacements: {
    customerId: userId,
    restaurantId: restaurantId,
    status: status,
    items: items, // 传对象而非字符串
  },
  type: QueryTypes.INSERT
});

3. 检查表名大小写匹配问题 #

Sequelize默认将模型名Order转为复数小写的orders作为表名，但你的手动SQL使用的是Orders（首字母大写）。若数据库表名实际为Orders，且运行环境为Linux（MySQL在Linux下区分表名大小写），会导致Sequelize找不到表。

修改模型定义，显式指定表名：

const Order = sequelize.define('Order', {
  // 字段定义保持不变
}, { 
  timestamps: true,
  tableName: 'Orders' // 与数据库实际表名一致
});

4. 排查事务未提交问题 #

如果代码在事务块中执行，忘记提交会导致数据无法写入数据库：

// 正确事务写法
const t = await sequelize.transaction();
try {
  await Order.create({ 
    customerId: userId,
    restaurantId: restaurantId,
    status: status,
    items: items
  }, { transaction: t });
  await t.commit(); // 必须提交事务
} catch (err) {
  await t.rollback();
  console.error(err);
}

5. 验证数据库连接配置 #

检查Sequelize连接配置，确保连接目标数据库正确，并开启日志查看实际执行的SQL：

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql',
  dialectOptions: {
    supportBigNumbers: true,
    bigNumberStrings: true,
  },
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  },
  logging: console.log // 输出Sequelize实际执行的SQL，与Workbench语句对比
});

开启日志后，可对比Sequelize发送的SQL与Workbench中成功执行的语句，快速定位差异。

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