Updating EdgeDB object using TypeScript/JS client library not working as expected for object links

Question

I am a beginner in EdgeDb and in one of my sample projects I created a schema in EdgeDb having a Candidate type and it contains candidate base details plus Education details. Education is another type that is multi linked to Candidate. Both of their structure are given below

 type Candidate {
        required first_name: str;
        required last_name: str;
        required dob: str;
        required profile_summary: str;
        required primary_contact_number: str;
        alternate_contact_number: str;
        required email: str;
        hobbies: array <str>;
        multi education_details: Education;
    }

  type Education {
        required course_name: str;
        required institution_name: str;
        required course_start_month: str;
        required course_start_year: str;
        required course_end_month: str;
        required course_end_year: str;
        pass_percentage: str;
        constraint exclusive on ((.course_name, .institution_name));
    }

The candidate can have zero or more education details. They can also update these education details like delete an education details or add new education details or update an already inserted education details.I was able to insert or update education details using for loops. But can't figure out how to do delete education details if it's not there as data does not include that.

Let's say we get the data like this

{
  "candidate_details": {
    "id": "887d4936-c69b-11ee-b4eb-9796ea46b1cc",
    "first_name": "Kiran",
    "last_name": "Muralee",
    "primary_contact_number": "9037682934",
    "alternate_contact_number": "7112658119",
    "dob": "03-10-1983",
    "hobbies": [
      "Pencil Drawing",
      "Carroms playing"
    ],
    
    "education_details": [
      {
        "institution_name": "BMC",
        "course_name": "CSE",
        "pass_percentage": "56%",
        "course_start_month": "10",
        "course_end_month": "6",
        "course_start_year": "2006",
        "course_end_year": "2010"
      }
    ]
  }
}

We can certainly do an Update using the following code

    let educationDetails = await e.params({ items: e.json }, (params) => {
      return e.for(e.json_array_unpack(params.items), (item) => {
    
        return  e.insert(e.Education, {
          institution_name: e.cast(e.str, item.institution_name),
          course_name: e.cast(e.str, item.course_name),
          pass_percentage: e.cast(e.str, item.pass_percentage),
          course_start_month: e.cast(e.str, item.course_start_month),
          course_end_month: e.cast(e.str, item.course_end_month),
          course_start_year: e.cast(e.str, item.course_start_year),
          course_end_year: e.cast(e.str, item.course_end_year)
        }).unlessConflict(education => ({
             on : [education.institution_name, education.course_name],
             else: e.update(e.Education, ()=>({
                 set: {
                        course_start_month,
                        course_end_month,
                        course_start_year,
                        course_end_year
                      }
             })
          });
      });
    }).run(client, {items:education_details}) //Get from candidate object data


    e.update(e.Candidate, (candidate) => ({
      filter: e.op(candidate.id, "=", e.uuid(id)),
      set: {
        first_name,
        last_name,
        dob:e.str(dob),
        primary_contact_number,
        alternate_contact_number,
        hobbies,
        profile_summary,
        education_details: e.select(educationDetails),
      }
    })).run(client)

But how do we incorporate code so that if there is need for any Education details to get deleted along with insert/update.Also if we have nested Objects, like if there is a projects type inside Education, how are we handle those inside our code.One possible solution is to add a Education details as JSON. But is there a cleaner way to do this?.

"education_details": [
      {
        "institution_name": "BMC",
        "course_name": "CSE",
        "pass_percentage": "56%",
        "course_start_month": "10",
        "course_end_month": "6",
        "course_start_year": "2006",
        "course_end_year": "2010",
        "projects": [
          {
            "project_name": "Medinova",
            "project_description": "A project on clinic systems"
          }
        ]
      }
    ]

Answer 1

The EdgeDB documentation about Insert / Handling conflicts​ / .unlessConflict validates your approach for inserting or updating Education: you use unlessConflict to either insert new Education details or update existing ones based on a unique constraint.

However, to manage deletions, you would need to:

• fetch the current Education details linked to the Candidate;
• compare these details with the incoming data;
• delete any Education records not present in the incoming data.

And for nested objects like Projects inside Education, you would essentially follow a similar approach. You would need to make sure each Project linked to Education is updated, inserted, or deleted as needed. That might involve additional loops and checks within each Education detail.

If Education details contain nested Projects and you are deleting an Education object, you will need to decide how to handle these Projects.

• If Projects are tightly coupled with Education and should not exist independently, you should delete them before or along with the Education object they are associated with.
• If Projects can be reassigned or are independent entities, you will need additional logic to handle these cases, which could involve updating their links or moving them to a different parent entity.

All that can be a bit complex because EdgeDB's query language is designed to be safe (and using strong typing) and explicit, meaning bulk deletions based on dynamic conditions will require careful handling.

It is important to handle these operations within a transaction to make sure data integrity.
EdgeDB supports transactions, so you should wrap these operations in a transaction block.

A simplified version of the process might be, using select and delete:

async function updateCandidateWithEducationDetails(client, candidateData) {
  // Start a transaction
  const transaction = client.transaction();
  await transaction.start();

  try {
    // Assume function implementations for handling projects and education details
    // These functions should encapsulate the logic for updating, inserting, and deleting
    // as necessary based on your application's rules

    // Step 1: Handle Projects for Education to be Deleted
    await handleProjectsForDeletedEducation(client, candidateData.education_details);

    // Step 2: Update or Insert Education Details (and their nested Projects)
    for (let educationDetail of candidateData.education_details) {
      await updateOrInsertEducationDetail(client, educationDetail);
    }

    // Step 3: Delete Unneeded Education Details
    // That would involve identifying which Education details need to be removed
    // based on the provided candidateData compared to what is currently stored
    await deleteUnneededEducationDetails(client, candidateData);

    // Step 4: Update Candidate Details
    // That is a simplified update operation; your actual update might include more details
    await client.query(`
      UPDATE Candidate
      SET {
        first_name := <str>$first_name,
        last_name := <str>$last_name,
        // Other candidate fields
      }
      WHERE .id = <uuid>$id;
    `, {
      id: candidateData.id,
      first_name: candidateData.first_name,
      last_name: candidateData.last_name,
      // Other parameters
    });

    // If all operations are successful, commit the transaction
    await transaction.commit();
  } catch (error) {
    // If any operation fails, roll back the transaction
    await transaction.rollback();

    // Handle the error, e.g., log it or throw it to be handled by higher-level logic
    console.error("Failed to update candidate and education details:", error);
    throw error; // Rethrow or handle as needed
  }
}

// Placeholder function for handling projects of education details to be deleted
async function handleProjectsForDeletedEducation(client, educationDetails) {
  // Logic to delete or reassign projects before deleting education details
}

// Placeholder function for updating or inserting an education detail
async function updateOrInsertEducationDetail(client, educationDetail) {
  // Insert or update logic, including handling of nested Projects
}

// Placeholder function for deleting unneeded education details
async function deleteUnneededEducationDetails(client, candidateData) {
  // Identify and delete education details not present in the update
}