Objection.js: How to insert only if not exist

[zefexdeveloper]

I have a function and that function is called addReaction, it will add the commentId and the userId in the join (pivot) table called reaction. I have a model called Reaction.

I was wondering, how can I insert commentId and userId only if it doesn't exist already? I'm asking to know if it's possible to do that without having to query first or relaying on erros such as unique constraints (which is something I don't want).

[koskimas]

Unique constraint is the correct way to do this with relational databases. Another way would be to use ON CONFLICT (postgres), but unfortunately there is no support for that in knex, and therefore neither in objection.

I'd just add the unique constraint.

[kibertoad]

@zefexdeveloper @koskimas What about upsertGraph method? http://vincit.github.io/objection.js/#graph-upserts

[koskimas]

That's not atomic. You need to use custom locks to make that atomic. Two upsertGraphs running in parallel would happily insert two rows. upsertGraph with a transaction and a proper use of forUpdate would work though.

[kibertoad]

@koskimas Can't you pass existing transaction to upserGraph method?

[koskimas]

Yes, but a transaction alone doesn't help here.

[zefexdeveloper]

@koskimas As it's a pivot (join) table, how can I add the unique constraint for both commentId and userId? Like if the user tries to add again a reaction to that comment it would not allow it.

[koskimas]

You can add composite unique constraints in knex migrations like this:

table.unique(['commentId', 'userId'])

Then you can use the objection-db-errors plugin to catch the UniqueViolationError.

[zefexdeveloper]

@koskimas Thank you, you rock. Is there a community like Slack, Discord or any other for Objection.js? It would be really nice. Keep up the good work.

[kibertoad]

@zefexdeveloper https://gitter.im/Vincit/objection.js