[BUG]: UUIDs broken in 0.23.2

[michaelgmcd]

What version of drizzle-orm are you using?

0.23.2

Describe the Bug

Hello, the following inserts break in >0.23.0 when using the RDS Data API and ids are UUIDs

const [result] = await db
    .insert(tableA)
    .values({ foo: 'bar' })
    .returning();

  const [otherResult] = await db
    .insert(tableB)
    .values({
      refId: result.id,
    })
    .returning();

The error returned is:

BadRequestException: ERROR: column "COLUMN_NAME" is of type uuid but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
at deserializeAws_restJson1BadRequestExceptionResponse (node_modules/.pnpm/@aws-sdk+client-rds-data@3.300.0/node_modules/@aws-sdk/client-rds-data/dist-cjs/protocols/Aws_restJson1.js:493:23)
    at deserializeAws_restJson1ExecuteStatementCommandError (node_modules/.pnpm/@aws-sdk+client-rds-data@3.300.0/node_modules/@aws-sdk/client-rds-data/dist-cjs/protocols/Aws_restJson1.js:399:25)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at <anonymous> (node_modules/.pnpm/@aws-sdk+middleware-serde@3.296.0/node_modules/@aws-sdk/middleware-serde/dist-cjs/deserializerMiddleware.js:7:24)
    at <anonymous> (node_modules/.pnpm/@aws-sdk+middleware-signing@3.299.0/node_modules/@aws-sdk/middleware-signing/dist-cjs/middleware.js:14:20)
    at <anonymous> (node_modules/.pnpm/@aws-sdk+middleware-retry@3.300.0/node_modules/@aws-sdk/middleware-retry/dist-cjs/retryMiddleware.js:27:46)
    at <anonymous> (node_modules/.pnpm/@aws-sdk+middleware-logger@3.296.0/node_modules/@aws-sdk/middleware-logger/dist-cjs/loggerMiddleware.js:7:26) {
  '$fault': 'client',
  '$metadata': {
    httpStatusCode: 400,
    requestId: 'f1ae4e48-4666-4e02-b3de-647babe72663',
    extendedRequestId: undefined,
    cfId: undefined,
    attempts: 1,
    totalRetryDelay: 0
  }
}

[michaelgmcd]

Downgrading to 0.22.0 works

[dubscode]

@michaelgmcd I "got around" this problem for now, without downgrading, by changing the column type to varchar in my schema. I could still have it generate UUID's by default, etc.

export const users = pgTable( 'users', { id: varchar('id') .primaryKey() .default(sqlgen_random_uuid()) .notNull()

Not a fix, and I'm sure I'm losing some advantages of the uuid column in PG, but it at least allowed me to move forward without having to downgrade.

[moosashah]

@dubscode I tried your work around solution but that didn't work either. I get an error

column "id" cannot be cast automatically to type uuid

[dubscode]

@moosashah you might need to rebuild the table or run an alter table migration on the id column to change it to a varchar (if you previously migrated it as the uuid column). I was working in a new app, so there wasn't a significant risk of tearing down the old table and remigrating it. I hope that can help some.

[AndriiSherman]

@dubscode @moosashah @michaelgmcd We will take a look at it with @dankochetov , hope will fix soon and hope you won't need to change your schema and loose data(even if it's development or testing phase)

[michaelgmcd]

Didn't have a chance to look into this myself yet. Appreciate it.

[moosashah]

@dubscode thank you, you were right

Had to drop the table (and all the migration files and create a new migration with drizzle-kit) and then the work around worked!!

[michaelgmcd]

Hey @AndriiSherman, any update on this? Would love to upgrade to the latest, but this is a blocker. Happy to help if it's a reasonable enough fix.