[Feature Request] Upsert generation

[tomer-amir-vonage]

Issue type:

[ ] question
[ ] bug report
[x] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

Hi,

I am trying to do an upsert to a large object and I am having trouble.
I saw #1090 but this solution is not very good for me.

to upsert I need to build my query with createQueryBuilder but onConflict only excepts string queries, which makes it very difficult with large objects since I have to write my query manually.

Here is what I tied to do:

await connenction.createQueryBuilder()
	.insert()
	.into(Model)
	.values(model)
	.onConflict(connection.createQueryBuilder().update(Model, model).where({
		uniqueField: someValue
	}).getQuery())
	.execute();

I think that having a function that if the insert fails just updates the whole object would be better.

is there any way to generate the onConflict query that I am missing?

Thanks!

[pleerock]

Here is what I tied to do:

does it work for you?

[tomer-amir-vonage]

Hi @pleerock
it does not... 😞
I'm having issues with parameters

Currently trying to work around it

[pleerock]

Try this:

const updateQb = connection.createQueryBuilder().update(Model, model).where({
		uniqueField: someValue
	}); 

await connenction.createQueryBuilder()
	.insert()
	.into(Model)
	.values(model)
	.onConflict(updateQb.getQuery())
        .setParameters(updateQb.getParameters())
	.execute();

not sure, maybe you'll need to set native parameters as well.

[tomer-amir-vonage]

I did it, but the parameters are not ordered the same in both queries..
also, you need to start the onConflict with '("violation_column") DO' and remove the table name from the update query...

[tomer-amir-vonage]

any news?

[pleerock]

What is your proposal?

[tomer-amir-vonage]

something like onConflict(...conflictFields: string[]).doUpdate() would be nice 😄

[Devang142]

Seems like onConflict method not working for mysql. Can you please add support for mysql as well?

[pthrasher]

EDIT The below solution still has problems. Namely, the update query and the insert query sort column names differently and therefore, values passed through end up not lining up properly for the update query.

Original Comment:
I got this working, but it ended up requiring some string replace to be done on the resulting update query.

See this example to understand what I did to make it work. It's dirty, and would be nice if supported directly in typeorm.

// Model.ts
@Entity()
class User {
  @PrimaryColumn({ type: 'uuid' })
  id: string
  
  @Column()
  firstName: string
  
  @Column()
  lastName: string
  
  @Column()
  age: number
}

// Wherever you're doing your query
const newUser = {
  id: uuidv4(), // let's assume this was passed
                // in from client and might be dupe
  firstName: 'bob',
  lastName: 'bobberson',
  age: 38,
}

const newUserSansId = {
  firstName: 'bob',
  lastName: 'bobberson',
  age: 38,
}

const updateQb = connection
  .createQueryBuilder()
  .update(User, newUserSansId)

await connection
  .createQueryBuilder()
  .insert()
  .into(User)
  .values(newUser)
  .onConflict(
    `(id) DO ${updateQb
      .getQuery()
      // Update the beginning of the statement. Normal updates start slightly
      // differently than ON CONFLICT DO UPDATE
      .replace(/^UPDATE "users"/i, 'UPDATE')
      // Update the parameter numbers. (increment by one to skip the id param)
      .replace(
        /\$(\d{1,2})/g,
        (wholeMatch, numberMatch) =>
          `$${Number.parseInt(numberMatch, 10) + 1}`,
      )}`,
  )
  .setParameters(updateQb.getParameters())
  .execute()

INSERT INTO "users" ("id", "firstName", "lastName", "age")
	VALUES ($1, $2, $3, $4)
	ON CONFLICT (id)
	DO
	UPDATE
	SET
		"firstName" = $2,
		"lastName" = $3,
		"age" = $4
	RETURNING
		"id",
		"firstName",
		"lastName",
		"age";

Additional Information:
The syntax for update after ON CONFLICT is slightly different than a normal update. Also, it gives you a bit more power than a normal update by letting you reference EXCLUDED (similar to NEW in update triggers).

So, for instance, version columns are busted in on conflict updates because the reference is ambigious:

SET "version" = "version" +1

vs:

SET "version" = "table_name"."version" + 1

Also, no where clause exists in my above example because the update only looks at the conflicting row. You can put a where clause if needed, but in my above example it is not needed.

[pbirsinger]

Seems like a lot of unnecessary workarounds when a simple upsert method would solve everything.

[JeremyBernier]

Yes TypeORM needs an upsert function.

For anyone in need of an upsert() function for PostgreSQL, here's the one I wrote

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

[cassioseffrin]

Hello.

The @pleerock suggestion would be very useful. Unfortunately I cannot do it with mysql. Upsert is much faster for massive inserts. It's avoid to run multiples queries to do one atomic operation. For my scenario I have to run a native query as follow bellow, the results of entire process were 3 times faster than using typeorm multiples queries:

const query = INSERT INTO product ( field1, filed2 ) VALUES('field1Value', field2Value) ON DUPLICATE KEY UPDATE field1='field1Value', field2='field2Value';|
await repoProduct.query(query);

However we know that get into native queries aren't so good when the entities have to update. So any news on upsert with query builder for mysql in typeorm?

[yestheboxer]

For everyone that can't upgrade anytime soon. I found this function to work pretty well for my needs.

export class CustomRepository<Entity> extends Repository<Entity> {
  async upsert(
    connect: FindConditions<Entity> | FindConditions<Entity>[],
    newFields: DeepPartial<Entity>,
  ): Promise<Entity> {
    // Use find conditions and search for a pre-existing entity.
    for (const findCondition of _.castArray(connect)) {
      const entity = await this.findOne(findCondition);
      if (entity) {
        // Combine entity with new fields and update DB.
        return this.save(await this.merge(entity, newFields));
      }
    }
    // No entity found. Create and save new entity.
    return this.save(newFields);
  }
 }