[BUG]: INSERT generates null values for omitted columns, violating not-null constraints during UPSERT

What version of `drizzle-orm` are you using?

0.31.2

What version of `drizzle-kit` are you using?

No response

Describe the Bug

Description

When using Drizzle ORM to perform an INSERT/UPSERT operation, the generated SQL includes all columns defined in the schema, even if they are not provided in the input data. For columns with NOT NULL constraints that are omitted from the input, this results in an attempt to insert NULL values, causing a constraint violation error.

This behavior prevents users from inserting partial data into tables where some NOT NULL columns are intentionally omitted during UPSERT.

Reproduction

import { sql } from 'drizzle-orm';
import { pgTable, uuid, text, integer } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

// Define the table
const users = pgTable('users', {
  id: uuid('id').primaryKey(),
  name: text('name').notNull(),
  age: integer('age').notNull(),
  organizationId: uuid('organization_id').notNull(),
});

// Setup the database connection
const pool = new Pool({
  connectionString: 'your_connection_string_here',
});
const db = drizzle(pool);

// Upsert function
async function upsertUsers(values: Partial<typeof users.$inferInsert>[]) {
  const query = db.insert(users).values(values).onConflictDoUpdate({
    target: users.id,
    set: {
      name: sql`excluded.name`,
      age: sql`excluded.age`,
    },
  });

  console.log('Generated SQL:', query.toSQL().sql);
  console.log('SQL Parameters:', query.toSQL().params);

  return query;
}

// Example usage
async function main() {
  const userData = {
    id: '123e4567-e89b-12d3-a456-426614174000',
    name: 'John Doe',
    age: 30,
    // organizationId is intentionally omitted to demonstrate the issue
  };

  try {
    console.log("Attempting UPSERT operation:");
    await upsertUsers([userData]);
    console.log('Upsert successful');
  } catch (error) {
    console.error('Upsert failed:', error);
  }
}

main().catch(console.error);

Generated SQL

INSERT INTO "users" ("id", "name", "age", "organization_id")
VALUES ($1, $2, $3, $4)
ON CONFLICT ("id") DO UPDATE SET "name" = excluded."name", "age" = excluded."age"

Parameters: ['123e4567-e89b-12d3-a456-426614174000', 'John Doe', 30, null]

Error

error: null value in column "organization_id" of relation "users" violates not-null constraint
detail: 'Failing row contains (123e4567-e89b-12d3-a456-426614174000, John Doe, 30, null).',

Expected behavior

It would be great to provide a way to specify which columns should be included in the INSERT statement, rather than automatically including all schema-defined columns

Maybe something like?

db
.insert({
  id: users.id,
  name: users.name,
  age: users.age,
})

Environment & setup

Node 18, Postgres 16

drizzle-team / drizzle-orm