CockroachDB
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
The fields passed to the datasource block are:
provider: Specifies thecockroachdbdata source connector.url: Specifies the connection URL for the CockroachDB database server. In this case, an environment variable is used to provide the connection URL.
While cockroachdb and postgresql connectors are similar, it is mandatory to use the cockroachdb connector instead of postgresql when connecting to a CockroachDB database from version 5.0.0.
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:
| Issue | Area | Notes |
|---|---|---|
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. | Schema | For 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. | Schema | For more information on generated values, see the CockroachDB documentation |
The @default(autoincrement()) attribute can only be used together with the BigInt field type. | Schema | For 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) | Prisma | Supported | Native database type attribute | Notes |
|---|---|---|---|---|
money | Decimal | Not yet | @db.Money | Supported in PostgreSQL but not currently in CockroachDB |
xml | String | Not yet | @db.Xml | Supported in PostgreSQL but not currently in CockroachDB |
jsonb arrays | Json[] | Not yet | N/A | Json[] supported in PostgreSQL but not currently in CockroachDB |
Other limitations
The following table lists any other current known limitations of CockroachDB compared to PostgreSQL:
| Issue | Area | Notes |
|---|---|---|
Primary keys are named primary instead of TABLE_pkey, the Prisma default. | Introspection | This 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. | Introspection | This 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. | Schema | In 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 supported | Client | Pushing 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 supported | Client | Searching on String fields without a full text index (e.g. where: { text: { search: "cat & dog", }, },) is currently not supported in CockroachDB |
| Integer division not supported | Client | Integer division (e.g. data: { int: { divide: 10, }, }) is currently not supported in CockroachDB |
Limited filtering on Json fields | Client | Currently 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.
Native type mapping from Prisma to CockroachDB
| Prisma | CockroachDB |
|---|---|
String | STRING |
Boolean | BOOL |
Int | INT4 |
BigInt | INT8 |
Float | FLOAT8 |
Decimal | DECIMAL(65,30) |
DateTime | TIMESTAMP(3) |
Json | JSONB |
Bytes | BYTES |
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) | Prisma | Supported | Native database type attribute | Notes |
|---|---|---|---|---|
INT | BIGINT, INTEGER | BigInt | ✔️ | @db.Int8 | |
BOOL | BOOLEAN | Bool | ✔️ | @db.Bool* | |
TIMESTAMP | TIMESTAMP WITHOUT TIME ZONE | DateTime | ✔️ | @db.Timestamp(x) | |
TIMESTAMPTZ | TIMESTAMP WITH TIME ZONE | DateTime | ✔️ | @db.Timestamptz(x) | |
TIME | TIME WITHOUT TIME ZONE | DateTime | ✔️ | @db.Time(x) | |
TIMETZ | TIME WITH TIME ZONE | DateTime | ✔️ | @db.Timetz(x) | |
DECIMAL(p,s) | NUMERIC(p,s), DEC(p,s) | Decimal | ✔️ | @db.Decimal(x, y) | |
REAL | FLOAT4, FLOAT | Float | ✔️ | @db.Float4 | |
DOUBLE PRECISION | FLOAT8 | Float | ✔️ | @db.Float8 | |
INT2 | SMALLINT | Int | ✔️ | @db.Int2 | |
INT4 | Int | ✔️ | @db.Int4 | |
CHAR(n) | CHARACTER(n) | String | ✔️ | @db.Char(x) | |
"char" | String | ✔️ | @db.CatalogSingleChar | Internal type for CockroachDB catalog tables, not meant for end users. |
STRING | TEXT, VARCHAR | String | ✔️ | @db.String | |
DATE | DateTime | ✔️ | @db.Date | |
ENUM | enum | ✔️ | N/A | |
INET | String | ✔️ | @db.Inet | |
BIT(n) | String | ✔️ | @Bit(x) | |
VARBIT(n) | BIT VARYING(n) | String | ✔️ | @VarBit | |
OID | Int | ✔️ | @db.Oid | |
UUID | String | ✔️ | @db.Uuid | |
JSONB | JSON | Json | ✔️ | @db.JsonB | |
| Array types | [] | ✔️ |
Introspection adds native database types that are not yet supported as Unsupported fields:
schema.prisma
