DrizzleKit check constraint generation missing value

I added a constraint to this table:

export const purchaseOrderLineItem = createTable(
  "purchase_order_line_item",
  {
    id: varchar("id", { length: 21 }).primaryKey().notNull(),
    length: numeric("length").notNull(),
    width: numeric("width"),
    quantity: integer("quantity").notNull(),
    lineItemCost: numeric("line_item_cost").notNull(),
    purchaseOrderId: varchar("purchase_order_id", { length: 21 })
      .notNull()
      .references(() => purchaseOrder.id),
    productId: text("product_id")
      .notNull()
      .references(() => product.id),
  },
  (table) => {
    return {
      uniqueProductIdLengthWidthForSamePOKey: uniqueIndex(
        "product_id_length_width_purchase_order_id_key",
      ).on(table.productId, table.length, table.width, table.purchaseOrderId),
      quantityMinZero: check("quantity_min_zero", gt(table.quantity, 0)),
    };
  },
);

export const purchaseOrderLineItem = createTable(
  "purchase_order_line_item",
  {
    id: varchar("id", { length: 21 }).primaryKey().notNull(),
    length: numeric("length").notNull(),
    width: numeric("width"),
    quantity: integer("quantity").notNull(),
    lineItemCost: numeric("line_item_cost").notNull(),
    purchaseOrderId: varchar("purchase_order_id", { length: 21 })
      .notNull()
      .references(() => purchaseOrder.id),
    productId: text("product_id")
      .notNull()
      .references(() => product.id),
  },
  (table) => {
    return {
      uniqueProductIdLengthWidthForSamePOKey: uniqueIndex(
        "product_id_length_width_purchase_order_id_key",
      ).on(table.productId, table.length, table.width, table.purchaseOrderId),
      quantityMinZero: check("quantity_min_zero", gt(table.quantity, 0)),
    };
  },
);

ALTER TABLE "purchase_order_line_item" ADD CONSTRAINT "quantity_min_zero" CHECK ("purchase_order_line_item"."quantity" > $1);

ALTER TABLE "purchase_order_line_item" ADD CONSTRAINT "quantity_min_zero" CHECK ("purchase_order_line_item"."quantity" > $1);

This generated the following SQL in the migration file. You can see that $1 was returned which should have been a 0, since the minimum is 0. This then leads to failing migrations. This leads to: Error during migration: NeonDbError: there is no parameter $1 Is this a known bug?

4 Replies

rphlmr ⚡•2mo ago

You should try

 check("quantity_min_zero", gt(table.quantity, sql`0`))

 check("quantity_min_zero", gt(table.quantity, sql`0`))

The reason is that using Drizzle helpers (that's ok!), params are passed as query args and not has 'values'

rphlmr ⚡•2mo ago

I have an example here: https://drizzle.run/lxapec3034aaen4ou5cj28kr

Drizzle Run

View and Check constraint - Drizzle Run

DYELbrahOP•2mo ago

Oh snap, noted. Perhaps the TypeScript types could be improved to force no raw numerics in there? Although that would be quite difficult since gt() is used in regular queries as well. Should we also do this when running regular queries?

rphlmr ⚡•2mo ago

The team will look at this 👍. When running regular queries it’s not the same, you can pass 0 like normal. We talked about fixing that or updating the doc I had the same issue when I first try check 😅

Gaming

Programming

DrizzleKit check constraint generation missing value