Bug Report: UUID Casting Issue in Drizzle KIT with PostgreSQL preventing updates and deletes

[angelgarrido]

Bug Report: UUID Casting Issue in Drizzle Kit (Studio) with PostgreSQL

Issue Description

It seems the system is not casting correctly UUID fields as ::UUID, and that prevents update or deleting created records using drizzle-kit studio.

Environment

• PostgreSQL Database: Deployed in ServerlessV2 with Data API
• AWS SDK:
  • @aws-sdk/client-rds-data: ^3.577.0
• Drizzle Versions:
  • drizzle-kit: ^0.21.1-674c9c2
  • drizzle-orm: ^0.30.10

Configuration

import { Resource } from "sst";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  driver: "aws-data-api",
  dialect: "postgresql",
  dbCredentials: {
    database: Resource.DatabaseRel.database,
    secretArn: Resource.DatabaseRel.secretArn,
    resourceArn: Resource.DatabaseRel.clusterArn,
  },
  schema: ["./packages/**/*.sql.ts"],
  out: "./packages/core/migrations",
});

Model Definition

import { pgTable, uuid, text, timestamp } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
    userId: uuid("user_id").defaultRandom().primaryKey().notNull(),
    name: text("name"),
    lastName: text("last_name"),
    lastName2: text("last_name_2"),
    stytchId: text("stytch_id"),
    createdAt: timestamp("created_at", { mode: 'string' }).defaultNow(),
    updatedAt: timestamp("updated_at", { mode: 'string' }).defaultNow(),
    deletedAt: timestamp("deleted_at", { mode: 'string' }),
});

Detailed error messages

These are the error details from frontend and backend:

ERROR: operator does not exist: uuid = text; 
Hint: No operator matches the given name and argument types. 
You might need to add explicit type casts.; 
Position: 36; 
SQLState: 42883

And this error in backend:

DatabaseErrorException: ERROR: operator does not exist: uuid = text; 
Hint: No operator matches the given name and argument types. 
You might need to add explicit type casts.; 
Position: 36; 
SQLState: 42883
    at de_DatabaseErrorExceptionRes (/path/to/node_modules/@aws-sdk/client-rds-data/dist-cjs/index.js:871:21)
    at de_CommandError (/path/to/node_modules/@aws-sdk/client-rds-data/dist-cjs/index.js:791:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /path/to/node_modules/@smithy/middleware-serde/dist-cjs/index.js:35:20
    at async /path/to/node_modules/drizzle-kit/bin.cjs:47263:22
    at async /path/to/node_modules/drizzle-kit/bin.cjs:47021:42
    at async /path/to/node_modules/drizzle-kit/bin.cjs:41232:26
    at AwsDataApiPreparedQuery.values (/path/to/node_modules/src/aws-data-api/pg/session.ts:111:18)
    at async proxy (/path/to/node_modules/drizzle-kit/bin.cjs:103612:31)
    at async /path/to/node_modules/drizzle-kit/bin.cjs:112188:26 {
  '$fault': 'client',
  '$metadata': {
    httpStatusCode: 400,
    requestId: '74d34f91-340c-478a-9830-3a6070a2d800',
    extendedRequestId: undefined,
    cfId: undefined,
    attempts: 1,
    totalRetryDelay: 0
  }
}

This issue seems to be related to UUID handling in Drizzle kit. Please investigate and advise on any potential fixes or workarounds. Thank you!

[AlexBlokh]

It does seem that ServerlessV2 indeed needs an explicit ::uuid
Drizzle Studio uses ORM under the hood for queries, we will add Aurora use cases for multiple data types, fix everything respectfully and then update Drizzle Studio

[angelgarrido]

However, using ORM in my code, I'm not having trouble, which is why I thought it was something related to the drizzle studio.
Is there anything i can do to help @AlexBlokh ?

[ludu12]

@angelgarrido in your ORM code are you explicitly casting to ::uuid ?

[trevorpfiz]

I am getting the same when trying to update or delete a record in the studio. Followed this https://ion.sst.dev/docs/start/aws/drizzle/.

DatabaseErrorException: ERROR: operator does not exist: uuid = text; Hint: No operator matches the given name and argument types. You might need to add explicit type casts.; Position: 54; SQLState: 42883

[pachoclo]

Me too

[angelgarrido]

@ludu12 This is not using ORM in my implementation. My implementation works fine, this problem is directly using drizzle-kit studio in a web browser, It seems is not casting :uuid.

[bas-suckling]

Following, this is also an issue for me using the Drizzle Studio web application, attempting to manually edit or delete records which have a UUID primary key.

[Steve2955]

Probably related to #2232

[FRMR1]

Same issue for me.

[angelgarrido]

What is the status for this bug?
Is something i could do to help?

[valentinbeggi]

Same issue 🥲