upsert with return functionality

[EvaLok]

proposing the following functionality (essentially insert... on conflict.. returning..):

const result = model.upsert({
    id: 123,
    someValue: "test"
}, {
    returnAll: true
});

console.log(result); // outputs entire updated model, including preexisting fields, auto-updated fields

const result = model.upsert({
    id: 123,
    someValue: "test"
});

console.log(result); // outputs some indicator as to whether or not an insert or an update occurred

const result = model.upsert({
    id: 123,
    someValue: "test"
}, {
    returnPartial: [
        'id',
        'someValue',
        'someOtherValue'
    ]
);

console.log(result); // outputs a partial object with specified fields loaded

at least in postgres, this is possible in a single operation, but it's not currently possible in a single operation with TypeORM (example not tested, just for showing a current rough solution..):

import { Column, PrimaryColumn, Repository, UpdateDateColumn } from "typeorm";
import { Entity } from "typeorm/decorator/entity/Entity";

export class SomeServiceClass {
    constructor(
        private repo: Repository<SomeAggregate>
    ) {}

    public async handleSomeEventWithTypeORMMethods( event: ISomeEvent ): Promise<SomeAggregate>  {
        let model = await this.repo.findOne({
            id: event.id,
        });

        if ( ! model ) {
            model = new SomeAggregate();
        }

        model.valueToUpdate = event.valueToUpdate;

        await this.repo.persist(model);

        const updatedModel = await this.repo.findOne({
            id: event.id,
        });

        return updatedModel;
    }

    public async handleSomeEventWithRawQuery( event: ISomeEvent ): Promise<SomeAggregate>  {
        const results = await this.repo.query(`
            INSERT INTO
                some_table
            (
                "id",
                "valueToUpdate"
            )
                VALUES
            (
                $1,
                $2
            )

            ON CONFLICT( "id" )
                DO UPDATE
                    SET
                        "valueToUpdate" = $2
                    WHERE
                        "some_table"."id" = $1

            RETURNING *
        `, [
            event.id,
            event.valueToUpdate
        ]);

        return results[0];
    }
}


@Entity()
export class SomeAggregate {
    @PrimaryColumn("uuid")
    public id: string;

    @Column("text")
    public valueToUpdate ?: string;

    @Column("text")
    public anotherValue ?: string;

    @UpdateDateColumn({type: "timestamp with time zone"})
    public modifiedAt ?: Date;
}

export interface ISomeEvent {
    id: string;
    valueToUpdate: string;
}

[pleerock]

I added onConflict method to insert query builder.
This functionality was made just to support onConflict clause inside InsertQueryBuilder and it only appends given ON CONFLICT statement into insertion query, no more then that.
It has such limited functionality due to complexity and flexibility of "on conflict" statement.
You can use it this way:

        await connection.createQueryBuilder()
            .insert()
            .into(Post)
            .values(post2)
            .onConflict(`("id") DO NOTHING`)
            .execute();

        await connection.createQueryBuilder()
            .insert()
            .into(Post)
            .values(post2)
            .onConflict(`("id") DO UPDATE SET "title" = :title`)
            .setParameter("title", post2.title)
            .execute();

For now upsert method will not be implemented repository/entity manager.
If you need a single query you have to use a bit inconvenient query and build onConflict on your own as in example above. If you simply want to a update an entity use regular save method instead.

Will be released in 0.1.6

[ShayBox]

+1 for actual upsert support

[landon9720]

The above examples show using onConflict to set the DO UPDATE SET ... clause with a single input record to values. But values also supports an array of input records. How can the DO UPDATE SET parameters be set per record passed to values?

[ajayvardhan]

@pleerock @Kononnable any word on support on array of inputs?

also, is this only for postgres or does it work for mysql as well?

[rsbh]

can we update row on conflict?

[matiasdelgado]

@landon9720 in postgres you can use the excluded key, like this:

await connection.createQueryBuilder()
    .insert()
    .into(Post)
    .values([post1, post2, ...])
    .onConflict(`("id") DO UPDATE SET "title" = excluded.title`)
    .execute();

[mschuttt]

What's the status on this issue?

[EvaLok]

@mschuttt i haven't been following this project for years, however, i ended up using another library instead which is based on sequelize. it has (user-friendly) upsert functionality and the typescript features are fairly similar if you're used to typeorm

https://github.com/RobinBuschmann/sequelize-typescript

[danielmhanover]

For those who do not want to finagle with onConflict themselves:

https://github.com/danielmhanover/typeorm-upsert

[sandokanelcojo]

Hi ! Is there any upsert on this issue?

[JeremyBernier]

For those who do not want to finagle with onConflict themselves:

https://github.com/danielmhanover/typeorm-upsert

Unfortunately the npm module doesn't work because the code shows up empty. Somebody submitted a PR a couple months ago to fix it danielmhanover/typeorm-upsert#3 which still hasn't been merged in. Seems that the repo isn't being maintained.

Here's the upsert() function I ended up writing for PostgreSQL:

import { getRepository, InsertResult } from "typeorm";

/**
 * Upsert for TypeORM on PostgreSQL
 * Returns InsertResult object (contains ID)
 * @param repo Repository
 * @param {object | object[]} data Data to upsert. Can be object or array
 * @param {string} primaryKey Name of column that is primary key
 * @returns {Promise<InsertResult>}
 */
export default function upsert(Entity, data, primaryKey: string): Promise<InsertResult> {
  const repo = getRepository(Entity);
  const row = Array.isArray(data) ? data[0] : data;
  const keys = Object.keys(row);

  if (keys.length < 1) {
    throw new Error("Cannot upsert without any values specified");
  }

  const updateStr = keys.map(key => `"${key}" = EXCLUDED."${key}"`).join(",");

  return repo
    .createQueryBuilder()
    .insert()
    .values(data)
    .onConflict(`("${primaryKey}") DO UPDATE SET ${updateStr}`)
    .execute();
}

https://gist.github.com/JeremyBernier/5683ebc8e83990a4d4e3d0abd1a9549d

[danielmhanover]

Sorry for the delay on maintaining that package - it is still being maintained but admittedly I went dark over January. Should work on npm now

[shadrech]

@danielmhanover Why not just contribute to typeorm rather than doing your own thing boss? 😊. Would be better if you incorporated your library into typeorm somehow

[moltar]

I don't think any upsert can work properly due to #2215.

If you are upserting with a PK in the object, hoping that the record identified by PK will be updated, it won't actually work.

TypeORM omits PKs from value list and the query ends up with insert without PK there, and thereby ON CONFLICT will never trigger.

Example of query generated, where the object did contain an id column with a value:

INSERT INTO "user" ("first_name", "last_name", "is_admin")
  VALUES ($1, $2, $3)
ON CONFLICT ("id")
  DO UPDATE SET
    "id" = EXCLUDED."id",
    "first_name" = EXCLUDED."first_name",
    "last_name" = EXCLUDED."last_name",
    "is_admin" = EXCLUDED."is_admin"
  RETURNING
    *

Object I was trying to upsert:

{ id: 1, firstName: 'baz', lastName: 'bar', isAdmin: false }

[moltar]

Otherwise, I think I have a pretty clean upsert implementation here:

import { EntityManager, QueryFailedError } from './'

interface Type<T> extends Function {
  new (...args: any[]): T
}

export interface UpsertOptions<E extends {}, K = keyof E> {
  skip?: K[]
  pk?: K | K[]
}

function arrayify<T>(maybe: T | T[]) {
  return Array.isArray(maybe) ? maybe : [maybe]
}

function objectKeys<T extends object>(obj: T) {
  return Object.keys(obj) as Array<keyof typeof obj>
}

export async function upsert<E extends {}>(
  entityManager: EntityManager,
  Entity: Type<E>,
  data: E,
  options: UpsertOptions<E> = {},
): Promise<E> {
  type K = keyof typeof data

  const pks = arrayify(options.pk || ('id' as K))
  const skip = options.skip || []
  const keys = objectKeys(data).filter((key) => !skip.includes(key))

  if (keys.length === 0) {
    throw new QueryFailedError('', [], 'Cannot upsert without values specified.')
  }

  const {
    namingStrategy: { columnName },
    driver: { escape },
  } = entityManager.connection

  const col = (key: K) => {
    return escape(columnName(key.toString(), undefined, []))
  }

  const pk = pks.map(col).join(', ')
  const set = keys
    .map(col)
    .map((k) => `${k} = EXCLUDED.${k}`)
    .join(', ')

  const { generatedMaps } = await entityManager
    .getRepository(Entity)
    .createQueryBuilder()
    .insert()
    .values(data)
    .onConflict(`(${pk}) DO UPDATE SET ${set}`)
    .returning('*')
    .execute()

  return generatedMaps[0] as E
}

[nowshad-sust]

import { EntityManager, QueryFailedError } from "typeorm";

interface Type<T> extends Function {
  new (...args: any[]): T;
}

export interface UpsertOptions<E extends {}, K = keyof E> {
  skip?: K[];
  pk?: K | K[];
}

function arrayify<T>(maybe: T | T[]) {
  return Array.isArray(maybe) ? maybe : [maybe];
}

function objectKeys<T extends object>(obj: T) {
  return Object.keys(obj) as Array<keyof typeof obj>;
}

export async function upsert<E extends {}>(
  entityManager: EntityManager,
  Entity: Type<E>,
  data: E | E[], // allow both single entity or multiple entities
  options: UpsertOptions<E> = {}
): Promise<E> {
  const row = Array.isArray(data) ? data[0] : data; // get a single entity
  type K = keyof typeof row;

  const pks = arrayify(options.pk || ("id" as K));
  const skip = options.skip || [];
  const keys = objectKeys(row).filter((key) => !skip.includes(key)); // get key from the single entity

  if (keys.length === 0) {
    throw new QueryFailedError(
      "",
      [],
      "Cannot upsert without values specified."
    );
  }

  const {
    namingStrategy: { columnName },
    driver: { escape },
  } = entityManager.connection;

  const col = (key: K) => {
    return escape(columnName(key.toString(), undefined, []));
  };

  const pk = pks.map(col).join(", ");
  const set = keys
    .map(col)
    .map((k) => `${k} = EXCLUDED.${k}`)
    .join(", ");

  const { generatedMaps } = await entityManager
    .getRepository(Entity)
    .createQueryBuilder()
    .insert()
    .values(data)
    .onConflict(`(${pk}) DO UPDATE SET ${set}`)
    .returning("*")
    .execute();

  return generatedMaps[0] as E;
}

@moltar Updating a little bit of your code can handle data as an array as well.

[moltar]

@nowshad-sust that solution is brittle, because it assumes that every item in the array has the same keys.

But I do like the idea of adding the array support.

[ahmadalfy]

I would love to share my findings after spending a lot of time looking into this issue. TypeORM documentation really needs a lot of work. I tried the Upsert library, tried the solutions mentioned here but nothing really worked.

I was using MySQL and sadly I discovered that onConflict doesn't work with MySQL (by enabling logging option!). The source code shows another method called orUpdate that works perfectly with MySQL. Here is my code:

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

@moltar it works with arrays as well.

[GD-Dheer]

I see the typeorm devs are currently integrating upsert functionality. Any approximate on how long this will be released in your prod version? @nebkat

Currently ran into this problem and debating if its worth writing a custom mysql solution or wait it out for your team

[nebkat]

I'm just a contributor, am also waiting for it to get merged. It breaks the API slightly so it might not be accepted for a while.

[GD-Dheer]

I'm just a contributor, am also waiting for it to get merged. It breaks the API slightly so it might not be accepted for a while.

Thanks for the quite reply :)

