NodeJS多表批量插入及外键约束报错问题求助

Hey there! Let's work through these two issues step by step, and I'll also fix some critical security and reliability gaps in your existing code.

First off, your current code has a big SQL injection risk because you're directly concatenating user input into SQL queries. We'll fix that with parameterized queries, plus use database transactions to ensure both inserts succeed or fail together (so you don't end up with a translation entry without its corresponding translation_to_lang record).

Here's the revised code with the multi-table insert logic:

app.post('/api/domain/:id/translation.json', function (req, res) {
  const domain_id = req.params.id;
  const { key, lang_id, value } = req.body;

  // Start a database transaction to ensure atomicity
  connection.beginTransaction(function(err) {
    if (err) { 
      return res.status(500).send({ code: 500, message: 'Failed to start transaction' });
    }

    // First insert into translation table
    connection.query(
      'INSERT INTO translation (`key`, `domain_id`) VALUES (?, ?)',
      [key, domain_id],
      function(error, translationResult) {
        if (error) {
          // Rollback if first insert fails
          return connection.rollback(function() {
            // Handle foreign key error here (we'll cover this in issue 2)
            if (error.code === 'ER_NO_REFERENCED_ROW_2') {
              return res.status(400).send({ code: 400, message: 'Invalid domain_id: No matching domain exists' });
            }
            throw error;
          });
        }

        const translationId = translationResult.insertId;

        // Second insert into translation_to_lang table
        connection.query(
          'INSERT INTO translation_to_lang (translation_id, lang_id, value) VALUES (?, ?, ?)',
          [translationId, lang_id, value],
          function(error) {
            if (error) {
              // Rollback if second insert fails
              return connection.rollback(function() {
                throw error;
              });
            }

            // Commit the transaction if both inserts succeed
            connection.commit(function(err) {
              if (err) {
                return connection.rollback(function() {
                  throw err;
                });
              }

              // Fetch the inserted translation data to return
              connection.query(
                'SELECT * FROM translation WHERE id = ?',
                [translationId],
                function(error, data_insert) {
                  if (error) throw error;
                  setTimeout(function () {
                    res.status(201).send({ code: 201, message: 'success', datas: data_insert });
                  }, 1000);
                }
              );
            });
          }
        );
      }
    );
  });
});

Key improvements here: #

• Parameterized queries: Replaced string concatenation with ? placeholders to eliminate SQL injection risks.
• Database transactions: Used beginTransaction, commit, and rollback to ensure both inserts are atomic—no partial data if one insert fails.
• Cleaner variable handling: Destructured req.body for better readability.

The foreign key constraint error occurs because you're trying to insert a translation linked to a non-existent domain. There are two reliable ways to fix this:

Option 1: Pre-check if the domain exists (recommended for clarity) #

Before running any inserts, query the domain table to verify the domain_id is valid:

app.post('/api/domain/:id/translation.json', function (req, res) {
  const domain_id = req.params.id;
  const { key, lang_id, value } = req.body;

  // First verify the domain exists
  connection.query(
    'SELECT id FROM domain WHERE id = ?',
    [domain_id],
    function(error, domainResult) {
      if (error) throw error;

      if (domainResult.length === 0) {
        return setTimeout(function () {
          res.status(400).send({ code: 400, message: 'Bad Request: Invalid domain_id' });
        }, 1000);
      }

      // Proceed with transaction and inserts as shown in issue 1...
      connection.beginTransaction(function(err) {
        // ... rest of the transaction code here
      });
    }
  );
});

Option 2: Catch the foreign key error directly #

If you prefer not to add an extra query, you can catch the ER_NO_REFERENCED_ROW_2 error code when it occurs during the insert, then return a 400 response:

// Inside the rollback block for the first insert
if (error.code === 'ER_NO_REFERENCED_ROW_2') {
  return res.status(400).send({ code: 400, message: 'Bad Request: Invalid domain_id - no matching domain exists' });
}

Why this works: #

MySQL throws the ER_NO_REFERENCED_ROW_2 code specifically when a foreign key constraint fails because the referenced row doesn't exist. By checking for this code, we can return a user-friendly 400 error instead of letting the server crash with an unhandled exception.

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