If you stumble upon this information in 2021, keep in mind that the Repository.save() method in Typeorm will perform an update or insert operation if it detects a match with a primary key. This functionality is also applicable when using sqlite.

According to the documentation:

/**
 * Saves all provided entities in the database.
 * If the entities do not exist in the database, they will be inserted; otherwise, updated.
 */

2023 Update

New feature alert: Introducing the upsert method!

await this.yourRepository.upsert({name: 'John'}, ['id']) // assuming id is unique

Dealing with constraints on multiple columns:

To define a constraint, use the Unique decorator

@Entity()
@Unique('constraint_name', ['col_one', 'col_two'])

Then you can utilize the upsert method in your code.

await this.yourRepository.upsert({name: 'John'}, ['constraint_name'])

For avoiding duplicates in MySQL and Postgres, you can utilize the INSERT IGNORE method:

await connection.createQueryBuilder()
        .insert()
        .into(Post)
        .values(post)
        .orIgnore()
        .execute();

If you're facing issues with the ONCONFLICT feature in MySQL, this solution might come in handy. Found on Github

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

If you're using Postgres and TypeORM and need to upsert multiple records, you can access the row you want to update/insert by utilizing the excluded keyword.

const articles = [{ id: 1, title: "First Article" }, { id: 2, title: "Second Article" }];

await connection.createQueryBuilder()
        .insert()
        .into(Article)
        .values(articles)
        .onConflict(`("id") DO UPDATE SET "title" = excluded."title"`)
        .execute();

This method has proven effective for me.

I incorporated the solution provided by @DedaDev.

Your specific entity:

@Entity()
@Unique('constraint_name', ['id'])

When it comes to your service:

await this.yourRepository.upsert(
  {
    id: uuid,
    key1: value1,
    ...
  },
  {
    skipUpdateIfNoValuesChanged: true, // If true, postgres will skip the update if no values would be changed (reduces writes)
    conflictPaths: ['id'], // column(s) name that you would like to ON CONFLICT
  },
);

Latest in 2021: TypeORM's version 0.2.40 introduces native upsert support

Learn more about the upsert feature in TypeORM here.

By harnessing the power of NestJS and TypeORM in the year 2024, you have the capability to utilize the upsert function to effectively manage all potential edge cases that may arise.

Imagine a scenario where you are working with an entity named Thing, which consists of attributes like name and color.

@Entity("thing")
@Unique(["name", "color"])
export class Thing {
  @PrimaryGeneratedColumn()
  id: string;

  @Column()
  name: string;

  @Column()
  color: string;

  // more properties can be added here
}

The challenge occurs when the id of the Thing entity is interconnected with various tables in your database, resulting in potential foreign key constraint violations.

You can seamlessly address this issue by implementing the upsert feature along with specific configuration options.

Here's an example implementation demonstrating how you can use upsert with detailed settings:

await repository.upsert(
  [
    { name: "a red thing", color: "red" },
    { name: "a blue thing", color: "blue" },
  ],
  {
    conflictPaths: ["name", "color"],
    skipUpdateIfNoValuesChanged: true,
    upsertType: "on-conflict-do-update",
  }
);

If you encounter any challenges despite this approach, you can resort to a fail-safe method utilizing the following code snippet:

const repository = dataSource.getRepository(Thing);

await repository
    .createQueryBuilder()
    .insert()
    .into(Thing)
    .values([
    { name: "a red thing", color: "red" },
    { name: "a blue thing", color: "blue" },
    ])
    .orUpdate(["name", "color"])
    .orIgnore()
    .execute();

A new tool is available to integrate with TypeORM for simplifying this process, you can find it here.

If you're looking to utilize the "preload" method within the Repository class, check out this link for more information: https://typeorm.delightful.studio/classes/repository_repository.repository.html#preload

The "preload" method creates a new entity based on the provided JavaScript object plan. If the entity already exists in the database, it will be loaded along with related data, then all values will be replaced with those from the new object. The updated entity is essentially the original entity from the database with updated properties. It's important to note that the object must have an entity id or primary key to locate the entity. If no entity is found with the given id, undefined will be returned.

Just keep in mind that, as mentioned earlier, you can only search by ID using this method.

Below is a standard code snippet for handling upsert operations:

async upsertRecord(createDtoGeneric, read: boolean = true) {
    const pk = createDtoGeneric.getPK()
    // Uncomment the lines below if the primary key is not an auto-generated column
    // if (!createDtoGeneric[createDtoGeneric.getPK()])
    //     createDtoGeneric.initPK()
    const upsertResponse = await this._repo.upsert(createDtoGeneric, { conflictPaths: createDtoGeneric.getConflictPaths(), skipUpdateIfNoValuesChanged: true, upsertType: 'on-conflict-do-update' })
    if (read) {
        delete createDtoGeneric[pk]
        return await this.findOneByQuery(createDtoGeneric)
    }
    else
        return upsertResponse;
}