drizzle-team / drizzle-team/drizzle-orm
[BUG]: drizzle-kit pull infers a one-to-one relation from a PARTIAL unique index
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### What version of `drizzle-kit` are you using?
1.0.0-rc.5
### What version of `drizzle-orm` are you using?
1.0.0-rc.5
### Describe the Bug
`drizzle-kit pull` treats a **partial** unique index (`CREATE UNIQUE INDEX ... WHERE ...`) as if it were a plain unique constraint when inferring relations, and generates a one-to-one relation where the data is one-to-many.
A partial unique index constrains a *subset* of rows. `UNIQUE (user_id) WHERE archived_at IS NULL` means "at most one **active** document per user" — a user may still have many documents in total. The generated relation says a user has exactly one.
This is silent: the generated code compiles, and queries traversing the relation return a single object instead of an array, so callers quietly see one row where they asked for all of them. It is a common pattern for soft-deleted or versioned rows, where "one current row per parent" is exactly the constraint you want.
Notably `schema.ts` **does** capture the predicate (`.where(sql\`(archived_at IS NULL)\`)`), so the information is available at generation time and is only dropped by the relation inference.
### Minimal reproduction
```sql
CREATE TABLE users (
id serial PRIMARY KEY
);
CREATE TABLE documents (
id serial PRIMARY KEY,
user_id integer NOT NULL REFERENCES users (id),
archived_at timestamptz
);
-- At most one ACTIVE document per user. A user may still have many documents.
CREATE UNIQUE INDEX documents_one_active_per_user
ON documents (user_id)
WHERE archived_at IS NULL;
```
The data this permits, which the generated relation contradicts:
```sql
INSERT INTO users (id) VALUES (1);
INSERT INTO documents (user_id, archived_at) VALUES (1, now()), (1, now()), (1, NULL);
-- 3 documents for user 1, all legal under the partial unique index
```
Then run `drizzle-kit pull`.
### Actual output
`relations.ts`:
```ts
export const relations = defineRelations(schema, (r) => ({
documents: {
user: r.one.users({
from: r.documents.userId,
to: r.users.id
}),
},
users: {
documents: r.one.documents(), // <-- wrong
},
}))
```
`schema.ts`, which does retain the predicate:
```ts
export const documents = pgTable("documents", {
id: serial().primaryKey(),
userId: integer("user_id").notNull().references(() => users.id),
archivedAt: timestamp("archived_at", { withTimezone: true }),
}, (table) => [
uniqueIndex("documents_one_active_per_user").using("btree", table.userId.asc().nullsLast()).where(sql`(archived_at IS NULL)`),
]);
```
### Expected output
```ts
users: {
documents: r.many.documents(),
},
```
### Control
Dropping the predicate and leaving everything else identical produces the correct relation, which isolates the `WHERE` clause as the trigger:
```sql
DROP INDEX documents_one_active_per_user;
CREATE INDEX documents_user_id_idx ON documents (user_id);
```
```ts
users: {
documents: r.many.documents(), // correct
},
```
A full (non-partial) `UNIQUE (user_id)` correctly yields `r.one`, so only the partial case is affected.
### Suggested fix
When deciding one-to-one vs one-to-many from a unique index, ignore any index whose `indpred` is non-null (`pg_index.indpred IS NOT NULL`), since such an index does not make the column unique across the table.
Contributor guide
Research direction
Start with the drizzle-kit pull relation inference path and inspect how PostgreSQL index metadata, especially pg_index.indpred, is handled. Compare the generated relations.ts and schema.ts for the partial unique index, then verify that partial indexes produce r.many while full unique indexes still produce r.one.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql, typescript
- Domain
- database, tooling
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 78/100