Sequelize原生查询插入MySQL数据报错求助

Hey there! Let's break down why your addBudget function is failing and how to fix it properly.

The Core Issues With Your Current Code #

• SQL Syntax Error: When you pass a string value like 'Book' for name, your concatenated SQL ends up looking like this:

  INSERT INTO budget (name, amount) VALUES(Book,10)
  

  Notice Book isn't wrapped in single quotes? MySQL will interpret this as a column name instead of a string value, which throws an immediate error.
• Critical SQL Injection Vulnerability: Directly concatenating user input into SQL statements is a huge security risk. Malicious input could delete tables, steal data, or compromise your entire database.
• Missing Error Handling: Your code doesn't catch errors, so you might not even be seeing the exact error message that would point you to the problem.

Fixed Solutions #

1. Use Parameterized Queries (Safe Native Query) #

Sequelize supports parameterized replacements, which automatically handle value formatting (like adding quotes for strings) and prevent SQL injection:

const addBudget = function(name, amount) {
  sequelize.query(
    'INSERT INTO budget (name, amount) VALUES(:name, :amount)',
    { 
      type: Sequelize.QueryTypes.INSERT,
      replacements: { name, amount } // Let Sequelize handle the value formatting
    }
  )
  .then(function (data) {
    console.log('inserted STEAMER data---> ', data);
  })
  .catch(function (err) { // Always catch errors to debug easily
    console.error('Insert failed: ', err);
  });
};

2. Use Sequelize Model Methods (Recommended Approach) #

Instead of writing raw queries, leverage Sequelize's built-in model methods—this is the intended way to work with the ORM and avoids raw SQL pitfalls:
First, make sure you have a Budget model defined (if you don't already):

const Budget = sequelize.define('budget', {
  name: {
    type: Sequelize.STRING,
    allowNull: false
  },
  amount: {
    type: Sequelize.INTEGER,
    allowNull: false
  }
});

Then rewrite your addBudget function using create():

// Using async/await for cleaner code (you can also use .then/.catch if preferred)
const addBudget = async function(name, amount) {
  try {
    const newBudget = await Budget.create({ name, amount });
    console.log('inserted STEAMER data---> ', newBudget);
  } catch (err) {
    console.error('Insert failed: ', err);
  }
};

Quick Tip #

Always include error handling (like the .catch() block or try/catch with async/await) so you can see exactly what's going wrong when something breaks—this will save you tons of time debugging!

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