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 ...

Get the entity back

upsert() returns "inserted" | "updated". To get the full entity back, call .get() with the same filter afterward.

// upsert returns "inserted" | "updated" — use get() to fetch afterward
const status = await User.upsert(
  { email: 'joe@example.com' },
  { name: 'Joseph' }
);

if (status === 'inserted') {
  console.log('Created new user');
} else {
  const user = await User.get({ email: 'joe@example.com' });
  console.log(user!.name); // "Joseph"
}

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: r, created: c } = 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 entity methods (.update(), .delete(),.refresh(), .toJSON()) attached. If the entity was not found, calling .update() on the unsaved instance will insert a new row.

// 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) {
  console.log('Found:', user.name);
}

// If not found: returns a non-persisted instance with entity methods
const newUser = await User.firstOrInit(
  { email: 'newuser@example.com' },
  { name: 'New User' }
);
// newUser has .update(), .delete(), .refresh(), .toJSON()
// Call .update() to persist (inserts since it has no PK)
await newUser!.update({ name: 'New User' });

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"

// Persist it — inserts a new row since there's no PK
await user!.update();

PreviousCRUD NextBatch Operations