CockroachDB example

This example shows how to:

• Connect Prisma to a CockroachDB database
• Create the database schema with raw SQL
• Populate the Prisma schema using prisma db pull
• Read and write data to the database using Prisma Client

Note: CockroachDB support in Prisma is currently in Preview, and Prisma Migrate isn't supported for now. For this reason, you will create the database schema with raw SQL.

The example consists of two parts:

• tests/prisma.test.ts: Jest test (in TypeScript) with a variety of Prisma Client queries and assertions to showcase access patterns
• src/script.ts: Node.js script with queries similar to the ones in the test.

Prerequisites

• Node.js installed.
• Docker installed

Note: You can also connect to a free CockroachDB Serverless Cluster.

1. Download example & install dependencies

Download this example:

curl https://codeload.github.com/prisma/prisma-examples/tar.gz/latest | tar -xz --strip=2 prisma-examples-latest/databases/cockroachdb

Install npm dependencies:

cd cockroachdb
npm install

Alternative:Clone this repository

Clone this repository:

git clone git@github.com:prisma/prisma-examples.git --depth=1

Install npm dependencies:

cd prisma-examples/databases/cockroachdb
npm install

2. Start a CockroachDB database server

There are two approaches to setting up a CockroachDB database:

1. Locally with Docker using the included docker-compose.yml file.
2. Using a free hosted CockroachDB Serverless.

(Option 1) Start CockroachDB with Docker

Run the following command from the cockroachdb folder to start a CockroachDB Docker container:

docker compose up -d

(Option 2) Using CockroachDB Serverless

Follow the following guide to create a free CockroachDB Serverless cluster.

3. Configure the database connection URL

Prisma uses the DATABASE_URL environment variable in .env in the cockroachdb folder to connect to the database.

Create the file:

touch .env

Then add the following line:

DATABASE_URL="postgresql://root@localhost:26257/prisma?sslmode=disable"

Note: If you're using CockroachDB Serverless, see .env.example for more information on how DATABASE_URL should look like with the cluster configruation.

4. Create the database schema in CockroachDB with Prisma Migrate

Now that you have defined the DATABASE_URL in .env, you will use Prisma Migrate to create a migration file with the SQL necessary to create the database schema.

Run the following command from the cockroachdb folder:

npx prisma migrate dev --name init

You should see the following output:

Your database is now in sync with your schema.

Note: The prisma migrate dev command will automatically generate Prisma Client for use in script.ts.

5. Run the tests and script

To run the test in tests/prisma.test.ts, run the following command:

npm run test

To run the script src/script.ts, run the following command:

npm run start

As a next step, explore the script.ts file to see how to use Prisma Client to read and write data in the database.

Resources

• Check out the Prisma docs
• Join our community on Discord to share feedback and interact with other users.
• Subscribe to our YouTube channel for live demos and video tutorials.
• Follow us on X for the latest updates.
• Report issues or ask questions on GitHub.