upsert
Create or update database records in SurrealDB. Insert new records when they don't exist, or merge data with existing records when they do.
Instructions
Upsert a record: create if it doesn't exist, merge/update if it does.
This tool is perfect when you want to ensure a record exists with specific data, regardless of whether it already exists. It will:
Create a new record with the specified ID if it doesn't exist
Merge the provided data into the existing record if it does exist
Always succeed (unless there's a database error)
Args: thing: The full record ID in format "table:id" (e.g., "user:john", "settings:global") data: The data for the record. If record exists, this will be merged with existing data namespace: Optional SurrealDB namespace override. If not provided, uses SURREAL_NAMESPACE env var. database: Optional SurrealDB database override. If not provided, uses SURREAL_DATABASE env var.
Returns: A dictionary containing: - success: Boolean indicating if upsert was successful - data: The record after upserting - created: Boolean indicating if a new record was created (vs updated) - error: Error message if upsert failed (only present on failure)
Examples: >>> await upsert("user:john", {"name": "John Doe", "email": "john@example.com"}) {"success": true, "data": {"id": "user:john", "name": "John Doe", ...}, "created": true}
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| thing | Yes | ||
| data | Yes | ||
| namespace | No | ||
| database | No |
Implementation Reference
- surreal_mcp/server.py:633-707 (handler)The primary handler for the 'upsert' tool. It validates input, resolves database context, checks record existence, invokes repo_upsert helper, and returns structured response including whether created or updated.@mcp.tool() async def upsert( thing: str, data: Dict[str, Any], namespace: Optional[str] = None, database: Optional[str] = None, ) -> Dict[str, Any]: """ Upsert a record: create if it doesn't exist, merge/update if it does. This tool is perfect when you want to ensure a record exists with specific data, regardless of whether it already exists. It will: - Create a new record with the specified ID if it doesn't exist - Merge the provided data into the existing record if it does exist - Always succeed (unless there's a database error) Args: thing: The full record ID in format "table:id" (e.g., "user:john", "settings:global") data: The data for the record. If record exists, this will be merged with existing data namespace: Optional SurrealDB namespace override. If not provided, uses SURREAL_NAMESPACE env var. database: Optional SurrealDB database override. If not provided, uses SURREAL_DATABASE env var. Returns: A dictionary containing: - success: Boolean indicating if upsert was successful - data: The record after upserting - created: Boolean indicating if a new record was created (vs updated) - error: Error message if upsert failed (only present on failure) Examples: >>> await upsert("user:john", {"name": "John Doe", "email": "john@example.com"}) {"success": true, "data": {"id": "user:john", "name": "John Doe", ...}, "created": true} >>> await upsert("user:john", {"email": "newemail@example.com"}) # Update existing {"success": true, "data": {"id": "user:john", "name": "John Doe", "email": "newemail@example.com", ...}, "created": false} >>> await upsert("settings:global", {"theme": "dark", "language": "en"}) {"success": true, "data": {"id": "settings:global", "theme": "dark", "language": "en"}, "created": true} """ try: ns, db = resolve_namespace_database(namespace, database) # Validate thing format if ":" not in thing: raise ValueError(f"Invalid record ID format: {thing}. Must be 'table:id'") logger.info(f"Upserting record {thing}") # Check if record exists try: existing = await repo_query(f"SELECT * FROM {thing}", namespace=ns, database=db) created = not existing or len(existing) == 0 except Exception: created = True # Extract table name for repo_upsert table = thing.split(":", 1)[0] # Perform upsert - pass full record ID result = await repo_upsert( table=table, id=thing, data=data, add_timestamp=True, namespace=ns, database=db ) # Get the first result upserted_record = result[0] if result else {} return { "success": True, "data": upserted_record, "created": created } except Exception as e: logger.error(f"Upsert failed for {thing}: {str(e)}") raise Exception(f"Failed to upsert {thing}: {str(e)}")
- Core database helper function that constructs and executes the SurrealQL UPSERT MERGE query, handling timestamps and parameters.async def repo_upsert( table: str, id: Optional[str], data: Dict[str, Any], add_timestamp: bool = False, namespace: Optional[str] = None, database: Optional[str] = None, ) -> List[Dict[str, Any]]: """Create or update a record in the specified table. Args: table: The table name id: Optional record ID (if provided, upserts that specific record) data: The record data to upsert add_timestamp: Whether to add/update the 'updated' timestamp namespace: Optional namespace override (uses env var if not provided) database: Optional database override (uses env var if not provided) Returns: The upserted record(s) """ data.pop("id", None) if add_timestamp: data["updated"] = datetime.now(timezone.utc) query = f"UPSERT {id if id else table} MERGE $data;" return await repo_query(query, {"data": data}, namespace=namespace, database=database)
- surreal_mcp/server.py:633-633 (registration)FastMCP decorator that registers the upsert function as an MCP tool, defining its schema from type hints and docstring.@mcp.tool()