Drizzle Team•11mo ago

Next auth with extended columns not able to create a user

I'm trying to create an app that relies on role based authentication with next auth and drizzle, the problem is when I try to create a user by signing in I get the following error, even tough there's a default applied in the schema:

[auth][cause]: PostgresError: null value in column "role" of relation "project1_user" violates not-null constraint

[auth][cause]: PostgresError: null value in column "role" of relation "project1_user" violates not-null constraint

This is my table:

export const users = pgTable('user', {
    id: uuid('id')
        .$default(() => cuid2.createId())
        .notNull()
        .primaryKey(),
    name: text('name'),
    email: text('email').notNull(),
    emailVerified: timestamp('emailVerified', { mode: 'date' }),
    password: text('password'),
    image: text('image'),
    role: RoleEnum('role')
        .$default(() => 'USER')
        .notNull(),
    status: UserStatusEnum('status')
        .$default(() => 'ACTIVE')
        .notNull(),
});

export const users = pgTable('user', {
    id: uuid('id')
        .$default(() => cuid2.createId())
        .notNull()
        .primaryKey(),
    name: text('name'),
    email: text('email').notNull(),
    emailVerified: timestamp('emailVerified', { mode: 'date' }),
    password: text('password'),
    image: text('image'),
    role: RoleEnum('role')
        .$default(() => 'USER')
        .notNull(),
    status: UserStatusEnum('status')
        .$default(() => 'ACTIVE')
        .notNull(),
});

Shouldn't those columns not be null and instead have the default value instead of null?

1 Reply

lutefdOP•11mo ago

When i create them manually through drizzle studio it works fine I put in the not null constraint because if it doesn't have it there for some reason it overides the default and inserts as null I've tried like this as well but no success

export const users = pgTable('user', {
    id: text('id')
        .$default(() => cuid2.createId())
        .notNull()
        .primaryKey(),
    name: text('name'),
    email: text('email').notNull(),
    emailVerified: timestamp('emailVerified', { mode: 'date' }),
    password: text('password'),
    image: text('image'),
    role: RoleEnum('role')
        .$default(() => sql`'USER'`)
        .notNull(),
    status: UserStatusEnum('status')
        .$default(() => sql`'ACTIVE'`)
        .notNull(),
});

export const users = pgTable('user', {
    id: text('id')
        .$default(() => cuid2.createId())
        .notNull()
        .primaryKey(),
    name: text('name'),
    email: text('email').notNull(),
    emailVerified: timestamp('emailVerified', { mode: 'date' }),
    password: text('password'),
    image: text('image'),
    role: RoleEnum('role')
        .$default(() => sql`'USER'`)
        .notNull(),
    status: UserStatusEnum('status')
        .$default(() => sql`'ACTIVE'`)
        .notNull(),
});

It seems to be a next-auth issue, when i create the user via credentials it works fine, even when not specified it uses the default and create the user with the values expected, but when the user is created via their sso providers it breaks trying to insert null

Gaming

Programming

Next auth with extended columns not able to create a user