批量插入需确保checkid/promise解析完成及公司批量导入问题咨询

Hey there! Let’s break down how to solve this batch upload problem with pg-promise—since you already know Vitaly’s patterns are solid, we can build right on that foundation. The core goal here is to safely check/create companies first, then link their IDs to your batch records, all while making sure promises resolve before moving to the next step.

1. Atomic "Get or Create" Company Function #

First, we need a reusable function that either fetches an existing company’s ID or creates a new one—atomically. This avoids race conditions (critical for batch operations!). The best way to do this is with a CTE query that handles both cases in one go:

const pgp = require('pg-promise')();

async function getOrCreateCompany(db, companyData) {
  // Assume your companies table has a UNIQUE constraint on 'name' (adjust to your unique field)
  return db.one(`
    WITH new_company AS (
      INSERT INTO companies (name, industry, location) -- Add your actual company fields
      VALUES ($(name), $(industry), $(location))
      ON CONFLICT (name) DO NOTHING
      RETURNING id
    )
    SELECT id FROM new_company
    UNION ALL
    SELECT id FROM companies WHERE name = $(name)
    LIMIT 1
  `, companyData);
}

Pro tip: Without that unique constraint on your company identifier (like name), the ON CONFLICT clause won’t work—so make sure you set that up first in your table schema.

2. Process Your Batch Efficiently #

You’ve got two solid options here, depending on your batch size and how many duplicate companies are in your records:

Option A: Sequential Processing (Small Batches) #

If your batch is small, processing each record one after another is simple and safe. Using await ensures each promise resolves before moving to the next step:

async function processSmallBatch(db, records) {
  for (const record of records) {
    // First, get or create the company
    const companyId = await getOrCreateCompany(db, record.company);
    
    // Then insert the record linked to the company ID
    await db.none(`
      INSERT INTO your_records_table (company_id, order_date, amount) -- Your record fields
      VALUES ($(companyId), $(orderDate), $(amount))
    `, { companyId, ...record });
  }
}

Option B: Parallel Deduplication (Large Batches) #

For bigger batches with lots of repeated companies, deduplicate first to avoid redundant database calls. This is way faster:

async function processLargeBatch(db, records) {
  // Step 1: Deduplicate companies (use your unique field as the key)
  const uniqueCompanies = [...new Map(records.map(r => [r.company.name, r.company])).values()];
  
  // Step 2: Resolve all company IDs in parallel
  const companyIdMap = await Promise.all(
    uniqueCompanies.map(async comp => {
      const id = await getOrCreateCompany(db, comp);
      return [comp.name, id];
    })
  ).then(entries => new Map(entries));
  
  // Step 3: Attach company IDs to all records
  const recordsWithIds = records.map(record => ({
    ...record,
    companyId: companyIdMap.get(record.company.name)
  }));
  
  // Step 4: Bulk insert all records (way more efficient than single inserts!)
  const insertQuery = pgp.helpers.insert(recordsWithIds, [
    'company_id', 'order_date', 'amount' // Match your record table columns
  ], 'your_records_table');
  
  await db.none(insertQuery);
}

The pgp.helpers.insert utility generates a clean bulk insert query—perfect for handling large volumes of data quickly.

Critical Things to Remember #

• Atomicity is non-negotiable: The CTE-based query ensures that checking and creating a company happens in one database transaction, so you never end up with duplicate companies from concurrent operations.
• Promise resolution: Using await (either in loops or with Promise.all) guarantees that all company ID promises resolve before you attempt to insert records. No broken links here!
• Unique constraints: Double-check that your companies table has a unique constraint on the field you’re using to identify existing companies. Without it, the whole check/create logic falls apart.

That should cover exactly what you need—whether you’re handling small batches or large ones, this approach keeps things safe and efficient.

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