ssjs.guide SFMC
⌘K

DE CRUD Patterns

Complete Data Extension Create, Read, Update, Delete patterns using both Platform.Function and Core library approaches.

Claims checked Test scripts included

Platform.Function Approach

Create (Insert)

// Single row insert
var rowsAdded = Platform.Function.InsertData(
    "UserProfiles",
    ["SubscriberKey", "Email", "FirstName", "LastName", "CreatedAt", "Status"],
    [subscriberKey, email, firstName, lastName, Platform.Function.Now(), "active"]
);
// rowsAdded === 1 on success; throws on primary key conflict

Read (Lookup)

// Single field, single row
var email = Platform.Function.Lookup("UserProfiles", "Email", "SubscriberKey", subKey);

// Multiple rows
var rows = Platform.Function.LookupRows("Orders", "Status", "pending");
for (var i = 0; i < rows.length; i++) {
    var order = rows[i];
    Write(order.OrderId + ": " + order.Total + "<br>");
}

// Multiple rows with sort and limit
var recentOrders = Platform.Function.LookupOrderedRows(
    "Orders", 10, "CreatedAt desc",
    "SubscriberKey", subKey
);

Update

// Update rows matching a filter
var rowsUpdated = Platform.Function.UpdateData(
    "UserProfiles",
    ["SubscriberKey"],
    [subKey],
    ["Status", "UpdatedAt"],
    ["inactive", Platform.Function.Now()]
);
// rowsUpdated === 0 if no match (no error thrown)

Upsert

// Insert or update based on primary key
Platform.Function.UpsertData(
    "UserProfiles",
    ["SubscriberKey"],        // key columns
    [subKey],                 // key values
    ["Email", "FirstName", "Status", "UpdatedAt"], // data columns
    [email, firstName, "active", Platform.Function.Now()] // data values
);

Delete

var rowsDeleted = Platform.Function.DeleteData(
    "UserProfiles",
    ["SubscriberKey"],
    [subKey]
);
Show test script
<script runat="server">
/*
 * Chapter: Platform.Function Approach
 * Proves:
 *   1. InsertData/UpdateData/UpsertData/DeleteData typeof.
 * EXPECTED OUTPUT: every line starts with PASS.
 */
function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function assertThrows(id, fn) {
    var threw = false, msg = "";
    try { fn(); } catch (ex) { threw = true; msg = "" + ex.message; }
    Platform.Response.Write((threw ? "PASS " : "FAIL ") + id + " -> " + (threw ? "threw: " + msg : "did NOT throw") + "\n");
}
function typeOfThunk(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW:" + ("" + ex.message); }
}
assert("InsertData", typeof Platform.Function.InsertData, "clrmethodinfo");
assert("UpdateData", typeof Platform.Function.UpdateData, "clrmethodinfo");
assert("UpsertData", typeof Platform.Function.UpsertData, "clrmethodinfo");
assert("DeleteData", typeof Platform.Function.DeleteData, "clrmethodinfo");
</script>

Core Library Approach

Platform.Load("core", "1.1.5");
var de = DataExtension.Init("UserProfiles");

// Create
de.Rows.Add({
    SubscriberKey: subKey,
    Email: email,
    FirstName: firstName,
    Status: "active",
    CreatedAt: Platform.Function.Now()
});

// Read (with filter — required on CloudPages)
var rows = de.Rows.Retrieve({
    Property: "SubscriberKey",
    SimpleOperator: "equals",
    Value: subKey
});
var profile = rows.length > 0 ? rows[0] : null;

// Update
de.Rows.Update(
    { Status: "inactive", UpdatedAt: Platform.Function.Now() },
    ["SubscriberKey"],
    [subKey]
);

// Delete
de.Rows.Remove("SubscriberKey", subKey);
Show test script
<script runat="server">
/*
 * Chapter: Core Library Approach
 * Proves:
 *   1. DataExtension.Init after Load.
 * EXPECTED OUTPUT: every line starts with PASS.
 */
function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function assertThrows(id, fn) {
    var threw = false, msg = "";
    try { fn(); } catch (ex) { threw = true; msg = "" + ex.message; }
    Platform.Response.Write((threw ? "PASS " : "FAIL ") + id + " -> " + (threw ? "threw: " + msg : "did NOT throw") + "\n");
}
function typeOfThunk(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW:" + ("" + ex.message); }
}
Platform.Load("core", "1.1.5");
assert("DataExtension", typeOfThunk(function () { return typeof DataExtension; }), "object");
assert("Init typeof", typeOfThunk(function () { return typeof DataExtension.Init; }), "function");
</script>

Pattern: Safe Upsert with Existence Check

// String() first — a Lookup result throws on a truthiness test when the field is empty
var existing = String(Platform.Function.Lookup(
    "UserProfiles", "SubscriberKey", "SubscriberKey", subKey));

if (existing === "" || existing === "null") {
    // Insert
    Platform.Function.InsertData("UserProfiles",
        ["SubscriberKey", "Email", "CreatedAt"],
        [subKey, email, Platform.Function.Now()]
    );
} else {
    // Update
    Platform.Function.UpdateData("UserProfiles",
        ["SubscriberKey"], [subKey],
        ["Email", "UpdatedAt"],
        [email, Platform.Function.Now()]
    );
}
Show test script
<script runat="server">
/*
 * Chapter: Safe Upsert with Existence Check
 * Proves:
 *   1. Lookup miss → String null path then UpsertData typeof.
 * EXPECTED OUTPUT: every line starts with PASS.
 */
