Migrate Your Schema into CockroachDB Cloud with Prisma Migrate
Tags: prisma, cockroachdb, migration
Published: 2023-07-28 20:58:54

#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](https://www.cockroachlabs.com/docs/stable/migration-overview.html)

##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](https://www.prisma.io/docs/concepts/components/prisma-migrate)

##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
```
2. 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

```
3. 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.

![envfile](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r3nbxechwviccg1x1z2l.png)

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

![migrate](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kjohzg2xulgejzu67e9h.png)

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.

![folder](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qd7uszeig4lzprcgggn9.png)


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.