TypeORM Upsert: Update If Exists, Create If Not Exists

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).

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:

• TypeORM: Check Whether a Row Exists or Not
• How to Store JSON Object with TypeORM
• Pagination in TypeORM (Find Options & QueryBuilder)
• Using ENUM Type in TypeORM
• TypeORM: Adding created_at and updated_at columns
• TypeORM: Add Columns with Array Data Type

You can also check out our Javascript category page, TypeScript category page, Node.js category page, and React category page for the latest tutorials and examples.