Node.js+Knex+MySQL关联表填充的Promise异步问题求助

Hey there! Let's work through your problem with filling the products_parts junction table using Node.js, Knex, and Promises. Async loops can be super confusing when you're new to Promises—trust me, I've been there. Let's break down how to rewrite your code to handle async operations properly, plus make it reusable for processing individual products later.

Why Your Current Code Isn't Working #

The main issues are:

• Using var for loop variables leads to variable hoisting problems—all your async callbacks end up using the last value of k and d.
• The for loop doesn't wait for each Knex query to finish, so you're firing off all database calls at once without tracking completion, which can cause race conditions or incomplete inserts.

Step-by-Step Solution #

Let's rewrite this with async/await (a cleaner syntax for working with Promises) and structure the code for reusability.

1. Create a Reusable Function for Processing a Single Part

First, let's make a function that handles checking if a part exists, inserting it if not, then linking it to the product. This will be useful both for bulk processing and individual products later.

const knex = require('./your-knex-config'); // Import your Knex config

// Assume this function is already defined somewhere
function get_normalised_description(partName) {
  return partName.trim().toLowerCase(); // Example normalization logic
}

async function processPartForProduct(productId, rawPartName) {
  const normalisedPartName = get_normalised_description(rawPartName);

  // Check if the part already exists in the parts table
  const existingPart = await knex('parts')
    .where('name', normalisedPartName)
    .first();

  let partId;
  if (existingPart) {
    partId = existingPart.id;
  } else {
    // Insert the new part and retrieve its auto-generated ID
    const [newPartId] = await knex('parts')
      .insert({ name: normalisedPartName })
      .returning('id');
    partId = newPartId;
  }

  // Link the product and part in the junction table
  await knex('products_parts')
    .insert({ product_id: productId, part_id: partId });
}

2. Create a Function for Processing a Single Product

Next, a function that takes a product object, splits its description, and processes each part. This will let you easily handle individual products later.

async function processProduct(product) {
  const { id: productId, description } = product;
  // Split description and filter out empty/whitespace-only parts
  const partNames = description.split(',').filter(part => part.trim() !== '');

  // Process all parts for this product in parallel (use for...of for sequential processing if needed)
  await Promise.all(
    partNames.map(partName => processPartForProduct(productId, partName))
  );
}

3. Bulk Process All Products

Now, let's fetch all products and process each one. We'll use await to ensure each product is handled properly (choose sequential or parallel processing based on your database load tolerance).

async function bulkProcessAllProducts() {
  try {
    const products = await knex('products').select('id', 'description');

    // Option 1: Process products sequentially (gentler on the database)
    for (const product of products) {
      await processProduct(product);
      console.log(`Successfully processed product ${product.id}`);
    }

    // Option 2: Process all products in parallel (faster, but higher DB load)
    // await Promise.all(products.map(product => processProduct(product)));

    console.log('All products processed successfully!');
  } catch (error) {
    console.error('Error during product processing:', error);
    throw error; // Re-throw if you want upstream error handling
  }
}

// Run the bulk processing
bulkProcessAllProducts();

4. Process Individual Products Later

When you need to handle a single product (or a subset), just call processProduct directly, or use a helper function to fetch and process by ID:

// Example: Process a single product by its ID
async function processSingleProductById(productId) {
  const product = await knex('products')
    .select('id', 'description')
    .where('id', productId)
    .first();

  if (!product) {
    throw new Error(`Product with ID ${productId} not found`);
  }

  await processProduct(product);
  console.log(`Processed product ${productId} successfully`);
}

// Usage example:
// processSingleProductById(456);

Key Improvements #

• Async/Await: Makes the code read like synchronous code, making it easier to follow and debug.
• Reusable Functions: processPartForProduct and processProduct work for both bulk and individual processing scenarios.
• Proper Error Handling: Wrapped in try/catch blocks to catch and log database errors.
• Avoids Race Conditions: Using await ensures each async operation completes before moving on (or Promise.all tracks all parallel operations).

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