Drizzle wants to truncate my tables when not needed

I have this schema:

export const users = sqliteTable('users', {
    id: text('id').notNull().primaryKey(),
    username: text('username').notNull(),
    created_at: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

export const users = sqliteTable('users', {
    id: text('id').notNull().primaryKey(),
    username: text('username').notNull(),
    created_at: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

I already have users in this table in production. Now I want to add a flag like this:

export const users = sqliteTable('users', {
    id: text('id').notNull().primaryKey(),
    username: text('username').notNull(),
    my_new_flag: int('my_new_flag').notNull().default(0), // New column
    created_at: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

export const users = sqliteTable('users', {
    id: text('id').notNull().primaryKey(),
    username: text('username').notNull(),
    my_new_flag: int('my_new_flag').notNull().default(0), // New column
    created_at: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

When I then run drizzle-kit push:sqlite (im using turso) it wants to truncate the table:

ALTER TABLE `users` RENAME TO `__old_push_users`;
CREATE TABLE `users` (
    `id` text PRIMARY KEY NOT NULL,
    `username` text NOT NULL,
    `my_new_flag` integer DEFAULT 0 NOT NULL,
    `created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);

INSERT INTO "users" SELECT * FROM "__old_push_users";
DROP TABLE `__old_push_users`;

 Warning  Found data-loss statements:
· You're about to add not-null my_new_flag column without default value, which contains 3 items

THIS ACTION WILL CAUSE DATA LOSS AND CANNOT BE REVERTED

Do you still want to push changes?
❯ No, abort
  Yes, I want to truncate 1 table

ALTER TABLE `users` RENAME TO `__old_push_users`;
CREATE TABLE `users` (
    `id` text PRIMARY KEY NOT NULL,
    `username` text NOT NULL,
    `my_new_flag` integer DEFAULT 0 NOT NULL,
    `created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);

INSERT INTO "users" SELECT * FROM "__old_push_users";
DROP TABLE `__old_push_users`;

 Warning  Found data-loss statements:
· You're about to add not-null my_new_flag column without default value, which contains 3 items

THIS ACTION WILL CAUSE DATA LOSS AND CANNOT BE REVERTED

Do you still want to push changes?
❯ No, abort
  Yes, I want to truncate 1 table

Am I missing something? To me this doesn't make sense.

1 Reply

Sandvich•7mo ago

Not sure if this is an sqlite limitation or a drizzle limitation. Either way you're not doing anything wrong

Gaming

Programming

Drizzle wants to truncate my tables when not needed