???? Understanding UPSERT in Databases & Fixing Our FeedLog Issue

??What is UPSERT & Why Use It?

UPSERT (a combination of UPDATE + INSERT) is a powerful database operation that:

? Inserts a new row if it doesn’t exist

? Updates the existing row if a conflict occurs

This is particularly useful when dealing with logs, user activities, and real-time updates, where we need to track changes without creating duplicates.

?? Our Use Case: The FeedLog Table

At our company, we use a FeedLogs table to track candidate shortlisting events for both project-based candidates and public candidates.

Each candidate should only have one entry per status. If a candidate gets shortlisted again, we only update the timestamp (updated_at) instead of creating a duplicate.

?? The Issue: Upsert Not Working as Expected

When trying to upsert into FeedLogs, we faced an issue where duplicate records were being created instead of updating the existing ones.

The original approach used a single unique constraint, but because NULL values are treated differently in SQL, it allowed duplicates.

?? The First Approach: Single Unique Constraint

Initially, we enforced uniqueness on project_candidate_id OR public_candidate_id like this:

CONSTRAINT unique_project_candidate UNIQUE (project_candidate_id, public_candidate_id);
        

?? Issue: This didn't work well because:

• One of the columns was always NULL, and PostgreSQL allows multiple NULL values in a unique constraint.
• Duplicate rows were still being inserted instead of updating the updated_at field.

? The Final Solution: Two Composite Unique Constraints with status_id

To ensure uniqueness while allowing multiple statuses for each candidate, we switched to two composite unique constraints:

-- Unique for project candidates
ALTER TABLE "FeedLogs" 
ADD CONSTRAINT unique_project_candidate_status 
UNIQUE (project_candidate_id, shortlist_status_id);

-- Unique for public candidates
ALTER TABLE "FeedLogs" 
ADD CONSTRAINT unique_public_candidate_status 
UNIQUE (public_candidate_id, shortlist_status_id);
        

? Now, candidates can have multiple statuses but no duplicates for the same status. ? If a candidate is already shortlisted, only updated_at is modified.

?? The Roadblock: Sequelize Doesn’t Handle Composite Unique Keys Well

While PostgreSQL correctly enforces the constraints, Sequelize’s upsert() doesn’t directly support composite keys in v6.

?? Solution for Sequelize v7+ If using Sequelize v7 or later, we can use conflictFields to define composite keys:

await FeedLog.upsert(
    {
        public_candidate_id: publicCandidateId,
        shortlist_status_id: statusId
    },
    {
        conflictFields: ["public_candidate_id", "shortlist_status_id"], // ? Ensure uniqueness
    }
);
        

?? Solution for Sequelize v6 (No conflictFields) Since conflictFields isn’t available in v6, the best approach is findOrCreate:

await FeedLog.findOrCreate({
    where: {
        public_candidate_id: publicCandidateId,
        shortlist_status_id: statusId
    },
    defaults: {} // Keeps existing values, only updates timestamps
});
        

?? Key Takeaways

? Use UPSERT for tracking updates without duplicates

? Avoid single unique constraints when dealing with NULL values

? Use composite unique constraints when handling multiple statuses

? Sequelize v7 supports conflictFields, but v6 requires findOrCreate

?? Have you faced similar UPSERT issues? How did you solve them? Let’s discuss! ??

#Coding #Database #SQL #PostgreSQL #Sequelize #Upsert #BackendDevelopment #Tech #SoftwareEngineering #WebDevelopment #DataIntegrity #DevTips #CodeOptimization #TechSolutions #FullStack #ORM #ScalingTech