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.GitHub
[BUG]: Serial type in PostgreSQL not recognized as having a default...
What version of drizzle-orm are you using? 0.26.1 What version of drizzle-kit are you using? 0.18.1 Describe the Bug I have encountered an issue with handling PostgreSQL's serial types (smallse...
PostgreSQL Documentation
8.1. Numeric Types
8.1. Numeric Types # 8.1.1. Integer Types 8.1.2. Arbitrary Precision Numbers 8.1.3. Floating-Point Types 8.1.4. Serial Types Numeric types consist of …
2 Replies
There is no downside with a custom migration (generated and maintained by drizzle kit) 😉
I try to never change a column type but create a new column in a first migration.
In your case, a non null serial should be enough to generate it?
@Raphaël M (@rphlmr) ⚡ thanks, was able to resolve the issues with this info.