Egon ORM

Appearance

Upserts ​

Insert a row, or update the one that is already there, in one statement.

Setup ​

The code of this recipe depends on these declarations: the imports, the schema classes, the repo, and the migration that creates the tables. Run the recipe in an empty database of its own: each recipe creates its own tables, and some have the same names (posts) with other columns.

ts
import { postgres } from "@egon-orm/postgres";
import { change, createRepo, from, Schema, t, timestamps } from "egon-orm";
import { createMigrator, defineMigration } from "egon-orm/migration";

const repo = createRepo({ adapter: postgres({ connectionString: process.env.DATABASE_URL }) });

class Setting extends Schema("settings", {
  key: t.string().primaryKey(),
  value: t.json<unknown>(),
  ...timestamps(),
}) {}

class DailyView extends Schema("daily_views", {
  pageId: t.integer().primaryKey(),
  day: t.date().primaryKey(), // a "YYYY-MM-DD" string
  views: t.integer(),
}) {}

// The tables, created by a migration.
const tables = defineMigration({
  up(m) {
    m.createTable("settings", (table) => {
      table.text("key").notNull().primaryKey();
      table.jsonb("value").notNull();
      table.timestamps();
    });
    m.createTable("daily_views", (table) => {
      table.integer("page_id").notNull().primaryKey(); // a composite key: page and day
      table.date("day").notNull().primaryKey();
      table.integer("views").notNull();
    });
  },
  down(m) {
    m.dropTable("daily_views");
    m.dropTable("settings");
  },
});

/** Creates the tables, in the database that DATABASE_URL names. */
export async function createTables(): Promise<void> {
  const admin = postgres({ connectionString: process.env.DATABASE_URL });
  await createMigrator({
    adapter: admin,
    migrations: [{ version: 20261021001201, name: "upserts_tables", migration: tables }],
  }).up();
  await admin.close();
}

Insert or update one row ​

The examples below start from these rows, after creating the tables:

ts
await createTables();
ts
// Insert the setting, or replace its value (and updatedAt) if the key exists.
const saved = await repo.insertOrThrow(change(Setting, { key: "theme", value: "dark" }), {
  onConflict: { target: ["key"], update: ["value", "updatedAt"] },
});
  • target is the unique key that may conflict: fields, { constraint: "name" }, or a fragment for an index on an expression. MySQL takes no target: any unique key conflicts there.
  • update is "all" (every inserted column), a list of fields, or { except: [...] }. List updatedAt when the update should touch it.
  • The returned row is the one in the table after the statement, whichever happened.

Insert unless it exists ​

ts
// Insert unless it exists; data is null when it did.
const created = await repo.insert(change(Setting, { key: "theme", value: "blue" }), {
  onConflict: "nothing",
});
if (created.success && created.data === null) {
  // the key was taken: nothing was written
}

With onConflict: "nothing", data is null when the row existed; its type includes null only with this option.

Many rows at once ​

ts
// An analytics import: store the latest totals per page and day, many rows in one
// statement. A row that is already there takes the imported values.
await repo.insertAll(
  DailyView,
  [
    { pageId: 1, day: "2026-10-20", views: 1 },
    { pageId: 2, day: "2026-10-20", views: 1 },
  ],
  { onConflict: { target: ["pageId", "day"], update: "all" } },
);

insertAll skips changesets: use it for data you trust, such as imports.

update writes the values of the row being inserted; it does not compute from the row that is there. To increment a counter, insert the row if it is missing, and otherwise update it with an expression:

ts
export async function countView(pageId: number, day: string) {
  const inserted = await repo.insertOrThrow(change(DailyView, { pageId, day, views: 1 }), {
    onConflict: "nothing",
  });
  if (inserted === null) {
    await repo.updateAll(
      from(DailyView, "v")
        .where({ pageId, day })
        .set(({ v }) => ({ views: v.views.add(1) })),
    );
  }
}

DailyView.day is a t.date() field: a calendar date with no time, which reads and writes as a "YYYY-MM-DD" string (Types), so countView takes the day as one. Counting the same page and day twice:

ts
await countView(3, "2026-10-21"); // a new row: views 1
await countView(3, "2026-10-21"); // the same row: views 2
const counted = await repo.getOrThrow(DailyView, { pageId: 3, day: "2026-10-21" });
counted.views; // => 2

Inserting first is safe when two requests count at once: one inserts, the other finds the row there and increments it.

Good to know

  • An upsert is not insertOrUpdate. insertOrUpdate chooses insert or update from the changeset (new record or loaded row) and never asks the database; onConflict lets the database decide.
  • A changeset with nested association changes cannot be combined with onConflict.
  • To read a row or create it, see Find or create.

See also ​