Upsert Data

Initialize the SDK

import cloudbase from "@cloudbase/js-sdk";

const app = cloudbase.init({
  env: env: "your-env-id", // Replace with your environment id
});

const db = app.rdb();

// or specify the instance and database
// const db = app.rdb({
//   instance: "<instance>",
//   database: "<database>"
// });

Upsert Data

Using the upsert() method to perform an update or insert operation; it inserts the record if it does not exist, otherwise updates it.

db.from(tableName).upsert(values, options);

• tableName: table name
• values: The data to upsert
• options: upsert options configuration

Parameter Description

Parameter	Type	Required	Description
values	object | Array	Required	The values to upsert. Pass an object to upsert a single row, or pass an array to upsert multiple rows
options	object	No	Configuration for upsert options

options Parameter Description

Parameter	Type	Required	Description
count	string	No	Counting algorithm used to calculate the number of upserted rows: "exact" - underlying execution of COUNT(*)
ignoreDuplicates	boolean	No	If true, duplicate rows are ignored. If false, duplicate rows are merged with existing rows
onConflict	string	No	Comma-separated unique index columns used to specify how to identify duplicate rows. When all specified columns are equal, two rows are considered duplicates. In MySQL, this typically corresponds to a unique index or primary key

Code Example

Upsert Data

// If a record with id 1 exists in the articles table, update the title to "MySQL Tutorial", otherwise insert a new record
const { data, error } = await db
  .from("articles")
  .upsert({ id: 1, title: "MySQL Tutorial" });

console.log("Upsert result:", data);

Batch Upsert Data

// Batch upsert multiple records: set the title of the record with id 1 to "MySQL Tutorial", and set the title of the record with id 2 to "Redis Guide"
const { data, error } = await db.from("articles").upsert([
  { id: 1, title: "MySQL Tutorial" },
  { id: 2, title: "Redis Guide" },
]);

console.log("Batch Upsert result:", data);

Specifying Conflict Columns

// Detect conflicts based on the title field; update if a record with title "Unique Title" exists, otherwise insert a new record with id 42
const { data, error } = await db
  .from("articles")
  .upsert(
    { id: 42, title: "Unique Title", content: "Article Content" },
    { onConflict: "title" }
  );

console.log("Upsert result with specified conflict columns:", data);

Response

{
    data: [
        {
            id: 1,
            title: title: "MySQL Tutorial",
            content: "content: "Article Content"
        }
    ],
    error: null
}

💡 Note: When using upsert(), you must include the primary key column to correctly determine whether to insert a new row or update an existing one. In MySQL, upsert is typically implemented using the ON DUPLICATE KEY UPDATE syntax. When the inserted data conflicts with an existing primary key or unique index, an update operation is performed. The onConflict parameter is used to specify the columns for conflict detection, corresponding to the unique index columns in MySQL.