How to do upserts in Knex.js (PostgreSQL)
Tags: knex, upserts, insert, sql
Published: 2020-04-26 12:11:11

---
title: How to do upserts in Knex.js (PostgreSQL)
published: true
description: How to do upserts in Knex.js (PostgreSQL)
tags: 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](https://knexjs.org/) together with [Objection.js](https://vincit.github.io/objection.js/). My database of choice is [PostgreSQL](https://www.postgresql.org/). At some point, I needed to do [UPSERTs](https://wiki.postgresql.org/wiki/UPSERT).

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](https://github.com/knex/knex/pull/3763) that would implement it for all engines.~~

**November 2020 update:** The [pull request](https://github.com/knex/knex/pull/3763) 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 👇

```js
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 👇

```js
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**:

```js
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:

```js
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!