Knex.js を使用してレコードを一括更新する QL のアプローチ

Web 開発の世界では、特に複数のレコードを一度に更新するような一括操作を処理する場合、データベースを効率的に操作することが重要です。在庫の管理、ユーザー データの処理、トランザクションの処理のいずれの場合でも、効率的かつ信頼性の高い方法で一括更新を実行する機能は不可欠です。

このガイドでは、Node.js の多用途クエリ ビルダーである Knex.js を使用してレコードを一括更新するための 3 つの重要な SQL テクニックを詳しく説明します。各アプローチはさまざまなシナリオに合わせて調整されており、特定のユースケースに基づいて明確な利点を提供します。以下について説明します:

1. 複数の条件による単一更新: 条件付きロジックを利用して、特定の条件に基づいてさまざまな更新を適用することで、単一のクエリで複数のレコードを更新できるメソッドです。

2. トランザクション内の個別のクエリによるバッチ更新: このアプローチでは、トランザクションを活用してアトミック性を確保し、複数の更新クエリを安全かつ効率的に実行します。

3. onConflict を使用した Upsert (挿入または更新): データが重複する危険を冒さずに新しいレコードを挿入するか、既存のレコードを更新する必要があるシナリオに最適です。

次のセクションでは、これらの各方法をさらに深く掘り下げ、その実装、利点、最適な使用例を検討します。これらのアプローチを理解することで、特定のニーズに最も適した手法を選択し、アプリケーションのパフォーマンスとデータ整合性の両方を最適化できます。

---

1. 複数の条件による単一の更新

データベース内の複数のレコードを更新する場合、効率が重要です。強力な手法の 1 つは、複数の条件を指定して単一の UPDATE クエリを使用することです。この方法は、特定の基準に基づいて、単一の SQL ステートメント内でさまざまな更新をさまざまなレコードに適用する必要がある場合に特に便利です。

コンセプト:

「複数条件による単一更新」アプローチの背後にある中心的な考え方は、単一の UPDATE クエリを使用して複数の行を変更し、各行がその固有の特性に基づいて異なる値を受け取る可能性があるということです。これは、UPDATE クエリ内で CASE ステートメントを使用することで実現され、更新が必要なフィールドごとに条件付きロジックを指定できます。

このアプローチを使用する理由:

• 効率: 少数から中程度の数のレコード (例: 数十から数百) の場合、複数の更新を 1 つのクエリに統合すると、レコード数が減り、パフォーマンスが大幅に向上します。データベースの往復回数。これは、高頻度の更新を扱う場合に特に有益です。ただし、非常に大規模なデータセット (数千以上) の場合、このアプローチはそれほど効果的ではない可能性があります。大規模なデータセットを処理するための代替方法については、このガイドの後半で説明します。

• シンプルさ: 多くの場合、単一のクエリで更新を管理する方が、複数の個別のクエリを実行する場合に比べて単純で保守しやすくなります。このアプローチにより、特に適度な数の更新を処理する場合に、データベース操作の複雑さが軽減され、コードが理解しやすくなります。

• オーバーヘッドの削減: クエリが減るとデータベースのオーバーヘッドが減り、全体的なパフォーマンスの向上につながります。これは、ネットワーク遅延やデータベース負荷が操作の速度に影響を与える可能性があるシナリオでは特に重要です。
  非常に多数のレコードの場合、潜在的なオーバーヘッドをより効果的に管理するために、このガイドで他の戦略を検討します。

実装例:

ここでは、Node.js 用の人気のある SQL クエリ ビルダーである Knex.js を使用してこのアプローチを実装する方法の実践的な例を示します。この例では、条件付きロジックを使用して、レコードの ID に基づいてさまざまな更新を適用し、複数のレコードの複数のフィールドを一度に更新する方法を示します:

const queryHeaderProductUpdate = 'UPDATE products SET '; // Start of the SQL UPDATE query
const updatesProductUpdate = []; // Array to hold the individual update statements
const parametersProductUpdate = []; // Array to hold the parameters for the query

const updateProducts = [
  { product_id: 1, name: 'New Name 1', price: 100, status: 'Active' },
  { product_id: 2, name: 'New Name 2', price: 150, status: 'Inactive' },
  { product_id: 3, name: 'New Name 3', price: 200, status: 'Active' }
];

// Extract the product IDs to use in the WHERE clause
const productIds = updateProducts.map(p => p.product_id);

