[BUG]: Serial type in PostgreSQL not recognized as having a default value
Problem
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 (`smallserial`, `serial`, and `bigserial`), it appears that Drizzle does not regard them as having default values, despite these types inherently including a default auto-incrementing behavior in PostgreSQL. [code block] Expected behavior In the above example, I'd expect the 'id' field to be inferred as nullable for inserts because `smallserial` inherently provides a default auto-incrementing value. Environment & setup _No response_
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Fix Default Value Recognition for PostgreSQL Serial Types in Drizzle ORM
The Drizzle ORM does not automatically recognize PostgreSQL's serial types (`smallserial`, `serial`, `bigserial`) as having default values. This is likely due to the ORM's type inference mechanism not accounting for the inherent auto-increment behavior of these types, leading to incorrect handling of nullable fields during inserts.
Awaiting Verification
Be the first to verify this fix
- 1
Update Drizzle ORM Type Definitions
Modify the type definitions in Drizzle ORM to explicitly recognize PostgreSQL serial types as having default values. This involves updating the type mapping logic to include a check for serial types and setting their default value to auto-increment.
typescripttype PostgreSQLSerial = 'smallserial' | 'serial' | 'bigserial'; if (type === 'smallserial' || type === 'serial' || type === 'bigserial') { return { default: 'auto-increment' }; } - 2
Create a Migration Script
Develop a migration script that alters existing tables to ensure that the serial columns are correctly set with default values. This will help in aligning the database schema with the ORM's expectations.
sqlALTER TABLE your_table_name ALTER COLUMN id SET DEFAULT nextval('your_sequence_name'); - 3
Test the Changes
Run unit tests to ensure that inserts into tables with serial columns work as expected. Verify that the 'id' field is treated as nullable and auto-incrementing during insert operations.
typescriptawait db.insert(your_table_name).values({}).execute(); - 4
Update Documentation
Revise the documentation for Drizzle ORM to include information about handling PostgreSQL serial types. This will help future users understand how to properly configure their schemas.
noneN/A - 5
Monitor for Issues
After deploying the changes, monitor the application logs and user feedback for any related issues. Be prepared to make further adjustments if necessary.
noneN/A
Validation
Confirm that the 'id' field in tables using serial types can be omitted during inserts and that the database automatically assigns the next value. Additionally, check that no errors are thrown during the insert operations.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep