Cannot use `crdb_region` in CockroachDB

[ppoddar-affordably]

Bug description

I'm using Prisma to query a dedicated CockroachDB multi-region instance. For REGIONAL BY ROW tables, there is a hidden internal crdb_region column created by CockroachDB. This column has an internal enum type crdb_internal_region that is composed of the different cloud regions where the DB Nodes are hosted. In the hosted offering, these are set by CockroachDB to be values like gcp-us-east1 (with dashes) and cannot be changed afaik.

If I try to explicitly insert or read values to crdb_region, then I have to include crdb_region in my Prisma schema for that model for it to show up in the client. However, because crdb_region is represented as an enum in Cockroach, I can't represent crdb_region as a String type. I tried defining an enum in Prisma like:

enum crdb_internal_region {
  gcp-us-east1
  gcp-europe-west1
  gcp-asia-south1
}

but there is an error since these enum values have dashes in them which are not currently allowed. As of now, I have no way to proceed.

References:
[0] https://www.cockroachlabs.com/blog/under-the-hood-multi-region/
[1] https://www.cockroachlabs.com/docs/stable/set-locality.html
[2] #273

Prisma information

Example schema:

model self_stated {
  id                             String
  user_id                        String
  created_at                     DateTime             @default(now()) @db.Timestamptz(6)
  updated_at                     DateTime             @default(now()) @db.Timestamptz(6)
  users                          users                @relation(fields: [crdb_region, user_id], references: [crdb_region, id], onDelete: Cascade, map: "self_stated_users_id_fk")
  crdb_region                    String  // Doesn't work!
  user_country_code              String

  @@id([crdb_region, id])
  @@index([user_id], map: "self_stated_user_id_index")
}

Error is:
Attempted to serialize scalar 'gcp-us-east1' with incompatible type 'String' for field crdb_region

Environment & setup

• OS: Debian
• Database: CockroachDB
• Node.js version: v16.13.1

Prisma Version

prisma                  : 3.8.0
@prisma/client          : Not found
Current platform        : debian-openssl-1.1.x
Query Engine (Node-API) : libquery-engine 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at ../../home/node/.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Migration Engine        : migration-engine-cli 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at ../../home/node/.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine    : introspection-core 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at ../../home/node/.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary           : prisma-fmt 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at ../../home/node/.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Default Engines Hash    : 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f
Studio                  : 0.452.0

[ppoddar-affordably]

Possible solutions I see are:

1. Native support for the cockroach crdb_internal_region type
2. Supporting enum -> string mapping (Add support for enum string assignments #273)
3. [Maybe] Allow for excluding of crdb_internal_region in the query result -- I'm not sure that this would fix it, but it would prevent crdb_region from being returned and having to be coerced from the enum to a string. (Add way to exclude fields via query arguments or globally #5042)

[janpio]

Have you tried to use prisma db pull on that dabase? What schema does that create? Most importantly, does it pick up the crdb_region column?

Alterantively or additionally: @map also exists for enum values, which enables you to do something like this:

enum crdb_internal_region {
  us_east1      @map("gcp-us-east1")
  europe_west1  @map("gcp-europe-west1")
  asia_south1   @map("gcp-asia-south1")
}

[ppoddar-affordably]

Does prisma db pull work for CockroachDB yet? I tried it and it did seem to work actually, but did not pull crdb_region-- likely because it was hidden.

Adding the @map seems to work -- thanks a ton! Is this in the docs? If not, can I request it be added and can #273 be updated/closed with this?

[janpio]

-- likely because it was hidden.

That would mean that manually creating the crdb_internal_region enum and adding the crdb_region would be a manual workaround for this for now.

Adding the @map seems to work -- thanks a ton! Is this in the docs? If not, can I request it be added and can #273 be updated/closed with this?

It is documented at https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#map, but that might not be very discoverable.

[ppoddar-affordably]

That would mean that manually creating the crdb_internal_region enum and adding the crdb_region would be a manual workaround for this for now.

Yes, this worked for me!

It is documented at https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#map, but that might not be very discoverable.

I was expecting this somewhere in the enum section of the docs -- but thanks for the pointer!

[janpio]

@ppoddar-affordably Has been some while, but I just noticed we forget to document the final workaround for you: Can you shared what you manually could cobble together that then worked? Thanks.

[ppoddar-affordably]

I added the following enum to my schema:

enum crdb_internal_region {
  gcp_us_east1      @map("gcp-us-east1")
  gcp_europe_west1  @map("gcp-europe-west1")
  gcp_asia_south1   @map("gcp-asia-south1")
}

and used that type for the crdb_region:

model prices {
  id                String                @id(map: "prices_pk")
  merchant_id       String
  tiers_mode        tiers_mode
  user_id           String?
  updated_at        DateTime              @default(now()) @db.Timestamptz(6)
  created_at        DateTime              @default(now()) @db.Timestamptz(6)
  tiers             Json?
  price_phases      price_phases[]
  crdb_region       crdb_internal_region?
  user_country_code String?
}

Note that this requirement makes adopting the workaround from #11317 (comment) harder since I can't blanket remove all uses of enums (which are the source of latency variability). My planned (but untested) workaround for now is to issue recurring queries on crdb_region every few seconds to make sure the enum values remained cached to avoid the slow query.