drizzle-orm: [BUG]: Serial type in PostgreSQL not recognized as having a default value

For those having issues getting the insert statement to correctly auto-generate the next number in the serial sequence, this worked for me:

Set your serial column value to <table>.<column_name>.default.

The id column I’m omitting here is of type smallserial and since that field is not nullable in the type InferModel<typeof table, 'insert'>, I can’t insert using db.insert(table).values(obj) without explicitly giving a value for the id which shouldn’t be required.

Hey, I had the same error but with serial. I found a very strange fix for it and I don’t know if it will work for everyone.

1. Comment out every serial field
2. Push
3. Uncomment the serial fields
4. Push again

This seems to work for me and the tables are correct. I’m not sure why this works, perhaps there is an issue with the code for generating the create table query, but not with the alter table query. This also seems to work with some other weird schema errors and I originally found this technique because I was having trouble with pushing composite keys.

I just investigated this. This is not an issue, I believe there are a couple misunderstandings here.

First of all, the issue under discussion is about the type that is inferred for the serial family of database types. If you are running into migrations/drizzle-kit issues, those are not being tracked here.

The second thing I see here is the confusion around nullable and nullish and undefined:

1. The serial data type is a short syntax for integer generated by default as identity not null. That means that it’s an auto incremented integer type that cannot be null.
2. In drizzle there is a very important distinction between null and undefined, although in JS both are kinda similar but not really, for drizzle null is the literal null value that will be passed to the database and undefined mean nothing will be passed to the database so that the database can insert the default value to that row.

@rogden suggested a trick to pass <table>.<column_name>.default to the values on insert, this just works by coincidence. This property is just a type convenience for the drizzle team and at runtime is actually undefined, so you might as well just not put <column_name> in the insert and you will get the same result.

Third, it’s very important to point out that a serial column is a tricky one, you can omit it in the insert and get the next generated integer, you can insert any number you like including ones not unique (as long as it’s not a primary key), but you CANNOT insert a null value into it.

I encourage anyone to do some tests for yourself in an actual db:

create table serial_test (
  id serial primary key,
  not_nullable serial not null,
  supposedly_nullable serial
);

insert into serial_test (id, not_nullable, supposedly_nullable) values (default, default, default)

insert into serial_test (id, not_nullable, supposedly_nullable) values (default, default, 5)

insert into serial_test (id, not_nullable, supposedly_nullable) values (default, default, null)

insert into serial_test (id, not_nullable, supposedly_nullable) values (default, default, 5)

• The first insert will work and the values in the database will be 1, 1, 1.
• The second insert will work and the values in the database will be 2, 2, 5.
• The third insert will fail and wont insert anything to the database, but the last one will insert 4, 4, 5 (the 3 is skipped).

Lastly, for the people having problems with migrations and push commands, the serial type have some weird behaviors. So it’s not drizzle’s fault. Check here if you want to know about it.

Anyway, the types are working properly. I just did a couple type checks and they are all good:

export const myEnum = pgEnum('my_enum', ['a', 'b', 'c']);

export const users = pgTable(
	'users_table',
	{
		id: serial('id').primaryKey(),
		uuid: uuid('uuid').defaultRandom().notNull(),
		homeCity: integer('home_city')
			.notNull()
			.references(() => cities.id),
		currentCity: integer('current_city').references(() => cities.id),
		serialNullable: serial('serial1'),
		serialNotNull: serial('serial2').notNull(),
		class: text('class', { enum: ['A', 'C'] }).notNull(),
		subClass: text('sub_class', { enum: ['B', 'D'] }),
		text: text('text'),
		age1: integer('age1').notNull(),
		createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
		enumCol: myEnum('enum_col').notNull(),
		arrayCol: text('array_col').array().notNull(),
	},
);

Expect<Equal<InferSelectModel<typeof users>, typeof users['$inferSelect']>>;

Expect<Equal<InferSelectModel<typeof users>, typeof users['_']['inferSelect']>>;

Expect<Equal<InferInsertModel<typeof users>, typeof users['$inferInsert']>>;

Expect<Equal<InferInsertModel<typeof users>, typeof users['_']['inferInsert']>>;

Expect<Equal<InferInsertModel<typeof users>, {
    homeCity: number;
    class: "A" | "C";
    age1: number;
    enumCol: "a" | "b" | "c";
    arrayCol: string[];
    id?: number | undefined;
    uuid?: string | undefined;
    currentCity?: number | null | undefined;
    serialNullable?: number | undefined;
    serialNotNull?: number | undefined;
    subClass?: "B" | "D" | null | undefined;
    text?: string | null | undefined;
    createdAt?: Date | undefined;
}>>;

Expect<Equal<InferSelectModel<typeof users>, {
    id: number;
    uuid: string;
    homeCity: number;
    currentCity: number | null;
    serialNullable: number;
    serialNotNull: number;
    class: "A" | "C";
    subClass: "B" | "D" | null;
    text: string | null;
    age1: number;
    createdAt: Date;
    enumCol: "a" | "b" | "c";
    arrayCol: string[];
}>>;

I’ll close this issue as resolved, but feel free to reach out if you need to.

I’m not sure how anyone is getting smallserial to even migrate. I’m immediately thrown this on push to db attempts

error: type "smallserial" does not exist

despite import { smallserial } from 'drizzle-orm/pg-core.

The case is also the same with serial.

Is this some beginners error? Fairly new to typescript and drizzle.

Here’s an update, InferModel<typeof table, 'insert'> is working correctly when I chenge my primary key, id, to be of type serial rather than smallserial. When I do this the inferred type correctly types the id field like so id? : number | undefined. When using the type smallserial, InferModel defines the id field with id : number, leading to the type error I was encountering.