The Evolution and Practice of Upsert Operations in TypeORM: From Save Method to Native Upsert Support

The Technical Evolution of Upsert Operations in TypeORM

In database operations, upsert (update or insert) is a common requirement that allows inserting records when they don't exist and updating them when they do. TypeORM, as a popular TypeScript ORM framework, has undergone significant technical evolution in this functionality.

Early Solution: Limitations of the Save Method

In earlier versions of TypeORM, developers typically used the Repository.save() method to implement upsert-like functionality. This method could automatically determine whether to perform an insert or update operation when the entity object contained primary key or unique identifier fields. However, this approach had clear limitations: when entity objects didn't contain primary key information, the save method couldn't identify existing records and could only perform insert operations, potentially leading to data duplication.

// Example of early save method implementation for upsert
let contraption = await thingRepository.save({id: 1, name: "New Contraption Name !"});

These limitations prompted developers to seek more complete solutions, particularly in business scenarios without explicit primary key identification.

Intermediate Solution: QueryBuilder with onConflict Clauses

As TypeORM evolved, developers began using QueryBuilder with database-specific onConflict clauses to implement true upsert functionality. This approach provided finer-grained control, allowing developers to specify conflict resolution strategies.

// Implementing upsert using onConflict in PostgreSQL
await connection.createQueryBuilder()
    .insert()
    .into(Post)
    .values(post2)
    .onConflict(`("id") DO NOTHING`)
    .execute();

// Updating specific fields of conflicting records
await connection.createQueryBuilder()
    .insert()
    .into(Post)
    .values(post2)
    .onConflict(`("id") DO UPDATE SET "title" = :title`)
    .setParameter("title", post2.title)
    .execute();

For MySQL databases, TypeORM provided the orUpdate method as an alternative:

// Implementing upsert using orUpdate in MySQL
await getConnection()
    .createQueryBuilder()
    .insert()
    .into(GroupEntity)
    .values(updatedGroups)
    .orUpdate({ 
        conflict_target: ['id'], 
        overwrite: ['name', 'parentId', 'web', 'avatar', 'description'] 
    })
    .execute();

While powerful, this approach required developers to have deep understanding of syntax differences between databases and resulted in relatively verbose code.

Modern Solution: Native Upsert Method

In the latest versions of TypeORM, the framework has finally introduced a native upsert method, significantly simplifying upsert implementation. This method accepts two parameters: the data to insert or update, and the fields or constraint names used to identify uniqueness.

// Using primary key as unique identifier
await this.yourRepository.upsert({name: 'John'}, ['id']);

// Using custom unique constraint
await this.yourRepository.upsert({name: 'John'}, ['constraint_name']);

To use custom constraints, you first need to define unique constraints in the entity class:

@Entity()
@Unique('constraint_name', ['col_one', 'col_two'])
export class YourEntity {
    // Entity definition
}

Technical Selection Recommendations

When choosing upsert implementation approaches, developers should consider the following factors:

1. TypeORM Version: For newer versions, prioritize the native upsert method
2. Database Type: Different databases have varying levels of upsert support, requiring compatible implementation approaches
3. Business Complexity: Use native methods for simple scenarios, while complex scenarios may require QueryBuilder flexibility
4. Performance Requirements: Native methods are typically optimized for better performance

Conclusion

The evolution of upsert functionality in TypeORM reflects the framework's responsive approach to developer needs. From the initial limitations of the save method, through flexible solutions implemented via QueryBuilder, to the final native upsert method, TypeORM provides developers with multiple options. In practical development, it's recommended to choose the most appropriate implementation based on specific technology stacks and business requirements, while staying updated with TypeORM version releases to adopt optimal technical solutions promptly.