Feature Request: Built-in Upsert/Merge for Modern Rust DSL

[ON38]

Feature Request: Built-in Upsert/Merge Operation in Modern Rust DSL

Preface: I am a database administrator, not a professional software developer. This request is based on my (and AugmentCode's) best understanding of HelixDB after working through the documentation and trial-and-error testing with the modern Rust DSL. We have not yet tested HelixQL's UpsertN functionality mentioned below directly. We apologize for any technical inaccuracies and welcome corrections or guidance.

Summary

Request for a built-in upsert_n (or merge_n) function in the modern Rust DSL to simplify idempotent node creation, achieving feature parity with the UpsertN function available in the legacy HelixQL API.

---

Use Case

We're building a SQL Server metadata tracking system using HelixDB. We extract metadata from 100+ SQL Servers, load it into a graph database for dependency analysis, and refresh daily with a clean snapshot.

The de-duplication challenge:

During a single load, we process thousands of objects that reference the same shared Extended Property values repeatedly.

Concrete example:

• Processing 1,000 tables that all have Extended Property value "GTM" for Extended Property "Owned_By"
• Without upsert: Create 1,000 duplicate "GTM" nodes ❌
• With upsert: Create 1 "GTM" node, referenced 1,000 times ✅

Why model values as nodes instead of properties?

We need to query by value efficiently (e.g., "Find all tables owned by "GTM". Extended Property name "Owned_By" with property value 'GTM'"). Modeling values as nodes with relationships enables:

• Efficient reverse lookup via graph traversal: PropertyValue → ExtendedProperty → Table
• Aggregations (count how many objects reference each value)
• Graph traversal queries (find all tables sharing the same property value)

If stored as properties on the node, these queries would require full table scans filtering on property values.

This pattern applies to many shared entity types:

• Property values (same value on hundreds of objects)
• Owner names (same user owns many objects)
• Categorical values (tags, labels, classifications)

What we need: "Get existing node if it exists, create if it doesn't" - exactly what HelixQL's UpsertN provides.

---

Background: HelixQL Has This, Modern Rust DSL Doesn't

HelixQL (Legacy) appears to provide simple, elegant upsert:

existing <- N<PropertyValue>::WHERE(_::{value}::EQ("GTM"))
node <- existing::UpsertN({value: "GTM"})

Behavior:

• Updates properties if node exists
• Creates node if it doesn't exist
• Merges properties (preserves unspecified fields)
• Clean, concise, easy to understand

Modern Rust DSL lacks equivalent functionality, requiring manual conditional logic to achieve the same result.

---

Current Workaround (Manual Upsert Pattern)

Currently, we implement upsert logic manually using conditional execution.

Example: Ensuring single "GTM" node for 1,000 references

write_batch()
    .var_as(
        "existing",
        g().n_where(SourcePredicate::eq("value", "GTM"))
    )
    .var_as_if(
        "created",
        BatchCondition::VarEmpty("existing".to_string()),
        g().add_n("PropertyValue", vec![
            ("value", PropertyValue::from("GTM")),
        ])
    )
    .returning(vec!["existing", "created"])

This works (validated in testing), but has significant drawbacks:

• ❌ Verbose (must implement for every entity type that needs de-duplication)
• ❌ Repeated boilerplate pattern across codebase
• ❌ Error-prone (easy to miss properties in conditional branches)
• ❌ Performance overhead (multiple queries per upsert vs single atomic operation)

---

Proposed Solution (AI Generated proposal)

Add a built-in upsert_n function to the modern Rust DSL:

// Proposed API (option 1: separate match/set properties)
write_batch()
    .var_as(
        "property_value",
        g().upsert_n(
            "ExtendedPropertyValue",
            vec![("value", "GTM")],  // Match on unique key
            vec![]                    // No additional properties to set
        )
    )
    .returning(vec!["property_value"])

Or (option 2: builder pattern):

write_batch()
    .var_as(
        "property_value",
        g().upsert_n("ExtendedPropertyValue")
            .match_on(vec![("value", "GTM")])
            .set(vec![])  // Optional: properties to update
    )
    .returning(vec!["property_value"])

Behavior:

1. Search for node with matching properties (atomic operation)
2. If found → return existing node (optionally update properties)
3. If not found → create with match properties (plus any set properties)
4. Return the node (created or found)

This ensures:

• Processing 1,000 tables with Extended Property "GTM" creates 1 node, not 1,000 duplicates
• Single atomic operation (no race conditions in concurrent loads)
• Simple, declarative syntax matching HelixQL's UpsertN

---

Comparison: HelixQL vs Modern Rust DSL

HelixQL (legacy - simple and concise):

existing <- N<PropertyValue>::WHERE(_::{value}::EQ("GTM"))
node <- existing::UpsertN({value: "GTM"})

2 lines of code

Modern Rust DSL (current manual pattern - verbose):

write_batch()
    .var_as(
        "existing",
        g().n_where(SourcePredicate::eq("value", "GTM"))
    )
    .var_as_if(
        "created",
        BatchCondition::VarEmpty("existing".to_string()),
        g().add_n("PropertyValue", vec![
            ("value", PropertyValue::from("GTM")),
        ])
    )
    .returning(vec!["existing", "created"])

13 lines of code

The verbosity gap (2 lines → 13 lines) creates a significant barrier when implementing this pattern across many entity types.

---

Benefits of Built-in Upsert

✅ Simplicity - 2-3 lines vs 20+ lines of boilerplate
✅ Maintainability - Less code to maintain per entity type
✅ Readability - Intent is clear (upsert vs conditional logic)
✅ Error Prevention - Single source of truth for properties
✅ Feature Parity - Matches HelixQL's UpsertN capability in modern DSL
✅ Migration Path - Eases transition from legacy HelixQL to production Rust DSL

---

Migration Path from Legacy

For teams using HelixQL's UpsertN, providing a Rust DSL equivalent would ease migration to the production-standard architecture without losing critical functionality.

---

Questions for HelixDB Team

1. Is this feature on the roadmap?
2. Are there technical constraints preventing a built-in upsert?
3. Is using unique constraints + error handling the intended approach for this use case?
4. Is the manual conditional pattern the recommended long-term approach?

---

Conclusion

A built-in upsert_n function would:

• Bring modern Rust DSL to feature parity with legacy HelixQL
• Reduce verbosity and improve developer experience (2 lines vs 23 lines)
• Enable smooth migration from legacy HelixQL to production-standard Rust DSL
• Lower barrier to adoption for projects requiring idempotent operations

We're excited about using HelixDB for this project and believe this feature would strengthen the modern Rust DSL significantly.

Thank you for considering this feature request!

---

Tested on: HelixDB 2.0.1, Rust SDK 2.0

[ON38]

My mistake, I found coalesce as the existing path I was requesting.