Update & Patch

This page covers fine-grained update operations on single-table data — embedded objects and embedded arrays stored directly on a record. For patching related records across foreign keys (FROM and VIA navigation properties), see Relations — Relational Patches.

Simple Updates

The simplest update sets scalar fields directly. The primary key must be in the payload to identify the record:

await users.updateOne({
  id: 1,
  name: 'Alice Smith',
  status: 'active',
})

Only the provided fields are changed — other fields remain untouched.

Embedded Object Patches

Nested objects stored on a record (not navigation properties) use a strategy-based approach controlled by @db.patch.strategy.

Replace Strategy (Default)

Without @db.patch.strategy, the entire nested object is overwritten. Omitted sub-fields are lost:

// Current: { address: { line1: '123 Main St', line2: 'Apt 4', city: 'Portland' } }

await table.updateOne({ id: 1, address: { city: 'Seattle' } })
// Result: { address: { city: 'Seattle' } }
// ⚠️ line1 and line2 are gone

Merge Strategy

With @db.patch.strategy 'merge', only the provided nested fields are updated — others are preserved:

@db.patch.strategy 'merge'
address: {
    line1: string
    line2?: string
    city: string
}

// Current: { address: { line1: '123 Main St', line2: 'Apt 4', city: 'Portland' } }

await table.updateOne({ id: 1, address: { city: 'Seattle' } })
// Result: { address: { line1: '123 Main St', line2: 'Apt 4', city: 'Seattle' } }
// ✅ line1 and line2 preserved

Embedded Array Patches

Arrays stored directly on the record support five patch operators for fine-grained manipulation:

Operator	Effect
$replace	Replace the entire array
$insert	Append new items
$upsert	Insert or update items by key
$update	Update existing items by key
$remove	Remove items by key or value

When multiple operators appear on the same field, they are always applied in order: remove → update → upsert → insert — regardless of the order they appear in the object.

SQL Adapters

In relational databases (SQLite, PostgreSQL, MySQL), arrays are stored as JSON columns. Patch operators work via read-modify-write. For collections that need frequent partial updates in SQL, consider modeling them as separate tables with FROM or VIA relations instead.

Primitive Arrays

For simple value arrays like tags: string[], operators work by value equality — no key fields are needed:

// Append items
await table.updateOne({ id: 1, tags: { $insert: ['urgent', 'reviewed'] } })

// Remove by value
await table.updateOne({ id: 1, tags: { $remove: ['draft'] } })

// Full replacement
await table.updateOne({ id: 1, tags: { $replace: ['final', 'approved'] } })

Unique Primitive Arrays

When @expect.array.uniqueItems is set, $insert automatically skips duplicates:

@expect.array.uniqueItems
tags: string[]

// Current tags: ['api', 'backend']
await table.updateOne({ id: 1, tags: { $insert: ['api', 'frontend'] } })
// Result: ['api', 'backend', 'frontend'] — 'api' was silently skipped

Keyed Object Arrays

@expect.array.key marks which properties identify an element inside an embedded object array. Keys are required for $update, $upsert, and key-based $remove:

variants: {
    @expect.array.key
    sku: string
    color: string
    stock: number
}[]

Multiple fields can be marked as keys to form a composite key — an element matches only when all key fields match.

Operations with Replace Strategy (Default)

// Insert a new variant
await table.updateOne({
  id: 1,
  variants: { $insert: [{ sku: 'B2', color: 'blue', stock: 10 }] },
})

// Update — replaces the entire matched element
await table.updateOne({
  id: 1,
  variants: { $update: [{ sku: 'B2', color: 'navy', stock: 8 }] },
})

// Remove by key
await table.updateOne({
  id: 1,
  variants: { $remove: [{ sku: 'B2' }] },
})

// Upsert — insert if not found, replace if found
await table.updateOne({
  id: 1,
  variants: { $upsert: [{ sku: 'C3', color: 'green', stock: 3 }] },
})

Under replace strategy, $update and $upsert replace the entire matched element — every required field must be present.

Operations with Merge Strategy

With @db.patch.strategy 'merge', updates merge into the existing element, preserving fields not explicitly provided:

@db.patch.strategy 'merge'
attributes: {
    @expect.array.key
    name: string
    value: string
    visible: boolean
}[]

// Current: [{ name: 'size', value: 'M', visible: true }]
await table.updateOne({
  id: 1,
  attributes: { $update: [{ name: 'size', value: 'XL' }] },
})
// Result: [{ name: 'size', value: 'XL', visible: true }] — 'visible' preserved

Keyless Object Arrays

For object arrays without @expect.array.key, matching falls back to full deep value equality. This means $remove works (match entire objects), but $update is effectively a no-op (there are no key fields to locate a target element for partial update):

// Append
await table.updateOne({
  id: 1,
  logs: { $insert: [{ message: 'Deployed', ts: 1710000000 }] },
})

// Remove by exact match
await table.updateOne({
  id: 1,
  logs: { $remove: [{ message: 'Deployed', ts: 1710000000 }] },
})

// Full replacement
await table.updateOne({ id: 1, logs: { $replace: [] } })

For anything beyond simple append/remove, add @expect.array.key to enable key-based matching.

JSON Fields

Fields annotated with @db.json reject all patch operators. The field is stored as a single opaque JSON column — only plain replacement is allowed:

@db.json
settings: {
    theme: string
    notifications: boolean
}

// ✅ Works — plain replacement
await table.updateOne({
  id: 1,
  settings: { theme: 'dark', notifications: false },
})

// ❌ Fails — patch operators rejected on @db.json fields
await table.updateOne({
  id: 1,
  settings: { $replace: { theme: 'dark' } },
})

The same applies to @db.json arrays — use a plain array value instead of patch operators.

Combining Operators

Multiple operators can be used on the same field, and multiple fields can be patched in one request:

await table.updateOne({
  id: 1,
  variants: {
    $remove: [{ sku: 'OLD' }],
    $update: [{ sku: 'A1', color: 'red', stock: 5 }],
    $insert: [{ sku: 'NEW', color: 'green', stock: 10 }],
  },
  tags: { $insert: ['reviewed'] },
  title: 'Updated title',
})

Operators are always applied in order: remove → update → upsert → insert.

What This Page Does NOT Cover

Navigation property patches — operating on related records across foreign keys using the same $insert, $update, $remove, $upsert, and $replace operators on FROM and VIA relations — are covered in Relations — Relational Patches.

Next Steps

• CRUD Operations — Basic insert, read, update, delete
• Queries & Filters — Filtering, sorting, and projection
• Transactions — Atomic multi-table operations
• Relations — Relational Patches — Patching navigation properties