upsertAll()

Signature

upsertAll() — returns struct

Available in: model Category: Create Functions

Description

Inserts or updates multiple records in a single batch operation (upsert). Uses database-specific conflict resolution syntax (e.g., ON CONFLICT … DO UPDATE for PostgreSQL/SQLite). The uniqueBy argument specifies which properties form the unique constraint for conflict detection.

Parameters

Name	Type	Required	Default	Description
records	array	yes	—	Array of structs, each containing property name/value pairs.
uniqueBy	string	yes	—	Comma-delimited list of property names that form the unique constraint for conflict detection.
timestamps	boolean	no	true	Set to false to skip automatic createdAt/updatedAt timestamping. An auto-stamped createdAt is only written when a row is inserted; an existing row keeps its original value (except on H2, whose MERGE ... KEY rewrites every column).
transaction	string	no	[runtime expression]	Set this to commit to update the database, rollback to run all the database queries but not commit them, or none to skip transaction handling altogether.
parameterize	any	no	true	Set to true to use cfqueryparam on all columns, or pass in a list of property names to use cfqueryparam on those only.

Examples

// 1. Upsert a batch of products using their SKU as the unique constraint
records = [
    {sku: "WIDGET-001", name: "Widget Standard", price: 9.99, stock: 100},
    {sku: "WIDGET-002", name: "Widget Deluxe",   price: 19.99, stock: 50},
    {sku: "GADGET-001", name: "Gadget Pro",       price: 49.99, stock: 25}
];
result = model("Product").upsertAll(records=records, uniqueBy="sku");
// result -> {upsertedCount: 3}

// 2. Upsert with a composite unique constraint (e.g., userId + date for daily stats)
stats = [
    {userId: 1, reportDate: "2024-06-01", pageViews: 42, clicks: 7},
    {userId: 2, reportDate: "2024-06-01", pageViews: 18, clicks: 3}
];
result = model("DailyStat").upsertAll(records=stats, uniqueBy="userId,reportDate");
// result -> {upsertedCount: 2}

// 3. Upsert without automatic timestamps (e.g., when importing legacy data)
imports = [
    {externalId: "EXT-100", title: "Legacy Record A", status: "active"},
    {externalId: "EXT-101", title: "Legacy Record B", status: "archived"}
];
result = model("ImportedRecord").upsertAll(
    records    = imports,
    uniqueBy   = "externalId",
    timestamps = false
);
// result -> {upsertedCount: 2}