Database Commons (cl_db_commons)

This plugin contains common functionality related to databases. It exports several functions and classes to perform different database operations.

Smart Update

Imagine you want to update several entries of a record (imagine updating orders_products of an order). Normally, you will delete all records and reinsert the new records. However, this can lead to performance problems and unnecessary insertion. SmartUpdate will help you only record where necessary.

Example

Here, we are updating the details of a customer. We want to remove / update / insert the details only where needed. Here is the code we are going to use:

var getDb = require("cl_db");
var { smartUpdate } = require("cl_db_commons");

// Update the customer itself
var updatePromise = db.customer.update({
   where: {
       id,
   },
   data: customer,
});

// Smart Update its entries
var updatedCustomerDetails = await smartUpdate(
    db.customerDetail // The table you want to update
    ,'id' // The field to match to check if it is updated or inserted
    ,customerDetails // The new values for customerDetails
    ,{
        where: {
            customerId: id,
        }
    }, // How to find which records to compare
    function buildNewDetails(detail) {
        detail.customer = {
            connect: {
                id,
            }
        }
        return detail;
    } // Any processing to do on new details before inserting them.
);

// Perform the whole update in a transaction
await db.$transaction(updatedCustomerDetails.concat(updatePromise));

Explanation

I will quickly explain its function:

1. Smart Update will fetch all records using the condition you provided as the fourth parameter from db.customerDetails table.
2. It will loop through all given values as the third parameter, comparing their IDs against the fetched records.
3. If the ID matches: It will check if any changes have been made and update it if necessary.
4. If the ID is not present or not exists in the database: It will consider this a new record and insert it into the database.
5. It will delete all remaining records unless you pass allowDelete: false, in the fourth parameter.