Skip to main content

CDC Sink: Patching

When patches run​

EventConfigure the script inWhen it runs
INSERTPatchAfter column mapping
UPDATEPatchAfter column mapping,
on the merged document
DELETE
(root table)		OnDelete.PatchBefore the document is deleted
DELETE
(embedded table)		OnDelete.PatchOn the parent document,
after the embedded item is removed

If OnDelete.IgnoreDeletes is enabled, the patch still runs,
but CDC Sink keeps the document or embedded item unless the script deletes it explicitly.

The patch context​

Every patch script runs with three values in scope - this, $row, and $old -
and can use the load(id) helper function to read another RavenDB document from the current database.
Understanding what each one holds - and how they differ - is the key to writing correct patches.

ObjectTypeWhat it holds
thisobjectThe RavenDB document currently being patched (the root document, or the parent document for an embedded table). Write changes to this when you want to modify that document.
$rowobjectThe incoming SQL row from the CDC event,
with all columns (mapped and unmapped). Read-only input.
$oldobject | nullThe previous stored document or embedded item, as it was before this change.
null when there is no previous value (a new document or embedded item).
Read-only snapshot.
load(id)functionLoads a RavenDB document by ID from the current database.
See loading related documents.

The next section compares these values side by side, since $row, $old, and this are easy to confuse.

Additional document functions - put(id, document) and del(id) - are also available.
See capabilities and limitations for details.

Comparing $row, $old, and this​

Aspect$row$oldthis
RepresentsIncoming SQL row from the CDC eventPrevious stored document or embedded item, before this changeRavenDB document currently being patched
Property namesOriginal SQL column names (customer_name)RavenDB document property names (CustomerName)RavenDB document property names (CustomerName)
Value typesRaw SQL types (see $row column types)Stored JSON types (whatever was last written to RavenDB)JSON values in the document being patched
On INSERTThe row being insertednull - no prior state existsThe newly built document
On UPDATEThe row's new valuesThe document/item as it was before this updateThe document after column mapping or embedded update
On DELETEDELETE event data (see note below)The document/item as it was before deletionThe document about to be deleted, or the parent document for an embedded delete
Mutable?No - read-only inputNo - read-only snapshotYes - write changes here to modify the current document

  • $row vs this - source row vs RavenDB document:

    $row is the raw SQL input: original column names, raw source values, including unmapped columns.
    this is the RavenDB document currently being patched, using RavenDB property names and JSON values.
    Use $row to read values from the source event, and write document changes to this.

  • $old vs this - previous state vs current patch target:

    $old is the stored document or embedded item before this change;
    this is the document currently being patched.
    Use $old to detect what changed or to reverse a previous contribution (see delta computations).

Given this column mapping:

Columns =
[
    new CdcColumnMapping() { Column = "customer_name", Name = "CustomerName" },
    new CdcColumnMapping() { Column = "amount",        Name = "Amount" },
]

In the patch script:

// $row - SQL column names, raw SQL types (the incoming row)
$row.customer_name   // string
$row.amount          // number (from numeric/decimal column)

// $old - mapped property names, stored JSON types (the previous state)
$old?.CustomerName   // string, or undefined when there is no previous value
$old?.Amount         // number, or undefined when there is no previous value

// this - mapped property names, after column mapping for this event (the new state)
this.CustomerName    // string (just set from $row.customer_name)
this.Amount          // number (just set from $row.amount)

  • Unmapped columns - those not listed in Columns - appear in $row but are not written by column mapping.
    They appear in this or later in $old only if a patch stores them in the document.

  • For root-table DELETE patches, changes made to this are saved only when the delete is skipped,
    for example with OnDelete.IgnoreDeletes.

  • $row on a PostgreSQL DELETE
    With PostgreSQL's default REPLICA IDENTITY, $row contains only the primary-key columns on DELETE events.
    If a DELETE patch needs values from the deleted row, prefer $old, which holds the previous RavenDB document or embedded item.

$row column types​

$row exposes all SQL columns using their original column names (not the mapped RavenDB property names).
The JavaScript type of each value depends on the source database type.

The table below covers the PostgreSQL source provider.
Provider-specific type mappings can differ for SQL Server and MySQL/MariaDB.

PostgreSQL​

