drizzle-team / drizzle-team/drizzle-orm

[BUG]: SQLite ALTER TABLE ADD COLUMN omits ON DELETE / ON UPDATE from REFERENCES clause

Open
#5,619 0 comments 0 reactions 0 assignees View on GitHub
bug bug/fixed-in-beta
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.