Cannot use `crdb_region` in CockroachDB

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] https://github.com/prisma/prisma/issues/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

Issue Analytics

State:
Created 2 years ago
Comments:7 (3 by maintainers)

Top GitHub Comments

2reactions

ppoddar-affordablycommented, Apr 6, 2022

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 https://github.com/prisma/prisma/issues/11317#issuecomment-1066749445 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.

1reaction

ppoddar-affordablycommented, Jan 12, 2022

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!

Top Results From Across the Web

Common Errors and Solutions | CockroachDB Docs

Understand and resolve common error messages written to stderr or logs.

A Demonstration of Multi-Region CockroachDB

In this paper, we describe how CockroachDB makes this easy for developers by providing a high-level declarative syntax that allows expressing data access...

Cockroach Labs CockroachDB Reviews, Ratings ... - Gartner

I'm satisfied with the overall experience of using CRDB. It's quite easy to deploy distributed SQL ACID database which is very challenging to...

CockroachDB - AWS Marketplace

CockroachDB lets you build modern apps on a familiar SQL database, ... Start instantly on CockroachDB Serverless, and use it for free without...

CockroachDB – Marketplace - Google Cloud Console

Choose from two deployment methods: CockroachDB serverless offers automated elastic scaling with a pay-for-what-you-use architecture that's ...