DEV Community: sue enuga

Migrate Your Schema into CockroachDB Cloud with Prisma Migrate

sue enuga — Fri, 28 Jul 2023 20:58:54 +0000

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.

Migrate your Schema into CRDB

Migrate your Data

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

CockroachDB cloud cluster (Dedicated or Serverless)

Node.js V16 or higher

Prisma Project Setup

Create a project Directory and navigate to the path.

mkdir hello-prisma
cd hello-prisma

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

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.

CockroachDB integration with Kafka Connectors

sue enuga — Fri, 28 Jul 2023 20:19:37 +0000

CockroachDB and Kafka Connectors

In this article I want to go over the steps on how to integrate cockroachDB with Kafka Connectors to import data into Kafka topics. CockroachDB has an inbuilt feature CDC/Changefeeds to push data to any sink that you can talk natively without having to integrate with a third party connector.CDC is a robust and mature feature of cockroachDB and it will let you stream data in a distributed architecture more efficiently.For instance, you can stream data changes to Kafka by just enabling rangefeeds at the row level and get the batched changes streamed to the kafka broker without another hop. For more information on how to do this please read more on our official docs. CDC

Disclaimer

Although the official docs say that CRDB is not supported, I was able to connect and stream changes. So, please test throughly when using this feature.Limitations

Kafka Connectors

If you don't already know what a kafka connector is, I took this official explanation out of their docs.

Kafka connectors are ready-to-use components, which can help us to import data from external systems into Kafka topics and export data from Kafka topics into external systems.

So, as we all know, Apache Kafka is a framework for event streaming. Events are contained in streams that can be produced, consumed, or processed. So, connectors acts as a bridge and ensure compatibility of event-streaming and non-event streaming pieces of technology. Data can be easily streamed into Kafka from a variety of sources and out of Kafka to a variety of targets thanks to Connectors.

So, today lets look at how to integrate CRDB with Kafka's PostgreSQL source connector.

PostgreSQL source connector

The Kafka Connect PostgreSQL Source connector for Confluent Cloud can obtain a snapshot of the existing data in a compatible PostgreSQL database and then monitor and record all subsequent row-level changes to that data. The connector supports Avro, JSON Schema, Protobuf, or JSON (schemaless) output data formats. All of the events for each table are recorded in a separate Apache Kafka® topic. The events can then be easily consumed by applications and services.

Note that deleted records are not captured and the changes are being pulled from the DB.
Please refer to their official docs for more information.

Integrating PostgreSQL source Connector with CRDB dedicated.

Pre-requisites:

Working CockroachDB connection with DB console access
Working confluent cloud console access >Note: you can always sign up for trial clusters for both of the above if you'd like to test any feature.

As a First step, get all the connection information from your cockroach DB console.

Goto your cluster, click on connect and get the connection info similar to the screenshot here below and save it. You'll also need the SSL cert to upload into the confluent connector authentication. So, on the same pop up page you should see an option to download the ca cert information from the command line section. This is your SSL cert.

Now, Go to confluent cloud and create a cluster by clicking on the + Add a cluster option.

You should have a cluster similar to above once you finish provisioning the cluster.

Click on the connectors from the cluster overview page to create a connector.

On the connector marketplace, search for postgresql and you should see the option to integrate the cluster with it, Continue selecting the topic prefix and provide kafka Credentials, You can either create new credentials or provide existing kafka API key & Secret.

Now, you should see the Authentication pane which should ask you for the postgres DB information, in our case CRDB's connection info that we captured from the earlier step. See below information for reference. Note, for SSL cert, you can make a copy of the cert to any folder on your page and upload it here.

Pick the configuration that you'd like for the messages and the table information for this connector

Additionally, you can make advanced changes to the connector and also apply any transformations for the messages. Once you click on finish your connector should be connected and start streaming the changes.

Please refer to the above image for the final overview of a created connector. For quick review of the messages that are being streamed from CRDB to Kakfa, you can go to the topic that was created and select messages tab. you should be able to see all the current messages that are being streamed like in the below screenshot.

Thanks for going through the article.

CockroachDB Integration with Superset

sue enuga — Wed, 26 Apr 2023 21:13:24 +0000