function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function assertThrows(id, fn) {
    var threw = false, msg = "";
    try { fn(); } catch (ex) { threw = true; msg = "" + ex.message; }
    Platform.Response.Write((threw ? "PASS " : "FAIL ") + id + " -> " + (threw ? "threw: " + msg : "did NOT throw") + "\n");
}
function typeOfThunk(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW:" + ("" + ex.message); }
}
var existing = String(null);
assert("miss path String(null)", existing, "null");
assert("UpsertData", typeof Platform.Function.UpsertData, "clrmethodinfo");
assert("Lookup", typeof Platform.Function.Lookup, "clrmethodinfo");
</script>

Pattern: Bulk Insert from Array

var submissions = Platform.Function.ParseJSON(rawBody + "");

var inserted = 0;
var errors = [];
for (var i = 0; i < submissions.length; i++) {
    var sub = submissions[i];
    if (!Platform.Function.IsEmailAddress(sub.email)) {
        errors.push({ index: i, reason: "invalid email" });
        continue;
    }
    try {
        Platform.Function.UpsertData("Leads",
            ["Email"], [sub.email],
            ["FirstName", "Source", "CreatedAt"],
            [sub.firstName || "", sub.source || "api", Platform.Function.Now()]
        );
        inserted++;
    } catch(e) {
        errors.push({ index: i, reason: e.message });
    }
}

Write(Platform.Function.Stringify({ inserted: inserted, errors: errors }));
Show test script
<script runat="server">
/*
 * Chapter: Bulk Insert from Array
 * Proves:
 *   1. Loop over array preparing rows (no DE write).
 * EXPECTED OUTPUT: every line starts with PASS.
 */
function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function assertThrows(id, fn) {
    var threw = false, msg = "";
    try { fn(); } catch (ex) { threw = true; msg = "" + ex.message; }
    Platform.Response.Write((threw ? "PASS " : "FAIL ") + id + " -> " + (threw ? "threw: " + msg : "did NOT throw") + "\n");
}
function typeOfThunk(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW:" + ("" + ex.message); }
}
var rows = [{ email: "a@x.com" }, { email: "b@x.com" }];
var prepared = [];
for (var i = 0; i < rows.length; i++) prepared.push(rows[i].email);
assert("prepared join", prepared.join(","), "a@x.com,b@x.com");
</script>

Pattern: Soft Delete (Status Flag)

Prefer marking records inactive over deleting them:

// Soft delete — sets Status = "deleted", preserves record
Platform.Function.UpdateData(
    "Orders",
    ["OrderId"], [orderId],
    ["Status", "DeletedAt"],
    ["deleted", Platform.Function.Now()]
);

// When reading, filter out deleted records
var active = Platform.Function.LookupRows("Orders", "Status", "active");
Show test script
<script runat="server">
/*
 * Chapter: Soft Delete Status Flag
 * Proves:
 *   1. UpdateData typeof for status flag pattern.
 * EXPECTED OUTPUT: every line starts with PASS.
 */
function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function assertThrows(id, fn) {
    var threw = false, msg = "";
    try { fn(); } catch (ex) { threw = true; msg = "" + ex.message; }
    Platform.Response.Write((threw ? "PASS " : "FAIL ") + id + " -> " + (threw ? "threw: " + msg : "did NOT throw") + "\n");
}
function typeOfThunk(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW:" + ("" + ex.message); }
}
assert("UpdateData", typeof Platform.Function.UpdateData, "clrmethodinfo");
var status = "inactive";
assert("status flag", status, "inactive");
</script>

Pattern: Pagination with LookupOrderedRows

var PAGE_SIZE = 25;
var pageNum = parseInt(Platform.Request.GetQueryStringParameter("page") || "1", 10);
var offset = (pageNum - 1) * PAGE_SIZE;

// SSJS has no OFFSET support — get more rows and slice
var all = Platform.Function.LookupOrderedRows(
    "Products",
    offset + PAGE_SIZE, // get enough rows
    "Name asc",
    "Status", "active"
);

var page = [];
for (var i = offset; i < Math.min(offset + PAGE_SIZE, all.length); i++) {
    page.push(all[i]);
}

Write(Platform.Function.Stringify({
    page: pageNum,
    pageSize: PAGE_SIZE,
    items: page,
    hasMore: all.length > offset + PAGE_SIZE
}));
Show test script
<script runat="server">
/*
 * Chapter: Pagination with LookupOrderedRows
 * Proves:
 *   1. LookupOrderedRows typeof.
 * EXPECTED OUTPUT: every line starts with PASS.
 */
function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function assertThrows(id, fn) {
    var threw = false, msg = "";
    try { fn(); } catch (ex) { threw = true; msg = "" + ex.message; }
    Platform.Response.Write((threw ? "PASS " : "FAIL ") + id + " -> " + (threw ? "threw: " + msg : "did NOT throw") + "\n");
}
function typeOfThunk(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW:" + ("" + ex.message); }
}
assert("LookupOrderedRows", typeof Platform.Function.LookupOrderedRows, "clrmethodinfo");
</script>

See Also

See Also

Suggest an edit Report an issue