Update column ONLY enabled when column is set as NOT NULL

[reisblucas]

This behavior is expected?

Because, I'm only enable to set some field like lastLoginAt when .notNull() is setted in schema, but in my case the column should be able to:

• NULL: when we insert NEW users.
• NOT NULL: when we update EXISTENT(obviously) users.

Is there some strategy to that, something that I missed?

// schema
export const users = pgTable(
  'users',
  {
    usersId: integer('users_id').primaryKey().generatedAlwaysAsIdentity({
      name: 'users_users_id_seq',
      startWith: 1,
      increment: 1,
      minValue: 1,
      maxValue: 100000,
      cache: 1,
    }),
    username: varchar({ length: 40 }).notNull(),
    password: varchar({ length: 300 }).notNull(),
    createdAt: timestamp('created_at', { mode: 'string' })
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp('updated_at', { mode: 'string' })
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    lastLoginAt: timestamp('last_login_at', { mode: 'string' }).notNull(), // <---- HERE
  },
  (table) => [unique('users_pk_2').on(table.usersId), unique('users_pk_3').on(table.username)],
);

The code below works because of .notNull on lastLoginAt

async updateSigninTime(username: string) {
    return this.db
      .update(users)
      .set({
        lastLoginAt: sql`CURRENT_TIMESTAMP`,
      })
      .where(eq(users.username, username));
  }

When we remove .notNull, now I'm not able to update the respective column above

Now I'm just able to change username or password, proof by intellisense:

[10:30:41 AM] File change detected. Starting incremental compilation...

src/users/users.service.ts:34:9 - error TS2353: Object literal may only specify known properties, and 'lastLoginAt' does not exist in type '{ username?: string | SQL<unknown> | PgColumn<ColumnBaseConfig<ColumnDataType, string>, {}, {}>; password?: string | SQL<unknown> | PgColumn<...>; }'.

34         lastLoginAt: sql`CURRENT_TIMESTAMP`,
           ~~~~~~~~~~~

[10:30:41 AM] Found 1 error. Watching for file changes.

Version:

drizzle-orm: ^0.36.4
drizzle-kit: ^0.28.1

[reisblucas]

Plus to that, when we use a .default(sqlCURRENT_TIMESTAMP) it's not possible to update this field. It's just able when we comment/remove that method and add notNull.

// log schema
...
historyDate: timestamp('history_date', { mode: 'string' })
      // .default(sql`CURRENT_TIMESTAMP`) <-- HERE, this exists in db but I can't set the exact schema here
      // so it's not reflecting my db in the schema at all
      .notNull(),

I understand that when we have this default value it always will add the current_timestamp or whatever we put inside the default method. But when we pass something, it should be able to use what I used to pass to that field/column. In this case, I inject the exact timestamp from another table that are acting in transaction.

After that it works as intended, but it seems to not reflect my db constraints at all... Because when I instrospect it reflect the schema with the correct constraints, now I have to overwrite, remove or something else to be able to work as expected inside my codebase.

[reisblucas]

Another problem is the GENERATE command, in created sql file as I said before not reflect my db because the CONSTRAINT should exist only in backend/codebase for practical purposes(access field inside query), but in db that CONSTRAINT should not exist!

CREATE TABLE IF NOT EXISTS "users" (
...
# wrong
	"last_login_at" timestamp NOT NULL,
# correct
        "last_login_at" timestamp
...
);

[reisblucas]

After that, all works on dev and probably will work in production because my DB schema on dev as expected with the divergence on my schema and db, just to achieve the expected behavior.

Now, it appears to be a Issue isntead of a empty discussion. So I start E2E test my application using pglite and found some problems, you can see my configuration below:

// drizzle-test.config.ts

export default defineConfig({
  out: `./drizzle/dev`, // using dev just to test and the migration will be the same...
  schema: './src/db/schema/index.ts',
  dialect: 'postgresql',
  driver: 'pglite',
  dbCredentials: {
    url: ':memory:',
  },
  schemaFilter: ['public'],
  breakpoints: true,
});

// // added this to my useFactory no nest
const envENVIRONMENT = configService.get<string>('NODE_ENV');
// testing env
if (envENVIRONMENT === 'test') {
  const client = new PGlite(':memory:');
  return drizzleE2E(client, { schema });
}

The connection works, but I need to create another schema. Because now, I'm using PGLite(package: @electric-sql/pglite) and it says the following in terminal error, as I mentioned the problem is based on the DIVERGENCE on schema.

Again, I just need to set .notNull on the users schema above JUST to be able to access object property inside my schema class and set the desired value on that column on db, it should not be like that BECAUSE it should be nullable and even be able to ACCESS my property.

Same problem when use default, I'm not able to access my property like nullable fields.

Am I missing something on schema configuration?!

ENVIRONMENT --> test
Listening port 3001
Something went wrong on user creation error: null value in column "last_login_at" of relation "users" violates not-null constraint
    at K_.De (~/backend/node_modules/.pnpm/@[email protected]/node_modules/@electric-sql/pg-protocol/src/parser.ts:432:11)

[reisblucas]

Solve this using typecast with $inferSelect instead of $inferInsert as I referenced on discord and it works fine, but I'll need to always typecast on insert/update to bring me the field to be able to inject.

It works but it seems to be a incorrect typescript inference by drizzle over schemas.

#2889 (comment)