Drizzle Team•16mo ago

Default values not working for insert

await db
    .insert(Drizzle.User)
    .values({
      email,
      password: Utils.getHash(password)
    })
    .returning();

await db
    .insert(Drizzle.User)
    .values({
      email,
      password: Utils.getHash(password)
    })
    .returning();

export const User = pgTable(
  'User',
  {
    email: text('email').notNull(),
    id: text('id').default(crypto.randomUUID()).primaryKey().notNull(),
    joinedDate: timestamp('joinedDate', { mode: 'string', precision: 3 }).defaultNow().notNull(),
    lastLoginDate: timestamp('lastLoginDate', { mode: 'string', precision: 3 }).defaultNow().notNull(),
    logins: integer('logins').default(1).notNull(),
    password: text('password').notNull()
  },
  (table) => ({
    email_key: uniqueIndex('User_email_key').on(table.email)
  })
);

export const User = pgTable(
  'User',
  {
    email: text('email').notNull(),
    id: text('id').default(crypto.randomUUID()).primaryKey().notNull(),
    joinedDate: timestamp('joinedDate', { mode: 'string', precision: 3 }).defaultNow().notNull(),
    lastLoginDate: timestamp('lastLoginDate', { mode: 'string', precision: 3 }).defaultNow().notNull(),
    logins: integer('logins').default(1).notNull(),
    password: text('password').notNull()
  },
  (table) => ({
    email_key: uniqueIndex('User_email_key').on(table.email)
  })
);

Aug 12, 10:07:45PM error: PostgresError: null value in column "id" of relation "User" violates not-null constraint
    at ErrorResponse (webpack-internal:///(api)/../../../node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:785:66)
    at handle (webpack-internal:///(api)/../../../node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:487:6)
    at Socket.data (webpack-internal:///(api)/../../../node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:328:9)
    at Socket.emit (node:events:513:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

Aug 12, 10:07:45PM error: PostgresError: null value in column "id" of relation "User" violates not-null constraint
    at ErrorResponse (webpack-internal:///(api)/../../../node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:785:66)
    at handle (webpack-internal:///(api)/../../../node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:487:6)
    at Socket.data (webpack-internal:///(api)/../../../node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:328:9)
    at Socket.emit (node:events:513:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

what is going on here? shouldn't the value be filled in by default?

10 Replies

Angelelz•16mo ago

To my understanding, you can't use default like that. I'm surprised it's not screaming at you with an error in the schema declaration. The default method receives a SQL function, that the database is supposed to execute, not the ORM. Meaning, It receives SQL in the form of

default(sql`CURRENT_TIMESTAMP()`)

default(sql`CURRENT_TIMESTAMP()`)

jakeleventhalOP•16mo ago

this code gives me the same error, as does just a plain string

sik•16mo ago

if you want to generate unique id for your id column. You can use uuid() instead of text() something like this

id: uuid('id').defaultRandom().primaryKey(),

id: uuid('id').defaultRandom().primaryKey(),

jakeleventhalOP•16mo ago

yeah, but i am trying to piecemeal migrate a prisma schema, and it will break the prisma side of things if i switch to UUID so for now i need to have text. i dont understand why my approach in original post wouldnt work? how else are defaults supposed to work for text fields

jakeleventhalOP•16mo ago

i think this is a bug. at the very least my schema ought to have a type error https://github.com/drizzle-team/drizzle-orm/issues/1035

GitHub

[BUG]: Default text field values do not work · Issue #1035 · drizzl...

What version of drizzle-orm are you using? 0.28.2 What version of drizzle-kit are you using? 0.19.12 Describe the Bug await db .insert(Drizzle.User) .values({ email, password: Utils.getHash(passwor...

rphlmr ⚡•16mo ago

According to the doc, a default has to be a constant like sql'test'::string``. https://orm.drizzle.team/docs/indexes-constraints#default

Indexes & Constraints – DrizzleORM

Drizzle ORM | %s

Angelelz•16mo ago

The default function is only used by Drizzle-kit during the migration. see https://github.com/drizzle-team/drizzle-orm/issues/304

GitHub

[Feature Request] Pass a function to default() · Issue #304 · dri...

What version of drizzle-orm are you using? 0.22.0 Describe the Feature Request I'm trying to port something from Prisma that uses a cuid type for the identifier column. Because the underlying d...

kelvindecosta•16mo ago

I think I’m running into the same issue. id is set to uuid.defaultRandom.primaryKey Is there some special “default” syntax for insert operations?

Angelelz•16mo ago

You can't currently pass a JS function to default() only a sql function, and you have to run a migration in order for it to pick up.

kelvindecosta•16mo ago

So, I fixed it by deleting the table and running push:pg again. Didn't even change a line of code in the schema.ts

Gaming

Programming

Default values not working for insert