DT
Drizzle Team•4w ago
lordbinbash

Issue: INSERT automatically includes ALL schema columns, even when not provided in .values() Drizzle

Issue: INSERT automatically includes ALL schema columns, even when not provided in .values() Drizzle is including columns in INSERT statements that I'm not explicitly providing in .values(). For nullable columns, it automatically sets them to NULL, which causes errors when the column doesn't exist in the database. Example schema 
export const users = sqliteTable("users", {
  id: integer("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email")
});
export const users = sqliteTable("users", {
  id: integer("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email")
});
Example Insert: 
await db.insert(users).values({
  id: 1,
  name: "John",
  // I'm NOT providing email
});
await db.insert(users).values({
  id: 1,
  name: "John",
  // I'm NOT providing email
});
Generated SQL: 
INSERT INTO "users" ("id", "name", "email") 
VALUES (1, 'John', NULL)
INSERT INTO "users" ("id", "name", "email") 
VALUES (1, 'John', NULL)
Why This Matters: When you have multiple environments (dev, staging, production), your code and database schema don't always match across all environments at the same time. I add email column to my TypeScript schema I deploy to dev → migration runs → column exists ✅ I deploy to staging → migration runs → column exists ✅ I deploy to production → migration hasn't run yet → column doesn't exist ❌ The same code works in dev and staging but breaks in production because Drizzle includes the email column in the INSERT statement even though I never asked for it. The root issue: Drizzle treats the TypeScript schema as the absolute source of truth and includes ALL nullable columns in INSERT statements (setting them to NULL), regardless of what I pass to .values(). What I expect: Only insert the columns I explicitly provide. If I don't include email in my .values() object, don't add it to the SQL INSERT statement.
5 Replies
JustWayne
JustWayne•4w ago
If the drizzle table schema does not declare a default value, then it knows that an insert that doesn't include that column will fail. So why not just declare your email column as email: text().default(null)? Then it won't include that column when you don't insert a value for it.
lordbinbash
lordbinbashOP•3w ago
Hmm, but these both work in SQL. The email column exists in staging DB and not in prod 
INSERT INTO "users" ("id", "name", "email") 
VALUES (1, 'John', NULL)
INSERT INTO "users" ("id", "name", "email") 
VALUES (1, 'John', NULL)
INSERT INTO "users" ("id", "name") 
VALUES (1, 'John')
INSERT INTO "users" ("id", "name") 
VALUES (1, 'John')
Drizzle schema (Typescript) is driving SQL queries (SQL). 
db.insert(users).values({
  id: 1, name: "John"
})
db.insert(users).values({
  id: 1, name: "John"
})
Results in 
INSERT INTO "users" ("id", "name", "email") 
VALUES (1, 'John', NULL)
INSERT INTO "users" ("id", "name", "email") 
VALUES (1, 'John', NULL)
The column does not exist in all environments yet
JustWayne
JustWayne•3w ago
This is not a problem for drizzle to fix. If you tell drizzle that a column exists, it's going to act as if it exists and it's going to do the right thing based on that fact. You are trying to make it do the wrong thing because of your migration timing issue. The answer is: Don't deploy your drizzle code that has the new column until after you've run a migration.
lordbinbash
lordbinbashOP•3w ago
But if the column is nullable? Should a schema defined in TS, be adding to the final SQL query? As I have shown previously, email exists here, is nullable, but we can omit it in SQL. Drizzle adds it to the query. 
INSERT INTO "users" ("id", "name") 
VALUES (1, 'John')
INSERT INTO "users" ("id", "name") 
VALUES (1, 'John')
The answer is: Don't deploy your drizzle code that has the new column until after you've run a migration.
Different environments buddy, migration is completed in some, migration has not been completed in prod. Big migration. Not ideal, but also I'm not sure if a TS schema, should drive a SQL query. It's doing magic
JustWayne
JustWayne•3w ago
your code and database schema don't always match across all environments at the same time.
That's simply not a drizzle problem. That's a developer operations problem.

Did you find this page helpful?