Joe•2y ago

primary and foreign key constraints?

Are there primary and foreign key constraints you can add in and between tables?

11 Replies

hey @Joe ! not yet, but this is definitely on our radar. specifically, we'd support... 1. adding a uniqueness constraint on a field 2. validating that a field is a foreign key (already supported with schema) 3. validating that a foreign key reference is valid (not supported yet) is that right?

JoeOP•2y ago

Yup, thanks! Sorry this is probably obvious but (2) you can do now ? I was looking at the schema docs but I don't think I understood how to do that -- was that based on the id field ? It's not super urgent for what I'm doing now but just want to understand if can extend to add more constraints at some point

Indy•2y ago

2 is something you can enforce at typescript typecheck time. See the usage of s.id("users") here: https://docs.convex.dev/database/schemas#writing-schemas

Defining a Schema | Convex Developer Hub

End-to-end type safety requires typing your tables.

JoeOP•2y ago

Ah thanks!

Indy•2y ago

This is not currently enforced at the DB level. We're actively working on that.

JoeOP•2y ago

that's fine -- appreciate the info

ian•2y ago

And to be clear, you can verify (3) at insert time by doing a db.get(someId) before inserting (which is fast). It happens within a transaction, so you have assurance that it existed when the document was created. But there's nothing to prevent a bug in your code from deleting that Id without clearing the references. Similarly, for (1), you can query for a field, lets say customId (hopefully you have an index on it) and check it's either missing or unique before doing an insert, and the serializable isolation we have for our transactions guarantees you'll never have two mutations insert the same thing. E.g.:

const existing = await db
  .query("mytable")
  .withIndex("by_customId", (q) => q.eq("customId", customId))
  .unique();
if (!existing) {
  await db.insert({ customId, ...other });
}

const existing = await db
  .query("mytable")
  .withIndex("by_customId", (q) => q.eq("customId", customId))
  .unique();
if (!existing) {
  await db.insert({ customId, ...other });
}

And for (2) today at runtime you can check that the object is an Id and you can check what table it references. And soon it will even fail to insert bad values at runtime if you have a schema defining its value, say an Id to a certain table.

JoeOP•2y ago

ah okay cool thanks thanks

smaccoun•2w ago

Is there an estimate for when this will be added? At least setting uniqueness constraints on the db level

Indy•2w ago

Wow blast from the past. Currently we're not planning to add uniqueness at the db level. This is generally done by checking of existence before writing in mutations. Since mutations are transactions you can be sure that this is fine. We do owe people more documented patterns on how to do this.

smaccoun•2w ago

Ah okay cool. Was just curious. I'm manually doing it now just was curious. Thanks 👍

primary and foreign key constraints?

Did you find this page helpful?