How to do upserts in Knex.js (PostgreSQL)

#knex #upserts #insert #sql

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("*");

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();

⚠️ 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)],
    );
}

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)],
    );
}

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!