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

drizzle-kit

drizzle-kit

drizzle-kit will generate a migration containing the following line:

ALTER TABLE "users" ALTER COLUMN "id" SET DATA TYPE serial;

ALTER TABLE "users" ALTER COLUMN "id" SET DATA TYPE serial;

ALTER TABLE "users" ALTER COLUMN "id" SET DATA TYPE serial;

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

Error: ERROR: type "serial" does not exist; SQLState: 42704

Error: ERROR: type "serial" does not exist; SQLState: 42704

Error: ERROR: type "serial" does not exist; SQLState: 42704

This is because

serial

serial

serial

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

integer

integer

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

createInsertSchema

createInsertSchema

createInsertSchema with an column of type integer named id that is notNull and primaryKey gives differing behavior on

parse()

parse()

parse()

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:

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(sql
```
default(sql
```
default(sql
```
default(sql
nextval('tablename_colname_seq')
```
)
```
)
```
)
```
)
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 …

rphlmr ⚡•12/26/23, 8:38 AM

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?

newId: bigserial("new_id", { mode: "number" })

newId: bigserial("new_id", { mode: "number" })

ZeitgeistOP•12/26/23, 6:04 PM

@Raphaël M (@rphlmr)

thanks, was able to resolve the issues with this info.

Migration Issues: can't ALTER TABLE to serial in Postgres / Drizzle-Zod Interaction

Similar Threads

Similar Threads

Similar Threads