PlanetScale: The performant MySQL database platform

PlanetScale is a database platform for MySQL that brings an interesting way to manage your database from local development to production. By using the concept of branching, which is inspired by the version control tool Git.

You can manage the database schema evolution like you do with your application's source code.

PlanetScale Features

PlanetScale provides a set of features to give a performant managed MySQL database to users and enhance the developer experience of updating the database schema. Here are some interesting features of PlanetScale

• Database branching: allows you to manage your database changes the way you do with your code using a Git provider like GitHub. You create a new branch for each database change to merge with the main branch.
• Non-blocking schema changes: allow users to update database tables without locking or causing downtime for the production database.
• Database Replication: It allows the creation of database replicas in read-only mode to support globally distributed applications, which makes your database highly available.
• Multi regions deployment: deploy a database in multiple regions in read-only mode; it allows you to reduce latency between your database and the backend application.
• Backup and Restore: It allows creating, scheduling, and restoring backups for production and development database branches in case of data loss.
• Authentication Security: you can use multi-factor authentication and Single Sign-On (SSO) to enforce your organization's security.
• Query insights: this allows you to find and optimize long-running database queries in your application.
• Query boost: improves the performance of queries by caching them, but it is still in limited beta.
• Great Developer Experience: You can manage everything in the web console or using the CLI. The documentation is excellent, with many tutorials in various languages and technologies.

Create an account

PlanetScale doesn't provide a free tier, but the Pro plan gives you a 10$ credit to try it; it will be enough to follow this post.

To create a PlatnetScale account, go to the Pricing page, and click on "Get Started"

You will be redirected to the registration page, where you can create an account through email or with GitHub. We will use the email method, so fill in an email and password, then click on "Sign up"

You will receive an email to confirm your account; proceed to confirmation, and once done, you will see the PlanetScale welcome page where you can create or import a database.

Click on the button to create a database. You are redirected to a page to configure your database.

1. Enter the database name.
2. Select a region to deploy your database.
3. Select the database cluster size; you can keep the preselected one.
4. Check the box to get the 10$ usage credit so you can test it.

Scroll down and add a credit card; this is required to prevent abusive usage of the platform.

Once the card information is added, click the button to create the database.

On the next page, you can see the notice about the database being initialized at the bottom right. On the page, click the button to create the database password.

This action will output the database username and the password; copy and save them somewhere so as not to lose them, as they will not be displayed anymore.

Next, you are asked to select your framework; you can ignore it.

At the bottom page, click on the button to go to your dashboard. You can see your newly created database.

Install the PlanetScale CLI

PlanetScale also has a CLI to perform tasks from your terminal or automate some actions. For developers, using the CLI is the way to go to avoid friction from switching between your development environment and the web console.

Throughout this tutorial, we will use the CLI to discover PlanetScale features.

The PlanetScale CLI is currently supported on Windows, Linux, and MacOS. Check out this page to see the installation instructions. Run the command below to install it on MacOS

brew install planetscale/tap/pscale

# required to run certain commands with the PlanetScale CLI
brew install mysql-client

To upgrade to the new version, just run the command: brew upgrade pscale

Verify the CLI is installed successfully by running: pscale --version

The use case

We will build a light authentication system with only a table to store users.

Let's see how to use the branching workflow provided by PlanetScale to create this database.

Authenticate from the CLI

We created an account through the web application with an email address and a password, and now we want to perform actions on behalf of this account through the CLI. We need to log in from the CLI first.

Open a terminal and run the command below:

pscale auth login

This command will open the browser and follow the instructions.

To authenticate from the CLI, you must confirm that the confirmation code generated in the Terminal is the same web page.

To log out, run the command pscale auth logout.

Create the database

We are now connected to the database; let's create our database with the name "auth-db".

pscale database create auth-db

To view the list of your database, run.

pscale database list

Promote the main branch to production

By default, the database is created with a default branch called "main"; to view the list of database branches, run the following command:

pscale branch list auth-db

We get the following output:

As we can see, the column "Production" value is set to "No", meaning it is not the production database. We need to change it to "Yes" with the command below:

pscale branch promote auth-db main

We get the following output:

Why promoting the main branch to production:

By doing this:

• Production database: We indicate it is the database branch our application deployed in production connects to.
• No direct update: The database can no longer be updated directly; it can only be done by creating a deploy request. This prevents accidentally changing the database schema with an inconsistent one and crashes the application.
• Source of truth: PlanetScale will ensure that new changes are consistent when merging a branch created from this branch. Otherwise, the branch will be seen as conflicted.

Create the table users

To create this table, let's create a new branch from the main; we will call it create-users-table.

pscale branch create auth-db create-users-table

Update database schema with PlanetScale Shell

When the branch is created, we need to write and execute the SQL code to create the table, and to do that, PlanetScale CLI makes it possible by opening a secure MySQL shell instance to our database's branch. Run the command below:

