Egon ORM

Appearance

Find or create ​

Return the row with a given unique value, creating it if it does not exist, safely when two requests ask at once.

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 { cast, createRepo, type Repo, Schema, t } from "egon-orm";
import { createMigrator, defineMigration } from "egon-orm/migration";

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

class Tag extends Schema("tags", {
  id: t.integer().primaryKey().dbGenerated(),
  name: t.string(),
}) {
  static changeset(tag: Tag | typeof Tag, params: unknown) {
    return cast(tag, params, ["name"])
      .validateRequired(["name"])
      .updateChange("name", (name) => name.trim().toLowerCase())
      .uniqueConstraint("name");
  }
}

// The tables, created by a migration.
const tables = defineMigration({
  up(m) {
    m.createTable("tags", (table) => {
      table.id({ type: "integer" });
      table.text("name").notNull().unique(); // tags_name_key: what onConflict needs
    });
  },
  down(m) {
    m.dropTable("tags");
  },
});

/** 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: 20261021000401, name: "find_or_create_tables", migration: tables }],
  }).up();
  await admin.close();
}

The function ​

ts
// Safe under concurrency: the unique index decides, and a lost race reads the winner's row.
export async function findOrCreateTag(db: Repo, name: string): Promise<Tag> {
  const changeset = Tag.changeset(Tag, { name });
  const inserted = await db.insertOrThrow(changeset, { onConflict: "nothing" }); // null on a conflict
  return inserted ?? (await db.getByOrThrow(Tag, { name: changeset.getField("name") }));
}

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

ts
await createTables();
ts
const first = await findOrCreateTag(repo, "TypeScript");
const again = await findOrCreateTag(repo, " typescript "); // the same tag
  • The insert goes first. With onConflict: "nothing", a conflict is not an error: the result is null, and the row that won is read by its unique value.
  • Reading first and inserting when nothing was found has a race: two requests both find nothing, and one insert fails. Inserting first lets the unique index decide.
  • The changeset normalises the name (trim, lower case), and the lookup uses the normalised value from changeset.getField("name").
  • The function takes the repo as an argument, so it works with tx inside a transaction.

Good to know

  • onConflict: "nothing" needs a unique index or constraint on the column.
  • On Postgres, a conflicting insert still uses a value of the serial sequence: ids get gaps. That is harmless.
  • An invalid changeset (an empty name) throws here, since the function uses insertOrThrow; return the result instead to show the errors on a form.

See also ​