Zeitgeist
DTDrizzle Team
•Created by Zeitgeist on 12/26/2023 in #help
Migration Issues: can't ALTER TABLE to serial in Postgres / Drizzle-Zod Interaction
In migrations generated for Postgres, changing a primary key from type 'integer' to 'serial' will generate an error. Executing
drizzle-kit will generate a migration containing the following line:
ALTER TABLE "users" ALTER COLUMN "id" SET DATA TYPE serial;
which, when the migration is done, will generate an error:
Error: ERROR: type "serial" does not exist; SQLState: 42704
This is because serial and related types are not actual types in the database -- https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL -- but rather a shorthand in CREATE TABLE to create an integer column with additional behavior. Some commentary can be found in this (related) issue: https://github.com/drizzle-team/drizzle-orm/issues/663
This has an interaction with Drizzle-Zod because calling createInsertSchema with an column of type integer named id that is notNull and primaryKey gives differing behavior on parse() -- it requires a value for an 'integer' type but not a 'serial' type.
I have started using drizzle/drizzle-kit relatively recently, so I apologize if I'm missing something obvious.
I have two questions:
1. Is there any downside to setting up custom migrations to create the sequences that are missing and using them as the default value with something like default(sqlnextval('tablename_colname_seq'))
2. I've looked at the drizzle-zod code and haven't been able to figure this out, is there a way to get the behavior (no validation) that comes with 'serial' on an 'integer' column?
Thank you.3 replies