Skip to main content

CDC Sink: Attachment Handling

  • To store a source column as a RavenDB attachment instead of a document property,
    set Type = CdcColumnType.Attachment on the column mapping entry.

  • Attachment mappings are typically used for source columns with binary, text, or vector content,
    such as bytea, varbinary, text, nvarchar, or pgvector vector columns.

  • Attachment mappings can be used on root tables and embedded tables:

    • Root-table attachments are stored on the mapped document.
    • Embedded-table attachments are stored on the parent document with a generated
      {PropertyName}/{primary-key-values}/ name prefix.

  • In this article:

Root table attachments​

Given a root SQL table with a binary column:

CREATE TABLE files (
    id        SERIAL PRIMARY KEY,
    filename  TEXT        NOT NULL,
    mime_type TEXT        NOT NULL,
    content   BYTEA       NOT NULL   -- binary attachment
);

Set Type = CdcColumnType.Attachment on the content column entry:

new CdcSinkTableConfig
{
    CollectionName = "Files",
    SourceTableName = "files",
    PrimaryKeyColumns = ["id"],
    Columns =
    [
        new CdcColumnMapping() { Column = "id",        Name = "Id" },
        new CdcColumnMapping() { Column = "filename",  Name = "Filename" },
        new CdcColumnMapping() { Column = "mime_type", Name = "MimeType" },
        new CdcColumnMapping()
        {
            Column = "content", Name = "file",
            // store this column as a RavenDB attachment
            Type = CdcColumnType.Attachment 
        },
    ]
}

The content column is stored as an attachment named "file" on the document.
The Name value on the attachment mapping becomes the attachment name.

The filename and mime_type columns are stored as regular document properties.
They are not used to set the RavenDB attachment name or content type.
The attachment content type is determined by the runtime value type:
application/octet-stream for binary values, text/plain; charset=utf-8 for text values.

Embedded table attachments​

Columns mapped as attachments on embedded tables are stored as attachments on the parent document.
The attachment name is prefixed with the embedded property name and primary key values.

Given a parent products table and a child photos table:

CREATE TABLE products (
    product_id  SERIAL PRIMARY KEY,
    name        TEXT NOT NULL
);

CREATE TABLE photos (
    product_id  INT  NOT NULL REFERENCES products(product_id),
    photo_num   INT  NOT NULL,
    caption     TEXT,
    thumbnail   BYTEA,          -- binary attachment
    PRIMARY KEY (product_id, photo_num)
);

Configure the embedded table with Type = CdcColumnType.Attachment on the attachment column:

new CdcSinkEmbeddedTableConfig
{
    SourceTableName = "photos",
    PropertyName = "Photos",
    PrimaryKeyColumns = ["photo_num"],
    JoinColumns = ["product_id"],
    Columns =
    [
        new CdcColumnMapping() { Column = "photo_num", Name = "PhotoNum" },
        new CdcColumnMapping() { Column = "caption",   Name = "Caption" },
        new CdcColumnMapping()
        {
            Column = "thumbnail", Name = "thumb",
            Type = CdcColumnType.Attachment
        },
    ]
}

A photo with photo_num = 1 creates an attachment named "Photos/1/thumb" on the parent document.
The prefix "Photos/1/" is generated from the PropertyName and the primary key value.

Attachment naming​

Root table attachments​

The attachment name is exactly the Name value on the mapping entry with Type = CdcColumnType.Attachment.

Column = "content", Name = "file", Type = CdcColumnType.Attachment
→ Attachment name: "file"

Embedded table attachments​

The attachment name is built from the embedded PropertyName, the embedded row's primary key values,
and the mapping Name. The generated attachment name has this format:

{PropertyName}/{primary-key-value-1}/.../{Name}

where {Name} is the Name value from the attachment column mapping.

PropertyName = "Photos"
PrimaryKeyColumns = ["photo_num"]  → photo_num = 1
Column = "thumbnail", Name = "thumb", Type = CdcColumnType.Attachment
→ Attachment name: "Photos/1/thumb"

For composite primary keys, the key values are added in PrimaryKeyColumns order and separated with /:

PrimaryKeyColumns = ["date", "seq"]  → date='2024-01', seq=3
→ Attachment name: "Photos/2024-01/3/thumb"

Attachment lifecycle​

  • INSERT
    A non-null attachment value creates an attachment on the document.

  • UPDATE
    A non-null attachment value replaces the existing attachment.
    If the new value is NULL, the attachment update is skipped and the existing attachment is left unchanged.

  • DELETE (embedded item)
    When the embedded delete is processed, attachments generated from that embedded item are removed from the parent document.

  • DELETE (root document)
    When the root document is deleted, the document and its attachments are deleted.

In this article