PostgreSQL TypeJavaScript Type in $rowNotes
integer / bigintnumber
real / double precisionnumber
numericnumber
booleanboolean
date / timestamp / timestamptzstringDate/time string, not a JavaScript Date
uuidstring
varchar / text / charstring
byteastringBase64-encoded
json / jsonbstringIn $row, JSON is raw text.
Call JSON.parse() to access it as an object / array
text[] / varchar[] / int[] / etc.ArrayElements are always strings, even for numeric array types
vector (pgvector)ArrayElements are JavaScript numbers (floats)

JSON/JSONB columns require explicit parsing:

var meta = JSON.parse($row.metadata);
this.Priority = meta.priority;
this.Label = meta.label;

Array columns:

this.TagCount = $row.tags.length;
this.FirstTag = $row.tags[0];
this.Tags = $row.tags;

// INT[] elements are strings - parse explicitly if numeric arithmetic is needed 
var first = parseInt($row.scores[0], 10);

pgvector vector columns (elements are numbers, not strings):

this.EmbeddingSum = $row.embedding.reduce(function(a, b) { return a + b; }, 0);
this.Embedding = $row.embedding;

Patch scope: root vs embedded​

Root table patches operate on the root document:

Patch = "this.Status = $row.is_active ? 'Active' : 'Inactive';"

this is the Orders (or whatever root collection) document.

Embedded table patches operate on the parent document, not the embedded item:

// Patch on order_lines embedded table
Patch = "this.TotalQuantity = (this.Lines || []).reduce((s, l) => s + l.Quantity, 0);"

this is the parent Orders document.
This lets you recompute parent-level aggregates when an embedded item is inserted, updated, or deleted.

Embedded table patches run with this set to the owning RavenDB document that contains the embedded item, not to the embedded item itself. $row is the incoming embedded-table row, and $old is the previous embedded item.

Capabilities and limitations​

What patches can do:

  • Read this, $row, and $old
  • Load documents from the current database with load(id)
  • Create or replace documents in the current database with put(id, document)
  • Delete documents from the current database with del(id)
  • Compute and transform property values
  • Set and modify document metadata
  • Use JavaScript logic such as conditionals, loops, and array methods
  • Use built-in JavaScript objects such as Date, Math, JSON, and Array

What patches cannot do:

  • Make external HTTP calls
  • Access the file system
  • Load documents from other RavenDB databases

Idempotency and failover​

After a failover, CDC Sink resumes from the last replicated @cdc-states checkpoint. If the previous mentor node processed changes that had not yet reached that checkpoint on the new node, those source changes may be read again.

A patch is idempotent when applying the same source change more than once leaves the document in the same final state.

// NOT idempotent - increments again on re-read
this.ViewCount = (this.ViewCount || 0) + 1;

// Idempotent - absolute value from SQL source
this.ViewCount = $row.view_count;

// Idempotent - delta via $old
const oldVal = $old?.Amount || 0;
const newVal = $row.amount || 0;
this.RunningTotal = (this.RunningTotal || 0) + (newVal - oldVal);

Column mapping itself is idempotent - writing the same mapped values again is safe.

Step limit​

Patch scripts are bounded by a step quota rather than a time limit.
Each operation in the script (assignment, loop iteration, function call) consumes steps.

The limit is controlled by the Patching.MaxStepsForScript configuration setting.
See Patching Configuration.

If a patch exceeds the limit, the CDC operation fails and is retried.

Keep patches focused and efficient - avoid unnecessary loops, repeated document loads, and large in-script computations.

Test a patch​

Validate a patch script against rows fetched from the configured source table before saving the task.
Testing runs CDC Sink mapping and the relevant Patch or OnDelete.Patch,
then returns the preview result without persisting any documents.

From Studio:
Use the Test tool in the CDC Sink task view.
This is the recommended way and requires no code.
See Test mapping for a step-by-step walkthrough.

Over HTTP:
The Test tool is backed by a REST endpoint you can call directly:

MethodEndpointAuth
POST/databases/{databaseName}/admin/cdc-sink/testDatabaseAdmin

Test mode previews the selected root table mapping and the relevant patch script.
Linked and embedded tables configured under that table are not exercised.

Common scenarios​

The following examples show common patching tasks:

Column transformation​

Transform or combine source columns into RavenDB document properties:

this.FullName = ($row.first_name + ' ' + $row.last_name).trim();
this.Status = $row.is_active ? 'Active' : 'Inactive';
this.CreatedAt = new Date($row.created_unix * 1000).toISOString();

Aggregation with embedded tables​

Recompute parent-level totals whenever an embedded item is inserted, updated, or deleted.

