drizzle-team / drizzle-team/drizzle-orm
[BUG]: SQLite ALTER TABLE ADD COLUMN omits ON DELETE / ON UPDATE from REFERENCES clause
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### Report hasn't been filed before.
- [x] I have verified that the bug I'm about to report hasn't been filed before.
### What version of `drizzle-orm` are you using?
0.45.2
### What version of `drizzle-kit` are you using?
0.31.10
### Other packages
_No response_
### Describe the Bug
**What is the undesired behavior?**
When using `drizzle-kit generate` with SQLite dialect, `ALTER TABLE ADD COLUMN ... REFERENCES` statements are generated without `ON DELETE` / `ON UPDATE` actions, even when the schema specifies them via `.references(() => table.id, { onDelete: 'set null' })`.
**Steps to reproduce:**
1. Define a schema with a foreign key that has `onDelete`:
```typescript
import { sqliteTable, text } from 'drizzle-orm/sqlite-core'
const users = sqliteTable('users', {
id: text('id').primaryKey(),
})
const tasks = sqliteTable('tasks', {
id: text('id').primaryKey(),
assignedTo: text('assigned_to').references(() => users.id, { onDelete: 'set null' }),
})
```
2. Run `drizzle-kit generate`
3. The generated migration SQL:
```sql
-- Actual (missing ON DELETE SET NULL)
ALTER TABLE `tasks` ADD `assigned_to` text REFERENCES users(id);
-- Expected
ALTER TABLE `tasks` ADD `assigned_to` text REFERENCES users(id) ON DELETE SET NULL;
```
**What is the desired result?**
The generated `ALTER TABLE ADD COLUMN` SQL should include `ON DELETE` and `ON UPDATE` clauses when specified in the schema.
**Root cause:**
Confirmed reproducible on drizzle-kit 0.31.10 / drizzle-orm 0.45.2
(originally discovered on 0.31.9 / 0.45.1).
The bug exists in the current source at `sqlgenerator.ts` lines 1881–1885.
In `drizzle-kit/src/sqlgenerator.ts`, `SQLiteAlterTableAddColumnConvertor.convert()` calls `SQLiteSquasher.unsquashFK()` which correctly parses `onDelete`/`onUpdate`, but the `referenceStatement` template literal only outputs `REFERENCES table(col)` without the action clauses:
```typescript
// current code — onDelete/onUpdate are parsed but ignored
const referenceAsObject = referenceData
? SQLiteSquasher.unsquashFK(referenceData)
: undefined;
const referenceStatement = `${
referenceAsObject
? ` REFERENCES ${referenceAsObject.tableTo}(${referenceAsObject.columnsTo})`
: ''
}`;
```
Compare with `SQLiteCreateTableConvertor` which correctly includes them:
```typescript
const onDeleteStatement = onDelete ? ` ON DELETE ${onDelete}` : '';
const onUpdateStatement = onUpdate ? ` ON UPDATE ${onUpdate}` : '';
```
**Note:** This affects both `sqlite` and `turso` dialects. `SQLiteAlterTableAddColumnConvertor.can()` matches both:
```typescript
statement.type === 'sqlite_alter_table_add_column' && (dialect === 'sqlite' || dialect === 'turso')
```
PostgreSQL and MySQL use separate `ALTER TABLE ADD CONSTRAINT FOREIGN KEY` statements (`PgCreateForeignKeyConvertor` / `MySqlCreateForeignKeyConvertor`) which correctly include `ON DELETE`/`ON UPDATE` and are not affected.
SQLite fully supports `ON DELETE` / `ON UPDATE` in `ALTER TABLE ADD COLUMN ... REFERENCES` syntax:
- https://www.sqlite.org/lang_altertable.html
- https://www.sqlite.org/syntax/foreign-key-clause.html
Contributor guide
Assessment
This issue has not been assessed yet.