pscale shell auth-db create-users-table

Now we are connected, we need to execute the code below to create the database:

CREATE TABLE `users` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `full_name` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `password` VARCHAR(100) NOT NULL,
    `role` VARCHAR(191) NOT NULL,
    `enabled` BOOLEAN NOT NULL DEFAULT false,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `users_email_key`(`email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

You can use an ORM such as Prisma or TypeORM to generate the code above from the entity definition as code.

Using Prisma ORM to build a Node.js application with MySQL

In this tutorial, we will see how to use Prisma with MySQL by defining our database structure, generating migrations, and performing CRUD operations.

Teco TutorialsEric Cabrel TIOGO

Copy the code above, paste it into your Terminal, then press ENTER to execute it.

Use the MYSQL command show tables to verify the table has been created successfully.

Create a deployment request

Now, we need to apply the change done on the branch create-users-table to the main branch. We need to create a deployment request; remember, the main branch can't be updated directly (connect to shell and execute the script)

Run the command below to create a deploy request:

pscale deploy-request create auth-db create-users-table

This step can be viewed as the pull request when working with GitHub. At this step, a teammate must review the database schema, approve if it looks good, or comment to make a suggestion/ask for clarity.

Review the deployment request

This step should be done by someone who didn't write the SQL script. So when the creator of the deployment request notifies you, you need to list the deployment requests first.

Run this command:

deploy-request list auth-db

We got the following output:

The column to pay attention to is "NUMBER" because we need it to view or update the deployment request. From the picture above, our deploy request number is 1.

To view the change made on the deploy request, run the command:

pscale deploy-request diff auth-db 1

We got the following output:

Reviewing the deployment request consists of adding a comment to the request or approving it; they aren't mutually exclusive.

There is no index on the columns role and enabled we could add a comment to notify the developer he forgot to add that. Here is an example of commenting on a deployment request:

pscale deploy-request review auth-db 1 --comment You forgot to add indexes on role and enabled. I will approve but create a new branch to fix it

Here is the output:

Approve and merge to the main branch

To approve the deploy request, run the command:

pscale deploy-request review auth-db 1 --approve

Once the deploy request is approved, we can merge it to the main branch. Note that the merge only copies the structure and not the data, so don't expect to see the data you have inserted through Shell in the main branch.

Run the command below to deploy in the main branch:

pscale deploy-request deploy auth-db 1

We got the following output:

The deployment to the production branch can take some time, depending on how many deployments are in the queue. Think of multiple teams working with the same database and other teams creating a deploy request before you.

Delete the branch

Once the deployment to the main branch is done, we no longer need the branch create-users-table so will delete it:

pscale branch delete auth-db create-users-table

Display the branch list to see if it has been deleted.

Add forgotten indexes

Since we know how PlanetScale will work, we will quickly review everything we did.

• Create the new branch: pscale branch create auth-db add-forgotten-indexes
• Connect to the shell and execute the script: pscale shell auth-db add-forgotten-indexes
• Execute the script below:

CREATE INDEX `users_role_idx` ON `users`(`role`);
CREATE INDEX `users_enabled_idx` ON `users`(`enabled`);

• Create a deployment request: pscale deploy-request create auth-db add-forgotten-indexes
• Verify the changes: pscale deploy-request diff auth-db 2 (2 is the number of the deploy-request)

• Approve the deployment request: pscale deploy-request review auth-db 2 --approve
• Deploy the request: pscale deploy-request deploy auth-db 2

We are all set!

Others CLI command

We have seen some useful commands of the PlanetScale CLI to show the branching workflow, but we didn't cover everything. To view other available commands, you can type pscale --help

Connect from a database GUI tool

We saw how to connect through the shell using the CLI, but how about GUI database tools?
To connect to the database using a GUI tool, go to the web UI to copy the connection string.

• On the dashboard page, click on the database you want to get the credentials
• On the next page, do the following steps:

• Once the password is generated, you will get the page with the credentials you need; copy them and fill in the inputs of the GUI tool.

Conclusion

At the end of this tutorial, we explored the branching workflow provided by PlanetScale to manage changes in our database. To learn more about it, check out their well-detailed documentation.

PlanetScale Documentation

Learn how to use PlanetScale to power your application

You are ready to build a backend application that uses PlanetScale for the database. I wrote a whole post to show you how:

Create a Node.js REST API using Prisma ORM and PlanetScale

This post shows you how to build a Node.js REST API using Prisma, an ORM allowing you to interact with a MySQL database managed by PlanetSCale

Teco TutorialsEric Cabrel TIOGO

Using PlanetScale and TypeORM to build a Node.js REST API

This tutorial will show how to use PlanetScale, the Serverless database, and TypeORM to build a REST API in Node.js and Typescript.

Teco TutorialsEric Cabrel TIOGO

Follow me on Twitter or subscribe to my newsletter to avoid missing the upcoming posts and the tips and tricks I occasionally share.