Upsert Data

Initialize the SDK

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

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

const db = app.rdb();

Upsert Data

Perform an upsert operation through the upsert() method. If the record does not exist, it will be inserted; if it exists, it will be updated.

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

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

Parameter Description

Parameter	Type	Required	Description
values	object | Array	Yes	Values to upsert. Pass an object to upsert a single row, or pass an array to upsert multiple rows
options	object	No	Upsert options configuration

options Parameter Description

Parameter	Type	Required	Description
count	string	No	Counting algorithm for counting 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 determine duplicate rows. Two rows are considered duplicates when all specified columns are equal. In PostgreSQL, this typically corresponds to a unique index or primary key

Code Examples

Upsert Data

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

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

Bulk Upsert Data

// Bulk upsert multiple records: set title to "PostgreSQL Tutorial" for id 1, set title to "Redis Guide" for id 2
const { data, error } = await db.from("articles").upsert([
  { id: 1, title: "PostgreSQL Tutorial" },
  { id: 2, title: "Redis Guide" },
]);

console.log('Bulk upsert result:', data);

Specify Conflict Columns

// Determine conflict based on the title field; if a record with title "Unique Title" exists, update it; 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 with conflict columns result:', data);

Return Result

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

Note: When using upsert(), you must include the primary key column so that it can correctly determine whether to insert a new row or update an existing one. In PostgreSQL, upsert is typically implemented via the ON CONFLICT DO 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 PostgreSQL.