[Documentation] Example schema for cockroachdb is not working

[erSitzt]

Bug description

In the docs with cockroachdb selected (https://www.prisma.io/docs/getting-started/setup-prisma/start-from-scratch/relational-databases/using-prisma-migrate-typescript-cockroachdb)

This is the example schema... which does not work The `autoincrement()` default function is defined only on BigInt fields on CockroachDB. Use sequence() if you want an autoincrementing Int field.

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  posts   Post[]
  profile Profile?
}

I found examples like below, but what is the correct way for a classic autoincrement with cockroachdb ?

model Question {
  id        String   @id @default(cuid())

How to reproduce

Try the example schema with provider = "cockroachdb"

Expected behavior

Working autoincrementing id field.
Correct documentation.

Prisma information

Environment & setup

• OS:
• Database:
• Node.js version:

Prisma Version

[tomhoule]

This should be fixed by #3364

[janpio]

@erSitzt https://www.prisma.io/docs/getting-started/setup-prisma/start-from-scratch/relational-databases/using-prisma-migrate-typescript-cockroachdb is now updated with our current recommendation on how to do things for CockroachDB. Please confirm this works and then close this issue. Thanks.

[erSitzt]

Thanks i got it working both with BigInt + autoincrement() and with Int + sequence()
I think this can be closed.

a little off topic question...
If you dont mind, why does BigInt with autoincrement generate initial entries in a table with an id like 777095211666309121 ? Is this because it need a hint to distribute data when scaling horizontally ? So sequential ids would be bad for this type of db ?

[janpio]

Fundamentally, yes - see https://www.cockroachlabs.com/docs/v21.2/sql-faqs#how-do-i-auto-generate-unique-row-ids-in-cockroachdb and related documenation from CockroachDB.

We are currently re-investigating this and might suggest different defaults for primary key columns (you might notice that our current choice does not necessarily represent best practices as communicated by CockroachDB itself) - but for now this works.

[erSitzt]

@janpio maybe add a hint to the problems arising from using BigInt ... my pet project is using NestJS and the first thing i ran into with BigInt was a Problem with JSON Stringify...

[janpio]

Where would you put that and how did you solve it?

[erSitzt]

I switched back to Int with sequence, that way i did not have to change/cast/convert anything for the other frameworks that ran into the JSON Stringify errors...

I think there should just be a note that there are still issues around in dealing with BigInt and maybe a hint for Int with sequence if someone wants something similar to a classic autoincrement id.