---
title: 'CockroachDB'
metaTitle: 'CockroachDB'
metaDescription: 'Guide to CockroachDB'
tocDepth: 3
toc: true
---

<TopBlock>

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.

</TopBlock>

:::info

The CockroachDB connector is generally available in versions `3.14.0` and later. It was first added as a [Preview feature](/orm/reference/preview-features) in version [`3.9.0`](https://github.com/prisma/prisma/releases/tag/3.9.0) with support for Introspection, and Prisma Migrate support was added in [`3.11.0`](https://github.com/prisma/prisma/releases/tag/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](/orm/prisma-schema)
- connect to your database, using Prisma ORM's [`cockroachdb` database connector](/orm/overview/databases/cockroachdb)
- use [Introspection](/orm/prisma-schema/introspection) for existing projects if you already have a CockroachDB database
- use [Prisma Migrate](/orm/prisma-migrate) to migrate your database schema to a new version
- use [Prisma Client](/orm/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](#how-to-use-cockroachdbs-native-types).

- **Creating database keys:** Prisma ORM allows you to generate a unique identifier for each record using the [`autoincrement()`](/orm/reference/prisma-schema-reference#autoincrement) function. For more information, see [How to use database keys with CockroachDB](#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](https://www.cockroachlabs.com/docs/stable/data-types.html) 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:

```sql
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:

```prisma file=schema.prisma showLineNumbers
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](/orm/overview/databases/cockroachdb#type-mapping-between-cockroachdb-and-the-prisma-schema).

### 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](https://www.cockroachlabs.com/blog/how-to-choose-db-index-keys/).

Instead, Prisma ORM provides the [`autoincrement()`](/orm/reference/prisma-schema-reference#autoincrement) attribute function, which uses CockroachDB's [`unique_rowid()` function](https://www.cockroachlabs.com/docs/stable/serial.html) for generating unique identifiers. For example, the following `User` model has an `id` primary key, generated using the `autoincrement()` function:

```prisma file=schema.prisma showLineNumbers
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()`](/orm/reference/prisma-schema-reference#sequence) function for CockroachDB. For a list of available options for the `sequence()` function, see our [reference documentation](/orm/reference/prisma-schema-reference#sequence).

For more information on generating database keys, see CockroachDB's [Primary key best practices](https://www.cockroachlabs.com/docs/v21.2/schema-design-table#primary-key-best-practices) guide.

## Example

To connect to a CockroachDB database server, you need to configure a [`datasource`](/orm/prisma-schema/overview/data-sources) block in your [Prisma schema](/orm/prisma-schema):

```prisma file=schema.prisma showLineNumbers
datasource db {
  provider = "cockroachdb"
}
```

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

```ts file=prisma.config.ts
import 'dotenv/config'
import { defineConfig, env } from 'prisma/config'

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

:::info

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](#connection-details) for the CockroachDB database server.

:::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](/orm/overview/databases/postgresql#connection-details) 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](https://www.cockroachlabs.com/docs/stable/int.html#considerations-for-64-bit-signed-integers)                  |
| 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](https://www.cockroachlabs.com/docs/stable/serial.html#generated-values-for-modes-rowid-and-virtual_sequence) |
| 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](https://www.cockroachlabs.com/docs/stable/serial.html#generated-values-for-modes-rowid-and-virtual_sequence) |

## Type mapping limitations in CockroachDB

The CockroachDB connector maps the [scalar types](/orm/prisma-schema/data-model/models#scalar-fields) from the Prisma ORM [data model](/orm/prisma-schema/data-model/models) to native column types. These native types are mostly the same as for PostgreSQL — see the [Native type mapping from Prisma ORM to CockroachDB](#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](https://github.com/cockroachdb/cockroach/issues/41578)          |
| `xml`                         | `String`   |  Not yet  | `@db.Xml`                      | Supported in PostgreSQL but [not currently in CockroachDB](https://github.com/cockroachdb/cockroach/issues/43355)          |
| `jsonb` arrays                | `Json[]`   |  Not yet  | N/A                            | `Json[]` supported in PostgreSQL but [not currently in CockroachDB](https://github.com/cockroachdb/cockroach/issues/23468) |

## Other limitations

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

| Issue                                                                                                | Area          | Notes                                                                                                                                                                                                                                                           |
| ---------------------------------------------------------------------------------------------------- | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index types `Hash`, `Gist`, `SpGist` or `Brin` are not supported.                                    | Schema        | In PostgreSQL, Prisma ORM allows [configuration of indexes](/orm/prisma-schema/data-model/indexes#configuring-the-access-type-of-indexes-with-type-postgresql) 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](https://github.com/cockroachdb/cockroach/issues/71388)                                                                                               |
| 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](https://github.com/cockroachdb/cockroach/issues/7821)                                                   |
| Integer division not supported                                                                       | Client        | Integer division (e.g. `data: { int: { divide: 10, }, }`) is currently [not supported in CockroachDB](https://github.com/cockroachdb/cockroach/issues/41448)                                                                                                    |
| Limited filtering on `Json` fields                                                                   | Client        | Currently CockroachDB [only supports](https://github.com/cockroachdb/cockroach/issues/49144) `equals` and `not` filtering on `Json` fields                                                                                                                      |

## Type mapping between CockroachDB and the Prisma schema

The CockroachDB connector maps the [scalar types](/orm/prisma-schema/data-model/models#scalar-fields) from the Prisma ORM [data model](/orm/prisma-schema/data-model/models) as follows to native column types:

> Alternatively, see the [Prisma schema reference](/orm/reference/prisma-schema-reference#model-field-scalar-types) 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](/orm/prisma-schema/introspection) adds native database types that are **not yet supported** as [`Unsupported`](/orm/reference/prisma-schema-reference#unsupported) fields:

```prisma file=schema.prisma showLineNumbers
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](/getting-started/prisma-orm/quickstart/cockroachdb)
- [Add to existing project](/getting-started/prisma-orm/add-to-existing-project/cockroachdb)

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](/orm/overview/databases/cockroachdb).
