drizzle-team / drizzle-team/drizzle-orm
[BUG]: SQLite migration table rebuild treats new column as string literal instead of default in INSERT INTO ... SELECT during push
- 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.44.2
### What version of `drizzle-kit` are you using?
0.31.4
### Other packages
_No response_
### Describe the Bug
When adding a new column with a `.unique()` constraint to an existing SQLite table, `drizzle-kit push` performs a table rebuild (creating a `__new_table`). Because the column does not yet exist in the source table, the generated `SELECT` statement includes the column name in double quotes.
In SQLite, if a double-quoted identifier in a `SELECT` clause does not match an existing column, it is treated as a **string literal**. This causes every row in the new table to be populated with the literal name of the column as a string, which immediately triggers a `UNIQUE constraint failed` error when the index is created.
### **To Reproduce**
1. Start with an existing table with data:
```typescript
export const user = sqliteTable("user", {
id: text("id").primaryKey(),
email: text("email").notNull(),
});
```
2. Update the schema to add a new unique column:
```typescript
export const user = sqliteTable("user", {
id: text("id").primaryKey(),
email: text("email").notNull(),
// New column, NULLABLE but unique, meaning should default to NULL on migration which satisfies unique constraint. While the unique() does not change anything, this will error now, where otherwise it fails silently with the behavior below
referralCode: text("referral_code").unique(),
});
```
3. Run `npx drizzle-kit push`.
### **Actual Behavior**
Drizzle generates and executes SQL similar to this, verified with `verbose` on `push`:
```sql
CREATE TABLE `__new_user` (
`id` text(255) PRIMARY KEY NOT NULL,
`email` text(255) NOT NULL,
`referral_code` text(255),
);
INSERT INTO `__new_user` ("id", "email", "referral_code")
SELECT "id", "email", "referral_code" FROM `user`; --> Problem Here
DROP TABLE `user`;
ALTER TABLE `__new_user` RENAME TO `user`;
CREATE UNIQUE INDEX `idx` ON `user` (`referral_code`);
```
Because `referral_code` is missing from the source `user` table, SQLite evaluates the `SELECT` as:
`SELECT id, email, 'referral_code' FROM user;` aka the literal 'referral_code'
This has a whole host of errors downstream but the one for if the new columns is unique is:
`LibsqlError: SQLITE_CONSTRAINT: UNIQUE constraint failed: user.referral_code`
Without the unique this fails silently and turns
| id | name | email |
| :--- | :--- | :--- |
| 1 | Aldiery | aldiery@example.com |
| 2 | John Doe | john@example.com |
Into
| id | name | email | **referral_code** |
| :--- | :--- | :--- | :--- |
| 1 | Aldiery | aldiery@example.com | **"referral_code"** |
| 2 | John Doe | john@example.com | **"referral_code"** |
| | | | ❌ *Duplicates trigger constraint error* |
### **Expected Behavior**
Drizzle should detect that the column is new and select `NULL` (or the default value) for that column during the rebuild:
```sql
INSERT INTO `__new_user` ("id", "email", "referral_code")
SELECT "id", "email", NULL FROM `user`;
```
---
### **Workaround for others facing this:**
1. Push the column **without** the `.unique()` constraint first if you are using it.
2. Once the column exists in the DB, run the proper sql command to null/default the column ensuring uniqueness.
3. Add `.unique()` or unique index back to the schema and push again.
Contributor guide
Assessment
This issue has not been assessed yet.