Migration to CockroachDB

Great!! You are either in the process of realizing that CRDB is the best distributed relational database out there or you've already figured that out and So, you are in the process of Migrating to CRDB.

There are three steps into migrating to CRDB.

1. Migrate your Schema into CRDB
2. Migrate your Data
3. Switch your application

In this article, I would like to focus entirely on how you can migrate your schema into CRDB using Prisma Migrate. If you would like to understand more about Migration, our docs are an excellent resource, Please follow along the Official CRDB Migration Documentation

Migrating Schema into CockroachDB

Migrating your schema into CRDB can be done in multiple ways.
The first option is to use a Schema Conversion tool, You can use this option when you have a .sql dump file of your schema and you'll be able to upload this file into your cloud console Migrations tab and its an interactive walk through of the process where you can fix any errors that you might run into and also retry the migration after.

An alternative to this approach is to use Prisma Migrate.

What is Prisma Migrate?

Prisma Migrate is a new feature that was released recently which enables you to migrate your schema to the database seamlessly.It also keeps your database schema in sync with your Prisma schema as it evolves by maintaining existing schema changes in your database. Prisma Migrate generates a history of .sql migration files and tracks the migration state.The workflow when using Prisma Migrate in your project would be iterative and look something like this:

Local development environment
Evolve your Prisma schema
Use either prisma migrate dev or prisma db push to sync your Prisma schema with the database schema of your local development database

Preview/ staging environment
Push your changes to the feature pull request
Use a CI system (e.g. GitHub Actions) to sync your Prisma schema and migration history with your preview database using prisma migrate deploy

Production
Merge your application code from the feature branch to your main branch
Use a CI system (e.g. GitHub Actions) to sync your Prisma schema and migration history with your production database using prisma migrate deploy

For Further information on this, Please checkout the Official Prisma Documentation

Migrating using Prisma Migrate

Pre-requisites

1. CockroachDB cloud cluster (Dedicated or Serverless)
2. Node.js V16 or higher

Prisma Project Setup

1. Create a project Directory and navigate to the path.

mkdir hello-prisma
cd hello-prisma

1. Initialize a Typescript project and add the Primsa CLI as a dependency to that, this should also create package.json file with an initial setup ready for your Typescript app

npm init -y
npm install prisma typescript ts-node @types/node --save-dev

1. Now initialize Typescript and Prisma cli.

npx tsc --init
npx prisma init

As part of the above initializing, a new directory called Prisma that contains a file called schema.prisma which basically has all your schema models & database connection information in it. It should also create a .env file in the root of the project directory.

Connecting your DB

In the schema.prisma file under the Prisma directory, you should find datasource block. See below code sample for reference. Here, you can find the information like provider and url.

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

See below image for reference of the URL and how it should look in your .env file.

You can also look at Prisma's official docs for more information on how to connect to other databases.
Once the connection has been made, you'll now be able to migrate the schema directly with cockroachDB without any other tools.

Migration using Prisma Migrate

schema.prisma file that was created under the prisma folder should have all the changes you want to make in your new database.To map your data model to the database schema, you need to first initialize the prisma migrate on the environment and in our case, dev.

npx prisma migrate dev --name init

This command does two things:
It creates a new SQL migration file for this migration
It runs the SQL migration file against the database.

Please see below image for reference

Any changes that will be made here on from the file will be considered and a new .migration file will be created under the migrations folder.

In Conclusion, we have several tools to migrate your schema to cockroachDB and my fav is MOLT. But,if you are a prisma native ORM shop and you'd like to leverage Primsa migrate to manage and apply your schema changes to the database, this is effective and is very simple and straightforward to use.