如何在Drizzle中创建带deleted_at IS NULL条件的唯一索引？

需求说明 #

使用Next.js、Neon与PostgreSQL，通过Drizzle创建members_table表，结构如下：

"members_table", {
  id: serial("id").primaryKey(),
  email: text("email").notNull(),
  team_id: text("team_id").notNull(),
  deleted_at: timestamp("deleted_at"),
}

同时需要实现部分唯一约束：同一email与team_id组合不能同时存在两条deleted_at为NULL的记录，但允许一条deleted_at为NULL、另一条deleted_at为具体时间（如2024-08-15）的同组合记录，对应SQL为：

CREATE UNIQUE INDEX email_team_id_unique_idx ON members_table (email, team_id) WHERE deleted_at IS NULL;

可行性与实现方案 #

完全可行，Drizzle ORM原生支持PostgreSQL的部分唯一索引，可以通过index()方法链式调用unique()和where()来实现。

完整Drizzle表定义代码 #

import { serial, text, timestamp, pgTable, index } from 'drizzle-orm/pg-core';

export const membersTable = pgTable("members_table", {
  id: serial("id").primaryKey(),
  email: text("email").notNull(),
  team_id: text("team_id").notNull(),
  deleted_at: timestamp("deleted_at"),
}, (table) => ({
  emailTeamIdUniqueIdx: index("email_team_id_unique_idx")
    .on(table.email, table.team_id)
    .unique()
    .where(table.deleted_at.isNull()),
}));

逻辑验证 #

• 当插入两条email和team_id相同且deleted_at均为NULL的记录时，PostgreSQL会触发唯一索引约束，直接报错
• 当插入一条deleted_at为NULL、另一条deleted_at为2024-08-15的同组合记录时，由于索引的WHERE条件仅限制deleted_at IS NULL的情况，因此可以正常插入

内容的提问来源于stack exchange，提问作者Oscar Alsos