Logo

dev-resources.site

for different kinds of informations.

Migrate Your Schema into CockroachDB Cloud with Prisma Migrate

Published at
7/28/2023
Categories
prisma
cockroachdb
migration
Author
sueenuga
Categories
3 categories in total
prisma
open
cockroachdb
open
migration
open
Author
8 person written this
sueenuga
open
Migrate Your Schema into CockroachDB Cloud with Prisma Migrate

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
Enter fullscreen mode Exit fullscreen mode
  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

Enter fullscreen mode Exit fullscreen mode
  1. Now initialize Typescript and Prisma cli.
npx tsc --init
npx prisma init
Enter fullscreen mode Exit fullscreen mode

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")
}
Enter fullscreen mode Exit fullscreen mode

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

envfile

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
Enter fullscreen mode Exit fullscreen mode

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

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

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.

cockroachdb Article's
30 articles in total
Favicon
CockroachDB: fast-start configuration on a fresh cluster
Favicon
CockroachDB on OpenShift: Separate your logs from data!
Favicon
Building an API with Go, PostgreSQL, Google Cloud and CockroachDB
Favicon
From CockroachDB to AWS SNS via AWS API Gateway
Favicon
Generate multiple, large, sorted CSV files with pseudo-random data
Favicon
From Scratch to Storage, Note App with AnalogJs, tRPC, Prisma and CockroachDB
Favicon
Building a REST API using NodeJS, ExpressJS, and CockroachDB
Favicon
Migrate Your Schema into CockroachDB Cloud with Prisma Migrate
Favicon
CockroachDB SSO login to the SQL prompt via JWT
Favicon
CockroachDB: Multi-Region OpenShift using Azure Virtual WAN
Favicon
Repaving CockroachDB in AWS EC2
Favicon
How to Build a Server with Hanami and CockroachDB | Ruby
Favicon
Experiment workload performance impact by number of Connections
Favicon
CockroachDB Integration with Superset
Favicon
Online Schema Changes with CRDB
Favicon
CockroachDB: row-level TTL to simulate Redis
Favicon
Jim's Guide to CockroachDB Naming Standards
Favicon
Memory Management in CockroachDB
Favicon
Efficiently deleting data
Favicon
Kafka 2 CockroachDB via JDBC Sink Connector Blueprint
Favicon
Running Multi-region CockroachDB on k8s -- the internals
Favicon
Running CockroachDB on k8s - with tweaks for Production
Favicon
Spark Update Optimizations
Favicon
A Tale of Two Connection Pools
Favicon
Repaving CockroachDB cluster node VMs the easy way
Favicon
CockroachDB: trace logging with Datadog
Favicon
Ingesting data from Kafka to CockroachDB via Kafka Connect
Favicon
Display CockroachDB metrics in Splunk Dashboards
Favicon
Build a CockroachDB Control Plane using Ansible Tower
Favicon
CockroachDB vs PostgreSQL

Featured ones: