Drizzle Team•13mo 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/postgres@3.3.5/node_modules/postgres/src/connection.js:785:66)
    at handle (webpack-internal:///(api)/../../../node_modules/.pnpm/postgres@3.3.5/node_modules/postgres/src/connection.js:487:6)
    at Socket.data (webpack-internal:///(api)/../../../node_modules/.pnpm/postgres@3.3.5/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/postgres@3.3.5/node_modules/postgres/src/connection.js:785:66)
    at handle (webpack-internal:///(api)/../../../node_modules/.pnpm/postgres@3.3.5/node_modules/postgres/src/connection.js:487:6)
    at Socket.data (webpack-internal:///(api)/../../../node_modules/.pnpm/postgres@3.3.5/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•13mo 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()`)

jakeleventhal•13mo ago

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

sik•13mo 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(),

jakeleventhal•13mo 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

jakeleventhal•13mo 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 ⚡•12mo 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•12mo 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•12mo 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•12mo 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•12mo 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