loading...

How to do upserts in Knex.js (PostgreSQL)

vvo profile image Vincent Voyer ใƒปUpdated on ใƒป3 min read

tl;dr; Since November 2020, you can use Knex.js's onConflict/merge/ignore API features to do upserts in Knex.js: https://knexjs.org/#Builder-onConflict


Hey there, here's a quick post on something that took me way too long to figure out how to do.

For my Node.js database needs, I am using Knex.js together with Objection.js. My database of choice is PostgreSQL. At some point, I needed to do UPSERTs.

What's an upsert? It's a way to express "I have this list of records, identified by this key or combination of keys, please insert them (if they are new) or update them (if they are already existing). Update or insert, that's it.

Upsert is not part of the SQL standard but luckily this has been implemented in PostgreSQL (and other engines) behind the keywords ON CONFLICT DO UPDATE/NOTHING.

This has yet to be implemented in Knex.js and there's a promising pull request that would implement it for all engines.

November 2020 update: The pull request got merged on Knex and you can now natively do upserts and find or create actions using Knex.

Here's an example:

Find a user, update the name if they exist, otherwise create the user ๐Ÿ‘‡

const [user] = await knex("users")
  .insert({
    name: "Vincent Voyer",
    email: "vincent@codeagain.com"
  })
  .onConflict("email")
  .merge()
  .returning("*");
Enter fullscreen mode Exit fullscreen mode

Create a user if they do not exist, otherwise do nothing ๐Ÿ‘‡

await knex("users")
  .insert({
    name: "Vincent Voyer",
    email: "vincent@codeagain.com"
  })
  .onConflict("email")
  .ignore();
Enter fullscreen mode Exit fullscreen mode

โš ๏ธ You cannot use returning("*") with ignore() though. But you can still request your user afterward.

The new onConflict/ignore/merge features are available in Knex.js >= 0.21.10 and well documented here: https://knexjs.org/#Builder-onConflict.

I am leaving the previous article content, which discusses how to do upserts before it was available in knex.js here:


For now, here's what you can do:

import Knex from "knex";

const knex = Knex({
  client: 'pg',
  connection: process.env.DATABASE_URL,
});

const records = [
  { user_id: 2, team_id: 20, role: "admin" },
  { user_id: 3, team_id: 10, role: "member" },
  { user_id: 4, team_id: 10, role: "admin" },
];

async function upsert() {
  const result = await knex.raw(
      `? ON CONFLICT (user_id, team_id)
              DO UPDATE SET
              role = EXCLUDED.role,
              updated_at = CURRENT_TIMESTAMP
            RETURNING *;`,
      [knex("roles").insert(records)],
    );
}
Enter fullscreen mode Exit fullscreen mode

This would insert or update the records, identified by the combination of user_id, team_id. This means you would need the table roles to have a unique constraint of user_id, team_id.

You could also do this:

import Knex from "knex";

const knex = Knex({
  client: 'pg',
  connection: process.env.DATABASE_URL,
});

const records = [
  { user_id: 2, team_id: 20, role: "admin" },
  { user_id: 3, team_id: 10, role: "member" },
  { user_id: 4, team_id: 10, role: "admin" },
];

async function upsert() {
  const result = await knex.raw(
      `? ON CONFLICT user_id
            DO NOTHING
            RETURNING *;`,
      [knex("roles").insert(records)],
    );
}
Enter fullscreen mode Exit fullscreen mode

If you wanted to insert or do nothing (only insert the new records).

This is the most straightforward solution I have seen which does not try to be smart and generic. You could always wrap all of that and extend the knex query builder if you wanted something more generic and reusable. If you happen to do so or if you have more questions, drop me a comment here!

Discussion

pic
Editor guide
Collapse
offirmo profile image
Offirmo

Merci Vincent ! Just what I needed!

Collapse
abdellani profile image
Mohamed ABDELLANI

Hi
Thanks for sharing.
There's also this package that implements upsert for you, but to be honest I didn't try.
npmjs.com/package/knex-upsert

Regards.

Collapse
vvo profile image
Vincent Voyer Author

Thanks, since this was a sensible operation for me I wanted something less generic where I have control over the code.