enforce unique indexes
is there a way to enforce unique indexes that combine multiple columns like in sql. like product id, product size, product color. this way when mutation would try to insert already existing combination of colums convex would throw an error?
10 Replies
Generally the way to do this is to create an index and write the query that efficiently checks for that item. That's the same way that unique constraints are implemented in SQL databases.
You can read the code for convex ents, it's doing the same thing you'd do manually.
Convex functions run inside the database, so the "extra round trip" is free, you're just doing what a SQL query planner would do for you in a SQL database.
Take the query that checks for uniqueness and the mutation that adds something and stick them in a helper function. Now you have code that does what you want: inserts, or throws if it exists
ok so manual check before insert
That's what ents is, taken to the extreme
yeah exactly
ok understand thanks
it's in a transaction so it's safe, and the code runs right next to the data so this is very cheap
So following up on this, i would need to have logic for each insert mutation rather than having my schema be able to enforce uniques per collection?
@mvols that's right, that and any other constraints you need that you can't express in the schema
each insert mutationwell not quite, I'd recommend writing a helper function to do the insert instead of rewriting the constraint for each mutation function
Im seeing
defineEnt
as discussed here:
https://discord.com/channels/1019350475847499849/1313402365646340107
is there a tradeoff in using defineEnt, compared to just defineTable?Ents is a library that does some of the things you'd do manually for you, just like the helper function where you'd check this
the idea is the same, using helpers (a helper here is a function that is not a
mutation(), query(), etc.
, see https://docs.convex.dev/production/best-practices/other-recommendations#use-helper-functions-to-write-shared-code) instead of trying to cram everything into each mutation funciton
a tradeoff in using defineEnt, compared to just defineTable?The tradeoff is that it's a library instead of your own function doing this, so you have less control
ahh very cool! that link helped out, i was struggling trying to find the correct typings when trying to figure this out, thats when i saw the
defineEnts
& figured that may be how to enforce at the database level