CockroachDB (Prisma ORM v6) (/docs/orm/v6/overview/databases/cockroachdb)

For the complete Prisma documentation index, see llms.txt. A markdown version of any docs page is available by appending .md to its URL.

Guide to CockroachDB

Location: ORM > v6 > Core Concepts > Supported databases > 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.

[!NOTE] 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.

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 horizontal scaling of your application

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

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.

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

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:

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.

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:

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.

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

datasource db {
  provider = "cockroachdb"
}

The database connection URL is configured in prisma.config.ts:

import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  datasource: {
    url: env("DATABASE_URL"),
  },
});

[!NOTE] When using Prisma CLI commands, environment variables are not automatically loaded. You'll need to use a package like dotenv to load environment variables from a .env file, or ensure your environment variables are set in your shell.

The fields passed to the datasource block are:

• provider: Specifies the cockroachdb data source connector.
• The url field is configured in prisma.config.ts and specifies the connection URL for the CockroachDB database server.

[!NOTE] 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.

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.

The following table lists differences between CockroachDB and PostgreSQL:

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:

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

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.

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

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

model Device {
  id       BigInt                  @id @default(autoincrement())
  interval Unsupported("INTERVAL")
}

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.

• Cloudflare D1: Guide to Cloudflare D1
• Database drivers: Learn how Prisma connects to your database using the built-in drivers and how you can use Prisma along with other JavaScript database drivers using driver adapters (Preview)
• Microsoft SQL Server: This page explains how Prisma can connect to a Microsoft SQL Server database using the Microsoft SQL Server database connector.
• MySQL/MariaDB: This page explains how Prisma can connect to a MySQL or MariaDB database using the MySQL database connector.
• Neon: Learn how to use Prisma ORM with Neon serverless PostgreSQL