PlanetScale: The MySQL serverless database

PlanetScale is a Serverless database for MySQL that brings a new way to manage your database from development to production. It provides the concept of branching, which is popular among Git platform providers.

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

PlanetScale Features

Apart from being a serverless database, here are the 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.
• Backup and Restore: It allows creating, scheduling, and restoring backups for production and development database branches.
• Access Control: within an organization, you can create roles and permissions and then assign them to users to restrict access to only necessary data or actions.
• Audit log: it allows you to view all the actions and events in your organization.
• 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 queries in your application.
• Query boost: improves the performance of queries by caching them, still in limited beta.
• Multi regions deployment: deploy a database in multiple regions; it allows you to reduce latency between your database and the backend application.
• Great Developer Experience: You can manage everything from a Web console or a CLI. The documentation is excellent, with many tutorials in various languages and technologies.

Create an account

PlanetScale offers a generous plan to build your web application with a serverless MySQL database. If you want to see others' pricing options, check them out.

Go to the signup page; you will have the page below:

Signup with the method you prefer. I use GitHub authentication; you will be asked to confirm your email when using the email method. Once done, you will reach a dashboard page below:

A button is at the top right of the page to create a database. Indeed, you can do everything through the Web UI, from the database creation to the monitoring in production.

PlanetScale also has a CLI, which is great when you don't have to go to Web UI to perform a task or want to automate some actions. This can create friction which impacts developer velocity. Throughout this tutorial, we will use the CLI to discover PlanetScale features.

Install the PlanetScale CLI

The CLI is currently supported on Windows, Linux, and MacOS. Check out this page to see the installation instructions. Here are the commands 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 branch to connect our application deployed in production.
• No direct update: The database can no longer be updated directly, but only by creating a deploy request. This prevents accidentally changing the database schema with an inconsistent one and crashes the application.
• Source of truth: When merging a branch created from this branch, PlanetScale, will ensure that new changes are consistent. 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

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

It is the end of this tutorial where we explored the branching workflow provided by PlanetScale to manage change 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:

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.