Unique constraint on multiple fields/columns
Hey guys, I'm getting my hands on Drizzle for the first time and I was wondering if/how, with the ORM, I can create a unique index based on two fields:
So basically something like this:
I've been browsing through the docs I can't seem to find/understand how to do that 😅
23 Replies
Drizzle ORM - next gen TypeScript ORM
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
use
uniqueIndex
instead of unique
both have the same syntax for defining
Oh my god I've been reading through this part twice without realizing this was the solution, thanks @T3NED
One quick question, why should I use
uniqueIndex()
instead of unique()
?depends if you want a unique index or only a unique constraint
in the original question you said "unique index", hence why I suggested using
uniqueIndex
instead of unique
shown in the exampleOh right, sorry the SQL world is something I'm pretty new into
Does this mean that
unique
is lighter in terms of performance, while uniqueIndex
is a heavier hit but makes it easier to retrieve a subgroup based on said index?unique index will have better query performance if used correctly since it's an index, and will unique will enforce uniqueness
unique will only enforce uniqueness
So in this case, I could use the
groupIdSlugUniqueIndex
field to get the subgroup I want, but with unique
I won't be able to?
Sorry if I'm saying wrong stuff
I'm checking SQL docs to understandyou can use either to enforce uniqueness, but a unique index is an index
difference in sql:
CONSTRAINT `subgroup_groupId_slug_unique` UNIQUE(`groupId`,`slug`);
CREATE UNIQUE INDEX `subgroup_groupId_slug_idx` ON `user` (`groupId`,`slug`);
it doesn't matter what you name the key (groupIdSlugUniqueIndex
) - that's just the convention i use, but has no effect on the sql generated.
either will work in your case, but it depends if you want to create an index
unique().on(t.groupId, t.slug)
uniqueIndex().on(t.groupId, t.slug)
So using an index would make finding by slug or groupId a subgroup a faster process?
on a table with lots of rows, yes... on a table with few rows you probably won't notice a difference
got it!
thanks, and sorry for the stupid questions
no such thing as a stupid question!!!
I don't think I'll use an index then, this is probably for business-sized solutions
you can always change it later, so if you're unsure, stick with just unique for now :)
got it!
thanks again for the thorough explanation
ofc, enjoy :)
last question (sorry @T3NED): in practice if at some point in the future I want to speed up the finding process of a row using the unique index you wrote above how should I select my row to successfully "hit"/"use" the index, making the process faster?
Like this ?
db.select({slug: 'abc', groupId: '789' }).from(subgroups)
that's not how an index works, no
i'd recommend researching into indexes :)
will do!
hey @T3NED, I'm back to haunt & bother you... 😅
so after doing what you taught me yesterday, I thought of adding soft-deletion to my table definition (
deleteAt
field which is null
by default), but that means that in my constraint I have to add the deletedAt
field, but only if it's null
.
This is what I currently have but I think this might not work because the constraint is not evaluating the deletedAt
field only when it's null
, it's evaluating the field whether it's null
or if it has defined value
(thanks for the follow on Twitter btw!)i would just set the slug to a random value when setting deleted_at
okay, I'll try that
I thought that Drizzle would be able to create unique indexes since it's pretty close to what SQL is (from my understanding) by doing something similar to this:
But I'll go with what you suggested!
something like that would be possible. I believe drizzle supports all sql if you use the helpers... or you can modify the migration file
nvm you're using mysql
.nullsNotDistinct()
is available, so i presume nulls are distinct by default?