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: "[email protected]"
  .onConflict("email")
  .merge()
  .returning("*");
    
⚠️ 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:
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)],
    

There's also this package that implements upsert for you, but to be honest I didn't try.

 npmjs.com/package/knex-upsert

Regards.
            
5. What Are Headings and Why Are They Important? (Many People Doesn't Even Know the Use of it?)
              WEBDEVTALES - Sep 6
      
Built on Forem — the open source software that powers DEV and other inclusive communities.
      
Made with love and Ruby on Rails. DEV Community © 2016 - 2024.