Migrations with new notNull() columns

what's the proper way to add a notNull() column through a migration? I just tried to add a column in my schema like

  foo: text('foo').notNull()

  foo: text('foo').notNull()

and generated the migration with the standard npx drizzle-kit generate:pg command but during the await migrate(...) call, I get an error that existing rows have a NULL value (which, yes... i'm just adding the column now). I don't see anything in the docs about customizing migrations to backfill existing rows in cases like this. Is there a way to properly handle this?

4 Replies

ansipedantic•11mo ago

(I should add that if I add a .default('bar') it still has the same error... I presume that this default is only applied to newly inserted rows, so it doesn't help fix the existing rows...?) this is the migration file:

ALTER TABLE "someTable" ADD COLUMN "foo" text NOT NULL;

ALTER TABLE "someTable" ADD COLUMN "foo" text NOT NULL;

and this is the error when I run the migrate(...) code

Query: select id, hash, created_at from "drizzle"."__drizzle_migrations" order by created_at desc limit 1
Query: ALTER TABLE "someTable" ADD COLUMN "foo" text NOT NULL;
PostgresError: column "foo" of relation "someTable" contains null values
at ErrorResponse (/workspaces/bugrules/node_modules/postgres/cjs/src/connection.js:771:26)
at handle (/workspaces/bugrules/node_modules/postgres/cjs/src/connection.js:473:6)
at Socket.data (/workspaces/bugrules/node_modules/postgres/cjs/src/connection.js:314:9)
at Socket.emit (node:events:513:28)
at addChunk (node:internal/streams/readable:324:12)
at readableAddChunk (node:internal/streams/readable:297:9)
at Socket.Readable.push (node:internal/streams/readable:234:10)
at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

Query: select id, hash, created_at from "drizzle"."__drizzle_migrations" order by created_at desc limit 1
Query: ALTER TABLE "someTable" ADD COLUMN "foo" text NOT NULL;
PostgresError: column "foo" of relation "someTable" contains null values
at ErrorResponse (/workspaces/bugrules/node_modules/postgres/cjs/src/connection.js:771:26)
at handle (/workspaces/bugrules/node_modules/postgres/cjs/src/connection.js:473:6)
at Socket.data (/workspaces/bugrules/node_modules/postgres/cjs/src/connection.js:314:9)
at Socket.emit (node:events:513:28)
at addChunk (node:internal/streams/readable:324:12)
at readableAddChunk (node:internal/streams/readable:297:9)
at Socket.Readable.push (node:internal/streams/readable:234:10)
at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

Mykhailo•11mo ago

Hello @ansipedantic! As your existing rows have a NULL value and you want to update your foo column with notNull option you can probably follow these steps: 1. Create a new column newFoo with NOT NULL and Default Value: This allows you to add the column without directly affecting existing data. 2. Copy data from foo to newFoo: If there are existing data in the old column foo that you want to retain, copy them into the new column. 3. Drop the old column foo: After ensuring that all necessary data has been copied into the new column, you can safely remove the old column. 4. Rename newFoo to foo: The final step is to rename the new column back to the original column name.

ansipedantic•11mo ago

Hi @solo thanks for the reply. However, this is not the case I am trying to solve. I am adding a brand new column foo to an existing table.. I am not updating an existing column. So, Step 1 in your above message is the equivilent of what I am trying to do, and what is causing the error oh hmm hang on.. I thought I had an issue w/ using .notNull().default('asdf') also throwing an error.. but i just tried again and it worked 😮 so, i need to make sure to provide a .default(...) when adding a .notNull() to an existing table ok.. sorry for the confusion @solo - but your answer also helped me figure it out.. so thank you!

Mykhailo•11mo ago

Nice, happy that issue is solved!

Gaming

Programming

Migrations with new notNull() columns