// Build the update statements for each field
updateProducts.forEach((item) => {
  // Add conditional logic for updating the 'name' field
  updatesProductUpdate.push('name = CASE WHEN product_id = ? THEN ? ELSE name END');
  parametersProductUpdate.push(item.product_id, item.name);

  // Add conditional logic for updating the 'price' field
  updatesProductUpdate.push('price = CASE WHEN product_id = ? THEN ? ELSE price END');
  parametersProductUpdate.push(item.product_id, item.price);

  // Add conditional logic for updating the 'status' field
  updatesProductUpdate.push('status = CASE WHEN product_id = ? THEN ? ELSE status END');
  parametersProductUpdate.push(item.product_id, item.status);

  // Add 'updated_at' field with the current timestamp
  updatesProductUpdate.push('updated_at = ?');
  parametersProductUpdate.push(knex.fn.now());

  // Add 'updated_by' field with the user ID
  updatesProductUpdate.push('updated_by = ?');
  parametersProductUpdate.push(req.user.userId);
});

// Construct the full query by joining the individual update statements and adding the WHERE clause
const queryProductUpdate = `${queryHeaderProductUpdate + updatesProductUpdate.join(', ')} WHERE product_id IN (${productIds.join(', ')})`;

// Execute the update query
await db.raw(queryProductUpdate, parametersProductUpdate);

このコードの動作:

1. クエリ ヘッダーを構築します。products テーブルの UPDATE ステートメントを開始します。

2. 条件付き更新を構築します: CASE ステートメントを使用して、product_id に基づいてフィールドごとに異なる更新を指定します。

3. 完全なクエリを生成します。更新ステートメントと WHERE 句を組み合わせます。

4. クエリを実行: 構築されたクエリを実行して、指定されたレコードに更新を適用します。

この手法を実装すると、条件付きロジックを使用して一括更新を効率的に処理でき、データベース操作がより合理化され、効果的になります。

Note: In the provided example, we did not use a transaction because the operation involves a single SQL query. Since a single query inherently maintains data integrity and consistency, there's no need for an additional transaction. Adding a transaction would only increase overhead without providing additional benefits in this context.

Having explored the "Single Update with Multiple Conditions" approach, which works well for a moderate number of records and provides simplicity and efficiency, we now turn our attention to a different scenario. As datasets grow larger or when atomicity across multiple operations becomes crucial, managing updates effectively requires a more robust approach.

Batch Updates with Individual Queries in a Transaction is a method designed to address these needs. This approach involves executing multiple update queries within a single transaction, ensuring that all updates are applied atomically. Let's dive into how this method works and its advantages.

---

2. Batch Updates with Individual Queries in a Transaction

When dealing with bulk updates, especially for a large dataset, managing each update individually within a transaction can be a robust and reliable approach. This method ensures that all updates are applied atomically and can handle errors gracefully.

Why Use This Approach:

• Scalability: For larger datasets where Single Update with Multiple Conditions might become inefficient, batch updates with transactions offer better control. Each query is executed separately, and a transaction ensures that all changes are committed together, reducing the risk of partial updates.

• Error Handling: Transactions provide a safety net by ensuring that either all updates succeed or none do. This atomicity guarantees data integrity, making it ideal for scenarios where you need to perform multiple related updates.

• Concurrency Control: Using transactions can help manage concurrent modifications to the same records, preventing conflicts and ensuring consistency.

Code Example

Here’s how you can implement batch updates with individual queries inside a transaction using Knex.js:

const updateRecordsInBatch = async () => {
    // Example data to update
    const dataToUpdate = [
        { id: 1, name: 'Updated Name 1', price: 100 },
        { id: 2, name: 'Updated Name 2', price: 200 },
        { id: 3, name: 'Updated Name 3', price: 300 }
    ];

    // Start a transaction
    const trx = await db.transaction();
    const promises = [];

    try {
        // Iterate over the data and push update queries to the promises array
        dataToUpdate.forEach(record => {
            promises.push(
                trx('products')
                    .update({
                        name: record.name,
                        price: record.price,
                        updated_at: trx.fn.now()
                    })
                    .where('id', record.id)
            );
        });

        // Execute all queries concurrently
        await Promise.all(promises);

        // Commit the transaction
        await trx.commit();
        console.log('All records updated successfully.');
    } catch (error) {
        // Rollback the transaction in case of error
        await trx.rollback();
        console.error('Update failed:', error);
    }
};

Explanation

1. Transaction Initialization: The transaction is started using db.transaction(), which ensures that all subsequent queries are executed within this transaction.

2. Batch Updates: Each update query is constructed and added to an array of promises. This method allows for multiple updates to be performed concurrently.

3. Executing Queries: Promise.all(promises) is used to execute all update queries concurrently. This approach ensures that all updates are sent to the database in parallel.

4. Committing or Rolling Back: If all queries succeed, the transaction is committed with trx.commit(). If any query fails, the transaction is rolled back with trx.rollback(), ensuring that no partial updates are applied.

