Skip to main content

CockroachDB

This guide discusses the concepts behind using Prisma ORM and CockroachDB, explains the commonalities and differences between CockroachDB and other database providers, and leads you through the process for configuring your application to integrate with CockroachDB.

info

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.

What is CockroachDB?

CockroachDB is a distributed database that is designed for scalability and high availability. Features include:

  • Compatibility with PostgreSQL: CockroachDB is compatible with PostgreSQL, allowing interoperability with a large ecosystem of existing products
  • Built-in scaling: CockroachDB comes with automated replication, failover and repair capabilities to allow easy horizontal scaling of your application

Commonalities with other database providers

CockroachDB is largely compatible with PostgreSQL, and can mostly be used with Prisma ORM in the same way. You can still:

Differences to consider

There are some CockroachDB-specific differences to be aware of when working with Prisma ORM's cockroachdb connector:

How to use Prisma ORM with CockroachDB

This section provides more details on how to use CockroachDB-specific features.

How to use CockroachDB's native types

CockroachDB has its own set of native data types which are supported in Prisma ORM. For example, CockroachDB uses the STRING data type instead of PostgreSQL's VARCHAR.

As a demonstration of this, say you create a User table in your CockroachDB database using the following SQL command:

CREATE TABLE public."Post" (
"id" INT8 NOT NULL,
"title" VARCHAR(200) NOT NULL,
CONSTRAINT "Post_pkey" PRIMARY KEY ("id" ASC),
FAMILY "primary" ("id", "title")
);

After introspecting your database with npx prisma db pull, you will have a new Post model in your Prisma Schema:

schema.prisma
model Post {
id BigInt @id
title String @db.String(200)
}

Notice that the title field has been annotated with @db.String(200) — this differs from PostgreSQL where the annotation would be @db.VarChar(200).

For a full list of type mappings, see our connector documentation.

How to use database keys with CockroachDB

When generating unique identifiers for records in a distributed database like CockroachDB, it is best to avoid using sequential IDs – for more information on this, see CockroachDB's blog post on choosing index keys.

Instead, Prisma ORM provides the autoincrement() attribute function, which uses CockroachDB's unique_rowid() function for generating unique identifiers. For example, the following User model has an id primary key, generated using the autoincrement() function:

schema.prisma
model User {
id BigInt @id @default(autoincrement())
name String
}

For compatibility with existing databases, you may sometimes still need to generate a fixed sequence of integer key values. In these cases, you can use Prisma ORM's inbuilt sequence() function for CockroachDB. For a list of available options for the sequence() function, see our reference documentation.

For more information on generating database keys, see CockroachDB's Primary key best practices guide.

Example

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

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

The fields passed to the datasource block are:

info

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:

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 ORM will introspect an INT column in CockroachDB as BigInt, whereas in PostgreSQL Prisma ORM 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 ORM data model to native column types. These native types are mostly the same as for PostgreSQL — see the Native type mapping from Prisma ORM to CockroachDB for details. However, there are some limitations:

CockroachDB (Type | Aliases)Prisma ORMSupportedNative 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 ORM 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 ORM 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 ORM 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 ORM data model as follows to native column types:

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

Native type mapping from Prisma ORM to CockroachDB

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

Mapping from CockroachDB to Prisma ORM types on Introspection

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

CockroachDB (Type | Aliases)Prisma ORMSupportedNative 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
model Device {
id BigInt @id @default(autoincrement())
interval Unsupported("INTERVAL")
}

More on using CockroachDB with Prisma ORM

The fastest way to start using CockroachDB with Prisma ORM is to refer to our Getting Started documentation:

These tutorials will take you through the process of connecting to CockroachDB, migrating your schema, and using Prisma Client.

Further reference information is available in the CockroachDB connector documentation.