Using Prisma with CockroachDB

This guide discusses the concepts behind using Prisma 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.

What is CockroachDB?

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

  • Built-in scaling: CockroachDB comes with automated replication, failover and repair capabilities to allow easy horizontal scaling of your application
  • Consistent transactions: CockroachDB is a relational database that supports consistent transactions that maintain data integrity
  • Compatibility with PostgreSQL: CockroachDB is compatible with PostgreSQL, allowing interoperability with a large ecosystem of existing products

Commonalities with other database providers

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

Differences to consider

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

How to use Prisma 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. 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 schema.prisma file:

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

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 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
1model User {
2 id BigInt @id @default(autoincrement())
3 name String
4}

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'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.

More on using CockroachDB with Prisma

The fastest way to start using CockroachDB with Prisma 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.

Edit this page on GitHub