If you use a Patch to maintain an aggregate, you must also provide an OnDelete.Patch that updates the aggregate when an item is deleted. Without it, deletes will leave the aggregate in an incorrect state.

new CdcSinkEmbeddedTableConfig
{
    SourceTableName = "order_lines",
    PropertyName = "Lines",
    PrimaryKeyColumns = ["line_id"],
    JoinColumns = ["order_id"],
    Columns =
    [
        new CdcColumnMapping() { Column = "line_id",  Name = "LineId" },
        new CdcColumnMapping() { Column = "quantity", Name = "Quantity" },
    ],

    // Runs on INSERT and UPDATE - recomputes total from current Lines array
    Patch = @"
        this.TotalQuantity = (this.Lines || [])
            .reduce((sum, line) => sum + (line.Quantity || 0), 0);
    ",

    // REQUIRED: Runs on DELETE - recomputes after item is removed from array
    OnDelete = new CdcSinkOnDeleteConfig
    {
        Patch = @"
            this.TotalQuantity = (this.Lines || [])
                .reduce((sum, line) => sum + (line.Quantity || 0), 0);
        "
    }
}

With the default delete behavior, the OnDelete.Patch runs after the item has already been removed from the array, so re-summing this.Lines gives the correct post-deletion total.

Delta computations​

Use $old to compute the difference between the previous embedded item value and the new source-row value,
so you can keep a running total without recomputing it from scratch.

Running-total patches that use $old must also provide an OnDelete.Patch that subtracts the deleted item's value. Without it, deletes leave the running total incorrect.

new CdcSinkEmbeddedTableConfig
{
    SourceTableName = "invoice_lines",
    PropertyName = "Lines",
    PrimaryKeyColumns = ["line_id"],
    JoinColumns = ["invoice_id"],
    Columns =
    [
        new CdcColumnMapping() { Column = "line_id", Name = "LineId" },
        new CdcColumnMapping() { Column = "amount",  Name = "Amount" },
    ],

    // INSERT: $old is null, so delta = new amount (0 → new)
    // UPDATE: $old has previous Amount, delta = new - old
    Patch = @"
        const oldAmount = $old?.Amount || 0;
        const newAmount = $row.amount || 0;
        this.RunningTotal = (this.RunningTotal || 0) + (newAmount - oldAmount);
    ",

    // REQUIRED: Subtract the deleted item's amount using $old
    OnDelete = new CdcSinkOnDeleteConfig
    {
        Patch = @"
            const deletedAmount = $old?.Amount || 0;
            this.RunningTotal = (this.RunningTotal || 0) - deletedAmount;
        "
    }
}

In OnDelete.Patch, $old contains the embedded item's last known state before deletion.

Computed fields​

Compute document fields from unmapped source columns that you don't want to store directly:

Columns =
[
    new CdcColumnMapping() { Column = "id",   Name = "Id" },
    new CdcColumnMapping() { Column = "name", Name = "Name" },
],
// base_price and tax_rate are NOT in Columns
Patch = @"
    this.FinalPrice = $row.base_price * (1 + $row.tax_rate);
    this.Discount = $row.is_vip ? $row.base_price * 0.1 : 0;
"

Document metadata​

Patches can set or clear RavenDB document metadata, including expiration:

this['@metadata'] = this['@metadata'] || {};

if ($row.expires_at) {
    this['@metadata']['@expires'] = new Date($row.expires_at).toISOString();
} else {
    delete this['@metadata']['@expires'];
}

this['@metadata']['SourceTable'] = 'orders';

Use load() to load a related RavenDB document and denormalize its data:

const customer = load('Customers/' + $row.customer_id);

if (customer) {
    this.CustomerName = customer.Name;
    this.CustomerEmail = customer.Email;
    this.CustomerTier = customer.Tier;
} else {
    // Related document does not exist yet, has not been synced yet, or was deleted
    this.CustomerName = null;
    this.CustomerEmail = null;
    this.CustomerTier = null;
}

Use load() when you want to copy related document values into the current document at patch time. The copied values are denormalized; later changes to the loaded document do not automatically update this document.

load() returns null if the document does not exist or has not yet been created by CDC Sink (race condition when multiple tables are loading in parallel). Always check for null before accessing properties.

When to use load():

  • Denormalizing slowly-changing reference data (customer name, category, region)
  • Capturing a snapshot of related data at insert time

Prefer linked tables when you only need to store a simple foreign-key reference by document ID.
They avoid denormalized-copy drift and the null-handling complexity of load().

In this article