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:

• model your database with the Prisma Schema Language
• connect to your database, using Prisma ORM's cockroachdb database connector
• use Introspection for existing projects if you already have a CockroachDB database
• use Prisma Migrate to migrate your database schema to a new version
• use Prisma Client in your application to query your database in a type safe way based on your Prisma Schema

Differences to consider

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

• Cockroach-specific native types: Prisma ORM's cockroachdb database connector provides support for CockroachDB's native data types. To learn more, see How to use CockroachDB's native types.

• Creating database keys: Prisma ORM allows you to generate a unique identifier for each record using the autoincrement() function. For more information, see How to use database keys with CockroachDB.

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:

• provider: Specifies the cockroachdb data 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.

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:

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 ORM will introspect an INT column in CockroachDB as BigInt, whereas in PostgreSQL Prisma ORM 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 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 ORM	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 ORM 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 ORM 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 ORM 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 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 ORM	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 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 ORM	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

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:

• Start from scratch
• Add to existing project

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.