CODEWITHSUNDEEP
node.jspostgresqlknex.js
Alin
Alin

Reputation: 14581

node.js and postgres bulk upsert or another pattern?

I am using Postgres, NodeJS and Knex.

I have the following situation:

  1. A database table with a unique field.

  2. In NodeJS I have an array of objects and I need to:

    a. Insert a new row, if the table does not contain the unique id, or

    b. Update the remaining fields, if the table does contain the unique id.

From my knowledge I have three options:

  1. Do a query to check for each if exists in database and based on the response, do a update or insert. This costs resources because there's a call for each array item and also a insert or update.
  2. Delete all rows that have id in array and then perform a insert. This would mean only 2 operations but the autoincrement field will keep on growing.
  3. Perform an upsert since Postgres 9.5 supports it. Bulk upsert seems to work and there's only a call to database.

Looking through the options I am aware of, upsert seems the most reasonable one but does it have any drawbacks?

Upvotes: 1

Views: 1236

Answers (1)

Aikon Mogwai
Aikon Mogwai

Reputation: 5225

Upsert is a common way.

Another way is use separate insert/update operations and most likely it will be faster:

  1. Define existing rows select id from t where id in (object-ids) (*)

  2. Update existing row by (*) result

  3. Filter array by (*) and bulk insert new rows.

See more details for same question here

Upvotes: 2

Related Questions