Upsert & findOrCreate & firstOrInit

SlintORM provides three methods for handling the common pattern of “insert or update” logic. Each handles the edge cases of race conditions and unsaved instances differently.

upsert

The upsert(filter, data) method inserts a row or updates it if a matching row already exists. It returns "inserted" or "updated". The SQL implementation varies by driver: Postgres uses ON CONFLICT, MySQL uses ON DUPLICATE KEY, and SQLite/MongoDB perform a manual two-step query.

// upsert(filter, data) — returns "inserted" | "updated"
// Postgres:  ON CONFLICT (filter columns) DO UPDATE SET ...
// MySQL:     ON DUPLICATE KEY UPDATE ...
// SQLite:    manual get → insert/update
// MongoDB:   manual get → insert/update

const result = await User.upsert(
  { email: 'joe@example.com' },  // filter — conflict columns
  { name: 'Joe', role: 'admin' } // data to insert or update
);

if (result === 'inserted') {
  console.log('New user created');
} else {
  console.log('Existing user updated');
}

Multi-column conflicts

// Multi-column conflict key (Postgres)
const result = await Booking.upsert(
  { roomId: 5, date: '2025-12-01' },
  { guestName: 'Alice', status: 'confirmed' }
);
// Generates: ON CONFLICT (roomId, date) DO UPDATE SET ...

Returning the entity

Chain .returning() to get the full entity back from an upsert instead of just the status string.

// With .returning() — get the entity back
const user = await User.upsert(
  { email: 'joe@example.com' },
  { name: 'Joseph' }
).returning();

console.log(user.name); // "Joseph" — full entity with .update(), .delete()

findOrCreate

findOrCreate(filter, defaults?) attempts to find a record matching the filter. If none is found, it creates one using the filter merged with defaults. Returns { record, created } — the record is always a full EntityWithUpdate<T> instance.

// findOrCreate(filter, defaults) — returns { record, created }
const { record, created } = await User.findOrCreate(
  { email: 'joe@example.com' },
  { name: 'Joe', role: 'user' }
);

if (created) {
  console.log('Created user:', record.id);
} else {
  console.log('Found existing user:', record.id);
}

// record is a full EntityWithUpdate<T> — can call .update(), .delete()
await record.update({ lastLoginAt: new Date().toISOString() });

Defaults from filter

// Defaults can be omitted if filter already has all required fields
const { record, created } = await User.findOrCreate(
  { email: 'alice@example.com', name: 'Alice', role: 'user' }
);
// Uses the filter object itself as the insert data

Atomicity notes

// Atomic find-or-create — runs inside a transaction
// (not all drivers support this; falls back to best-effort on SQLite/MongoDB)
const { record, created } = await User.findOrCreate(
  { email: 'rare@example.com' },
  { name: 'Rare', role: 'user' }
);

if (!created) {
  // Another request may have inserted it between our check and insert
  // race condition is handled at the DB level where possible
}

firstOrInit

firstOrInit(filter, defaults?) returns the first matching record, or creates an unsaved instance (not persisted). The returned entity has a isNew property set to true when it has not been saved. Call .update() on it to persist.

// firstOrInit(filter, defaults?) — returns record or unsaved instance
// If found: returns the matching entity (persisted)
const user = await User.firstOrInit(
  { email: 'joe@example.com' }
);

if (user.isNew) {
  // Was not found — user is an unsaved instance
  // You can modify it and call .update() to persist
  user.name = 'Joe';
  await user.update();  // inserts the row
} else {
  console.log('Found:', user.name);
}

With default values

// With defaults — merges defaults onto the unsaved instance
const user = await User.firstOrInit(
  { email: 'newuser@example.com' },
  { name: 'New User', role: 'user', active: true }
);

console.log(user.name);   // "New User"
console.log(user.role);   // "user"
console.log(user.isNew);  // true — not persisted yet

// Persist it
await user.update();
console.log(user.isNew);  // false — now saved

PreviousCRUD NextBatch Operations