Suggestion on PostgreSQL INSERT ON CONFLICT (Upsert)

[ozum]

Hi,

Currently I'm using knex.raw for INSERT ON CONFLICT queries:

knex.raw('INSERT INTO table (id) VALUES (?) ON CONFLICT (id) DO UPDATE SET id = EXCLUDED.id RETURNING id', [id])
    .then((result) => {});

I want to benefit knex.insert and other methods at least part of the query for this type queries. Is there a better way to build this query using any combination of knex.select / knex.raw?

[RubenCordeiro]

I think that knex.raw is still the best way to write Upsert statements with KnexJS. Check the issue #54.

[hayeah]

I put together a monkey patch to experiment with upsert. See:

#54 (comment)

It works like this:

knex("oauths").insert(oauth).onConflict(["provider", "uid"],{
  info: profile,
  updated_at: new Date(),
});

[plurch]

I wrote a relatively simple function which works for my use cases. It uses knex.insert and toString()

https://gist.github.com/plurch/118721c2216f77640232

[ivoneijr]

Nice Suggestion!

[duncanfinney]

@plurch Very cool. Thank you.

[elhigu]

closing as duplicate #54

[ron-liu]

I tried the solution @plurch mentioned. All good except it didn't escape ? properly. Check the below case:

Given the table definition:

create table abc (
	name varchar(100) PRIMARY KEY
)

And the below code:

  const insertSql = knex('abc')
    .insert({ name: '? - \\? - \\? - \\\\? # /? - //? - ///? - ////?' })
    .toString()
  console.log(insertSql)

  await knex.raw(insertSql)

It ends up with the below in the db:
$1 - $2 - $3 - $4 # /$5 - //$6 - ///$7 - ////$8

My working environment is:

• macbook
• postgres running on docker container: postgres:12-alpine

[ron-liu]

I end up using the old school way as below:

const main = async () => {
  await knex('abc')
    .insert({ name: 'a' })
    .catch(x => {
      if (!isUniqueViolationError(x)) throw x
    })

  console.log('done')
}

main()

const isUniqueViolationError = err => err.code === '23505'