Using batch updates with individual queries inside a transaction provides a reliable way to manage large datasets. It ensures data integrity through atomic transactions and offers better control over concurrent operations. This method is especially useful when Single Update with Multiple Conditions may not be efficient for very large datasets.

---

3. Upsert (Insert or Update) Using onConflict

When you're working with data that might need to be inserted or updated depending on its existence in the database, an "upsert" operation is the ideal solution. This approach allows you to handle both scenarios—insert new records or update existing ones—in a single, streamlined operation. It's particularly useful when you want to maintain data consistency without having to write separate logic for checking whether a record exists.

Why Use This Approach:

• Simplicity: An upsert enables you to combine the insert and update operations into a single query, simplifying your code and reducing the need for additional checks.

• Efficiency: This method is more efficient than performing separate insert and update operations, as it minimizes database round-trips and handles conflicts automatically.

• Conflict Handling: The onConflict clause lets you specify how to handle conflicts, such as when records with unique constraints already exist, by updating the relevant fields.
  

const productData = [
  {
    product_id: 1,
    store_id: 101,
    product_name: 'Product A',
    price: 10.99,
    category: 'Electronics',
  },
  {
    product_id: 2,
    store_id: 102,
    product_name: 'Product B',
    price: 12.99,
    category: 'Books',
  },
  {
    product_id: 3,
    store_id: 103,
    product_name: 'Product C',
    price: 9.99,
    category: 'Home',
  },
  {
    product_id: 4,
    store_id: 104,
    product_name: 'Product D',
    price: 15.49,
    category: 'Garden',
  },
];

await knex('products')
  .insert(productData)
  .onConflict(['product_id', 'store_id'])
  .merge({
    product_name: knex.raw('EXCLUDED.product_name'),
    price: knex.raw('EXCLUDED.price'),
    category: knex.raw('EXCLUDED.category'),
  });

Explanation

1. データ定義: 挿入または更新する製品レコードを表すオブジェクトの配列である productData を定義します。各オブジェクトには、product_id、store_id、product_name、価格、カテゴリが含まれます。

2. 挿入または更新: knex('products').insert(productData) 関数は、productData 配列から製品テーブルに各レコードを挿入しようとします。

3. 競合の処理: onConflict(['product_id', 'store_id']) 句は、product_id と store_id の組み合わせで競合が発生した場合に次のステップを実行する必要があることを指定します。

4. マージ (競合時に更新): 競合が検出されると、merge({...}) メソッドは、productData からの新しい product_name、price、category の値で既存のレコードを更新します。 knex.raw('EXCLUDED.column_name') 構文は、挿入される値を参照するために使用され、データベースがこれらの値で既存のレコードを更新できるようにします。

upsert 操作で onConflict 句が正しく機能するには、関連する列が一意制約の一部である必要があります。仕組みは次のとおりです:

• 単一の一意の列: onConflict 句で単一の列を使用する場合、その列はテーブル全体で一意である必要があります。この一意性により、データベースはこの列に基づいてレコードが既に存在するかどうかを正確に検出できます。
• 複数の列: onConflict 句で複数の列を使用する場合、これらの列の組み合わせは一意である必要があります。この一意性は、一意のインデックスまたは制約によって強制され、これらの列の結合値がテーブル全体で一意であることが保証されます。

インデックスと制約:
インデックス: 1 つ以上の列の一意のインデックスにより、データベースは値の一意性を効率的にチェックできます。一意のインデックスを定義すると、データベースはそれを使用して、指定された列の値がすでに存在するかどうかを迅速に確認します。これにより、onConflict 句が競合を正確に検出して処理できるようになります。

制約: 一意制約により、1 つ以上の列の値が一意であることが保証されます。この制約は、値の重複を防ぐルールを適用し、データベースがこれらの列に基づいて競合を検出できるようにするため、onConflict 句が機能するために非常に重要です。

複数の条件を使用した単一更新のアプローチと同様に、更新/挿入操作にはトランザクションは必要ありません。レコードの挿入または更新を行う単一のクエリが関与するため、トランザクション管理による追加のオーバーヘッドなしで効率的に動作します。

---

結論

各手法には、コードの簡素化やデータベースの対話の削減から、データの整合性の確保や競合の効率的な処理まで、明確な利点があります。ユースケースに最適な方法を選択することで、アプリケーションのより効率的で信頼性の高い更新を実現できます。

これらのアプローチを理解すると、データベース操作を特定のニーズに合わせて調整し、パフォーマンスと保守性の両方を向上させることができます。一括更新や複雑なデータ管理タスクを扱う場合でも、ワークフローを最適化し、開発プロジェクトでより良い成果を達成するには、適切な戦略を選択することが重要です。

以上がKnex.js を使用してレコードを一括更新する QL のアプローチの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。