Objection.js: `findOrCreate` method?

[Secretmapper]

findOrCreate is a pretty common use case. Right now, I have to write a significant amount of code to run this safely.

Thoughts?

[koskimas]

Would this:

await Person
  .query()
  .findOrInsert({foo: 1, bar: 'blaa'})

be equal to this:

let model = {foo: 1, bar: 'blaa'};
let fetched = await Person.query().where(model);
if (fetched.length === 0) {
  await Person.query().insert(model);
}

[Secretmapper]

There's a possible race condition there before the initial search and the insert, so something like this would be more apt I think:

let model = {foo: 1, bar: 'blaa'};
let fetched = await Person.query().where(model);
if (fetched.length === 0) {
  try {
    await Person.query().insert(model)
  } catch(e) {
    if (isUniqueConstraintError) {
      fetched = await Person.query().where(model);
    }
  }
}

If you think this is within the scope of objection.js, I'll be happy to submit a PR.

[ersims]

It should be possible to provide a separate where-clause to identify which record to update. We often have to create/update/delete records based on non-primary-key columns (still unique though) as seen below where we want to update a record only if we can find one where externalId=123.

await Person
  .query()
  .findOrInsert({foo: 1, bar: 'blaa', externalId: 123}, {externalId: 123})

[elhigu]

These sound like so specialized methods that I would just extend objection.js:s query builder with my favorite flavor of findOrCreateOrModifyOrMaybeDelete method by project basis.

[koskimas]

I agree with @elhigu. findOrInsert is a bit too specialized for objection.

[maltem-za]

@elhigu @koskimas Two years later and I was looking for this function. I think it's a pity you decided not to add it - findOrCreate / findOrInsert is a common enough scenario IMO, especially when working with M2M relations (for example, where there is a more or less finite set of distinct rows on one side of the relation, like colours).

Not adding it means this wheel will keep getting reinvented, and possibly in an unsafe (non-transactional) manner.

Update or delete queries with non-matching where clauses would have no effect anyway, so I don't see why one would need anything specialized there. These scenarios are already supported.

My 2c :)

[DoWhileGeek]

Its been two more years since the last admonishment, and I think its even more of a pity now that its still not here.

[imwillhang]

how is this still not a part of objection? like seriously, wat

[elhigu]

Yep, nobody hasn't even cared enough to create an objection.js plugin module implementing this. 🤷‍♂️

[sayhicoelho]

I solved my problem by implementing my own QueryBuilder class.

BaseQueryBuilder.js

class BaseQueryBuilder extends QueryBuilder {
  async findOrInsert(model) {
    const result = await this.where(model).first()

    if (!result) {
      return await this.insert(model)
    }

    return result
  }
}

BaseModel.js

class BaseModel extends Model {
  static get QueryBuilder() {
    return BaseQueryBuilder
  }
}

Person.js

class Person extends BaseModel {
  static tableName = 'persons'
}

Example:

const person = await Person.query().findOrInsert({ name: 'Example' })

[olivierlacan]

One way Rails/ActiveRecord has recently handled the likelihood of race conditions at the application level that only get resolved at the database constraint level is to leverage (however slowly) the low-level database exception to create createOrFind method instead of a findOrCreate method (which is likely to miss a concurrently created record after it fails to find one and result in an index error on the creation attempt). So while a findOrCreate function would be nice, I think a createOrFind one would be even nicer.

[acro5piano]

TypeScript version (not tested)

import type { Raw } from 'objection'
export type { Model, Raw } from 'objection'

declare module 'objection' {
  interface QueryBuilder<M extends Model, R> {
    findOrInsert(params: Partial<M>): Promise<M>
  }
}

class BaseQueryBuilder<M extends Model, R> extends QueryBuilder<M, R> {
  async findOrInsert(model: Partial<M>) {
    const result = await this.where(model).first()

    if (!result) {
      return await this.insert(model)
    }

    return result
  }
}