[BUG]: better-sqlite3 migration generates invalid default boolean

[dallenbaldwin]

What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

When in boolean mode, integers generate invalid default values, leading to a sqlite error

example

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  username: text('username').notNull(),
  admin: int('admin', { mode: 'boolean' }).notNull().default(false),
})

CREATE TABLE `users` (
    `id` text PRIMARY KEY NOT NULL,
    `username` text NOT NULL,
    `admin` integer DEFAULT false NOT NULL
);

Expected behavior

In this situation, the default should be 0, not false

Environment & setup

No response

[dallenbaldwin]

I was able to find a work around, and my findings may be telling.

I was following Lucia Auth's walkthrough in their docs to create my user. You call lucia.createUser, which as far as I can tell, just invokes whichever create method is associated with the driver (in this case Drizzle), to create your users, along with any user attributes not required for the auth flow. Lucia doesn't allow you to use database defaults as part of their flow; you must provide a value for each non-nullable value, so I did have to change that.

Removing the default, pushing changes to the database, confirming the DDL was correct and trying again, I got the same sqlite error. As long as I was using a boolean int, Drizzle wasn't validating what Lucia was sending at create or wasn't translating the boolean into an int.

this is an example of what everything looked like while I was getting the errors

// not the actual values, but you get the idea
const username = 'username'
const password = 'password'

const user = await auth.createUser({
  key: {
    providerId: 'username',
    password,
    // lowercase to ensure uniqueness
    providerUserId: username.toLowerCase(),
  },
  attributes: { username, admin: false },
})

I was able to get it to work by changing the attributes

const user = await auth.createUser({
  key: {
    providerId: 'username',
    password,
    // lowercase to ensure uniqueness
    providerUserId: username.toLowerCase(),
  },
  attributes: { username, admin: (0 as any) },
})

If I need to send this bug report up to Lucia, I'm more than happy to do so, but it may be something you'll want to know about.

[ConorRoberts]

I am experiencing the same issue.

This sort of error also occurs when using a SQLite int column type with mode="timestamp_ms". The .default() method accepts a date as a parameter and writes the migration with a date string as the default value.

ex.

my_table.ts

const myTable = sqliteTable("my_table", {
  id: text("id").primaryKey(),
  createdAt: int("created_at", { mode: "timestamp_ms" }).default(new Date()),
});

migration.sql

CREATE TABLE `my_table` (
  `id` text PRIMARY KEY NOT NULL,
  `created_at` integer DEFAULT 2023-12-31T15:16:53.371Z NOT NULL
);