What is Superset ??

Superset is a fast, lightweight modern web application that is loaded with options that make it easy for users of all skill sets to explore and visualize their data, from simple pie charts to highly detailed deck.gl geospatial charts. so, in simple words its used for analyzing your data.

Why Integrate it with CockroachDB ??

CockraochDB is a highly available & reliable database which makes consistency as a priority and is best suited for all your OLTP needs.However, to analyze this data you can use any intelligent visualization tools. One of such easy lightweight tool is Apache Superset. Together with Superset & CockroachDB now you have a Datastore with analytical capabilities.

Installing Superset with docker

Pre-requisite:Docker
There seems to be multiple ways to install superset. I wanted to try out the installation using Docker as its the most recommended approach. You can follow the Installation instructions from their docs.
See below images for reference.

Note: superset is not officially supported in Windows as of 04/23, So, if you need to test this out, quickly spin up a linux/mac instance on hyperV or something similar.

Integrating Superset with cockroachDB

Pre-requisite: Working instance of CockroachDB and its connection parameters.
CockroachDB has multiple offerings & you can use any of these to integrate with superset. I used the serverless instance of CRDB here for the exercise.
For folks who are familiar with Cockroach workload, I loaded my serverless instance with MOVR workload
According to Superset documentation to integrate Superset with cockroachDB, Superset requires a python DB-API database driver and a SQL Alchemy dialect to be installed for cockroachDB. Now, lets see how to install the driver.

Installing the database driver to support cockroachDB

1.Create requirements-local.txt

# From the repo root...
touch ./docker/requirements-local.txt

2.Add the cockroachdb driver

echo "cockroachdb" >> ./docker/requirements-local.txt

3.Rebuild your local docker image with new driver

docker-compose build --force-rm

4.Start Superset via Docker Compose

docker-compose -f docker-compose-non-dev.yml pull
docker-compose -f docker-compose-non-dev.yml up

Now, superset should have all the necessary packages installed for connecting with CockroachDB.

Adding Cockroach DB as a data store in Superset

1.Go to Superset UI at localhost:8088 and login.
2.On the right top corner, next to settings you'll see a + icon. click on it & select Data and navigate to connect database. See below image for reference.

In the window you'll be prompted to Select a database to connect and here you'll need to go to the Supported Databases and choose cockroachDB. See below image

In second screen after selecting cockroachDB, you need to provide the connection details. Enter the primary Credentials, select Basic and enter the SQLAlCHEMY URI which should be formatted specifically for cockroach DB. See below on how to format it.

cockroachdb://root:pass@{hostname}:{port}/{database}?sslmode=verify-full

example: cockroachdb://sue:samplepassword@sue-sampletest-host:26257/movr?sslmode=verify-full

As I'm using a secure serverless cluster,I'm required to provide my root.crt in the Security section, under Root Certificate. So, I copied the root cert which is by default located in the $HOME/.postgresql/root.crt.

4.Once the connection is successful, you'll see CockroachDB in the Superset Databases

Creating Dashboards in Superset

Creating Dashboards in Superset is super simple, you can follow their docs for creating your first dashboard.

I created a bunch of dashboards for my dataset, see below image for reference.

Yay !! There you go, you have now successfully integrated cockroachDB with Superset and created some dashboards as well.

Online Schema Changes with CRDB

sue enuga — Mon, 24 Apr 2023 08:51:19 +0000

What exactly is the Online Schema Change Problem ?

Traditionally Changing a table's schema on the go has been one of the most challenging problems in relational databases, In today's fast paced applications & agile dev cycles, Database crew often find that they need to make changes to the schema frequently & sometimes on a weekly basis. Running an Alter Table or any DDL statement will put the table in an inaccessible state, including reads. This is the kind of downtime which most applications cannot simply afford to have. That drives the need for a no downtime Online schema solution.

CockroachDB's Online Schema Change Overview

