Night•3y ago

Unique Indexes

Do table indexes automatically have a unique attribute? I am trying to insert a row into a table if and only if the combination of two columns does not already exist, and I have an index on the table for those two columns. How do I enforce this uniqueness?

12 Replies

alexcole•3y ago

There's currently no built-in support for unique indexes. Multiple documents can have the same values of indexed fields. As you said though, you can build this yourself with a separate query that calls db.query().withIndex(). And yep, the index will make it efficient!

alexcole•3y ago

Also fun fact - This doesn't work in most other databases! It's only because of Convex's strong consistency using OCC that we avoid race conditions where 2 mutations both insert documents at the same time. You can read more at https://docs.convex.dev/understanding/deep-dive/occ

OCC and Atomicity | Convex Developer Hub

In the fundamentals section, we

alexcole•3y ago

But yeah, in the future we may add a .unique() attribute to indexes to make this more ergonomic.

NightOP•3y ago

I'm loving Convex more the more I learn about it That's a very convenient feature But now I am getting this error: "Invalid index range. Tried to query index standings.combo but the query didn't use the index fields in order." This is my code:

export default mutation(withUser(async ({ db, user }, race) => {

  if ((await db.get(new Id('races', race))).ended) {
    return;
  }

  const standingId = await db.query('standings').withIndex('combo', q => q.eq('race', race) && q.eq('user', user)).collect();
  if (standingId) return

  return await db.insert('standings', {
    race: new Id('races', race),
    user: user._id
  })
}));

export default mutation(withUser(async ({ db, user }, race) => {

  if ((await db.get(new Id('races', race))).ended) {
    return;
  }

  const standingId = await db.query('standings').withIndex('combo', q => q.eq('race', race) && q.eq('user', user)).collect();
  if (standingId) return

  return await db.insert('standings', {
    race: new Id('races', race),
    user: user._id
  })
}));

But I am querying them in the same order that I indexed them

sshader•3y ago

I'm not sure if this is your error but q.eq('race', race).eq('user', user)? (instead of the &&)

james•3y ago

yes this is the issue

james•3y ago

https://docs.convex.dev/using/indexes#querying-using-indexes

Indexes | Convex Developer Hub

Indexes are a data structure that allow you to speed up your

james•3y ago

admittedly our index query syntax is confusing here

NightOP•3y ago

Ah I see, yes, thank you

james•3y ago

the reasoning here is that the chained query syntax @sshader shared explicitly codifies the order of the fields, whereas && is commutative, but we could improve this to make it less error-prone and can certainly improve the error message

NightOP•3y ago

Yeah, I think it makes sense because it goes with the ordering of the columns when defining the index, but if the error message could indicate this, that would be very helpful (one of my few complaints with NextJS is that the error messages tell me absolutely nothing about where to start debugging) Or, perhaps list these "common error messages" in the documentation for Indexes and each page

alexcole•3y ago

Yeah, I agree that error message is terrible. Lemme poke and it and see if it can be improved...

Unique Indexes

Did you find this page helpful?