The CockroachDB data source connector connects Prisma to a CockroachDB database server.

The CockroachDB connector is generally available in versions 3.14.0 and later. It was first added as a Preview feature in version 3.9.0 with support for Introspection, and Prisma Migrate support was added in 3.11.0.

Example

To connect to a CockroachDB database server, you need to configure a datasource block in your Prisma schema file:

schema.prisma
1datasource db {
2 provider = "cockroachdb"
3 url = env("DATABASE_URL")
4}

The fields passed to the datasource block are:

Connection details

CockroachDB uses the PostgreSQL format for its connection URL. See the PostgreSQL connector documentation for details of this format, and the optional arguments it takes.

Differences between CockroachDB and PostgreSQL

The following table lists differences between CockroachDB and PostgreSQL:

IssueAreaNotes
By default, the INT type is an alias for INT8 in CockroachDB, whereas in PostgreSQL it is an alias for INT4. This means that Prisma will introspect an INT column in CockroachDB as BigInt, whereas in PostgreSQL Prisma will introspect it as Int.SchemaFor more information on the INT type, see the CockroachDB documentation
When using @default(autoincrement()) on a field, CockroachDB will automatically generate 64-bit integers for the row IDs. These integers will be increasing but not consecutive. This is in contrast to PostgreSQL, where generated row IDs are consecutive and start from 1.SchemaFor more information on generated values, see the CockroachDB documentation
The @default(autoincrement()) attribute can only be used together with the BigInt field type.SchemaFor more information on generated values, see the CockroachDB documentation

Type mapping limitations in CockroachDB

The CockroachDB connector maps the scalar types from the Prisma data model to native column types. These native types are mostly the same as for PostgreSQL — see the PostgreSQL connector documentation for details. However, there are some limitations, listed below.

Alternatively, see the Prisma schema reference for type mappings organized by Prisma type.

CockroachDB (Type | Aliases)PrismaSupportedNative database type attributeNotes
moneyDecimalNot yet@db.MoneySupported in PostgreSQL but not currently in CockroachDB
xmlStringNot yet@db.XmlSupported in PostgreSQL but not currently in CockroachDB
jsonb arraysJson[]Not yetN/AJson[] supported in PostgreSQL but not currently in CockroachDB

Other limitations

The following table lists any other current known limitations of CockroachDB compared to PostgreSQL:

IssueAreaNotes
Primary keys are named primary instead of TABLE_pkey, the Prisma default.IntrospectionThis means that they are introspected as @id(map: "primary"). This will be fixed in CockroachDB 22.1.
Foreign keys are named fk_COLUMN_ref_TABLE instead of TABLE_COLUMN_fkey, the Prisma default.IntrospectionThis means that they are introspected as @relation([...], map: "fk_COLUMN_ref_TABLE"). This will be fixed in CockroachDB 22.1
Index types Hash, Gist, SpGist or Brin are not supported.SchemaIn PostgreSQL, Prisma allows configuration of indexes to use the different index access method. CockroachDB only currently supports BTree and Gin.
Pushing to Enum types not supportedClientPushing to Enum types (e.g. data: { enum { push: "A" }, }) is currently not supported in CockroachDB
Searching on String fields without a full text index not supportedClientSearching on String fields without a full text index (e.g. where: { text: { search: "cat & dog", }, },) is currently not supported in CockroachDB
Integer division not supportedClientInteger division (e.g. data: { int: { divide: 10, }, }) is currently not supported in CockroachDB
Limited filtering on Json fieldsClientCurrently CockroachDB only supports equals and not filtering on Json fields

Type mapping between CockroachDB and the Prisma schema

The CockroachDB connector maps the scalar types from the Prisma data model as follows to native column types:

Alternatively, see the Prisma schema reference for type mappings organized by Prisma type.

Mapping from Prisma to CockroachDB types on migration

PrismaCockroachDB
StringSTRING
BooleanBOOL
IntINT4
BigIntINT8
FloatFLOAT8
DecimalDECIMAL(65,30)
DateTimeTIMESTAMP(3)
JsonJSONB
BytesBYTES

Mapping from CockroachDB to Prisma types on Introspection

When introspecting a CockroachDB database, the database types are mapped to Prisma according to the following table:

CockroachDB (Type | Aliases)PrismaSupportedNative database type attributeNotes
INT | BIGINT, INTEGERBigInt✔️@db.Int8
BOOL | BOOLEANBool✔️@db.Bool*
TIMESTAMP | TIMESTAMP WITHOUT TIME ZONEDateTime✔️@db.Timestamp(x)
TIMESTAMPTZ | TIMESTAMP WITH TIME ZONEDateTime✔️@db.Timestamptz(x)
TIME | TIME WITHOUT TIME ZONEDateTime✔️@db.Time(x)
TIMETZ | TIME WITH TIME ZONEDateTime✔️@db.Timetz(x)
DECIMAL(p,s) | NUMERIC(p,s), DEC(p,s)Decimal✔️@db.Decimal(x, y)
REAL | FLOAT4, FLOATFloat✔️@db.Float4
DOUBLE PRECISION | FLOAT8Float✔️@db.Float8
INT2 | SMALLINTInt✔️@db.Int2
`INT4Int✔️@db.Int4
CHAR(n) | CHARACTER(n)String✔️@db.Char(x)
"char"String✔️@db.CatalogSingleCharInternal type for CockroachDB catalog tables, not meant for end users.
STRING | TEXT, VARCHARString✔️@db.String
DATEDateTime✔️@db.Date
ENUMenum✔️N/A
INETString✔️@db.Inet
BIT(n)String✔️@Bit(x)
VARBIT(n) | BIT VARYING(n)String✔️@VarBit
OIDInt✔️@db.Oid
UUIDString✔️@db.Uuid
JSONB | JSONJson✔️@db.JsonB
Array types[]✔️

Introspection adds native database types that are not yet supported as Unsupported fields:

schema.prisma
1model Device {
2 id Int @id @default(autoincrement())
3 interval Unsupported("INTERVAL")
4}
Edit this page on GitHub