[DedaDev]

Is there a plausible method of using this to use a constraint for the conflict target?

this worked for me,
#4122 (comment)

[conoremclaughlin]

For anyone that stumbles upon this from Google, upsert is directly supported in ^0.2.40. Thanks to the hard work of of the TypeORM folks:
#8104

Which closed:
#2363

The new method signature is:

orUpdate(overwrite: string[], conflictTarget?: string | string[]): this;

So for @ahmadalfy's solution, you'll now need:

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

And you can find the current implementation of orUpdate here:

typeorm/src/query-builder/InsertQueryBuilder.ts

Line 356 in 26581d0

if (this.connection.driver.supportedUpsertType === "on-conflict-do-update") {

[LuisOfCourse]

What about unique set of colums? I have the following example

@Entity()
@Unique('dayofweek-pickuppoint', ['pickuppoint', 'dayOfWeek'])
export class WorkDays {
	@PrimaryGeneratedColumn({ type: 'bigint' })
	id: string;

	@ManyToOne(() => Pickuppoint, (pickuppoint) => pickuppoint.workhours)
	pickuppoint: Pickuppoint;

	@Column({ type: 'enum', enum: DayOfWeekEnum })
	dayOfWeek: DayOfWeekEnum;

	@Column({ type: 'time' })
	startMorning: string;

	@Column({ type: 'time' })
	endMorning: string;

	@Column({ type: 'time' })
	startAfternoon: string;

	@Column({ type: 'time' })
	endAfternoon: string;
}

this.workHourRepository
			.createQueryBuilder()
			.insert()
			.values(plaindays)
			.orUpdate(
				[
					'dayOfWeek',
					'startMorning',
					'startAfternoon',
					'endMorning',
					'endAfternoon',
				],
				[ 'dayofweek-pickuppoint'],
			)
			.execute();

This example here gives the following errror: "QueryFailedError: column "dayofweek-pickuppoint" does not exist". So you cant use Unique set of columns?

[CNLHC]

What about unique set of colums? I have the following example

@Entity()
@Unique('dayofweek-pickuppoint', ['pickuppoint', 'dayOfWeek'])
export class WorkDays {
	@PrimaryGeneratedColumn({ type: 'bigint' })
	id: string;

	@ManyToOne(() => Pickuppoint, (pickuppoint) => pickuppoint.workhours)
	pickuppoint: Pickuppoint;

	@Column({ type: 'enum', enum: DayOfWeekEnum })
	dayOfWeek: DayOfWeekEnum;

	@Column({ type: 'time' })
	startMorning: string;

	@Column({ type: 'time' })
	endMorning: string;

	@Column({ type: 'time' })
	startAfternoon: string;

	@Column({ type: 'time' })
	endAfternoon: string;
}

this.workHourRepository
			.createQueryBuilder()
			.insert()
			.values(plaindays)
			.orUpdate(
				[
					'dayOfWeek',
					'startMorning',
					'startAfternoon',
					'endMorning',
					'endAfternoon',
				],
				[ 'dayofweek-pickuppoint'],
			)
			.execute();

This example here gives the following errror: "QueryFailedError: column "dayofweek-pickuppoint" does not exist". So you cant use Unique set of columns?

Same problem here. I am looking for how to achieve INSERT ... ON CONFLICT CONSTRAINT 'foo' DO UPDATE ...

[chiragdev8062]

    .returning('*')

it can return id ?

[omerd-cyera]

@LuisOfCourse @CNLHC Did you find a solution/workaround?

[Standin-Alone]

upsert typeorm function not working.... still inserting even i defined the column that should be unique

[mlevkovsky]

upsert typeorm function not working.... still inserting even i defined the column that should be unique

is you're column nullable? In Mysql null values can't be used for a constraint

[ajangidm]

For anyone that stumbles upon this from Google, upsert is directly supported in ^0.2.40. Thanks to the hard work of of the TypeORM folks: #8104

Which closed: #2363

The new method signature is:

orUpdate(overwrite: string[], conflictTarget?: string | string[]): this;

So for @ahmadalfy's solution, you'll now need:

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

And you can find the current implementation of orUpdate here:

typeorm/src/query-builder/InsertQueryBuilder.ts

Line 356 in 26581d0

if (this.connection.driver.supportedUpsertType === "on-conflict-do-update") {

Does this solution work for

For anyone that stumbles upon this from Google, upsert is directly supported in ^0.2.40. Thanks to the hard work of of the TypeORM folks: #8104

Which closed: #2363

The new method signature is:

orUpdate(overwrite: string[], conflictTarget?: string | string[]): this;

So for @ahmadalfy's solution, you'll now need:

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

And you can find the current implementation of orUpdate here:

typeorm/src/query-builder/InsertQueryBuilder.ts

Line 356 in 26581d0

if (this.connection.driver.supportedUpsertType === "on-conflict-do-update") {

@conoremclaughlin, does this work for postgres?

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

In my case it is inserting new rows for all the entities, while I am trying to update entities which have PK property set.
I am using following versions:
node: 18.16.0
typeorm: 0.3.17
postgres: 12.1

[erBhushanPawar]

I know I'm too late to catch on this but here is my solution

async upsertRecord(createDtoGeneric, read: boolean = true) {
        const pk = createDtoGeneric.getPK()
        // enable below if pk is not 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;
    }