CockroachDB was actually architected from ground-up to be highly available & Resilient for all OLTP needs and while we had the chance to address this Online Schema changes Problem, we agreed that we want to provide a simpler way to update the schema using any DDL statements and make sure the system doesn't suffer any negative consequences during this change. The schema change engine is a built-in feature requiring no additional tools, resources, or ad-hoc sequencing of operations.
For instance, when you run Alter on a table in CRDB, a background job gets kicked off in the background and CRDB engine seamlessly takes care of converting the old schema with the new changes without holding locks on the underlying table data.
So basically, Your application's queries can run normally without affecting any read/write latency and the data is in a consistent state throughout the entire schema change process allowing you to access the old data until the new changes are rolled out.
For detailed Information of how this is done internally you can checkout our
Official documentation

Testing out Online Schema Changes with CRDB's Dedicated cloud

Now, lets see how cockroach DB can handle online schema changes in reality. For the exercise here, the goal is to make changes to the schema in an active system and see if CRDB engine can handle the changes gracefully with no downtime.

Pre-work before the exercise

Created a Dedicated Cluster in CRDB with the below configuration

Created a new user management schema with some relational tables like User, Appointments, Locations etc and loaded the tables with at-least 1M records per table.
We inserted data using the patterns adopted from the reference blog by my co-author @jhatcher9999 :
To simulate the db to behave as an active system with constant reads/writes, we used CRDB's open source tool Workload tool- QueryBench(tailored to this schema).

In the above Database Console, you can notice the QPS is about 10K & the system is now active by accepting both reads/writes through the workload tool, Query Bench.

After the pre-work is completed, You can use one of many ways to connect to the clusters' sql shell to test the different scenarios for Altering the schema.

Alter Table to Add a New Column
For the first scenario, let's add a column to an existing table 'users' and see how the db handles it.

ALTER TABLE USERS ADD COLUMN user_marital_status CHAR(1) NULL;

Observations:The total time for executing the alter was around 120ms and as soon as the alter command was issued, a background job was created under the jobs section in the console & you could monitor the status of this DDL statement from here.

Alter Table to Add a New Column with Backfill
For this second scenario, we are adding a column to users table with a backfill.

ALTER TABLE USERS ADD COLUMN user_subscribed_status CHAR(1) NOT NULL DEFAULT 'y';

Observations: Users table has 6M records and Adding a column with backfill to this table took a little longer than the previous scenario & I noticed that the background job was created for this statement as well and I could monitor/manage the status through the console. Also, the active reads/writes to the system were never affected during this change as you can see in the image below from the DB console.

Alter the Table's Primary Key
For this scenario, We'll be testing how the system reacts to changing a primary key for the table while the system is actively taking requests for reads/writes.

ALTER TABLE appointments ALTER PRIMARY KEY USING COLUMNS (id, userid);

Observations: Altering the primary key was almost instantaneous & seamless. Total time of execution was under 120ms.There was a background job which took care of the changes under the covers & none of the reads/writes were affected while doing so.

Drop the newly added columns in the table
This scenario is just to test dropping any unused columns form the table. In our case, we wanted to drop the two columns which we just added, one without a backfill & the other with a backfill.

ALTER TABLE USERS DROP COLUMN user_marital_status;
ALTER TABLE USERS DROP COLUMN user_subscribed_status;

Observations: We ran both the commands back to back & the system did create a job on the background to monitor the statement and did not notice any errors while the columns were being dropped and the requested were still served without any issues by the DB.

Revert the primary key to original
For the final scenario, we just reverted the primary key to go back to using just the Id for the appointments table.

ALTER TABLE appointments ALTER PRIMARY KEY USING COLUMNS (id);

Observations: After running the Alter, I immediately noticed that a background job was created for processing the changes & the sql shell returned as soon the changes were completed. There were no errors in serving the reads/writes for this scenario as well.

Conclusion

Overall, we ran the above scenarios and the database handled them exceptionally well. We did not run into any errors or retries for the reads/writes from the workload, so the system was continuously able to handle requests during the schema changes. Also, these jobs that were being created for these DDL statements in the background can be both monitored & managed so that you can pause/resume them on the go.
you can refer to the below screenshots for total list of statements with their execution timings and notice zero sql errors during execution. so, that's how the online schema change problem was resolved for good by CockroachDB & the entire DB kingdom lived happily ever after.

Special Thanks to my friend @jhatcher9999 for collaborating on this article with me and making this exercise more fun & exciting.