Cookbook tip about traditional UNIQUE constraints, the RethinkDB way

[thelinuxlich]

So a user can learn about concatenating those fields that compose a UNIQUE constraint into the table id and leverage ReQL inserts with conflict options.

[danielmewes]

This question comes up quite a bit. We should try to come up with a reasonably solid code example that handles this.

[ralyodio]

I'm also interested in seeing this.

[deoqc]

I was trying to combine the r.do and r.branch command to validate unique ​username and unique ​email​ (I have a separate table for each with primary key r.uuid([username or email]). So:

r.do(
  r.table('usernames').get(username_id), 
  r.table('emails').get(email_id), 
  (username_exists, email_exists) => (
    r.branch(
      username_exists,
      r.error('Username already exists'),
      email_exists,
      r.error('Email already exists'),
      >>[save]
    )
  )
)

The problem is the last command >>[save] is running even if username_exists is true and r.error('Username already exists.') is throw.

I notice that I'm using thinky models in the >>[save] step (would it matter?).

What is wrong?

I'm looking for the best way to do it, even though know it won't be atomic and may have inconsistencies that will need to be dealt with.

[danielmewes]

@deoqc Is >>[save] a ReQL query? (e.g. r.table(...).insert(...)) or does it call Thinky functions directly? If it calls into Thinky, it might not run as part of the branch query, but will instead be executed once on the client-side, no matter what the branch is evaluated to.

[deoqc]

@danielmewes it is option 2, it call Thinky function and so it is executed no matter what.

Two quick questions:

• If I called a regular ReQL query in >>[save], would r.do / r.branch be the recommended pattern (aka rethinkdb way) for these uniqueness checks?
• I know Thinky is not officially supported, but any clue on why this work this way and how could I use Thinky following the recommended pattern would be deeply appreciated.

[danielmewes]

@deoqc Sorry for my late response. r.branch would be the way to do this in ReQL. Note that there's a chance for a race-condition here, since you need to check uniqueness in two tables. You can atomically check for uniqueness in one table by simply trying to insert into it. The insert will fail if the primary key already exists, and otherwise perform the insert.

RethinkDB doesn't support multi-table (or even multi-document) transactions, so in your case you might need to implement additional logic to resolve conflicts between concurrent inserts.

If you think that conflicting concurrent modifications will be rare, the r.branch solution where you do a get on each table might be good enough though.

I'm not sure how this can be done in Thinky natively (pinging @neumino).

[neumino]

Thinky's save command run a query and return a promise, so you can't have it inside r.branch. You have to manually do it with Reql at the moment.

[deoqc]

Thanks a lot @danielmewes for your time.

I will do some try / catch while inserting to db, since this is the only safe way. And if some insert fail, rollback removing what was inserted.

[ralyodio]

Are there any plans to add support for unique attributes on a table?

I also want to ensure a username and an email in a Users table is unique.

[Uriel29]

?