[BUG]: .default() does not work on boolean pg types

[codesfromshad]

What version of drizzle-orm are you using?

0.31.2

What version of drizzle-kit are you using?

0.22.7

Describe the Bug

When I set .default(sql'FALSE').notNull() or .default(false).notNull(), manually entering a row does not result in FALSE by default.

Check the snapshot:

The CHECK() constraint, using sql magic operator also doesn't work. I am aware the drizzle did not implement this yet.

Please check the code snippet below for understanding what I am trying to achieve:

export const trackers = createTable(
  "tracker",
  {
    id: uuid("id").primaryKey(),
    createdAt: timestamp("created_at", {
      mode: "date",
      withTimezone: true,
    }).default(sql`CURRENT_TIMESTAMP`).notNull(),
    hashedId: varchar("hashed_id", { length: 255 }).notNull(),
    country: varchar("country", { length: 2 }).notNull(),
    isMalicious: boolean("is_malicious").default(sql`FALSE`).notNull(),
    canBypassProtection: boolean("can_bypass_protection").default(sql`FALSE`).notNull(),
  },
  (tracker) => ({
    hashedIdIdx: index("tacker_hashedId_idx").on(tracker.hashedId),
    canBypassProtectionCstr:
      sql`CHECK (can_bypass_protection = FALSE OR (can_bypass_protection = TRUE AND is_malicious = TRUE))`, // <-- DOESN'T WORK
  })
);

Expected behavior

It should be FALSE by default.

Environment & setup

No response

[raydelvecchio]

same here! been driving me crazy

[AndriiSherman]

What is the createTable function? It would be awesome if you could send a full repro example, so I can add it to the test cases and fix it if needed

[L-Mario564]

@codesfromshad Is this issue specific to Drizzle Studio or are you encountering this issue while running a query with the ORM?

[MGSimard]

Same thing here.

export const blogs = createTable(
  "blogs",
  {
    id: serial("id").primaryKey(),
    author: varchar("author")
      .notNull()
      .references(() => userTable.username),
    title: varchar("title", { length: 255 }).notNull(),
    active: boolean("active").notNull().default(false),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updated_at").$onUpdate(() => new Date()),
    deletedAt: timestamp("deleted_at"),
  },
  (table) => ({
    blogTitleUniqueIdx: uniqueIndex("blog_title_uniqueIdx").on(table.title),
    /* Only force uniqueness on author's "current" blog (don't include deleted ones) */
    authorBlogUniqueIdx: uniqueIndex("author_blog_uniqueIdx")
      .on(table.author)
      .where(sql`"deleted_at" IS NULL`),
  })
);

.default(false) does not apply - therefore when "active" boolean isn't explicitly supplied, the entry isn't created due to the notNull() constraint.

I could explicitly include active: false on every DB insert, but you'd at least expect stuff to work? Or not take months of being swept under the rug? Isn't this a pretty huge deal?

Also, createTable is just an affix prepend helper for multi-project schema. export const createTable = pgTableCreator((name) =>vertexblog_${name});

[L-Mario564]

.default(false) does not apply

@MGSimard What do you mean by that? That the default value statement isn't being generated in migrations or that inside an insert query the default value isn't being applied?

I tried both anyways and both behave as expected in the latest version of ORM and Kit (v0.33.0 and v0.24.2 respectively). If it's an issue with an insert statement, I'm curious to know what driver you're using and how you've defined the query that's causing the issue.

[MGSimard]

.default(false) does not apply

@MGSimard What do you mean by that? That the default value statement isn't being generated in migrations or that inside an insert query the default value isn't being applied?

I tried both anyways and both behave as expected in the latest version of ORM and Kit (v0.33.0 and v0.24.2 respectively). If it's an issue with an insert statement, I'm curious to know what driver you're using and how you've defined the query that's causing the issue.

I mean it's intrinsically both isn't it? When you db:push this table schema, the default(false) constraint doesn't actually get applied to the column, which inherently means that whether you run an insert query or attempt to create a row manually in drizzle studio, the "active" column will default to null rather than false, which will obviously throw an error because of notNull(). You're being forced to supply an explicit true/false boolean on every insert or update, because default(false) isn't actually being applied on table creation, so any insert will not default to false. The exact context supplied by both the issue's OP and myself. Neither default(false) or default(sqlFALSE) work. This is on latest.

This behaviour can be observed simply by attempting to add an entry in drizzle studio, the untouched "active" column which is supposed to have a default(false) constraint actually says "NULL" instead of "DEFAULT". This is on the exact table described by the above schema.

I haven't tested on a non multi-project schema yet, but considering that if your testing of boolean .default() does indeed work on your end, and the fact that OP is using syntax indicating they're also doing multi-project schema -- could this possibly be yet another bug introduced by the use of multi-project schema, somehow? Same as this other issue introduced a few months ago?

• This is the multi-project schema documentation: https://orm.drizzle.team/kit-docs/conf#multi-project-schema
• schema.ts @blogs
• drizzle.config.ts
• actions.ts, bottom @createBlog
• The other project sharing the DB, as vertexdb_*

[RomanNabukhotnyi]

@codesfromshad @raydelvecchio @MGSimard Hey! This should be fixed.