Support for ON CONFLICT

[tamlyn]

Postgres 9.5 introduced the ON CONFLICT clause which enables upsert behavior.

Would you be open to a PR to add support for this to Massive as an option on insert? Simplest solution would be to add a second options parameter. e.g.

db.table.insert({id: 'might exist'}, {onConflict: 'do nothing'})
// or
db.table.insert({id: 'might exist'}, {onConflict: 'do update'})

It should be possible to generate the rest of the do update clause automatically.

Alternatively the options could be flags like {ignoreConflict: true} and {upsert: true} respectively.

As mentioned in #278 (comment) this this could also be used for save.

[vitaly-t]

A typical ON CONFLICT clause is static, which means it can be simply appended to the generated query. And this is how it is done within pg-promise, b.t.w.

[dmfay]

Yes I would! Boolean options are preferable; onConflictIgnore and onConflictUpdate, maybe?

I am actually in the middle of some reorganizing around statement generation so you may want to hold off for a day or so to avoid a different kind of conflict :)

[dmfay]

@tamlyn I just merged the big statement refactor down, so you're good to go on this!

[Kikobeats]

That's an awesome feature, is it in the roadmap? 🙂

I made a upsert workaround:

const upsert = async (table, collection) =>
    aigle.eachSeries(collection, async data => {
      const id = data.id
      const instance = await table.findOne({ id })
      return isNil(instance) ? table.insert(data) : table.update({ id }, data)
    })

using it

await upsert(db.customers, customersPlans)
await upsert(db.quotas, quotas)
await upsert(db.plans, plans)

one thing, why is not possible use insert with custom id? :((

[dmfay]

Massive doesn't have a "roadmap" as such. The goal has always been to cover ~90% of day-to-day database usage and make doing the remaining 10% with SQL as easy as possible; there's not much to get all project management over since that goal's been comfortably met for some time, and everything else is, functionally speaking, gravy. If you'd like to take a crack at generating a proper ON CONFLICT DO UPDATE clause, feel free -- I'm happy to talk over ideas and pull requests! Otherwise it'll happen when it happens.

Can you open another issue for your custom id problem and clarify it a little more?