Unable to use native database types with Prisma and CockroachDB

[harshhhdev]

Bug description

I had opened my Prisma schema on a new machine with my editor configured. The schema I was using is attached below.

You can ignore most of the schema -- the only important part is the Question model as the type of the content model was VARCHAR(100). The Prisma extension gave me an error, which said Invalid Native type VarChar.

This seems like something which has happened after I updated Prisma to v4.0.0 as everything was working fine before. If I remove the @db.VarChar(100) and keep it to just a string, I'm unable to run the migration because it changes the type of the column.

How to reproduce

1. Create a new Prisma schema with provider set to 'CockroachDB'
2. Create a model
3. Mark a field of type String with @db.VarChar(100)
4. See error

Expected behavior

I would expect that I can use the native database type @db.VarChar by default without any errors, as in the documentation about 'Supported database types and features' it clearly shows us that we can use it.

Prisma information

datasource db {
  provider = "cockroachdb"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

enum Status {
  ANSWERED
  UNANSWERED
}

model Question {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  status    Status   @default(UNANSWERED)
  content   String   @db.VarChar(100)
  answer    String?
}

model Link {
  id     String @id @default(uuid())
  slug   String @unique
  url    String
  userId String
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Account {
  id                       String   @id @default(cuid())
  createdAt                DateTime @default(now())
  updatedAt                DateTime @updatedAt
  userId                   String
  type                     String
  provider                 String
  providerAccountId        String
  refresh_token            String?
  refresh_token_expires_in Int?
  access_token             String?
  expires_at               Int?
  token_type               String?
  scope                    String?
  id_token                 String?
  session_state            String?
  oauth_token_secret       String?
  oauth_token              String?
  user                     User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model User {
  id            String    @id @default(cuid())
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  isAdmin       Boolean   @default(false)
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
  links         Link[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

Environment & setup

• OS: I was running this on GitHub Codespaces in the browser
• Database: CockroachDB
• Node.js version: v16.14.2

Prisma Version

Note, this is inside of a GitHub Codespace

@harshhhdev ➜ /workspaces/harshhhdev.github.io (master ✗) $ yarn prisma -v
yarn run v1.22.15
$ /workspaces/harshhhdev.github.io/node_modules/.bin/prisma -v
Environment variables loaded from .env
prisma                  : 4.0.0
@prisma/client          : 4.0.0
Current platform        : debian-openssl-1.1.x
Query Engine (Node-API) : libquery-engine da41d2bb3406da22087b849f0e911199ba4fbf11 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Migration Engine        : migration-engine-cli da41d2bb3406da22087b849f0e911199ba4fbf11 (at node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine    : introspection-core da41d2bb3406da22087b849f0e911199ba4fbf11 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary           : prisma-fmt da41d2bb3406da22087b849f0e911199ba4fbf11 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Default Engines Hash    : da41d2bb3406da22087b849f0e911199ba4fbf11
Studio                  : 0.465.0

[harshhhdev]

I copy pasted the schema shown in the 'supported database types and features' example, and it worked without an error:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id    Int    @id @default(autoincrement())
  name  String @db.VarChar(200)
  posts Post[]
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String  @db.VarChar(150)
  published Boolean @default(true)
  authorId  Int
  author    User    @relation(fields: [authorId], references: [id])
}

However, when I switched the provider from postgresql to cockroachdb, that's when I got the error. This seems to be a new error after support for CockroachDB was GA, as back when it was a preview feature I didn't face this issue.

[janpio]

On CockroachDB, you can use @db.String to achieve what you want. I fear our documentation is not up to date here, as is the auto completion of our VSCode extension.

Misleading suggestion:

Following error message:

https://www.prisma.io/docs/concepts/database-connectors/cockroachdb#type-mapping-limitations-in-cockroachdb seems to be misleading and skip over the differences in the native types.

[harshhhdev]

Hey @janpio,

Thank you for getting back to me quickly 😄. Using @db.String solved the issue. There was another reference of the native database type of STRING being mapped to @db.String here, however I do think it'd it's necessary that it should be mentioned in the type mapping limitations section. Could I send a quick PR to fix the documentation?

[janpio]

You can, but we are also trying to figure out what the actual differences are - most probably there are more than just VarChar => String. We also need to fix our auto completion and everything.

[tomhoule]

VarChar is not a valid native type on cockroachdb but autocompletion was not reflecting that. We fixed this in prisma/prisma-engines#3044 — thanks for reporting the issue!