[BUG]: $inferInsert and $inferSelect are not composable

[tmeisenh]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.36.1

What version of drizzle-kit are you using?

0.28.0

Other packages

No response

Describe the Bug

Using database first, I have an existing table that looks like this

CREATE TABLE example(
    	id integer primary key GENERATED BY DEFAULT AS IDENTITY,
	valid_to timestamptz,
	created timestamptz NOT NULL,
	updated timestamptz NOT NULL
);

The drizzle-kit generated schema might look like this

  {
    id: integer().generatedByDefaultAsIdentity({
      name: 'example_id_seq',
      startWith: 1,
      increment: 1,
      minValue: 1,
      maxValue: 2147483647,
      cache: 1,
    }),
    validTo: timestamp('valid_to', { withTimezone: true, mode: 'date' }),
    created: timestamp({ withTimezone: true, mode: 'date' }).notNull(),
    updated: timestamp({ withTimezone: true, mode: 'date' }).notNull(),
  },

The $inferInsert type will evaluate to

export interface ExampleInferInsert  {
	validTo: Date | null | undefined;
	created: Date;
	updated: Date;
}

The $inferSelect type will evaluate to

export interface ExampleInferSelect {
	id: number;
	validTo: Date | null;
	created: Date;
	updated: Date;
}

As you can see, the optional/nullable fields have different types. This means I can't use the inferInsert data to compose an inferSelect data like I want to do in my unit/e2e tests.

const inputData: ExampleInferInsert = {
validTo: null,
created: new Date(),
updated: new Date()
};


// complains about validTo's type from ExampleInferInsert not being compatible with ExampleInferSelect's validTo type
const expected: ExampleInferSelect = {
	...inputData,
	id: expect.any(Number),
}

// complains about validTo's type from ExampleInferInsert not being compatible with ExampleInferSelect's validTo type
const expected0: ExampleInferSelect = {
	...inputData,
	id: 0,
}

const actual = await dao.save(inputData);
expect(actual).toEqual(expected);

My expectation is that I could use the $inferInsert to compose a $selectInput. Maybe there is a way for the $inferInsert to not have it be undefined and just be or null?