TypeORM Upsert: Update If Exists, Create If Not Exists

Updated: September 20, 2022 By: Snowball Post a comment

Upsert (this term is combined from two words: update and insert) is a database operation that updates a record it already exists in a table. Otherwise, insert a new row if that record doesn’t exist.

This succinct, practical article shows you how to perform upsert operations in TypeORM. You can use a query builder (very flexible) or the upsert() method (very concise). Another possible solution is to use the save() method (this only works when you provide an id – primary key).

Table Of Contents

1 Using Query Builder

2 Using the upsert() method

3 Using the save() method

Using Query Builder

Let’s say we have an entity called User as declared below (email addresses are unique):

// KindaCode.com
// User entity
import { Entity, PrimaryGeneratedColumn, Column, Unique } from 'typeorm';

@Entity()
@Unique(['email'])
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  email: string;

  @Column()
  name: string;
}

Then we can do an upsert like this:

const userRepository = myDataSource.getRepository(User);
userRepository
      .createQueryBuilder()
      .insert()
      .into(User)
      .values([
        { email: '[email protected]', name: 'Hello' },
        { email: '[email protected]', name: 'Goodbye' },
      ])
      .orUpdate({ conflict_target: ['email'], overwrite: ['name'] })
      .execute();

If an email already exists in the database, that user’s name will be updated. Otherwise, a new user will be created with the provided email address and name.

Using the upsert() method

The upsert() method is intuitive and convenient to achieve the goal:

const userRepository = myDataSource.getRepository(User);
await userRepository.upsert(
      [
        { email: '[email protected]', name: 'Hello Update' },
        { email: '[email protected]', name: 'Goodbye New Update' },
      ],
      ['email']
);

This method is supported by AuroraDataApi, Cockroach, Mysql, Postgres, and SQLite database drivers.

Using the save() method

Using the save() method seems neat and elegant but this approach only works when you provide ids:

const userRepository = myDataSource.getRepository(User);
await userRepository.save([
      { id: 1, email: '[email protected]', name: 'Hello Update' },
      { id: 2, email: '[email protected]', name: 'Goodbye Update' },
]);

I think most people will prefer the upsert() method for the job.

That’s it. Further reading: