drizzle-team / drizzle-team/drizzle-orm
[BUG]: drizzle-kit pull reports index_duplicate for valid MSSQL index names reused across tables
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### What version of `drizzle-orm` are you using?
`1.0.0-rc.4`
### What version of `drizzle-kit` are you using?
`1.0.0-rc.4`
### Other packages
- `mssql@12.6.0`
- Node.js `24.16.0`
### Describe the Bug
`drizzle-kit pull` with `dialect: "mssql"` reports `index_duplicate` when two different tables in the same schema have indexes with the same name.
SQL Server allows index names to be reused across different tables/views. The index name only needs to be unique within the table or view. Microsoft docs for `CREATE INDEX` state:
> Index names must be unique within a table or view, but don't have to be unique within a database.
Docs: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql
However, Drizzle Kit's MSSQL DDL mapping currently appears to validate index names by schema + index name only:
```ts
for (const index of interim.indexes) {
const isConflictNamePerSchema = ddl.indexes.one({ schema: index.schema, name: index.name });
if (isConflictNamePerSchema) {
errors.push({
type: 'index_duplicate',
schema: index.schema,
table: index.table,
name: index.name,
});
}
ddl.indexes.push(index);
}
```
This causes a valid SQL Server schema to fail introspection.
### Minimal reproduction
Run this on SQL Server in an empty database:
```sql
CREATE TABLE dbo.duplicate_index_a (
id int NOT NULL,
value int NULL
);
CREATE TABLE dbo.duplicate_index_b (
id int NOT NULL,
value int NULL
);
CREATE INDEX IX_DUPLICATE_NAME ON dbo.duplicate_index_a(value);
CREATE INDEX IX_DUPLICATE_NAME ON dbo.duplicate_index_b(value);
```
Then run `drizzle-kit pull` with `dialect: 'mssql'` and `schemaFilter: ['dbo']`.
Expected behavior:
- Both tables are introspected successfully.
- Index identity should include the table/view, e.g. schema + table + index name.
Actual behavior:
```txt
Failed to map the introspected schema
index_duplicate (dbo.duplicate_index_b.IX_DUPLICATE_NAME)
```
### Real-world context
This happens on an existing SQL Server ERP database during full schema introspection. The database has a valid repeated index name on four different tables:
```txt
IX_UN_CER_FULLSEGUIMENT_C
- dbo.deac0001
- dbo.dece0001
- dbo.dere0001
- dbo.dtic0001
```
The indexes are regular filtered unique nonclustered indexes on their respective tables. SQL Server accepts this, but `drizzle-kit pull` fails before producing the schema.
### Possible fix
For MSSQL, index duplicate detection should probably use table/view scope:
```ts
ddl.indexes.one({ schema: index.schema, table: index.table, name: index.name })
```
or otherwise model index identity as unique within `(schema, table, name)` rather than `(schema, name)`.
Contributor guide
Research direction
Start at the MSSQL DDL mapping loop shown in the issue and trace the drizzle-kit pull introspection path. Reproduce the two-table SQL Server schema with reused index names; done means both indexes are introspected successfully without index_duplicate and their table-scoped identity is handled correctly.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql, typescript
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 70/100