Drizzle Team•11mo ago

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:

export const subgroups = mysqlTable("subgroup", {
  id: serial("id").primaryKey(),
  groupId: text("groupId").notNull(),
  slug: text("slug").notNull(), // I'd like this to be unique, but not in the entire table, only unique within the subgroups related to `groupId`
});

export const subgroupsRelations = relations(
  subgroups,
  ({ one, many }) => ({
    group: one(groups, {
      fields: [subgroups.groupId],
      references: [groups.id],
    }),
  })
);

export const subgroups = mysqlTable("subgroup", {
  id: serial("id").primaryKey(),
  groupId: text("groupId").notNull(),
  slug: text("slug").notNull(), // I'd like this to be unique, but not in the entire table, only unique within the subgroups related to `groupId`
});

export const subgroupsRelations = relations(
  subgroups,
  ({ one, many }) => ({
    group: one(groups, {
      fields: [subgroups.groupId],
      references: [groups.id],
    }),
  })
);

So basically something like this:

const validResult = [ // ✅
  {
    id: '123',
    groupId: '456', // Different group ID
    slug: 'abc', // Same slug
  },
  {
    id: '123',
    groupId: '789', // Different group ID
    slug: 'abc', // Same slug
  }
]

const invalidResult = [ // ❌
  {
    id: '123',
    groupId: '456', // Same group ID
    slug: 'abc', // Same slug
  },
  {
    id: '123',
    groupId: '456', // Same group ID
    slug: 'abc', // Same slug
  }
]

const validResult = [ // ✅
  {
    id: '123',
    groupId: '456', // Different group ID
    slug: 'abc', // Same slug
  },
  {
    id: '123',
    groupId: '789', // Different group ID
    slug: 'abc', // Same slug
  }
]

const invalidResult = [ // ❌
  {
    id: '123',
    groupId: '456', // Same group ID
    slug: 'abc', // Same slug
  },
  {
    id: '123',
    groupId: '456', // Same group ID
    slug: 'abc', // Same slug
  }
]

I've been browsing through the docs I can't seem to find/understand how to do that 😅

23 Replies

George•11mo ago

https://orm.drizzle.team/docs/indexes-constraints#unique

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

George•11mo ago

George•11mo ago

use uniqueIndex instead of unique both have the same syntax for defining

export const subgroups = mysqlTable("subgroup", {
  id: serial("id").primaryKey(),
  groupId: text("groupId").notNull(),
  slug: text("slug").notNull(),
}, (t) => ({
  groupIdSlugUniqueIndex: uniqueIndex().on(t.groupId, t.slug),
}));

export const subgroups = mysqlTable("subgroup", {
  id: serial("id").primaryKey(),
  groupId: text("groupId").notNull(),
  slug: text("slug").notNull(),
}, (t) => ({
  groupIdSlugUniqueIndex: uniqueIndex().on(t.groupId, t.slug),
}));

GridOP•11mo ago

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()?

George•11mo ago

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 example

GridOP•11mo ago

Oh 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?

George•11mo ago

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

GridOP•11mo ago

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 understand

George•11mo ago

you 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)

GridOP•11mo ago

So using an index would make finding by slug or groupId a subgroup a faster process?

George•11mo ago

on a table with lots of rows, yes... on a table with few rows you probably won't notice a difference

GridOP•11mo ago

got it! thanks, and sorry for the stupid questions

George•11mo ago

no such thing as a stupid question!!!

GridOP•11mo ago

I don't think I'll use an index then, this is probably for business-sized solutions

George•11mo ago

you can always change it later, so if you're unsure, stick with just unique for now :)

GridOP•11mo ago

got it! thanks again for the thorough explanation

George•11mo ago

ofc, enjoy :)

GridOP•11mo ago

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)

George•11mo ago

that's not how an index works, no i'd recommend researching into indexes :)

GridOP•11mo ago

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.

export const subgroups = mysqlTable(
  "subgroups",
  {
    id: serial("id").primaryKey(),
    groupId: varchar("group_id", { length: 256 }).notNull(),
    slug: varchar("slug", { length: 32 }).notNull(),
    deletedAt: timestamp("deleted_at"),
  },
  (t) => ({
    subgroupIdSlugUniqueConstraint: unique().on(t.groupId, t.slug, t.deletedAt),
  })
);

export const subgroups = mysqlTable(
  "subgroups",
  {
    id: serial("id").primaryKey(),
    groupId: varchar("group_id", { length: 256 }).notNull(),
    slug: varchar("slug", { length: 32 }).notNull(),
    deletedAt: timestamp("deleted_at"),
  },
  (t) => ({
    subgroupIdSlugUniqueConstraint: unique().on(t.groupId, t.slug, t.deletedAt),
  })
);

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!)

George•11mo ago

i would just set the slug to a random value when setting deleted_at

GridOP•11mo ago

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:

CREATE UNIQUE INDEX unique_constraint_name ON subgroups (group_id, slug) WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX unique_constraint_name ON subgroups (group_id, slug) WHERE deleted_at IS NULL;

But I'll go with what you suggested!

George•11mo ago

something like that would be possible. I believe drizzle supports all sql if you use the helpers... or you can modify the migration file ~~.nullsNotDistinct() is available, so i presume nulls are distinct by default?~~ nvm you're using mysql

Gaming

Programming

Unique constraint on multiple fields/columns